Discovering SQL: A Hands-On Guide for Beginners

RDBMS-SPECIFIC OPTIMIZATION

Besides generic optimizations which by and large are applicable to every relational database system, there are quite a few vendor-specific optimizations which utilize concepts and facilities available only in this particular RDBMS. Of course, there is a price to pay in terms of reduced portability of the system, but sometimes the tradeoff might be justified.

Oracle 10g/11g

In version 10g, Oracle shipped with SQLAccess advisor (part of the DBMS_ADVISOR package, which was replaced with SQL Performance Analyzer in version 11g). The focus of the tool is to scan your database schema objects, and come up with recommendations for best indices and materialized views (refer to Chapter 8 for more information).

One of the most controversial issues is using Oracle SQL hints in your queries. A hint is not a suggestion for the Oracle optimizer to consider; it is a direct order to cease and desist, and just do as told. (I would add “if possible” to this sentence; there is a reason why it's called “hints” – not every hint is implementable.) There is a number of hints that can be used with Oracle that deal with JOIN orders and operations, access plans and query transformations, index utilization strategy, and more. Here is an example of Oracle forcing the query optimizer to use index IX_BK_ISBN created on bk_ISBN column:

SELECT /*+ index(bk ix_bk_ISBN) */ 
    bk_title
   ‚bk_publisher
FROMbooksbk;

As you can see, the syntax has nothing to do with SQL proper, has arcane syntax, ...