|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.