Re: 문제51. (오늘의 마지막 문제) 아래의 SQL의 조인 순서는 무조건 nested loop 조인이 ..

[19기_이현서]

튜닝 전

select  /*+ leading(t s)  use_nl(s)  */  t.calendar_year, sum(amount_sold)
     from  sales200  s , times200   t
     where  s.time_id = t.time_id
      and  t.week_ending_day_id = 1582
     group  by  t.calendar_year;
     
  select * from  table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

----

---

튜닝 후

create index times200_time_id on times200(time_id);
create index sales200_time_id on sales200(time_id);

select  /*+ leading(t s)  use_nl(s) index(s sales200_time_id) */  t.calendar_year, sum(amount_sold)
   from  sales200  s , times200   t
   where  s.time_id = t.time_id
    and  t.week_ending_day_id = 1582
   group  by  t.calendar_year;
 select * from  table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

---

| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |      1 |00:00:00.01 |     312 |
|   1 |  HASH GROUP BY                |                  |      1 |      4 |      1 |00:00:00.01 |     312 |
|   2 |   NESTED LOOPS                |                  |      1 |   4386 |   6203 |00:00:00.01 |     312 |
|   3 |    NESTED LOOPS               |                  |      1 |   4403 |   6203 |00:00:00.01 |      86 |
|*  4 |     TABLE ACCESS FULL         | TIMES200         |      1 |      7 |      7 |00:00:00.01 |      54 |
|*  5 |     INDEX RANGE SCAN          | SALES200_TIME_ID |      7 |    629 |   6203 |00:00:00.01 |      32 |
|   6 |    TABLE ACCESS BY INDEX ROWID| SALES200         |   6203 |    629 |   6203 |00:00:00.01 |     226 |