Optimizer Access Paths

reference : http://www.juliandyke.com/Optimisation/Operations/Operations.html

ACCESS / SCANS

Optimizer Access Paths
– Table
– B-Tree
– Bitmap
– Table Cluster

Joins
Join Methods
– Hash
– Nested
– Sort Merge
Join Type
– Inner
– Outer
– Semi
– Anti
– full table scan
– rowid scan
– index scan
– rowid scan
– index scan
unique scan
range scan
index skip scan
index (descending) range scan
full index scan
fast full index scan
– cluster scan
– hash scan

JOINS

SECTION 1
Understanding Access Paths for the Query Optimizer

Full Table Scans
Rowid Scans
Index Scans
Cluster Access
Hash Access
Sample Table Scans

SECTION 2
Understanding Joins

Nested Loop Joins
Hash Joins
Sort Merge Joins
Cartesian Joins
Outer Joins –

NESTED LOOPS join, the SORT-MERGE join, and the CLUSTER join. (from Oracle 6)
CARTESIAN join. (from Oracle 6)
HASH join (from Oracle 7.3)
INDEX join (from Oracle 8i)

ACCESS PATHS BY QUERY OPTIMIZER


<< FULL TABLE SCAN >>

hr@XE> select * from employees;
-- did not give results due to space --

107 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117


-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
9405 bytes sent via SQL*Net to client
457 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
107 rows processed

<< ROWID SCAN >>

hr@XE> select job_id, job_title from jobs
2 where rowid = 'AAAC9CAAEAAAABHAAR';

JOB_ID JOB_TITLE
---------- -----------------------------------
HR_REP Human Resources Representative

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3196446718
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| JOBS | 1 | 33 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
503 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select job_id, job_title, max_salary
2 from jobs
3 where job_id = 'MK_REP' and min_salary = 10500;

Execution Plan
----------------------------------------------------------
Plan hash value: 1302208962
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 33 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MIN_SALARY"=105000
2 - access("JOB_ID"='MK_REP')

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
394 bytes sent via SQL*Net to client
369 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


<< INDEX UNIQUE SCAN >>

SQL> select country_name from countries where country_id = 'AR';

Execution Plan
----------------------------------------------------------
Plan hash value: 3815972664
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| COUNTRY_C_ID_PK | 1 | 12 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COUNTRY_ID"='AR')

----------------------------------------------------------

<< INDEX RANGE SCAN >>

SQL> select country_name from countries where country_id in ('AS','AT','CA');

COUNTRY_NAME
----------------------------------------
Canada

Execution Plan
----------------------------------------------------------
Plan hash value: 4047571820
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 36 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
|* 2 | INDEX RANGE SCAN| COUNTRY_C_ID_PK | 3 | 36 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COUNTRY_ID"='AS' OR "COUNTRY_ID"='AT' OR "COUNTRY_ID"='CA')


<< INDEX FULL SCAN >>

SQL> select country_name,region_id from countries where country_id > 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1253225340
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 1 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

<< INDEX SCAN >> SQL>

select country_name from countries where country_id like '%A%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1253225340
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 1 | 12 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COUNTRY_ID" LIKE '%A%')
-------------------------------------------------------------

<< NESTED LOOP >>
SQL> select c.country_name,r.region_name from countries c,regions r
2 where c.region_id < 5
3 and c.country_name = 'Japan'
4 and c.region_id = r.region_id;

COUNTRY_NAME REGION_NAME
---------------------------------------- -------------------------
Japan Asia Execution Plan
----------------------------------------------------------
Plan hash value: 2128980668
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 1 | 12 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C"."COUNTRY_NAME"='Japan' AND "C"."REGION_ID"<5)
4 - access("C"."REGION_ID"="R"."REGION_ID") filter("R"."REGION_ID"<5)

<< CARTESIAN JOIN >>

SQL> select c.country_name,r.region_name from countries c,regions r; 

COUNTRY_NAME REGION_NAME 
---------------------------------------- ------------------------- 
Argentina Europe 
Australia Europe 
Belgium Europe 
Brazil Europe 
Canada Europe 
Switzerland Europe 
China Europe 
Germany Europe 
Denmark Europe 
Egypt Europe 
..
..
100 rows selected.
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 4015116663 
------------------------------------------------------------------------------------------ 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
------------------------------------------------------------------------------------------ 
| 0 | SELECT STATEMENT | | 100 | 2000 | 6 (0)| 00:00:01 | 
| 1 | MERGE JOIN CARTESIAN | | 100 | 2000 | 6 (0)| 00:00:01 | 
| 2 | TABLE ACCESS FULL | REGIONS | 4 | 44 | 3 (0)| 00:00:01 | 
| 3 | BUFFER SORT | | 25 | 225 | 3 (0)| 00:00:01 | 
| 4 | INDEX FAST FULL SCAN| COUNTRY_C_ID_PK | 25 | 225 | 1 (0)| 00:00:01 | 
------------------------------------------------------------------------------------------


<< NESTED LOOP OUTER JOIN >>

SQL> select c.country_name,r.region_name from countries c,regions r
2 where c.region_id < 5
3 and c.region_id = r.region_id(+);
COUNTRY_NAME REGION_NAME
---------------------------------------- -------------------------
Argentina Americas
Australia Asia
Belgium Europe
Brazil Americas
Canada Americas
Switzerland Europe
China Asia
Germany Europe
Denmark Europe
Egypt Middle East and Africa
France Europe
HongKong Asia
Israel Middle East and Africa
India Asia
Italy Europe
Japan Asia
Kuwait Middle East and Africa
Mexico Americas
Nigeria Middle East and Africa
Netherlands Europe
Singapore Asia
United Kingdom Europe
United States of America Americas
Zambia Middle East and Africa
Zimbabwe Middle East and Africa
25 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 132727052
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 650 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 25 | 650 | 2 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 300 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C"."REGION_ID"<5)
4 - access("C"."REGION_ID"="R"."REGION_ID"(+))
filter("R"."REGION_ID"(+)<5)


<< HASH LOOP OUTER JOIN >>

SQL> select c.country_name,r.region_name from countries c,regions r
2 where c.region_id < 5
3 and c.region_id = r.region_id(+)
4 group by c.country_name, r.region_name;

COUNTRY_NAME REGION_NAME
---------------------------------------- -------------------------
Australia Asia
Japan Asia
Singapore Asia
China Asia
HongKong Asia
Zimbabwe Middle East and Africa
Argentina Americas
Switzerland Europe
Denmark Europe
Egypt Middle East and Africa
Mexico Americas
Israel Middle East and Africa
Kuwait Middle East and Africa
Zambia Middle East and Africa
Brazil Americas
Canada Americas
Germany Europe
Italy Europe
Netherlands Europe
United States of America Americas
Belgium Europe
India Asia
France Europe
Nigeria Middle East and Africa
United Kingdom Europe

25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4041069854
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 650 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 25 | 650 | 3 (34)| 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 25 | 650 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 300 | 1 (0)| 00:00:01 |
|  4 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("C"."REGION_ID"<5)
5 - access("C"."REGION_ID"="R"."REGION_ID"(+))
filter("R"."REGION_ID"(+)<5)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
1147 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25 rows processed


<< FULL OUTER JOIN >>

SQL> select c.country_name,r.region_name
from countries c FULL OUTER JOIN regions r
ON c.region_id = r.region_id;

COUNTRY_NAME REGION_NAME
---------------------------------------- -------------------------
Argentina Americas
Australia Asia
Belgium Europe
Brazil Americas
Canada Americas
Switzerland Europe
China Asia
Germany Europe
Denmark Europe
Egypt Middle East and Africa
France Europe
HongKong Asia
Israel Middle East and Africa
India Asia
Italy Europe
Japan Asia
Kuwait Middle East and Africa
Mexico Americas
Nigeria Middle East and Africa
Netherlands Europe
Singapore Asia
United Kingdom Europe
United States of America Americas
Zambia Middle East and Africa
Zimbabwe Middle East and Africa

25 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2753070374
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 936 | 6 (17)| 00:00:01 |
| 1 | VIEW | | 26 | 936 | 6 (17)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS OUTER | | 25 | 700 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 350 | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 (0)| 00:00:01 |
| 7 | MERGE JOIN ANTI | | 1 | 17 | 4 (25)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| REGIONS | 4 | 56 | 2 (0)| 00:00:01 |
| 9 | INDEX FULL SCAN | REG_ID_PK | 4 | | 1 (0)| 00:00:01 |
|* 10 | SORT UNIQUE | | 25 | 75 | 2 (50)| 00:00:01 |
| 11 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 25 | 75 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("C"."REGION_ID"="R"."REGION_ID"(+))
10 - access("C"."REGION_ID"="R"."REGION_ID")
filter("C"."REGION_ID"="R"."REGION_ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets consistent gets
0 physical reads
0 redo size
1194 bytes sent via SQL*Net to client
391 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
25 rows processed
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s