Tutorial by Examples

Statement-level parallel hints are the easiest: SELECT /*+ PARALLEL(8) */ first_name, last_name FROM employee emp; Object-level parallel hints give more control but are more prone to errors; developers often forget to use the alias instead of the object name, or they forget to include some objec...

Use Nested Loops. Usage : use_nl(A B) This hint will ask the engine to use nested loop method to join the tables A and B. That is row by row comparison. The hint does not force the order of the join, just asks for NL. SELECT /*+use_nl(e d)*/ * FROM Employees E JOIN Departments D on E.Department...

"Use DIRECT PATH method for inserting new rows". The APPEND hint instructs the engine to use direct path load. This means that the engine will not use a conventional insert using memory structures and standard locks, but will write directly to the tablespace the data. Always creates new b...

Instructs the engine to use hash method to join tables in the argument. Usage : use_hash(TableA [TableB] ... [TableN]) As explained in many places, "in a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It the...

The FULL hint tells Oracle to perform a full table scan on a specified table, no matter if an index can be used. create table fullTable(id) as select level from dual connect by level < 100000; create index idx on fullTable(id); With no hints, the index is used: select count(1) from fullTabl...

Oracle (11g and above) allows the SQL queries to be cached in the SGA and reused to improve performance. It queries the data from cache rather than database. Subsequent execution of same query is faster because now the data is being pulled from cache. SELECT /*+ result_cache */ number FROM main_tab...