Partitions in Oracle

Oracle has the concept of partitions: the idea that a set of records is stored in one partition, that is directly accessible. This avoids the situation that large segments of data must be investigated before a record is found.

The code to generate such table is:

CREATE TABLE "HR"."FCT_BLN_ALLOC_USAGE"
( "PROCESSING_DATE_SK" NUMBER,
"INCREMENT_OFFSET_SEC" NUMBER,
"ETL_REFRESH_DATE" DATE,
"USG_ID" VARCHAR2(50 CHAR),
"BLN_ID" VARCHAR2(50 CHAR),
"ALN_ID" VARCHAR2(50 CHAR),
"SOURCE_SYSTEM_ID" VARCHAR2(3 CHAR)
) PARTITION BY RANGE ("PROCESSING_DATE_SK") INTERVAL (1)
(PARTITION "P_FIRST" VALUES LESS THAN (1)) ;

Based on the values that we enter for PROCESSING_DATE_SK, the record is positioned in one partition or another.

We can derive the partition names from next query:

SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'FCT_BLN_ALLOC_USAGE'; 

The partition names can then be used in a sql that derives the records from a certain partitiopn:

select * from FCT_BLN_ALLOC_USAGE partition (SYS_P2932)

However, such action requires two steps:

1. Retrieve the partition names (which is often not allowed)
2. Use the partition name in a sql statement.

A more step is to retrieve the records in one statement. This looks like:

SELECT *
FROM FCT_BLN_ALLOC_USAGE PARTITION FOR (2);