Partitioned table
partition이란?
- 테이블, 인덱스를 파티션 단위로 나누어서 저장하는 것
- 하나의 테이블이 별도의 세그먼트로 저장되고 여러 테이블스페이스에 저장될 수 있다.
장점
- 관리상 장점
- 성능상 장점
- 파티션 프루닝(pruning) : 불필요한 파티션을 액세스 하지 않고 필요한 파티션만 액세스
- xplan으로 확인 가능
- 파티션 와이즈 조인 : emp 파티션, dept 파티션 둘다 파티션 테이블이라면 둘을 조인할때 해당 파티션끼리만 조인을 할 수 있다.
- 병렬처리를 할 수 있다.
파티션의 종류
- range 파티셔닝
- 파티션 키값의 범위(RANGE)에 따라서 각각의 파티션에 데이터를 저장하는 방식
- 데이터를 골고루 분포하게 (well distribute 하게) 집어 넣는다.
====================================================================================================
<<RANGE PARTITION>>
USER01@orcl2>
create table range_sales
(prod_id number(6),
cust_id number,
time_id date,
channel_id char(1),
promo_id number(6),
quantity_sold number(3),
amount_sold number(10,2)
)
PARTITION BY RANGE (time_id)
(PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE));
USER01@orcl2>
@insert_part=================================================================
set echo on
insert into range_sales
values (10,68060,to_date('09-dec-1999','dd-mon-yyyy'),3,9999,28,4900) ;
insert into range_sales
values (11160,17450,to_date('15-dec-1999','dd-mon-yyyy'),2,9999,19,798) ;
insert into range_sales
values (1340,33710,to_date('31-dec-1999','dd-mon-yyyy'),3,9999,16,1264) ;
insert into range_sales
values (25270,65880,to_date('01-jan-1998','dd-mon-yyyy'),1,9999,5,210) ;
insert into range_sales
values (1615,73480,to_date('05-jan-1998','dd-mon-yyyy'),5,9999,8,96) ;
insert into range_sales
values (1900,84910,to_date('06-mar-1998','dd-mon-yyyy'),2,9999,42,378) ;
insert into range_sales
values (8085,37900,to_date('30-apr-1998','dd-mon-yyyy'),2,9999,1,68) ;
insert into range_sales
values (755,26590,to_date('01-may-1998','dd-mon-yyyy'),1,9999,11,132) ;
commit ;
set echo off
============================================================================
USER01@orcl2> select count(*) from range_sales;
COUNT(*)
----------
8
-- 파티션 테이블에 관한 dictionary 확인
col owner for a8
col tablespace_name for a10
col segment_name for a15
col partiton_name for a15
col segment_type for a16
select owner, segment_name, partition_name, segment_type, segment_subtype, tablespace_name
from dba_segments
where segment_name = 'RANGE_SALES';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SEGMENT_SU TABLESPACE
-------- --------------- --------------- ---------------- ---------- ----------
USER01 RANGE_SALES SALES_Q1_1998 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q1_1999 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q2_1998 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q2_1999 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q3_1998 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q3_1999 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q4_1998 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q4_1999 TABLE PARTITION ASSM USERDATA
USER01 RANGE_SALES SALES_Q4_2000 TABLE PARTITION ASSM USERDATA
9 rows selected.
USER01@orcl2> save part
USER01@orcl2> select owner, table_name, tablespace_name, partitioned from dba_tables
where table_name in ('RANGE_SALES', 'EMP');
OWNER TABLE_NAME TABLESPACE PAR
-------- ------------------------------ ---------- ---
SCOTT EMP USERS NO
HR EMP USERS NO
USER1 EMP INVENTORY NO
USER01 EMP USERDATA NO
USER01 RANGE_SALES YES
-- RANGE_SALES 테이블은 테이블 스페이스가 존재하지 않고, 각각의 세그먼트들이 테이블스페이스 정보를 가지고 있다
USER01@orcl2> save tab
USER01@orcl2> select t.table_name, s.partition_name, s.tablespace_name
from user_segments s, user_tables t where s.segment_name = t.table_name
and t.partitioned ='YES'
order by 1,2;
TABLE_NAME PARTITION_NAME TABLESPACE
------------------------------ ------------------------------ ----------
RANGE_SALES SALES_Q1_1998 USERDATA
RANGE_SALES SALES_Q1_1999 USERDATA
RANGE_SALES SALES_Q2_1998 USERDATA
RANGE_SALES SALES_Q2_1999 USERDATA
RANGE_SALES SALES_Q3_1998 USERDATA
RANGE_SALES SALES_Q3_1999 USERDATA
RANGE_SALES SALES_Q4_1998 USERDATA
RANGE_SALES SALES_Q4_1999 USERDATA
RANGE_SALES SALES_Q4_2000 USERDATA
USER01@orcl2> select table_name, partitioning_type, subpartitioning_type, partition_count
from user_part_tables;
TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT
------------------------------ --------- --------- ---------------
RANGE_SALES RANGE NONE 9
-- user_tab_partitions 에서도 확인 가능
USER01@orcl2> select * from range_sales where time_id = '01-JAN-98';
USER01@orcl2> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +predicate +partition'));
USER01@orcl2> save xplan_p
USER01@orcl2> @xplan_p
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID da32j95h0cbrx, child number 0
-------------------------------------
select * from range_sales where time_id = '01-JAN-98'
Plan hash value: 656747272
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 8 |
| 1 | PARTITION RANGE SINGLE| | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 8 |
|* 2 | TABLE ACCESS FULL | RANGE_SALES | 1 | 1 | KEY | KEY | 1 |00:00:00.01 | 8 |
----------------------------------------------------------------------------------------------------------------
-- 하나의 파티션만(PARTITION RANGE SINGLE) 풀 액세스를 진행
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"='01-JAN-98')
Note
-----
- dynamic sampling used for this statement (level=2)
USER01@orcl2> select * from range_sales;
USER01@orcl2> @xplan_p
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fdxdc4f3nvagv, child number 0
-------------------------------------
select * from range_sales
Plan hash value: 3403784322
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 8 |00:00:00.01 | 40 |
| 1 | PARTITION RANGE ALL| | 1 | 1 | 1 | 9 | 8 |00:00:00.01 | 40 |
| 2 | TABLE ACCESS FULL | RANGE_SALES | 9 | 1 | 1 | 9 | 8 |00:00:00.01 | 40 |
-------------------------------------------------------------------------------------------------------------
-- 모든 파티션(PARTITION RANGE ALL) 풀 액세스를 진행
Note
-----
- dynamic sampling used for this statement (level=2)
USER01@orcl2> select * from range_sales where cust_id = 12345;
-- 없는 데이터
USER01@orcl2> @xplan_p
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4pvp68rmjmynk, child number 0
-------------------------------------
select * from range_sales where cust_id = 12345
Plan hash value: 3403784322
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.01 | 39 |
| 1 | PARTITION RANGE ALL| | 1 | 1 | 1 | 9 | 0 |00:00:00.01 | 39 |
|* 2 | TABLE ACCESS FULL | RANGE_SALES | 9 | 1 | 1 | 9 | 0 |00:00:00.01 | 39 |
-------------------------------------------------------------------------------------------------------------
-- 모든 파티션(PARTITION RANGE ALL) 풀 액세스를 진행
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_ID"=12345)
Note
-----
- dynamic sampling used for this statement (level=2)
<<파티션 프루닝>>
-- PARTITION RANGE ALL
--PARTITION RANGE SINGLE
====================================================================================================
- hash 파티셔닝
- hash function 을 이용한 파티셔닝
- 인덱스 없이도, 빠른 검색이 가능(equal)
- non equal 일 경우 범위 검색이기 때문에 큰 소용이 없다.
- 데이터가 균등하게 들어가야할때 사용
- 해쉬함수를 이용해서 파티션 갯수만 정해주면 오라클 서버가 알아서 데이터를 분배하는 방식으로 저장
<<Hash partition>>
USER01@orcl2> create tablespace part_tbs1
datafile '/u01/app/oracle/orcl2/part_tbs1.dbf' size 100m autoextend on ;
Tablespace created.
USER01@orcl2> create tablespace part_tbs2
datafile '/u01/app/oracle/orcl2/part_tbs2.dbf' size 100m autoextend on ;
Tablespace created.
USER01@orcl2> create tablespace part_tbs3
datafile '/u01/app/oracle/orcl2/part_tbs3.dbf' size 100m autoextend on ;
Tablespace created.
USER01@orcl2> create tablespace part_tbs4
datafile '/u01/app/oracle/orcl2/part_tbs4.dbf' size 100m autoextend on ;
Tablespace created.
USER01@orcl2> create table cust_hash (cust_id number, cust_city varchar2(30) )
partition by hash(cust_city)
partitions 4
store in (part_tbs1 , part_tbs2 , part_tbs3 , part_tbs4 );
Tablespace created.
USER01@orcl2> select count(*) from cust_hash;
no rows selected
USER01@orcl2> insert into cust_hash
select cust_id, cust_city
from customers;
USER01@orcl2> select count(*) from cust_hash;
COUNT(*)
----------
55500
USER01@orcl2> select owner, segment_name, partition_name, segment_type, segment_subtype, tablespace_name
from dba_segments
where segment_name = 'CUST_HASH';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SEGMENT_SU TABLESPACE
-------- --------------- ------------------------------ ---------------- ---------- ----------
USER01 CUST_HASH SYS_P21 TABLE PARTITION ASSM PART_TBS1
USER01 CUST_HASH SYS_P22 TABLE PARTITION ASSM PART_TBS2
USER01 CUST_HASH SYS_P23 TABLE PARTITION ASSM PART_TBS3
USER01 CUST_HASH SYS_P24 TABLE PARTITION ASSM PART_TBS4
USER01@orcl2> select t.table_name, s.partition_name, s.tablespace_name
from user_segments s, user_tables t where s.segment_name = t.table_name
and t.partitioned ='YES'
order by 1,2;
TABLE_NAME PARTITION_NAME TABLESPACE
------------------------------ ------------------------------ ----------
CUST_HASH SYS_P21 PART_TBS1
CUST_HASH SYS_P22 PART_TBS2
CUST_HASH SYS_P23 PART_TBS3
CUST_HASH SYS_P24 PART_TBS4
RANGE_SALES SALES_Q1_1998 USERDATA
RANGE_SALES SALES_Q1_1999 USERDATA
RANGE_SALES SALES_Q2_1998 USERDATA
RANGE_SALES SALES_Q2_1999 USERDATA
RANGE_SALES SALES_Q3_1998 USERDATA
RANGE_SALES SALES_Q3_1999 USERDATA
RANGE_SALES SALES_Q4_1998 USERDATA
RANGE_SALES SALES_Q4_1999 USERDATA
RANGE_SALES SALES_Q4_2000 USERDATA
USER01@orcl2> select table_name, partitioning_type, subpartitioning_type, partition_count
from user_part_tables;
TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT
------------------------------ --------- --------- ---------------
CUST_HASH HASH NONE 4
RANGE_SALES RANGE NONE 9
-- 파티션 명을 지정하는 방법
USER01@orcl2> select count(*) from cust_hash partition(sys_p21);
COUNT(*)
----------
13691
USER01@orcl2> select count(*) from cust_hash partition(sys_p22);
COUNT(*)
----------
13173
USER01@orcl2> select count(*) from cust_hash partition(sys_p23);
COUNT(*)
----------
12678
USER01@orcl2> select count(*) from cust_hash partition(sys_p24);
COUNT(*)
----------
15958
-- 다 더하면 55500
USER01@orcl2> select * from cust_hash partition(sys_p24) where rownum<5;
CUST_ID CUST_CITY
---------- ------------------------------
5920 Frederick
9475 Montara
20142 Helmond
27253 Molino
USER01@orcl2> select * from cust_hash partition for (5920);
-- 해당 파티션 키가 들어있는 파티션을 보여달라는 뜻.
USER01@orcl2> select * from cust_hash where cust_id = 5920;
CUST_ID CUST_CITY
---------- ------------------------------
5920 Frederick
USER01@orcl2> @xplan_p
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 85bwwfyn9wgxq, child number 0
-------------------------------------
select * from cust_hash where cust_id = 5920
Plan hash value: 541585916
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 184 |
| 1 | PARTITION HASH ALL| | 1 | 2 | 1 | 4 | 1 |00:00:00.01 | 184 |
|* 2 | TABLE ACCESS FULL| CUST_HASH | 4 | 2 | 1 | 4 | 1 |00:00:00.01 | 184 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CUST_ID"=5920)
Note
-----
- dynamic sampling used for this statement (level=2)
- list 파티셔닝
- composite 파티셔닝 6가지
- range-hash
- range-list
- range-range
- list-hash
- list_list
- list_range
- <hash가 앞에 오면 아무런 장점이 없다.>
Local Index
- 파티션 키 (P.KEY) 값이 파티션 테이블과 인덱스가 일치한다.
- 관리할때 편하다. 하지만 하나의 특정 파티션 키만 사용할 것이 아니기때문에, Global Index를 사용한다.
Global Index
- 파티션 키 (P.KEY) 값이 파티션 테이블과 인덱스가 일치하지 않는다.
- 테이블과 같은 칼럼으로 파티션 되지만, 그 범위가 틀리거나 혹은 다른 칼럼으로 파티션이 이루어지는 경우
OLTP
- tx, IRS
OLAP
- Full, Parallel(병렬)
/*
다음의 조건에 만족하는 Partitioned Table을 생성합니다.
Table Owner : SH
Table Name : SALES_INTERVAL
(PROD_ID NUMBER
CUST_ID NUMBER
TIME_ID DATE
CHANNEL_ID NUMBER
PROMO_ID NUMBER
QUANTITY_SOLD NUMBER(10,2)
AMOUNT_SOLD NUMBER(10,2)
Partition Method : Interval Partitioning
Partition Column : TIME_ID
Interval : 1 Month
Initial Partition : BEFORE_01_2013 VALUES LESS THAN (to_date('01-01-2013','dd-mm-yyyy'))
Tablespace : PART_TBS1, PART_TBS2, PART_TBS3, PART_TBS4
*/
[orcl2:sqlt]$ sqlplus /as sysdba
SYS@orcl2> conn / as sysdba
SYS@orcl2> create tablespace part_tbs1
datafile '/u01/app/oracle/orcl2/part_tbs1.dbf' size 100m autoextend on ;
SYS@orcl2> create tablespace part_tbs2
datafile '/u01/app/oracle/orcl2/part_tbs2.dbf' size 100m autoextend on ;
SYS@orcl2> create tablespace part_tbs3
datafile '/u01/app/oracle/orcl2/part_tbs3.dbf' size 100m autoextend on ;
SYS@orcl2> create tablespace part_tbs4
datafile '/u01/app/oracle/orcl2/part_tbs4.dbf' size 100m autoextend on ;
SH@orcl2> create table sales_interval (
prod_id number,
cust_id number,
time_id date,
channel_id number,
promo_id number,
quantity_sold number(10,2),
amount_sold number(10,2))
partition by range(time_id)
interval(numtoyminterval(1,'month')) store in (part_tbs1, part_tbs2, part_tbs3, part_tbs4)
(partition before_01_2013 values less than (to_date('01-01-2013','dd-mm-yyyy')) tablespace part_tbs1);
Table created.
SH@orcl2>
insert into sales_interval
values (10,68060,to_date('09-dec-2012','dd-mon-yyyy'),3,9999,28,4900) ;
SH@orcl2>
insert into sales_interval
values (11160,17450,to_date('15-dec-2012','dd-mon-yyyy'),2,9999,19,798) ;
SH@orcl2>
insert into sales_interval
values (1340,33710,to_date('31-dec-2012','dd-mon-yyyy'),3,9999,16,1264) ;
SH@orcl2>
insert into sales_interval
values (25270,65880,to_date('01-jan-2013','dd-mon-yyyy'),1,9999,5,210) ;
SH@orcl2>
insert into sales_interval
values (1615,73480,to_date('05-jan-2013','dd-mon-yyyy'),5,9999,8,96) ;
SH@orcl2>
insert into sales_interval
values (1900,84910,to_date('06-mar-2013','dd-mon-yyyy'),2,9999,42,378) ;
SH@orcl2>
insert into sales_interval
values (8085,37900,to_date('30-apr-2013','dd-mon-yyyy'),2,9999,1,68) ;
SH@orcl2>
insert into sales_interval
values (755,26590,to_date('01-may-2013','dd-mon-yyyy'),1,9999,11,132) ;
SH@orcl2> commit ;
SH@orcl2> select partition_name, high_value from user_tab_partitions
where table_name='SALES_INTERVAL';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
BEFORE_01_2013 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P21 TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P22 TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P23 TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P24 TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
5 rows selected.
====================================================================================================
Partitioned Table과 Non Partitioned Table의 plan 비교
===================================================================================================
SH@orcl2> select partition_name, high_value from user_tab_partitions
where table_name='SALES'
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SALES_1995 TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_1996 TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_H1_1997 TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_H2_1997 TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_1998 TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1998 TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1998 TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1998 TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_1999 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_1999 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_1999 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_1999 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2000 TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2000 TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2000 TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2000 TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2001 TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2001 TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2001 TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2001 TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2002 TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2002 TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2002 TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2002 TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q1_2003 TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q2_2003 TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q3_2003 TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SALES_Q4_2003 TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
28 rows selected.
SH@orcl2> create table sales_no
tablespace part_tbs4
as
select * from sales;
SH@orcl2> alter session set statistics_level=all;
SH@orcl2> select * from sales where
time_id between to_date('1998-04-15','yyyy-mm-dd')
and to_date('1998-07-15','yyyy-mm-dd');
SH@orcl2> @xplan
Plan hash value: 279964487
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38035 |00:00:00.52 | 553 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 58529 | 38035 |00:00:00.52 | 553 |
|* 2 | TABLE ACCESS FULL | SALES | 2 | 58529 | 38035 |00:00:00.19 | 553 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("TIME_ID"<=TO_DATE(' 1998-07-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "TIME_ID">=TO_DATE(' 1998-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
SH@orcl2> select * from sales_no
where time_id between to_date('1998-04-15','yyyy-mm-dd')
and to_date('1998-07-15','yyyy-mm-dd')
SH@orcl2> @xplan
Plan hash value: 2530597758
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 38035 |00:00:00.23 | 4830 | 4446 |
|* 1 | TABLE ACCESS FULL| SALES_NP | 1 | 21049 | 38035 |00:00:00.23 | 4830 | 4446 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("TIME_ID">=TO_DATE(' 1998-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"TIME_ID"<=TO_DATE(' 1998-07-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Note
-----
- dynamic sampling used for this statement (level=2)
'DATABASE(oracleDB 11g) > Tuning' 카테고리의 다른 글
| [SQL Tuning]바인드 변수의 사용 (0) | 2019.05.20 |
|---|---|
| [SQL Tuning]옵티마이저 통계 (0) | 2019.05.20 |
| [SQL Tuning]Subquery operation(서브쿼리 오퍼레이션) (0) | 2019.05.20 |
| [SQL Tuning]Join operation(조인 오퍼레이션) (0) | 2019.05.20 |
| [SQL Tuning]인덱스(Index)의 기초 (0) | 2019.05.20 |