Brief Introduction to Oracle SQL Optimization

Oracle is still quite common, so I have studied Oracle SQL optimization and I will share it with you here, hoping it will be useful to you. Oracle SQL optimization is a complex topic, and even requires the entire book to introduce the nuances of Oracle SQL optimization. However, there are some basic rules that every Oracle DBA must follow. These rules can improve the performance of their systems. The goal of SQL optimization is simple:

◆ Eliminate unnecessary full table search for large tables: Unnecessary full table search results in a large number of unnecessary I/O operations, which slows down the performance of the entire database. Tuning experts first evaluate the SQL statement based on the number of rows returned by the query. In an ordered table, if the query returns less than 40% rows, or in an unordered table, less than 7% rows are returned, then this query can be adjusted to use an index instead of the full table search. For unnecessary full table search, the most common optimization method is to increase the index. You can add standard B-tree indexes to a table, bitmap indexes, and function-based indexes. To determine whether to eliminate a full table search, you can carefully check the I/O overhead of index search and the overhead of full table search, their overhead is related to the reading of data blocks and the possible parallel execution, and the two are compared. In some cases, the elimination of unnecessary full table search can be achieved by forcing an index. You only need to add an index prompt to the SQL statement.

◆ When full table search is the fastest way to access a table, you can store full table search for small tables in the cache. tuning experts should ensure that a special data buffer is used as the row buffer. In Oracle7, you can use the alter table xxx cache statement. In Oracle8 or above, small tables can be forced to be buffered in the KEEP pool.

◆ Optimal Index usage: it is particularly important to improve the query speed. Sometimes Oracle can select multiple indexes for query. tuning experts must check each index and ensure that Oracle uses the correct index. It also includes the use of bitmap and function-based indexes.

◆ Optimal JOIN Operation: some queries use nested loop join faster, some are HASH join faster, and others are sort-merge join faster.

These rules seem simple, but they account for 90% of SQL tuning tasks, and they do not need to fully understand the internal operations of Oracle SQL. Here we will give a brief overview of Oracle SQL optimization.

1. Oracle Spatial Learning Experience
2. Master talk about Oracle Spatial
3. Comprehensive summary of Oracle Data Types
4. Quick introduction to Oracle SQL
5. Oracle Dual table