Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/1.test.sql

Last active March 17, 2025 02:03
Show Gist options
  • Save xtender/8ef6140f4f47139591afcde4a0f09a66 to your computer and use it in GitHub Desktop.
Save xtender/8ef6140f4f47139591afcde4a0f09a66 to your computer and use it in GitHub Desktop.
Download ZIP
Partition pruning with global indexes
Raw
1.test.sql
set echo on feed on serverout off;
create table tpart (pkey int, notkey int,padding varchar2(4000))
partition by range(pkey)
(
partition p1 values less than (2)
,partition p2 values less than (3)
,partition p3 values less than (4)
);
insert into tpart(pkey,notkey,padding)
select ceil(n/1000) pkey, n, rpad('x',4000,'x') from xmltable('1 to 3000' columns n int path '.');
commit;
select pkey,min(notkey),max(notkey) from tpart group by pkey order by 1;
call dbms_stats.gather_table_stats('','tpart');
alter session set statistics_level=all;
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;
select * from dbms_xplan.display_cursor('','','allstats last +predicate');
create index ix_tpart on tpart(notkey);
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;
select * from dbms_xplan.display_cursor('','','allstats last +predicate');
select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey;
select * from dbms_xplan.display_cursor('','','allstats last +predicate');
select count(*) from tpart where notkey between 2500 and 2600 and pkey=2;
select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');
select count(*) from tpart where notkey between 2500 and 2600 and pkey=3;
select * from dbms_xplan.display_cursor('','','allstats last +predicate');
Raw
2.output.sql
SQL> set echo on feed on serverout off;
SQL> create table tpart (pkey int, notkey int,padding varchar2(4000))
2 partition by range(pkey)
3 (
4 partition p1 values less than (2)
5 ,partition p2 values less than (3)
6 ,partition p3 values less than (4)
7 );
Table created.
SQL> insert into tpart(pkey,notkey,padding)
2 select ceil(n/1000) pkey, n, rpad('x',4000,'x') from xmltable('1 to 3000' columns n int path '.');
3000 rows created.
SQL> commit;
Commit complete.
SQL> select pkey,min(notkey),max(notkey) from tpart group by pkey order by 1;
PKEY MIN(NOTKEY) MAX(NOTKEY)
---------- ----------- -----------
1 1 1000
2 1001 2000
3 2001 3000
3 rows selected.
SQL> call dbms_stats.gather_table_stats('','tpart');
Call completed.
SQL> alter session set statistics_level=all;
Session altered.
SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1
Plan hash value: 3052279832
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1007 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1007 |
| 2 | PARTITION RANGE SINGLE| | 1 | 1 | 0 |00:00:00.01 | 1007 |
|* 3 | TABLE ACCESS FULL | TPART | 1 | 1 | 0 |00:00:00.01 | 1007 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("NOTKEY">=2500 AND "NOTKEY"<=2600 AND "PKEY"=1))
20 rows selected.
SQL> create index ix_tpart on tpart(notkey);
Index created.
SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=1;
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
SQL_ID 0qzh3zxgpc65z, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=1
Plan hash value: 494535298
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART | 1 | 1 | 0 |00:00:00.01 | 2 | 1 |
|* 3 | INDEX RANGE SCAN | IX_TPART | 1 | 102 | 101 |00:00:00.01 | 2 | 1 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PKEY"=1)
3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)
21 rows selected.
SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=pkey/pkey;
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 0q59p4akv4cm0, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and
pkey=pkey/pkey
Plan hash value: 4115825992
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 103 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 103 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART | 1 | 34 | 0 |00:00:00.01 | 103 |
|* 3 | INDEX RANGE SCAN | IX_TPART | 1 | 102 | 101 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PKEY"="PKEY"/"PKEY")
3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)
22 rows selected.
SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=2;
COUNT(*)
----------
0
1 row selected.
SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate +partition');
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID fw7yx554pvv4n, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=2
Plan hash value: 494535298
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 2 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART | 1 | 1 | 2 | 2 | 0 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | IX_TPART | 1 | 102 | | | 101 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PKEY"=2)
3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)
21 rows selected.
SQL> select count(*) from tpart where notkey between 2500 and 2600 and pkey=3;
COUNT(*)
----------
101
1 row selected.
SQL> select * from dbms_xplan.display_cursor('','','allstats last +predicate');
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID 1gup2pp2qvbj2, child number 0
-------------------------------------
select count(*) from tpart where notkey between 2500 and 2600 and pkey=3
Plan hash value: 494535298
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 103 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 103 |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TPART | 1 | 102 | 101 |00:00:00.01 | 103 |
|* 3 | INDEX RANGE SCAN | IX_TPART | 1 | 102 | 101 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PKEY"=3)
3 - access("NOTKEY">=2500 AND "NOTKEY"<=2600)
21 rows selected.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment