Hypercharge SQL Performance with Function-based indexing

I am now one year into writing my next book "Oracle SQL Tuning: The Definitive Reference", and I had an epiphany about SQL tuning that all Oracle professionals should know.

The vast majority of SQL tuning can be avoided by following a few simple rules:

- Carefully formulate the SQL syntax to conform to standard practices
- Always use complete optimizer statistics (using dbms_stats)
- Make sure that all indexes exist, especially function-based indexes

The single most common mistake that I see on shops is SQL that performs poorly because the DBA or developer did not know how to create a function-based index.  A function-based index (FBI) is a Godsend because it allows you to match virtually any WHERE clause predicate with a matching index.

With an FBI in place, Oracle goes directly to the rows you need, minimizing database I/O and improving overall performance.  Better yet, adding missing function-based indexes servers as a silver bullet (an act that changes the entire workload landscape).  When you add an FBI, Oracle instantly recognized the new index and re-parsed all SQL that might benefit from it such that a single FBI might improve the performance of thousands of SQL statements.

Continue reading below to see why function-based indexes are so critical to SQL performance:


Ion for Oracle automates Oracle tuning!

Using the principles of expert systems technology, BC has developed an important new Oracle tuning tool, Ion for Oracle.  Ion quickly isolate the reports on the important optimization issues, separating the wheat from the chaff like no other performance tuning software.

After several years of hard work, Ion for Oracle is finally available for general release.  Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.

Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access.  To try Ion for free, see here:



Need a health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds

Just call 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:



There's Always Room at the Top

Burleson Consulting continues to grow as the Oracle community recognizes that high quality Oracle support and training is a cost effective option in these tough economic times.

If you have distinguished yourself in your Oracle career and are ready to step up and enjoy the rewards of joining the ranks of America's top tier support consultants we would like to hear from you.

Evidence of high achievement as an Oracle professional might include publication of books and articles for the Oracle community, achieving an advanced degree from a competitive university and seeking out hands-on experience with complex databases. See here for details and a full qualifications list: