실행계획 순서분석

B+Tree 의 First Order 방식의 읽는 순서

 - 자식 중의 종손 먼저

 - 하위 tree 가 있다면 하위 다 읽고 다음 형재로

 

 

예)
SELECT  /*+ gather_plan_statistics */ O.PAYTYPE, O.STATUS, O.CUSTNO, OD.PRICE
FROM ORDERS O, ORDERDETAILS OD 
WHERE O.ORDERNO = OD.ORDERNO
AND O.PAYTYPE ='계좌이체'
AND STATUS  = '결제완료'

 

PLAN_TABLE_OUTPUT
SQL_ID  4bwaukvdmsqhs, child number 0
-------------------------------------
SELECT  /*+ gather_plan_statistics */ O.PAYTYPE, O.STATUS, O.CUSTNO, 
OD.PRICE  FROM ORDERS O, ORDERDETAILS OD   WHERE O.ORDERNO = OD.ORDERNO 
 AND O.PAYTYPE ='계좌이체'  AND STATUS  = '결제완료'
 
Plan hash value: 3432454088
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |      1 |        |       |       |   100K(100)|          |  13207 |00:00:02.91 |     367K|    300K|       |       |          |
|*  1 |  HASH JOIN         |              |      1 |    118K|  5345K|  2816K|   100K  (1)| 00:00:04 |  13207 |00:00:02.91 |     367K|    300K|  5750K|  1509K| 6072K (0)|
|*  2 |   TABLE ACCESS FULL| ORDERS       |      1 |  60026 |  2110K|       | 82130   (1)| 00:00:04 |  59463 |00:00:01.88 |     300K|    300K|       |       |          |
|   3 |   TABLE ACCESS FULL| ORDERDETAILS |      1 |    199K|  1951K|       | 18184   (1)| 00:00:01 |    199K|00:00:00.15 |   66734 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("O"."ORDERNO"="OD"."ORDERNO")
   2 - filter(("STATUS"='결제완료' AND "O"."PAYTYPE"='계좌이체'))
 
Note
-----
   - this is an adaptive plan

 

2 -> 3 -> 1 -> 0

 

 

 

 

SELECT  /*+ gather_plan_statistics */ O.PAYTYPE, O.STATUS, O.CUSTNO, OD.PRICE
FROM ORDERS O, ORDERDETAILS OD , CUSTOMERS C
WHERE O.ORDERNO = OD.ORDERNO
AND O.CUSTNO = C.CUSTNO
AND O.PAYTYPE ='계좌이체'
AND O.STATUS  = '결제완료'
AND C.GRADE ='VIP'

 

PLAN_TABLE_OUTPUT
SQL_ID  6q7wkqawr9kfd, child number 0
-------------------------------------
SELECT  /*+ gather_plan_statistics */ O.PAYTYPE, O.STATUS, O.CUSTNO, 
OD.PRICE  FROM ORDERS O, ORDERDETAILS OD , CUSTOMERS C  WHERE O.ORDERNO 
= OD.ORDERNO  AND O.CUSTNO = C.CUSTNO  AND O.PAYTYPE ='계좌이체'  AND 
O.STATUS  = '결제완료'  AND C.GRADE ='VIP'
 
Plan hash value: 1858064850
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |       |   101K(100)|          |   4500 |00:00:02.89 |     370K|    300K|       |       |          |
|*  1 |  HASH JOIN          |              |      1 |  40467 |  2213K|   101K  (1)| 00:00:04 |   4500 |00:00:02.89 |     370K|    300K|  2614K|  1443K| 3287K (0)|
|*  2 |   HASH JOIN         |              |      1 |  20414 |   917K| 83055   (1)| 00:00:04 |  20250 |00:00:02.16 |     303K|    300K|  1797K|  1797K| 2686K (0)|
|*  3 |    TABLE ACCESS FULL| CUSTOMERS    |      1 |   3394 | 33940 |   924   (1)| 00:00:01 |   3394 |00:00:00.01 |    3340 |      0 |       |       |          |
|*  4 |    TABLE ACCESS FULL| ORDERS       |      1 |  60026 |  2110K| 82130   (1)| 00:00:04 |  59463 |00:00:02.07 |     300K|    300K|       |       |          |
|   5 |   TABLE ACCESS FULL | ORDERDETAILS |      1 |    199K|  1951K| 18184   (1)| 00:00:01 |    199K|00:00:00.17 |   66666 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("O"."ORDERNO"="OD"."ORDERNO")
   2 - access("O"."CUSTNO"="C"."CUSTNO")
   3 - filter("C"."GRADE"='VIP')
   4 - filter(("O"."STATUS"='결제완료' AND "O"."PAYTYPE"='계좌이체'))
 
Note
-----
   - this is an adaptive plan

3-> 4 -> 2 -> 5 -> 1 -> 0

 

 

SELECT  /*+ gather_plan_statistics LEADING(O OD) INDEX(C CUSTOMERS_PK) NO_SWAP_JOIN_INPUTS(C)*/ O.PAYTYPE, O.STATUS, O.CUSTNO, OD.PRICE
FROM ORDERS O, ORDERDETAILS OD , CUSTOMERS C
WHERE O.ORDERNO = OD.ORDERNO
AND O.CUSTNO = C.CUSTNO
AND O.PAYTYPE ='계좌이체'
AND O.STATUS  = '결제완료'
AND C.GRADE ='VIP'

 

PLAN_TABLE_OUTPUT
SQL_ID  b0fdzjv9z1mah, child number 0
-------------------------------------
SELECT  /*+ gather_plan_statistics LEADING(O OD) INDEX(C CUSTOMERS_PK) 
NO_SWAP_JOIN_INPUTS(C)*/ O.PAYTYPE, O.STATUS, O.CUSTNO, OD.PRICE FROM 
ORDERS O, ORDERDETAILS OD , CUSTOMERS C WHERE O.ORDERNO = OD.ORDERNO 
AND O.CUSTNO = C.CUSTNO AND O.PAYTYPE ='계좌이체' AND O.STATUS  = '결제완료' 
AND C.GRADE ='VIP'
 
Plan hash value: 2735866815
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |       |       |   104K(100)|          |   4500 |00:00:02.79 |     370K|    300K|       |       |          |
|*  1 |  HASH JOIN                           |              |      1 |    102K|  5598K|  6744K|   104K  (1)| 00:00:05 |   4500 |00:00:02.79 |     370K|    300K|  1997K|  1522K| 2957K (0)|
|*  2 |   HASH JOIN                          |              |      1 |    118K|  5345K|  2816K|   100K  (1)| 00:00:04 |  13207 |00:00:02.75 |     366K|    300K|  5750K|  1509K| 6023K (0)|
|*  3 |    TABLE ACCESS FULL                 | ORDERS       |      1 |  60026 |  2110K|       | 82130   (1)| 00:00:04 |  59463 |00:00:01.77 |     300K|    300K|       |       |          |
|   4 |    TABLE ACCESS FULL                 | ORDERDETAILS |      1 |    199K|  1951K|       | 18184   (1)| 00:00:01 |    199K|00:00:00.15 |   66638 |      0 |       |       |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS    |      1 |   3394 | 33940 |       |  3421   (1)| 00:00:01 |   3394 |00:00:00.02 |    3497 |      0 |       |       |          |
|   6 |    INDEX FULL SCAN                   | CUSTOMERS_PK |      1 |  10000 |       |       |    21   (0)| 00:00:01 |  10000 |00:00:00.01 |      66 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("O"."CUSTNO"="C"."CUSTNO")
   2 - access("O"."ORDERNO"="OD"."ORDERNO")
   3 - filter(("O"."STATUS"='결제완료' AND "O"."PAYTYPE"='계좌이체'))
   5 - filter("C"."GRADE"='VIP')
 
Note
-----
   - this is an adaptive plan

 

3 -> 4 -> 2 -> 6 -> 5 -> 1 -> 0