1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | drop table 고객 ; drop table 주문; create table 고객( 고객번호 varchar2(10) primary key, 고객명 varchar2(100) , 연락처 varchar2(12) , 거주지역코드 varchar(2) ) ; create table 주문( 주문번호 varchar(20) primary key , 고객번호 varchar2(10) not null, 주문일시 timestamp , 주문금액 number , 배송지 varchar2(100) , constraint fk_주문 foreign key(고객번호) references 고객(고객번호) ) partition by range(주문일시) ( partition p3 values less than (timestamp '2015-04-01 00:00:00.000000' ) , partition p4 values less than (timestamp '2015-05-01 00:00:00.000000' ) , partition p5 values less than (timestamp '2015-06-01 00:00:00.000000' ) , partition p6 values less than (timestamp '2015-07-01 00:00:00.000000' ) , partition p7 values less than (timestamp '2015-08-01 00:00:00.000000' ) , partition p8 values less than (timestamp '2015-09-01 00:00:00.000000' ) , partition p9 values less than (timestamp '2015-10-01 00:00:00.000000' ) , partition p10 values less than (timestamp '2015-11-01 00:00:00.000000' ) , partition p11 values less than (timestamp '2015-12-01 00:00:00.000000' ) , partition p12 values less than (timestamp '2016-01-01 00:00:00.000000' ) , partition pmax values less than (maxvalue) ); insert into 고객 select object_id, object_name, '0100001004' , lpad(namespace, 2 ,'0') from all_objects where rownum <= 10000 ; update 고객 set 고객명 = '김철수' , 거주지역코드 = '02' where 고객번호 = '3' ; update 고객 set 고객명 = '홍길동', 거주지역코드 = '05' where 고객번호 = '6' ; commit; truncate table 주문; insert into 주문 select lpad(rownum, 20, '0' ) , round(dbms_random.value(3, 6)) , add_months(created, -24) + rownum , round(dbms_random.value(10000, 50000)) , object_type from all_objects where rownum <= 20000 ; select * from 고객; select * from 주문; select /*+ ordered gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지 from 고객 c, 주문 o where o.주문일시 between to_date('20150301', 'yyyymmdd') and to_date('20150314235959', 'yyyymmddhh24miss') and o.고객번호 = c.고객번호 and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동') order by o.주문일시, c.고객명 ; ------------------------------------------------------------------------------------------------------------------------ --| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------ --| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 108 | | | | --| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.03 | 108 | 2048 | 2048 | 2048 (0)| --|* 2 | HASH JOIN | | 1 | 1 | 1 |00:00:00.03 | 108 | 1335K| 1335K| 538K (0)| --|* 3 | TABLE ACCESS FULL | 고객 | 1 | 199 | 2 |00:00:00.02 | 68 | | | | --| 4 | PARTITION RANGE SINGLE| | 1 | 1 | 6 |00:00:00.01 | 37 | | | | --|* 5 | TABLE ACCESS FULL | 주문 | 1 | 1 | 6 |00:00:00.01 | 37 | | | | ------------------------------------------------------------------------------------------------------------------------ select /*+ ordered gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지 from 고객 c, 주문 o where o.주문일시 between to_date('20150301', 'yyyymmdd') and to_date('20150314235959', 'yyyymmddhh24miss') and o.고객번호 = c.고객번호 --and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동') and (c.거주지역코드 , c.고객명 ) in ( ('02','김철수'), ('05','홍길동')) order by o.주문일시, c.고객명 ; create index 고객_ix01 on 고객(거주지역코드, 고객명) ; create index 주문_ix01 on 주문(고객번호, 주문일시) ; --주문 글로벌인덱스 사용 --------------------------------------------------------------------------------------------------------------------------------------------------- --| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------- --| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 10 | 19 | | | | --| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.01 | 10 | 19 | 2048 | 2048 | 2048 (0)| --| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 10 | 19 | | | | --| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 9 | 19 | | | | --| 4 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 5 | 8 | | | | --| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| 고객 | 2 | 1 | 2 |00:00:00.01 | 5 | 8 | | | | --|* 6 | INDEX RANGE SCAN | 고객_IX0| 2 | 1 | 2 |00:00:00.01 | 4 | 8 | | | | --|* 7 | INDEX RANGE SCAN | 주문_IX0| 2 | 1 | 1 |00:00:00.01 | 4 | 11 | | | | --| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID | 주문 | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------- --주문 로컬인덱스 사용 drop index 주문_ix01 ; create index 주문_x1 on 주문(고객번호, 주문일시) local ; ------------------------------------------------------------------------------------------------------------------------------------------ --| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------ --| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 | | | | --| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.01 | 8 | 2048 | 2048 | 2048 (0)| --| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 | | | | --| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | | | | --| 4 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 5 | | | | --| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| 고객 | 2 | 1 | 2 |00:00:00.01 | 5 | | | | --|* 6 | INDEX RANGE SCAN | 고객_IX0| 2 | 1 | 2 |00:00:00.01 | 4 | | | | --| 7 | PARTITION RANGE SINGLE | | 2 | 1 | 1 |00:00:00.01 | 2 | | | | --|* 8 | INDEX RANGE SCAN | 주문_X1 | 2 | 1 | 1 |00:00:00.01 | 2 | | | | --| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | 주문 | 1 | 1 | 1 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------ select /*+ ordered use_concat gather_plan_statistics*/o.주문일시, o.주문번호, c.고객번호, c.고객명, c.연락처, o.주문금액, o.배송지 from 고객 c, 주문 o where o.주문일시 between to_date('20150301', 'yyyymmdd') and to_date('20150314235959', 'yyyymmddhh24miss') and o.고객번호 = c.고객번호 --and c.거주지역코드 || c.고객명 in ('02김철수', '05홍길동') --and (c.거주지역코드 , c.고객명 ) in ( ('02','김철수'), ('05','홍길동')) and ((c.거주지역코드 = '02' and c.고객명 = '김철수') or (c.거주지역코드 = '03' and c.고객명 = '홍길동') ) order by o.주문일시, c.고객명 ; ------------------------------------------------------------------------------------------------------------------------------------------ --| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------ --| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 7 | | | | --| 1 | SORT ORDER BY | | 1 | 1 | 1 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)| --| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | | | | --| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 | | | | --| 4 | INLIST ITERATOR | | 1 | | 1 |00:00:00.01 | 5 | | | | --| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| 고객 | 2 | 1 | 1 |00:00:00.01 | 5 | | | | --|* 6 | INDEX RANGE SCAN | 고객_IX0| 2 | 1 | 1 |00:00:00.01 | 4 | | | | --| 7 | PARTITION RANGE SINGLE | | 1 | 1 | 1 |00:00:00.01 | 1 | | | | --|* 8 | INDEX RANGE SCAN | 주문_X1 | 1 | 1 | 1 |00:00:00.01 | 1 | | | | --| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | 주문 | 1 | 2 | 1 |00:00:00.01 | 1 | | | | ------------------------------------------------------------------------------------------------------------------------------------------ --Predicate Information (identified by operation id): ----------------------------------------------------- -- --6 - access(("C"."고객명"='김철수' AND "C"."거주지역코드"='02' OR "C"."고객명"='홍길동' AND "C"."거주지역코드"='03')) --8 - access("O"."고객번호"="C"."고객번호" AND "O"."주문일시">=TIMESTAMP' 2015-03-01 00:00:00' AND "O"."주문일시"<=TIMESTAMP' 2015-03-14 --23:59:59') select * from table(dbms_xplan.display_cursor(null, null, 'allstats last')) ; | cs |
조건절을 가공하면 인덱스를 사용할 수 없다.
OR 또는 UNION ALL 을 사용할 수 있다. OR 사용시 USE_CONCAT 힌트를 같이사용해야 한다.
주문은 파티션 테이블이다. 위와 같을때 일반글로벌인덱스보다 로컬 Nonprefixed 인덱스가 유용할 수 있다.
정확히 하나의 파티션만 읽는다.
보기와 같을때 인덱스는 고객번호 + 주문일시로 하자 , 주문일시를 선두조건으로 두면 데이터가 흩어지므로
인덱스 스캔시 비효율이 발생할 수 있다.
[출처] https://weplaythedb.tistory.com/16
