[13]

SQL> set autotrace on

SQL> create table 월별고객별판매집계

2  as

3  select rownum 고객번호

4       , '2008' || lpad(ceil(rownum/100000), 2, '0') 판매월

5       , decode(mod(rownum, 12), 1, 'A', 'B') 판매구분

6       , round(dbms_random.value(1000,100000), -2) 판매금액

7  from   dual

8  connect by level <= 1200000 ;

테이블이 생성되었습니다.

SQL>

SQL>

SQL> create index 월별고객별판매집계_IDX1 on 월별고객별판매집계(판매구분, 판매월);

인덱스가 생성되었습니다.

SQL>

SQL> select count(*)

2  from   월별고객별판매집계 t

3  where  판매구분 = 'A'

4  and    판매월 between '200801' and '200812';

COUNT(*)

----------

100000

Execution Plan

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

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |                |     1 |     8 |   291   (2)|

|   1 |  SORT AGGREGATE   |                |     1 |     8 |            |

|*  2 |   INDEX RANGE SCAN| 월별고객별판매 |   100K|   782K|   291   (2)|

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

Predicate Information (identified by operation id):

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

2 - access("판매구분"='A' AND "판매월">='200801' AND "판매월"<='200812')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

32  recursive calls

0  db block gets

363  consistent gets

282  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL>

SQL> create index 월별고객별판매집계_IDX2 on 월별고객별판매집계(판매월, 판매구분);

인덱스가 생성되었습니다.

SQL>

SQL> select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)

2  from   월별고객별판매집계 t

3  where  판매구분 = 'A'

4  and    판매월 between '200801' and '200812';

COUNT(*)

----------

100000

Execution Plan

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

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

| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |                |     1 |     8 |  3471   (2)|

|   1 |  SORT AGGREGATE   |                |     1 |     8 |            |

|*  2 |   INDEX RANGE SCAN| 월별고객별판매 |   100K|   782K|  3471   (2)|

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

Predicate Information (identified by operation id):

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

2 - access("판매월">='200801' AND "판매구분"='A' AND "판매월"<='200812')

filter("판매구분"='A')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

5  recursive calls

0  db block gets

3162  consistent gets

3100  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> select /*+ index(t 월별고객별판매집계_IDX2) */ count(*)

2  from   월별고객별판매집계 t

3  where  판매구분 = 'A'

4  and    판매월 in ( '200801', '200802', '200803', '200804', '200805', '200806'

5                   , '200807', '200808', '200809', '200810', '200811', '200812' ) ;

COUNT(*)

----------

100000

Execution Plan

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

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

| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT   |                |     1 |     8 |    14   (0)|

|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |

|   2 |   INLIST ITERATOR  |                |       |       |            |

|*  3 |    INDEX RANGE SCAN| 월별고객별판매 |   100K|   782K|    14   (0)|

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

Predicate Information (identified by operation id):

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

3 - access(("판매월"='200801' OR "판매월"='200802' OR "판매월"='200803' OR

"판매월"='200804' OR "판매월"='200805' OR "판매월"='200806' OR "판

매월"='200807' OR

"판매월"='200808' OR "판매월"='200809' OR "판매월"='200810' OR "판

매월"='200811' OR

"판매월"='200812') AND "판매구분"='A')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

5  recursive calls

0  db block gets

386  consistent gets

2  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL>

SQL> select /*+ INDEX_SS(t 월별고객별판매집계_IDX2) */ count(*)

2  from   월별고객별판매집계 t

3  where  판매구분 = 'A'

4  and    판매월 between '200801' and '200812' ;

COUNT(*)

----------

100000

Execution Plan

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

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

| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT |                |     1 |     8 |   193  (25)|

|   1 |  SORT AGGREGATE  |                |     1 |     8 |            |

|*  2 |   INDEX SKIP SCAN| 월별고객별판매 |   100K|   782K|   193  (25)|

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

Predicate Information (identified by operation id):

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

2 - access("판매월">='200801' AND "판매구분"='A' AND "판매월"<='200812')

filter("판매구분"='A')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

4  recursive calls

0  db block gets

372  consistent gets

0  physical reads

0  redo size

424  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed