|
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 인덱스가 유용할 수 있다.
정확히 하나의 파티션만 읽는다.
보기와 같을때 인덱스는 고객번호 + 주문일시로 하자 , 주문일시를 선두조건으로 두면 데이터가 흩어지므로
인덱스 스캔시 비효율이 발생할 수 있다.
SMALL
'Oracle' 카테고리의 다른 글
| Real-Time SQL Monitoring (0) | 2020.10.19 |
|---|---|
| Database Sample Schemas (0) | 2020.09.26 |
| Oracle_Home 경로 확인 (0) | 2020.08.08 |
| SQLP - SQL자격검정실전문제 실기문제1 풀이 (0) | 2020.06.29 |
| Oracle 히든파라미터 조회방법 (0) | 2020.06.26 |