SQL Performance Tuning with reference to Oracle
|Developed by Hardeep Singh|
|Copyright||© Hardeep Singh, 2009|
|Released under Creative Commons Attribution Non-commercial No Derivative Works license.|
|The code may not be used commercially without permission.|
|The code does not come with any warranties, explicit or implied.|
|The code cannot be distributed without this header.|
Problem: We are required to tune a SQL query written for Oracle.
CREATE INDEX book_author_index
ON book (book_id, author_id);
(Using SQL Plus)
set autotrace on;
To see the plan without executing the query:
set autotrace traceonly explain;
(Using SQL Developer)
On the query press F6.
Explain plan interpretation:
'Table access full' means that the complete table needs to be searched in order to find the information. ‘Index range scan’ means that index will be used, however the filters provided in the WHERE clause may not be ‘complete’.
'Index full scan' means the entire index will be searched.
'Table access by index rowid' means the rows are being located using an index.
Hash, Nested loops and Merge are all different types of joins.
These are the most commonly seen operations in an explain plan.
This is how you can generate statistics on a table called BOOK owned by SYSADM:
EXEC DBMS_STATS.gather_table_stats('SYSADM', 'BOOK');
This is how the leading hint is applied:
SELECT /*+ LEADING(b) */ author_name
FROM author a, book b
WHERE a.author_id=b.author_id AND