List partitioned table
create table tq84_part_tab (
val number,
par varchar(1)
)
partition by list (par) (
partition tq84_part_a values ('A'),
partition tq84_part_b values ('B'),
partition tq84_part_c values ('C'),
partition tq84_part_d values ('D'),
partition tq84_part_e values ('E'),
partition tq84_part_f values ('F')
);
Number
When a partition can be identified uniquely, the partition number is the value of partition_start and partition_stop.
explain plan for
select
val
from
tq84_part_tab
where
par = 'D';
--
-- ---------------------------------------------------------------
-- | Id | Operation | Name | Pstart| Pstop |
-- ---------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | | |
-- | 1 | PARTITION LIST SINGLE| | 4 | 4 |
-- | 2 | TABLE ACCESS FULL | TQ84_PART_TAB | 4 | 4 |
-- ---------------------------------------------------------------
select *
from
user_tab_partitions
where
table_name = 'TQ84_PART_TAB' and
partition_position = 4;
From … to
Using between causes partition_start and partition_stop to be filled with the first and last partition number of the partitions that match the values in the between clause.
explain plan for
select
val
from
tq84_part_tab
where
par between 'B' and 'D';
select * from table(dbms_xplan.display(format => 'basic +partition'));
--
-- -----------------------------------------------------------------
-- | Id | Operation | Name | Pstart| Pstop |
-- -----------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | | |
-- | 1 | PARTITION LIST ITERATOR| | 2 | 4 |
-- | 2 | TABLE ACCESS FULL | TQ84_PART_TAB | 2 | 4 |
-- -----------------------------------------------------------------
KEY(I)
explain plan for
select
val
from
tq84_part_tab
where
par in ('B', 'D');
--
-- ---------------------------------------------------------------
-- | Id | Operation | Name | Pstart| Pstop |
-- ---------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | | |
-- | 1 | PARTITION LIST INLIST| |KEY(I) |KEY(I) |
-- | 2 | TABLE ACCESS FULL | TQ84_PART_TAB |KEY(I) |KEY(I) |
-- ---------------------------------------------------------------
Note, there is also KEY(SQ) and KEY(AP) where SQ stands for subquery, and AP for and pruning.
INVALID
There is no partition where par = 'X', therefore, the following statement fills INVALID into Pstart and Pstop.
explain plan for
select
val
from
tq84_part_tab
where
par = 'X';
select * from table(dbms_xplan.display(format => 'basic +partition'));
--
-- --------------------------------------------------------------
-- | Id | Operation | Name | Pstart| Pstop |
-- --------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | | |
-- | 1 | PARTITION LIST EMPTY| |INVALID|INVALID|
-- | 2 | TABLE ACCESS FULL | TQ84_PART_TAB |INVALID|INVALID|
-- --------------------------------------------------------------
Sub-partitioned table
drop table tq84_part_intv_rng purge;
create table tq84_part_intv_rng (
num number,
grp varchar2(1),
val number
)
partition by range (num) interval (100)
subpartition by list (grp)
subpartition template (
subpartition sub_part_a values ('A'),
subpartition sub_part_b values ('B'),
subpartition sub_part_c values ('C')
)
(
partition values less than ( 100 )
);
insert into tq84_part_intv_rng values (888, 'B', 42);
insert into tq84_part_intv_rng values (888, 'A', 99);
insert into tq84_part_intv_rng values (333, 'C', 78);
insert into tq84_part_intv_rng values (555, 'A', -1);
insert into tq84_part_intv_rng values (111, 'B', -1);
insert into tq84_part_intv_rng values ( 0, 'A', 0);
select
prt.high_value prt_hv,
sub.high_value sub_hv,
prt.partition_position prt_pos,
sub.subpartition_position sub_pos,
sub.subpartition_name,
sub.partition_name,
prt.subpartition_count,
prt.composite
from
user_tab_subpartitions sub join
user_tab_partitions prt on sub.partition_name = prt.partition_name
where
sub.table_name = 'TQ84_PART_INTV_RNG';
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 0 and
grp = 'A';
select * from table(dbms_xplan.display(format=>'basic +partition'));
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 0 and
grp = 'B';
select * from table(dbms_xplan.display(format=>'basic +partition'));
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 0 and
grp = 'C';
select * from table(dbms_xplan.display(format=>'basic +partition'));
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 111 and
grp = 'A';
select * from table(dbms_xplan.display(format=>'basic +partition'));
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 111 and
grp = 'B';
select * from table(dbms_xplan.display(format=>'basic +partition'));
explain plan for
select
val
from
tq84_part_intv_rng
where
num = 111 and
grp = 'C';
select * from table(dbms_xplan.display(format=>'basic +partition'));