all_object 를 가지고 테스트 테이블을 생성한다.


SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 27 16:58:42 2012


Copyright (c) 1982, 2011, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> drop table t

  2  ;

drop table t

           *

ERROR at line 1:

ORA-00942: table or view does not exist



SQL> create table t as select * from all_objects;


Table created.


SQL> create index t_idx_1 on t(owner, object_type, object_name);


Index created.


SQL> create index t_idx_2 on t(object_name,object_type,owner);


Index created.


SQL> select count(distinct owner), count(distinct object_type),
count(distinct object_name), count(*) from t;


COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)   COUNT(*)

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

                  23                         40                      43667      72317


t table과 t_idx_1, t_idx_2를 생성함 보다시피 owner, type, object_name 순으로 되어있다.


SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;


BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

    4078088         90              2               28


SQL> analyze index t_idx_2 validate structure;


Index analyzed.


SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;


BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

    4070060         90              1               12



opt_cmpr_pctsave를 확인해보면, t_idx_1 이 좀 더 압축률이 좋다.


declare

cnt int;

begin

for x in (select /*+ full(t) */ owner, object_type, object_name from t)

loop


select /*+ index( t t_idex_1) */ count(*) into cnt

from t

where object_name = x.object_name

and object_type = x.object_type

and owner=x.owner;


select /*+ index( t t_idex_2) */ count(*) into cnt

from t

where object_name = x.object_name

and object_type = x.object_type

and owner=x.owner;


end loop;

end;

/


t_idx_1, t_idx_2 에 대한 trace를 생성후 pkprof로 정렬해 보았다.


  • t_idx_1의 내용

SQL ID: 3p4vdphg2zz5z Plan Hash: 3814365591


SELECT /*+ index( t t_idex_1) */ COUNT(*)

FROM

 T WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER=:B1



call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute  72317      6.81       6.70          0          3          0           0

Fetch    72317      8.45       8.91          0     217496          0       72317

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

total   144635     15.27      15.62          0     217499          0       72317


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 83     (recursive depth: 1)

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         1          1          1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=114 us)

         1          1          1   INDEX RANGE SCAN T_IDX_1
                                    (cr=3 pr=0 pw=0 time=47 us cost=3 size=765 card=17)(object id 77389)


  • t_idx_2의 내용.

?SELECT /*+ index( t t_idex_2) */ COUNT(*)

FROM

 T WHERE OBJECT_NAME = :B3 AND OBJECT_TYPE = :B2 AND OWNER=:B1



call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute  72317      6.99       6.93          0          3          0           0

Fetch    72317      8.55       8.89          0     217496          0       72317

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

total   144635     15.55      15.83          0     217499          0       72317


Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 83     (recursive depth: 1)

Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

         1          1          1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=170 us)

         1          1          1   INDEX RANGE SCAN T_IDX_1
                                    (cr=3 pr=0 pw=0 time=119 us cost=3 size=765 card=17)(object id 77389)



읽은 row수 및 실행처리시간이 큰차이가 없다.