Nested Loop 예제

1. 한쪽 연결고리 이상
   
   select *
   from sales s, customers c
   where s.customer_id = c.customer_id
                     X                    O                    --  index  유무
   and s.saledate like '2010%'                        --> 10만건, saledate index 존재
   and c.last_name = 'Lewis';                        --> 10건, last_name index 존재
   
   select statement
       nested loop
         table access (sales)
           index      (saledate idx)
         table access (customers)
           index      (customer_id idx)
   
   
2. 양쪽 연결고리 정상
   
   create index sales_customer_id_idx on sales(customer_id);
   
   select *
   from sales s, customers c
   where s.customer_id = c.customer_id
                    O                 O            -- index 존재 유무
   and s.saledate like '2010%'            --> 10만건, saledate index 존재
   and c.last_name = 'Lewis';             --> 10건  , last_name index 존재
   
   select statement
       nested loop
         table access (customers)
           index      (last_name idx)
         table access (sales)
           index      (customer_id idx)
   
   select statement                             --> 관련 포스트 : http://ukja.tistory.com/166
       table access (sales)
         nested loop
           table access (customers)
             index      (last_name idx)
             index      (customer_id idx)
   
   
3. 추가 예제
   
   select /*+ use_nl (d l) */ * 
   from hr.departments d, hr.locations l
   where d.location_id = l.location_id;
   
   ------------------------------------------------------------------------------------------------
   | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
   ------------------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |                  |    27 |  1863 |     4   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |     1 |    20 |     1   (0)| 00:00:01 |
   |   2 |   NESTED LOOPS              |                  |    27 |  1863 |     4   (0)| 00:00:01 |
   |   3 |    TABLE ACCESS FULL        | LOCATIONS        |    23 |  1127 |     3   (0)| 00:00:01 |
   |*  4 |    INDEX RANGE SCAN         | DEPT_LOCATION_IX |     4 |       |     0   (0)| 00:00:01 |
   ------------------------------------------------------------------------------------------------
   
   - locations table의 row의 크기가 더 적기 때문에 locations -> departments로 진행
   - where절에 범위를 줄일 수 있는 조건절이 별도로 없기 때문에 시작 table인 locations을 Full Table scan 한다.
   
   select * 
   from hr.employees e, hr.departments d, hr.locations l
   where d.location_id = l.location_id
   and e.department_id = d.department_id;
   
     ---------------------------------------------------------------------------------------------
     | Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT              |             |   106 | 14522 |     5   (0)| 00:00:01 |
     |   1 |  NESTED LOOPS                 |             |   106 | 14522 |     5   (0)| 00:00:01 |
     |   2 |   NESTED LOOPS                |             |   106 |  9328 |     4   (0)| 00:00:01 |
     |   3 |    TABLE ACCESS FULL          | EMPLOYEES   |   107 |  7276 |     3   (0)| 00:00:01 |
     |   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    20 |     1   (0)| 00:00:01 |
     |*  5 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
     |   6 |   TABLE ACCESS BY INDEX ROWID | LOCATIONS   |     1 |    49 |     1   (0)| 00:00:01 |
     |*  7 |    INDEX UNIQUE SCAN          | LOC_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
     ---------------------------------------------------------------------------------------------
   
   
   select /*+ use_nl (d l) */ * 
   from hr.departments d, hr.locations l
   where d.location_id = l.location_id
   and l.COUNTRY_ID = 'US';
   
     --------------------------------------------------------------------------------------------------
     | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
     --------------------------------------------------------------------------------------------------
     |   0 | SELECT STATEMENT              |                  |    14 |   966 |     3   (0)| 00:00:01 |
     |   1 |  TABLE ACCESS BY INDEX ROWID  | DEPARTMENTS      |     4 |    80 |     1   (0)| 00:00:01 |
     |   2 |   NESTED LOOPS                |                  |    14 |   966 |     3   (0)| 00:00:01 |
     |   3 |    TABLE ACCESS BY INDEX ROWID| LOCATIONS        |     4 |   196 |     2   (0)| 00:00:01 |
     |*  4 |     INDEX RANGE SCAN          | LOC_COUNTRY_IX   |     4 |       |     1   (0)| 00:00:01 |
     |*  5 |    INDEX RANGE SCAN           | DEPT_LOCATION_IX |     4 |       |     0   (0)| 00:00:01 |
     --------------------------------------------------------------------------------------------------
   
        4 - access("L"."COUNTRY_ID"='US')
        5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")