Partitioning Table 100 Queries

1–20: Creating Partitioned Tables

-- 1. Range partition on DATE column

CREATE TABLE sales_range (

  sale_id NUMBER, sale_date DATE, amount NUMBER

)

PARTITION BY RANGE (sale_date) (

  PARTITION p_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY')),

  PARTITION p_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY')),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 2. List partition on VARCHAR2 column

CREATE TABLE employees_list (

  emp_id NUMBER, emp_name VARCHAR2(100), region VARCHAR2(20)

)

PARTITION BY LIST (region) (

  PARTITION p_north VALUES ('NORTH'),

  PARTITION p_south VALUES ('SOUTH'),

  PARTITION p_west VALUES ('WEST')

);

 

-- 3. Hash partition on NUMBER column

CREATE TABLE employees_hash (

  emp_id NUMBER, emp_name VARCHAR2(100)

)

PARTITION BY HASH (emp_id) PARTITIONS 8;

 

-- 4. Composite Range-List partition

CREATE TABLE orders_comp (

  order_id NUMBER, order_date DATE, region VARCHAR2(20)

)

PARTITION BY RANGE (order_date)

SUBPARTITION BY LIST (region) (

  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')) (

    SUBPARTITION p_2023_north VALUES ('NORTH'),

    SUBPARTITION p_2023_south VALUES ('SOUTH')

  ),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 5. Interval partition by month (11g+)

CREATE TABLE sales_interval (

  sale_id NUMBER, sale_date DATE, amount NUMBER

)

PARTITION BY RANGE (sale_date) INTERVAL (NUMTODSINTERVAL(1, 'MONTH')) (

  PARTITION p_before_2020 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY'))

);

 

-- 6. Reference partitioning (master-detail)

CREATE TABLE departments (

  dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(100)

) PARTITION BY HASH (dept_id) PARTITIONS 4;

 

CREATE TABLE employees_ref (

  emp_id NUMBER, emp_name VARCHAR2(100), dept_id NUMBER

)

PARTITION BY REFERENCE (dept_id)

REFERENCES departments(dept_id);

 

-- 7. Composite Range-Hash partitioning

CREATE TABLE transactions (

  txn_id NUMBER, txn_date DATE, cust_id NUMBER

)

PARTITION BY RANGE (txn_date)

SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4 (

  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 8. Range partition on NUMBER column

CREATE TABLE inventory (

  item_id NUMBER, category_id NUMBER, qty NUMBER

)

PARTITION BY RANGE (category_id) (

  PARTITION p_cat1 VALUES LESS THAN (100),

  PARTITION p_cat2 VALUES LESS THAN (200),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 9. Hash partition on virtual column

CREATE TABLE virtual_hash (

  id NUMBER, created_date DATE,

  created_year NUMBER GENERATED ALWAYS AS (EXTRACT(YEAR FROM created_date)) VIRTUAL

)

PARTITION BY HASH (created_year) PARTITIONS 4;

 

-- 10. List partition on NUMBER column

CREATE TABLE departments_list (

  dept_id NUMBER, dept_name VARCHAR2(100), region_id NUMBER

)

PARTITION BY LIST (region_id) (

  PARTITION p_reg1 VALUES (1, 2, 3),

  PARTITION p_reg2 VALUES (4, 5, 6)

);

 

-- 11. Composite List-Hash partition

CREATE TABLE emp_list_hash (

  emp_id NUMBER, region VARCHAR2(20)

)

PARTITION BY LIST (region)

SUBPARTITION BY HASH (emp_id) SUBPARTITIONS 4 (

  PARTITION p_north VALUES ('NORTH'),

  PARTITION p_south VALUES ('SOUTH')

);

 

-- 12. Create partitioned index on a partitioned table

CREATE INDEX idx_sales_amount ON sales_range(amount) LOCAL;

 

-- 13. Create global partitioned index on a partitioned table

CREATE INDEX idx_sales_amount_global ON sales_range(amount)

GLOBAL PARTITION BY HASH (amount) PARTITIONS 4;

 

-- 14. Create interval partition on TIMESTAMP column

CREATE TABLE log_data (

  log_id NUMBER, log_time TIMESTAMP, message VARCHAR2(4000)

)

PARTITION BY RANGE (log_time) INTERVAL (NUMTODSINTERVAL(1, 'DAY')) (

  PARTITION p_old VALUES LESS THAN (TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))

);

 

-- 15. Partition by composite key (RANGE on DATE + LIST on REGION)

CREATE TABLE sales_comp2 (

  sale_id NUMBER, sale_date DATE, region VARCHAR2(20)

)

PARTITION BY RANGE (sale_date)

SUBPARTITION BY LIST (region) (

  PARTITION p_q1 VALUES LESS THAN (TO_DATE('01-APR-2024','DD-MON-YYYY')) (

    SUBPARTITION p_q1_north VALUES ('NORTH'),

    SUBPARTITION p_q1_south VALUES ('SOUTH')

  ),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 16. Create table with ROW MOVEMENT enabled

CREATE TABLE employees_rowmove (

  emp_id NUMBER, dept_id NUMBER

)

PARTITION BY RANGE (dept_id) (

  PARTITION p_10 VALUES LESS THAN (20),

  PARTITION p_20 VALUES LESS THAN (MAXVALUE)

)

ROW MOVEMENT;

 

-- 17. Reference partition with ON DELETE CASCADE

CREATE TABLE departments_ref (

  dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(100)

) PARTITION BY HASH (dept_id) PARTITIONS 2;

 

CREATE TABLE employees_ref2 (

  emp_id NUMBER, emp_name VARCHAR2(100), dept_id NUMBER

)

PARTITION BY REFERENCE (dept_id) ON DELETE CASCADE

REFERENCES departments_ref(dept_id);

 

-- 18. Create partitioned external table

CREATE TABLE ext_sales (

  sale_id NUMBER, sale_date DATE, amount NUMBER

)

ORGANIZATION EXTERNAL (

  TYPE ORACLE_LOADER

  DEFAULT DIRECTORY ext_dir

  ACCESS PARAMETERS (

    RECORDS DELIMITED BY NEWLINE

    FIELDS TERMINATED BY ','

    MISSING FIELD VALUES ARE NULL

    (sale_id, sale_date DATE 'YYYY-MM-DD', amount)

  )

  LOCATION ('sales_data.csv')

)

PARTITION BY RANGE (sale_date) (

  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

 

-- 19. Partitioned table with encryption

CREATE TABLE confidential_data (

  id NUMBER, sensitive_info VARCHAR2(4000)

)

PARTITION BY HASH (id) PARTITIONS 4

ENCRYPT;

 

-- 20. Create partitioned global temporary table

CREATE GLOBAL TEMPORARY TABLE temp_sales (

  sale_id NUMBER, sale_date DATE, amount NUMBER

)

ON COMMIT PRESERVE ROWS

PARTITION BY RANGE (sale_date) (

  PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

  PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

________________________________________

21–40: Altering Partitions

-- 21. Add partition

ALTER TABLE sales_range ADD PARTITION p_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'));

 

-- 22. Drop partition

ALTER TABLE sales_range DROP PARTITION p_2020;

 

-- 23. Split partition

ALTER TABLE sales_range SPLIT PARTITION p_2021 AT (TO_DATE('01-JUL-2021','DD-MON-YYYY'))

INTO (PARTITION p_2021_h1, PARTITION p_2021_h2);

 

-- 24. Merge partitions

ALTER TABLE sales_range MERGE PARTITIONS p_2021_h1, p_2021_h2 INTO PARTITION p_2021;

 

-- 25. Truncate partition

ALTER TABLE sales_range TRUNCATE PARTITION p_2021;

 

-- 26. Rename partition (Oracle 12c+)

ALTER TABLE sales_range RENAME PARTITION p_2021 TO p_2021_renamed;

 

-- 27. Exchange partition with table without validation

CREATE TABLE old_sales AS SELECT * FROM sales_range WHERE sale_date < TO_DATE('01-JAN-2021','DD-MON-YYYY');

ALTER TABLE sales_range EXCHANGE PARTITION p_2020 WITH TABLE old_sales WITHOUT VALIDATION;

 

-- 28. Exchange partition with validation

ALTER TABLE sales_range EXCHANGE PARTITION p_2021 WITH TABLE old_sales VALIDATION;

 

-- 29. Move partition to different tablespace

ALTER TABLE sales_range MOVE PARTITION p_2021 TABLESPACE sales_ts_2;

 

-- 30. Modify partition compression

ALTER TABLE sales_range MODIFY PARTITION p_2021 COMPRESS FOR OLTP;

 

-- 31. Enable ROW MOVEMENT (required for some partition operations)

ALTER TABLE sales_range ENABLE ROW MOVEMENT;

 

-- 32. Coalesce hash partitions

ALTER TABLE employees_hash COALESCE PARTITION 8;

 

-- 33. Drop subpartition

ALTER TABLE orders_comp DROP SUBPARTITION p_2023_north;

 

-- 34. Add subpartition

ALTER TABLE orders_comp ADD SUBPARTITION p_2024_north VALUES ('NORTH');

 

-- 35. Modify partition to noclobber compression

ALTER TABLE sales_range MODIFY PARTITION p_2021 NOCOMPRESS;

 

-- 36. Mark index partition unusable

ALTER INDEX idx_sales_amount LOCAL PARTITION p_2021 UNUSABLE;

 

-- 37. Rebuild index partition

ALTER INDEX idx_sales_amount LOCAL PARTITION p_2021 REBUILD;

 

-- 38. Modify partition high value

ALTER TABLE sales_range MODIFY PARTITION p_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022','DD-MON-YYYY'));

 

-- 39. Exchange partition with truncate

ALTER TABLE sales_range EXCHANGE PARTITION p_2021 WITH TABLE old_sales WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

 

-- 40. Disable partition

ALTER TABLE sales_range MODIFY PARTITION p_2021 UNUSABLE;

 

41–60: Partition Metadata and Statistics

-- 41. List partitions of a table

SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'SALES_RANGE';

 

-- 42. Number of rows per partition

SELECT partition_name, num_rows FROM user_tab_partitions WHERE table_name = 'SALES_RANGE';

 

-- 43. List subpartitions

SELECT partition_name, subpartition_name FROM user_tab_subpartitions WHERE table_name = 'ORDERS_COMP';

 

-- 44. Check partition key columns

SELECT column_name FROM user_part_key_columns WHERE name = 'SALES_RANGE';

 

-- 45. Show partitioning type

SELECT partitioning_type, subpartitioning_type FROM user_part_tables WHERE table_name = 'SALES_RANGE';

 

-- 46. Partition statistics last analyzed

SELECT partition_name, last_analyzed FROM user_tab_partitions WHERE table_name = 'SALES_RANGE';

 

-- 47. Find tables with partitioning enabled

SELECT table_name FROM user_part_tables;

 

-- 48. Show global and local indexes

SELECT index_name, partitioned FROM user_indexes WHERE table_name = 'SALES_RANGE';

 

-- 49. Show partition statistics with stale stats flag

SELECT partition_name, stale_stats FROM user_tab_partitions WHERE table_name = 'SALES_RANGE';

 

-- 50. Query partition_key_value for a specific row

SELECT sale_id, sale_date, partition_name

FROM sales_range PARTITION FOR (TO_DATE('15-JUN-2021','DD-MON-YYYY'));

 

61–80: Data Manipulation on Partitions

-- 51. Insert into specific partition

INSERT INTO sales_range PARTITION (p_2021) VALUES (1001, DATE '2021-06-15', 500);

 

-- 52. Update in specific partition

UPDATE sales_range PARTITION (p_2021) SET amount = amount * 1.05 WHERE sale_id = 1001;

 

-- 53. Delete from specific partition

DELETE FROM sales_range PARTITION (p_2021) WHERE sale_id = 1001;

 

-- 54. Bulk insert via partition exchange

CREATE TABLE new_sales_data AS SELECT * FROM sales_range WHERE sale_date BETWEEN TO_DATE('01-JAN-2022','DD-MON-YYYY') AND TO_DATE('31-DEC-2022','DD-MON-YYYY');

ALTER TABLE sales_range EXCHANGE PARTITION p_2022 WITH TABLE new_sales_data WITHOUT VALIDATION;

 

-- 55. Partition pruning with date filter

SELECT * FROM sales_range WHERE sale_date BETWEEN DATE '2021-01-01' AND DATE '2021-12-31';

 

-- 56. Parallel DML on partitioned table

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(sales_range,4) */ INTO sales_range SELECT * FROM sales_staging;

 

-- 57. Merge data into partitioned table

MERGE INTO sales_range s

USING sales_updates u

ON (s.sale_id = u.sale_id)

WHEN MATCHED THEN UPDATE SET s.amount = u.amount

WHEN NOT MATCHED THEN INSERT (sale_id, sale_date, amount) VALUES (u.sale_id, u.sale_date, u.amount);

 

-- 58. Partition-wise joins (performance tip)

SELECT /*+ partition(s) partition(o) */

  s.sale_id, o.order_id

FROM sales_range s JOIN orders_comp o ON s.sale_id = o.order_id

WHERE s.sale_date = TO_DATE('15-JUN-2021','DD-MON-YYYY');

 

-- 59. Delete old partitions for archiving

ALTER TABLE sales_range DROP PARTITION p_2020;

 

-- 60. Insert partition data using dynamic partitioning

INSERT INTO sales_range VALUES (2000, TO_DATE('15-JUN-2024','DD-MON-YYYY'), 1000);

 

81–100: Advanced Partitioning and Utilities

-- 61. Gather stats on partition

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'SALES_RANGE', partname => 'P_2021');

 

-- 62. Enable parallel query on partitioned table

ALTER TABLE sales_range PARALLEL (DEGREE 8);

 

-- 63. Disable partition pruning in session

ALTER SESSION SET optimizer_dynamic_sampling = 0;

 

-- 64. Exchange partition with validation

ALTER TABLE sales_range EXCHANGE PARTITION p_2021 WITH TABLE old_sales VALIDATION;

 

-- 65. Using virtual columns for partitioning

CREATE TABLE sales_virtual (

  sale_id NUMBER,

  sale_date DATE,

  sale_year NUMBER GENERATED ALWAYS AS (EXTRACT(YEAR FROM sale_date)) VIRTUAL

)

PARTITION BY RANGE (sale_year) (

  PARTITION p_2020 VALUES LESS THAN (2021),

  PARTITION p_2021 VALUES LESS THAN (2022)

);

 

-- 66. Rename partitioned table

ALTER TABLE sales_range RENAME TO sales_range_new;

 

-- 67. Drop partitioned table including partitions

DROP TABLE sales_range PURGE;

 

-- 68. Partition pruning with bind variables

VARIABLE v_date DATE;

EXEC :v_date := TO_DATE('15-JUN-2021','DD-MON-YYYY');

SELECT * FROM sales_range WHERE sale_date = :v_date;

 

-- 69. Query user_tab_partitions for row count and size

SELECT partition_name, num_rows, blocks, empty_blocks

FROM user_tab_partitions WHERE table_name = 'SALES_RANGE';

 

-- 70. Use DBMS_REDEFINITION to redefine partitioned table online

BEGIN

  DBMS_REDEFINITION.START_REDEF_TABLE('USER','SALES_RANGE','SALES_RANGE_NEW');

  -- Additional steps for redefinition...

END;

/

 

-- 71. Create interval partition with global indexes

CREATE TABLE sales_interval2 (

  sale_id NUMBER,

  sale_date DATE,

  amount NUMBER

)

PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (

  PARTITION p_before_2020 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY'))

);

 

CREATE INDEX idx_sales_amount_global ON sales_interval2(amount)

GLOBAL;

 

-- 72. Enable flashback on partitioned table

ALTER TABLE sales_range ENABLE ROW MOVEMENT;

FLASHBACK TABLE sales_range TO BEFORE DROP;

 

-- 73. Split partition with UPDATE GLOBAL INDEXES

ALTER TABLE sales_range SPLIT PARTITION p_2021 AT (TO_DATE('01-JUL-2021','DD-MON-YYYY'))

INTO (PARTITION p_2021_h1, PARTITION p_2021_h2) UPDATE GLOBAL INDEXES;

 

-- 74. Exchange partition and truncate table in one step

ALTER TABLE sales_range EXCHANGE PARTITION p_2021 WITH TABLE old_sales WITHOUT VALIDATION UPDATE GLOBAL INDEXES;

 

-- 75. Creating partitioned materialized view

CREATE MATERIALIZED VIEW sales_mv

PARTITION BY RANGE (sale_date)

BUILD IMMEDIATE

AS SELECT * FROM sales_range;

 

-- 76. Create partitioned synonym (public synonym for partitioned table)

CREATE PUBLIC SYNONYM sales_syn FOR sales_range;

 

-- 77. Query partition pruning with explain plan

EXPLAIN PLAN FOR

SELECT * FROM sales_range WHERE sale_date = TO_DATE('15-JUN-2021','DD-MON-YYYY');

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

-- 78. Partition wise export with expdp

-- Command-line example (not SQL):

-- expdp user/pass directory=dp_dir dumpfile=sales_%U.dmp logfile=sales.log tables=sales_range partition=p_2021 parallel=4

 

-- 79. Partition wise import with impdp

-- Command-line example (not SQL):

-- impdp user/pass directory=dp_dir dumpfile=sales_%U.dmp logfile=sales_imp.log tables=sales_range parallel=4

 

-- 80. Use DBMS_PART to manage partitions programmatically

DECLARE

  part_count NUMBER;

BEGIN

  part_count := DBMS_PART.GET_NUM_PARTITIONS('SALES_RANGE');

  DBMS_OUTPUT.PUT_LINE('Number of partitions: ' || part_count);

END;

/

 

-- 81. Gather stats on subpartition

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'ORDERS_COMP', partname => 'P_2023', subpartname => 'P_2023_NORTH');

 

-- 82. Drop table partition preserving data

ALTER TABLE sales_range DROP PARTITION p_2020 UPDATE GLOBAL

INDEXES;

-- 83. Insert data with partition key value check

INSERT INTO sales_range VALUES (1002, TO_DATE('15-JUN-2021','DD-MON-YYYY'), 200);

-- 84. Using partition function in SQL query

SELECT sale_id FROM sales_range PARTITION FOR (TO_DATE('15-JUN-2021','DD-MON-YYYY'));

-- 85. Create index on subpartition

CREATE INDEX idx_orders_subp ON orders_comp SUBPARTITION p_2023_north (order_id);

-- 86. Drop subpartition with update global indexes

ALTER TABLE orders_comp DROP SUBPARTITION p_2023_north UPDATE GLOBAL INDEXES;

-- 87. Use partition pruning on HASH partition

SELECT * FROM employees_hash WHERE emp_id = 12345;

-- 88. Query data dictionary for global indexes

SELECT index_name FROM user_indexes WHERE global_stats = 'YES';

-- 89. Partition pruning with multiple keys

SELECT * FROM sales_range WHERE sale_date = TO_DATE('15-JUN-2021','DD-MON-YYYY') AND amount > 100;

-- 90. Enable monitoring on partitioned table

ALTER TABLE sales_range MONITORING;

-- 91. Disable monitoring on partitioned table

ALTER TABLE sales_range NOMONITORING;

-- 92. Add virtual column and partition by it

ALTER TABLE sales_range ADD (sale_month NUMBER GENERATED ALWAYS AS (EXTRACT(MONTH FROM sale_date)) VIRTUAL);

-- 93. Partition exchange with row movement enabled

ALTER TABLE sales_range ENABLE ROW MOVEMENT;

ALTER TABLE sales_range EXCHANGE PARTITION p_2021 WITH TABLE old_sales WITHOUT VALIDATION;

-- 94. Merge partitions with update global indexes

ALTER TABLE sales_range MERGE PARTITIONS p_2021, p_2022 UPDATE GLOBAL INDEXES;

-- 95. Create partitioned table with LOB columns

CREATE TABLE docs_partitioned (

doc_id NUMBER, doc_date DATE, doc_data CLOB

)

PARTITION BY RANGE (doc_date) (

PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

-- 96. Create partitioned table with XMLType column

CREATE TABLE xml_partitioned (

id NUMBER, created_date DATE, xml_doc XMLTYPE

)

PARTITION BY RANGE (created_date) (

PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),

PARTITION p_max VALUES LESS THAN (MAXVALUE)

);

-- 97. Compress partition with advanced compression

ALTER TABLE sales_range MODIFY PARTITION p_2021 COMPRESS FOR QUERY HIGH;

-- 98. Use parallel DDL for partition operations

ALTER TABLE sales_range SPLIT PARTITION p_2021 AT (TO_DATE('01-JUL-2021','DD-MON-YYYY')) PARALLEL;

-- 99. Drop global index on partitioned table

DROP INDEX idx_sales_amount_global;

-- 100. Rebuild partitioned table’s indexes online

ALTER INDEX idx_sales_amount REBUILD ONLINE;