39. Always use the first column of the index
If the index is built in multiple columns, the optimizer selects the index only when its first column is referenced by the WHERE clause.
Translator presses:
This is also a simple and important rule. See the examples below.
SQL> Create Table Multiindexusage (INDA NUMBER, INDB NUMBER, DESCR VARCHAR2 (10));
Table created.
SQL> CREATE INDEX MULTINDEX ON MULTIINDEXUSAGE (INDA, INDB);
Index created.
SQL> Set Autotrace Traceonly
SQL> Select * from multiindexusage where index = 1;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (By Index Rowid) of 'MultiIndexusage'
2 1 Index (Range Scan) of 'Multindex' (non-unique)
SQL> Select * from multiindexusage where indexusage where indB = 1;
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 Table Access (Full) of 'MultiIndexusage'
Obviously, when references the second column of the index, the optimizer uses a full table scan and ignores the index.
40. Oracle internal operation
When the query is executed, Oracle uses internal operations. The following table shows several important internal operations.
Oracle Clause
Internal operation
ORDER BY
Sort ORDER BY
Union
Union-all
Minus
Minus
INTERSECT
INTERSECT
Distinct, Minus, INTERSECT, UNION
Sort unique
Min, max, count
Sort aggate
GROUP BY
Sort group by
Rownum
Count Or Count STOPKEY
Queries Involving Joins
Sort Join, Merge Join, NESTED LOOPS
CONNECT BY
CONNECT BY
41. Replace Union with union-all (if possible)
When the SQL statement needs UNION two query results collection, the two result collections are merged in the way Union-ALL and then sorted before the final result is output.
If you replace Union with UNION ALL, this sort is not necessary. Efficiency will be improved.
Example:
Inefficient:
SELECT ACCT_NUM, Balance_AMT
From debit_transactions
WHERE TRAN_DATE = '31 -Dec-95 '
Union
SELECT ACCT_NUM, Balance_AMT
From debit_transactions
WHERE TRAN_DATE = '31 -Dec-95 '
Efficient:
SELECT ACCT_NUM, Balance_AMT
From debit_transactions
WHERE TRAN_DATE = '31 -Dec-95 '
Union all
SELECT ACCT_NUM, Balance_amtfrom Debit_Transactions
WHERE TRAN_DATE = '31 -Dec-95 '
Translator presses:
It should be noted that UNION ALL will repeat the same record in the two result sets. So everyone is still
To analyze the feasibility of UNION ALL from business needs.
Union will sort the results collection, this operation uses the Sort_Area_Size this memory. For this
Optimization of block memory is also quite important. The following SQL can be used to query the sorted consumption
SELECT SUBSTR (Name, 1, 25) "Sort Area Name",
Substr (Value, 1, 15) "Value"
From v $ sysstat
WHERE Name Like 'Sort%'
42. Use Tips (HINTS)
For table access, you can use two hints.
Full and Rowid
Full Hint told Oracle to access the specified table using full mete-scan.
E.g:
SELECT / * FULL (EMP) * / *
From EMP
WHERE EMPNO = 7893;
Rowid Hint tells Oracle to access the table using the Table Access By RowID.
Typically, you need to use Table Access By Rowid, especially when accessing a big table, you need to know the value of ROIWD or use an index.
If a big table is not set to a cached table and you want its data to end in the end of the query, still stay
In SGA, you can use Cache Hint to tell the optimizer to keep the data in SGA. Usually Cache Hint and Full Hint work together.
E.g:
SELECT / * FULL (Worker) Cache (Worker) * / *
From Work;
Index Hint tells Oracle to use an index-based scan mode. You don't have to explain the specific index name
E.g:
SELECT / * INDEX (LODGING) * / LODGING
From lodging
WHERE manager = 'bill Gates';
In the case of hint without using Hint, the above query should also use the index, however, if the index is too much, your optimizer is CBO, the optimizer may ignore the index. In this case, you can Mandatory Oracle with Index Hint Use the index.
Oracle Hints also includes all_rows, first_rows, rule, us_nl, us_merge, us_hash, and so on.
Translator presses:
Using Hint, we are unsatisfactory to the Oracle Optimizer's default execution path, requires manual modification.
This is a very skillful job. I suggested that only Hint is optimized for specific, few SQL.
It is still confident to Oracle's optimizer (especially CBO)
