Further reading: SQL performance tuning

Watch the video tutorial here if you haven’t already.

Creating indexes:

CREATE INDEX book_author_index

ON book (book_id, author_id);

Generating plan:

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

Statistics:

This is how you can generate statistics on a table called BOOK owned by SYSADM:

EXEC DBMS_STATS.gather_table_stats('SYSADM', 'BOOK');

Hints:

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
reference_book=’Y’;

Further references:

Indexes:

http://lc.leidenuniv.nl/awcourse/oracle/server.920/a96533/ex_plan.htm

http://www.orafaq.com/node/1420

Statistics:

http://www.oradev.com/create_statistics.jsp

Hints:

http://www.adp-gmbh.ch/ora/sql/hints/index.html

http://tinyurl.com/5ck3qm