Search notes:
Oracle: List partitioned tables
drop table tq84_list_partition purge;
create table tq84_list_partition (
id number,
txt varchar2(10),
sector varchar2(10),
--
constraint tq84_list_partition_pk primary key (id)
)
partition by list (sector) (
partition tq84_list_partition_east values ('East' ),
partition tq84_list_partition_west values ('West' ),
partition tq84_list_partition_north values ('North'),
partition tq84_list_partition_south values ('South', 'Center')
);
select partition_name from user_tab_partitions where table_name = 'TQ84_LIST_PARTITION';
--
-- TQ84_LIST_PARTITION_EAST
-- TQ84_LIST_PARTITION_NORTH
-- TQ84_LIST_PARTITION_SOUTH
-- TQ84_LIST_PARTITION_WEST
insert into tq84_list_partition values (1, 'eee', 'East');
insert into tq84_list_partition values (2, 'EEE', 'East');
insert into tq84_list_partition values (3, 'w' , 'West');
insert into tq84_list_partition values (4, 'ww' , 'West');
commit;
Execution plan: Accessing a single partition
If the criteria of a select stement permit it, the optimizer is able to figure out that only one partition needs to be selected from. The The corresponding
execution plan operation is
PARTITION LIST with the option
SINGLE:
explain plan for
select *
from
tq84_list_partition
where
sector = 'West';
select * from table(dbms_xplan.display);
--
-- -------------------------------------------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-- -------------------------------------------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 1 | 57 | 274 (0)| 00:00:01 | | |
-- | 1 | PARTITION LIST SINGLE| | 1 | 57 | 274 (0)| 00:00:01 | KEY | KEY |
-- | 2 | TABLE ACCESS FULL | TQ84_LIST_PARTITION | 1 | 57 | 274 (0)| 00:00:01 | 2 | 2 |
-- -------------------------------------------------------------------------------------------------------------