Spotlight on Oracle – SQL Tuning Features

Utilizing SQL tuning features

Each release of Oracle introduces new and improved SQL performance features. In particular the cost-based optimizer contains improvements in each release—many of which are undocumented. Some of the Oracle features that can help your SQL performance are:

• Hash joins. This join algorithm improves the performance of joins that previously used the sort-merge algorithm. It is  invoked automatically unless HASH_JOIN_ENABLED=FALSE.
• Anti-joins. The anti-join algorithm allows efficient execution of queries that use NOT IN sub-queries. These types of queries were typically performance problems in earlier versions of Oracle. You can invoke the anti-join with the MERGE_AJ or HASH_AJ hints (in the sub-query), or by setting ALWAYS_ANTI_JOIN=TRUE.
• Histograms. Histograms allow the cost-based optimizer to make more informed decisions regarding the distribution of data within a column. They are created using the FOR COLUMNS clause of the ANALYZE command.
• Partitioning. The partition view and the partitioned table allow subsets of large tables to be processed separately.
• Parallel DML. DML statements (UPDATE, INSERT, DELETE) can be processed using parallel processing. For DELETE and UPDATE operations, the table involved should be partitioned.
• Fast full index scan. Fast index scans using multi-block reads and parallel query processing if the index includes all the columns required to satisfy the query.