Wednesday, February 16, 2011

Oracle: Local partitioned index and partition pruning (Performance Tuning)



drop table tbl_txn purge;

create table tbl_txn (
txn_id number,
txn_type char(20),
col1 char(20),
col2 char(20),
col3 char(20),
txn_tms timestamp)
partition by range (txn_tms) (
partition p20110215 values less than (to_date('20110216','yyyymmdd')) tablespace parttbs,
partition p20110216 values less than (to_date('20110217','yyyymmdd')) tablespace parttbs,
partition p20110217 values less than (to_date('20110218','yyyymmdd')) tablespace parttbs,
partition p20110218 values less than (to_date('20110219','yyyymmdd')) tablespace parttbs,
partition p20110219 values less than (to_date('20110220','yyyymmdd')) tablespace parttbs)
/


declare
cdate date;
c number;
cchar char(20);
begin
cdate := to_date('20110215','yyyymmdd');
c := 1;
while cdate < to_date('20110220','yyyymmdd')
loop
select case mod(c,3) when 1 then 'A' when 2 then 'B' when 3 then 'C' else 'D' end
into cchar
from dual;
insert into tbl_txn values (c,cchar,'dummy','dummy','dummy',cdate);
if mod(c,1000) = 0 then
commit;
end if;
c := c+1;
cdate := cdate+(1/(60*60*24));
end loop;
end;
/

alter table tbl_txn add constraint tbl_txn_pk primary key (txn_id);

create index tbl_txn_n1 on tbl_txn(txn_type) local;

exec dbms_stats.gather_table_stats(null,'TBL_TXN')



SQL> drop table tbl_txn purge;

Table dropped.

Elapsed: 00:00:00.24
SQL>
SQL> create table tbl_txn (
2 txn_id number,
3 txn_type char(20),
4 col1 char(20),
5 col2 char(20),
6 col3 char(20),
7 txn_tms timestamp)
8 partition by range (txn_tms) (
9 partition p20110215 values less than (to_date('20110216','yyyymmdd')) tablespace parttbs,
10 partition p20110216 values less than (to_date('20110217','yyyymmdd')) tablespace parttbs,
11 partition p20110217 values less than (to_date('20110218','yyyymmdd')) tablespace parttbs,
12 partition p20110218 values less than (to_date('20110219','yyyymmdd')) tablespace parttbs,
13 partition p20110219 values less than (to_date('20110220','yyyymmdd')) tablespace parttbs)
14 /

Table created.

Elapsed: 00:00:00.06
SQL>
SQL>
SQL> declare
2 cdate date;
3 c number;
4 cchar char(20);
5 begin
6 cdate := to_date('20110215','yyyymmdd');
7 c := 1;
8 while cdate < to_date('20110220','yyyymmdd')
9 loop
10 select case mod(c,3) when 1 then 'A' when 2 then 'B' when 3 then 'C' else 'D' end
11 into cchar
12 from dual;
13 insert into tbl_txn values (c,cchar,'dummy','dummy','dummy',cdate);
14 if mod(c,1000) = 0 then
15 commit;
16 end if;
17 c := c+1;
18 cdate := cdate+(1/(60*60*24));
19 end loop;
20 end;
21 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:46.61
SQL>
SQL> alter table tbl_txn add constraint tbl_txn_pk primary key (txn_id);

Table altered.

Elapsed: 00:00:01.08
SQL>
SQL> create index tbl_txn_n1 on tbl_txn(txn_type) local;

Index created.

Elapsed: 00:00:03.48
SQL>
SQL> exec dbms_stats.gather_table_stats(null,'TBL_TXN')

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.07

SQL> select segment_name,partition_name,bytes from user_segments where segment_name='TBL_TXN';

SEGMENT_NAME PARTITION_NAME BYTES
-------------------- -------------------- ----------
TBL_TXN P20110215 10485760
TBL_TXN P20110216 10485760
TBL_TXN P20110217 10485760
TBL_TXN P20110218 10485760
TBL_TXN P20110219 10485760

Elapsed: 00:00:00.07

SQL> set lin 120
SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 820060006

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
---------------------------------------------------------------------------------------------------

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 898817563

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
-----------------------------------------------------------------------------------------------------

SQL> alter table tbl_txn modify (txn_tms not null);

Table altered.

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 820060006

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 5 | 5 |
---------------------------------------------------------------------------------------------------

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 898817563

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 579 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 174K| 5437K| 559 (2)| 00:00:07 | 4 | 5 |
-----------------------------------------------------------------------------------------------------

SQL> create index tbl_txn_n2 on tbl_txn(txn_tms,txn_type) local;

Index created.

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110219','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3005629620

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 110 (3)| 00:00:02 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 86400 | 2700K| 110 (3)| 00:00:02 | 5 | 5 |
------------------------------------------------------------------------------------------------------

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms >=to_date('20110218','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 2408720091

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 563 (6)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 563 (6)| 00:00:07 | | |
| 2 | PARTITION RANGE ITERATOR| | 174K| 5437K| 543 (3)| 00:00:07 | 4 | 5 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 174K| 5437K| 543 (3)| 00:00:07 | 4 | 5 |
--------------------------------------------------------------------------------------------------------



SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms < to_date('20110216','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 3005629620

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 120 (11)| 00:00:02 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 110 (3)| 00:00:02 | 1 | 1 |
| 3 | INDEX FAST FULL SCAN | TBL_TXN_N2 | 86400 | 2700K| 110 (3)| 00:00:02 | 1 | 1 |
------------------------------------------------------------------------------------------------------

SQL> drop index tbl_txn_n2;

Index dropped.

SQL> select txn_type, count(*) from tbl_txn
2 where txn_tms < to_date('20110216','yyyymmdd')
3 group by txn_type;

Execution Plan
----------------------------------------------------------
Plan hash value: 820060006

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 1 | HASH GROUP BY | | 3 | 96 | 291 (6)| 00:00:04 | | |
| 2 | PARTITION RANGE SINGLE| | 86400 | 2700K| 281 (2)| 00:00:04 | 1 | 1 |
| 3 | TABLE ACCESS FULL | TBL_TXN | 86400 | 2700K| 281 (2)| 00:00:04 | 1 | 1 |
---------------------------------------------------------------------------------------------------