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:
- Retrieve the partition names (which is often not allowed)
- 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);