SQL> create tablespace test_tbs datafile size 3000M;
테이블 영역이 생성되었습니다.
SQL> CREATE UNDO TABLESPACE UNDOTBS2
2 DATAFILE SIZE 4000M;
테이블 영역이 생성되었습니다.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
시스템이 변경되었습니다.
SQL> create user tester identified by test default tablespace test_tbs temporary tablespace temp;
사용자가 생성되었습니다.
SQL> grant connect, resource, dba to tester;
권한이 부여되었습니다.
SQL> connect tester/test;
연결되었습니다.
SQL> @C:\oracle\ora92\rdbms\admin\utlxplan.sql;
테이블이 생성되었습니다.
SQL> COPY FROM scott/tiger@orcl CREATE emp USING select * FROM emp;
배열의 인출/바인드 크기는 15입니다. (배열 크기는 15)
작업이 완성되면 커밋됩니다. (복사 완료: 0)
최대 긴 크기는 80 입니다. (롱 80)
테이블 EMP가 생성되었습니다.
14 행이 선택되었습니다(scott@orcl 로부터).
14 행이 입력되었습니다(EMP 에).
14 행이 EMP( DEFAULT HOST 연결의)으로 커밋되었습니다.
SQL> COPY FROM scott/tiger@orcl CREATE dept USING select * FROM dept;
배열의 인출/바인드 크기는 15입니다. (배열 크기는 15)
작업이 완성되면 커밋됩니다. (복사 완료: 0)
최대 긴 크기는 80 입니다. (롱 80)
테이블 DEPT가 생성되었습니다.
4 행이 선택되었습니다(scott@orcl 로부터).
4 행이 입력되었습니다(DEPT 에).
4 행이 DEPT( DEFAULT HOST 연결의)으로 커밋되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> create table test1t (seq number,
2 empno varchar2(04),
3 deptno number(02),
4 sale_date varchar2(8),
5 qty number) ;
테이블이 생성되었습니다.
경 과: 00:00:00.00
SQL> insert into test1t (seq, empno, deptno, sale_date, qty)
2 select rownum
3 ,b.empno
4 , a.deptno
5 , to_char(to_date(sysdate-round(abs(ceil(dbms_random.random))/10000000,0)),'yyyymmdd') ran
6 , 100 + round(abs(ceil(dbms_random.random))/10000000,0) qty
7 from dept a, emp b,
8 (select * from dual connect by level < 200000) c;
11199944 개의 행이 만들어졌습니다.
경 과: 00:17:02.00
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 COUNT
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 VIEW
5 4 CONNECT BY (WITH FILTERING)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (FULL) OF 'DUAL'
8 6 TABLE ACCESS (BY USER ROWID) OF 'DUAL'
9 5 NESTED LOOPS
10 9 BUFFER (SORT)
11 10 CONNECT BY PUMP
12 9 FILTER
13 12 TABLE ACCESS (FULL) OF 'DUAL'
14 3 TABLE ACCESS (FULL) OF 'EMP'
15 2 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11199944 rows processed
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> create index test1t_idx on test1t (sale_date);
인덱스가 생성되었습니다.
경 과: 00:11:11.07
SQL> /* sale_date로 sort한 테이블 생성*/
SQL> create table test2t as
2 select * from test1t order by sale_date, rowid;
테이블이 생성되었습니다.
경 과: 00:14:02.07
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> create index test2t_idx on test2t (sale_date);
인덱스가 생성되었습니다.
경 과: 00:03:52.07
SQL> analyze table test1t compute statistics;
테이블이 분석되었습니다.
경 과: 00:05:54.01
SQL> select avg_row_len from user_tables where table_name = 'TEST1T';
AVG_ROW_LEN
-----------
30
경 과: 00:00:00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS (OUTER)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
8 7 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
9 6 TABLE ACCESS (CLUSTER) OF 'TAB$'
10 9 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE)
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
12 11 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
13 4 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
14 3 TABLE ACCESS (CLUSTER) OF 'USER$'
15 14 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
16 2 TABLE ACCESS (CLUSTER) OF 'SEG$'
17 16 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE)
18 1 TABLE ACCESS (CLUSTER) OF 'TS$'
19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select avg(count(*)) from test1t group by sale_date;
AVG(COUNT(*))
-------------
51851.5926
경 과: 00:00:25.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24275 Card=1 Bytes=8
)
1 0 SORT (AGGREGATE) (Cost=24275 Card=1 Bytes=8)
2 1 SORT (GROUP BY) (Cost=24275 Card=1 Bytes=8)
3 2 TABLE ACCESS (FULL) OF 'TEST1T' (Cost=4734 Card=111999
44 Bytes=89599552)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /* (30 * 51851.5926) * 1.1 / 1024 = 1670 (1700으로 잡음) */
SQL> create cluster test1c(sale_date varchar2(8))
2 size 1700
3 tablespace test_tbs;
클러스터가 생성되었습니다.
경 과: 00:00:00.00
SQL> create index test1c_idx on cluster test1c
2 tablespace test_tbs;
인덱스가 생성되었습니다.
경 과: 00:00:00.00
SQL> create table test1ct (seq number,
2 empno varchar2(04),
3 deptno number(02),
4 sale_date varchar2(8),
5 qty number)
6 cluster test1c(sale_date) ;
테이블이 생성되었습니다.
경 과: 00:00:00.00
SQL> insert into test1ct
2 select * from test2t;
11199944 개의 행이 만들어졌습니다.
경 과: 00:09:45.08
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST2T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11199944 rows processed
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> /* 클러스터파라미터를 크게 잡았을 경우의 속도 */
SQL> create cluster test2c(sale_date varchar2(8))
2 size 8600
3 tablespace test_tbs;
클러스터가 생성되었습니다.
경 과: 00:00:00.02
SQL> create index test2c_idx on cluster test2c
2 tablespace test_tbs;
인덱스가 생성되었습니다.
경 과: 00:00:00.00
SQL> create table test2ct (seq number,
2 empno varchar2(04),
3 deptno number(02),
4 sale_date varchar2(8),
5 qty number)
6 cluster test2c(sale_date) ;
테이블이 생성되었습니다.
경 과: 00:00:00.01
SQL> insert into test2ct
2 select * from test2t;
11199944 개의 행이 만들어졌습니다.
경 과: 00:09:02.08
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST2T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11199944 rows processed
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> /* 클러스터파라미터를 작게 잡았을 경우의 속도 */
SQL> create cluster test3c(sale_date varchar2(8))
2 size 512
3 tablespace test_tbs;
클러스터가 생성되었습니다.
경 과: 00:00:00.02
SQL> create index test3c_idx on cluster test3c
2 tablespace test_tbs;
인덱스가 생성되었습니다.
경 과: 00:00:00.01
SQL> create table test3ct (seq number,
2 empno varchar2(04),
3 deptno number(02),
4 sale_date varchar2(8),
5 qty number)
6 cluster test2c(sale_date) ;
테이블이 생성되었습니다.
경 과: 00:00:00.00
SQL> insert into test3ct
2 select * from test2t;
11199944 개의 행이 만들어졌습니다.
경 과: 00:15:56.06
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST2T'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11199944 rows processed
SQL> commit;
커밋이 완료되었습니다.
경 과: 00:00:00.00
SQL> analyze table DEPT compute statistics;
테이블이 분석되었습니다.
경 과: 00:00:00.02
SQL> analyze table EMP compute statistics;
테이블이 분석되었습니다.
경 과: 00:00:00.00
SQL> analyze table TEST1CT compute statistics;
테이블이 분석되었습니다.
경 과: 00:09:11.06
SQL> analyze table TEST2CT compute statistics;
테이블이 분석되었습니다.
경 과: 00:09:20.03
SQL> analyze table TEST3CT compute statistics;
테이블이 분석되었습니다.
경 과: 00:08:23.04
SQL> analyze table TEST1T compute statistics;
테이블이 분석되었습니다.
경 과: 00:32:21.02
SQL> analyze table TEST2T compute statistics;
테이블이 분석되었습니다.
경 과: 00:07:40.04
SQL>
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL>
SQL> /*일반 테이블 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select count(*) from test1t where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:26.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4734 Card=1 Bytes=13
)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST1T' (Cost=4734 Card=78377 By
tes=1018901)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*일반 테이블 인덱스사용 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select /*+ index(test1t test1t_idx) */count(*) from test1t where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:05:06.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1089922 Card=1 Bytes
=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1T' (Cost=1089922
Card=78377 Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST1T_IDX' (NON-UNIQUE) (Cost=
4893 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*클러스터링 팩터를 향상시킨(sort한 테이블sale_date,rowid) 테이블 인덱스사용 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select /*+ index(test2t test2t_idx) */count(*) from test2t where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:15.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12641 Card=1 Bytes=1
3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST2T' (Cost=12641 Ca
rd=78377 Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST2T_IDX' (NON-UNIQUE) (Cost=
4893 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*클러스터 테이블(size파라미터는 공식대로) 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select count(*) from test1ct where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:04.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (CLUSTER) OF 'TEST1CT' (Cost=131 Card=78377
Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST1C_IDX' (NON-UNIQUE) (Cost=
5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*클러스터 테이블(size파라미터는 크게) 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select count(*) from test2ct where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:27.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (CLUSTER) OF 'TEST2CT' (Cost=131 Card=78377
Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST2C_IDX' (NON-UNIQUE) (Cost=
5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*클러스터 테이블(size파라미터는 1/3) 조회*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> select count(*) from test3ct where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:24.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (CLUSTER) OF 'TEST3CT' (Cost=131 Card=78377
Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST2C_IDX' (NON-UNIQUE) (Cost=
5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*결합인덱스 생성*/
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> create index test1ct_idx2 on test1ct(sale_date, deptno);
인덱스가 생성되었습니다.
경 과: 00:07:26.07
SQL> /*책에는 룰힌트나 RBO인 경우 결합인덱스가 사용된다고 했고 동영상강의에서는 클러스터만보면 좋아서 미치고 팔짝 뛴다고 했다 */
SQL> select /*+ CLUSTER(test1ct) */ count(*) from test1ct where sale_date between '20070220' and '20070301' and deptno = 10 and seq < 500000;
COUNT(*)
----------
5857
경 과: 00:00:12.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1CT' (Cost=37 Card
=19594 Bytes=293910)
3 2 INDEX (RANGE SCAN) OF 'TEST1CT_IDX2' (NON-UNIQUE) (Cos
t=5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>/*사용제한을 걸면 클러스터를 탄다고 했는데 사용하지 않는다.*/
SQL> select count(*) from test1ct where sale_date between '20070220' and '20070301' and deptno|| '' = 10 and seq < 500000;
COUNT(*)
----------
5857
경 과: 00:00:04.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1CT' (Cost=7 Card=
784 Bytes=11760)
3 2 INDEX (RANGE SCAN) OF 'TEST1CT_IDX2' (NON-UNIQUE) (Cos
t=5 Card=17556)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /*인덱스하고 클러스터하고 있으면 인덱스를 선택?? 힌트를 잘못 썼나?*/
SQL> drop index test1ct_idx2;
인덱스가 삭제되었습니다.
경 과: 00:00:02.03
SQL> select /*+ CLUSTER(test1ct) */ count(*) from test1ct where sale_date between '20070220' and '20070301' and deptno = 10 and seq < 500000;
COUNT(*)
----------
5857
경 과: 00:00:01.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (CLUSTER) OF 'TEST1CT' (Cost=131 Card=19594
Bytes=293910)
3 2 INDEX (RANGE SCAN) OF 'TEST1C_IDX' (NON-UNIQUE) (Cost=
5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /*RBO로 변경*/
SQL> connect / as sysdba
연결되었습니다.
SQL> alter system set optimizer_mode='RULE' scope=spfile;
시스템이 변경되었습니다.
경 과: 00:00:00.01
SQL> set autotrace on
SQL> set timing on
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.01
SQL> /*인덱스만 스캔해버린다 결합인덱스사용*/
SQL> select count(*) from test1ct where sale_date between '20070220' and '20070301' and deptno = 10;
COUNT(*)
----------
130582
경 과: 00:00:01.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST1CT_IDX2' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*클러스터인덱스와 결합인덱스중 결합인덱스 사용*/
SQL> select count(*) from test1ct where sale_date between '20070220' and '20070301';
COUNT(*)
----------
522362
경 과: 00:00:01.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TEST1CT_IDX2' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events 'immediate trace name flush_cache level 1';
세션이 변경되었습니다.
경 과: 00:00:00.00
SQL> /*테이블 엑세스를 유도하기 위해 체크조건 사용 했을 때도 결합인덱스 사용*/
SQL> select count(*) from test1ct where sale_date between '20070220' and '20070301' and seq < 500000;
COUNT(*)
----------
23322
경 과: 00:00:09.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'TEST1CT'
3 2 INDEX (RANGE SCAN) OF 'TEST1CT_IDX2' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /*클러스터를 사용하기 위한 힌트사용 결국 제 테스트의 결과 클러스터는 룰베이스에서 클러스터 힌트를 주었을때만 되더라..이상하다..*/
SQL> select /*+ CLUSTER(test1ct) */ count(*) from test1ct where sale_date between '20070220' and '20070301' and seq < 500000
COUNT(*)
----------
23322
경 과: 00:00:01.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=131 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (CLUSTER) OF 'TEST1CT' (Cost=131 Card=78377
Bytes=1018901)
3 2 INDEX (RANGE SCAN) OF 'TEST1C_IDX' (NON-UNIQUE) (Cost=
5 Card=1755646)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed