SQL Script Files for Session# S307725 – Oracle Database 11g Best Practices for using Partitioning in HA Environments and VLDBs
-- Setting SQL*Plus environment variables
SET ECHO OFF
SET FEEDBACK ON
SET PAGES 1500
SET LINES 150
SET TIMING ON
SET TIME OFF
SET VERIFY OFF
COLtable_nameFOR A30
COL partition_name FOR A20
COL high_valueFOR A20
COL intervalFOR A30
BREAKON partition_name
-- Cleaning the schema before starting the demo
SET TERMOUT OFF
DROP PACKAGE MANAGE_HD_PACK;
DROP TABLE HISTORICAL_DATA_1 PURGE;
DROP TABLE HISTORICAL_DATA_2 PURGE;
DROP TABLE HISTORICAL_DATA_3 PURGE;
DROP TABLE INTERVAL_PARTITIONING_DEMO PURGE;
DROP TABLE SALES PURGE;
DROP TABLE SALES_SYSTEM PURGE;
DROP TABLE ORDER_ITEMS PURGE;
DROP TABLE ORDERS PURGE;
DROP TABLE REF_PARTITION_CHILD PURGE;
DROP TABLE REF_PARTITION_PARENT PURGE;
PURGE RECYCLEBIN;
SET TERMOUT ON
SET ECHO ON
------
-- Demo: IntervalRange Partitioning
------
-- Creating an interval range partitioned table
CREATE TABLE INTERVAL_PARTITIONING_DEMO
(serial_numNUMBER,
nameVARCHAR2(32))
PARTITION BY RANGE (serial_num) INTERVAL (10000)
STORE IN (PARTITIONS_TBS_1,PARTITIONS_TBS_2,PARTITIONS_TBS_3)
( PARTITION p1 VALUES LESS THAN (10001) TABLESPACE PARTITIONS_TBS_1,
PARTITION p2 VALUES LESS THAN (20001) TABLESPACE PARTITIONS_TBS_2,
PARTITION p3 VALUES LESS THAN (30001) TABLESPACE PARTITIONS_TBS_3);
-- Showing the table partitions created
SELECTpartition_position,partition_name,tablespace_name,high_value
FROMuser_tab_partitions
WHEREtable_name='INTERVAL_PARTITIONING_DEMO';
-- Populating the table with 50,000 rows
BEGIN
FOR i IN 1..50000 LOOP
INSERT INTO INTERVAL_PARTITIONING_DEMO
VALUES (i,SYS_GUID());
END LOOP;
COMMIT;
END;
/
-- Gathering statistics on the table
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'INTERVAL_PARTITIONING_DEMO')
-- Showing the table partitions created
SELECTpartition_position,partition_name,tablespace_name,high_value,last_analyzed,num_rows
FROMuser_tab_partitions
WHEREtable_name='INTERVAL_PARTITIONING_DEMO';
-- Querying the minimum and maximum values from a partition without knowing the partition name
SELECTMIN(serial_num),MAX(serial_num)
FROMINTERVAL_PARTITIONING_DEMO PARTITION FOR (40000);
-- Showing the execution plan for the above query
EXPLAIN PLAN FOR
SELECTMIN(serial_num),MAX(serial_num)
FROMINTERVAL_PARTITIONING_DEMO PARTITION FOR (40000);
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
-- Inserting 10,000 new values
INSERT /*+ APPEND */ INTO INTERVAL_PARTITIONING_DEMO NOLOGGING
SELECTserial_num+100000,name
FROMINTERVAL_PARTITIONING_DEMO
WHERE ROWNUM<=10000;
COMMIT;
-- Showing the table partitions created
SELECTpartition_position,partition_name,tablespace_name,high_value
FROMuser_tab_partitions
WHEREtable_name='INTERVAL_PARTITIONING_DEMO';
-- Inserting 30,000 new in-between values
INSERT /*+ APPEND */ INTO INTERVAL_PARTITIONING_DEMO NOLOGGING
SELECTserial_num+70000,name
FROMINTERVAL_PARTITIONING_DEMO
WHERE ROWNUM<=30000;
COMMIT;
-- Showing the table partitions created
SELECTpartition_position,partition_name,tablespace_name,high_value
FROMuser_tab_partitions
WHEREtable_name='INTERVAL_PARTITIONING_DEMO';
------
-- Demo: Interval with Virtual Partitioning
------
-- Creating the interval range partitioned SALES table with the PROD_TYPE virtual column
CREATE TABLE SALES
(
PROD_IDNUMBERNOT NULL,
CUST_IDNUMBER NOT NULL,
TIME_IDDATE NOT NULL,
CHANNEL_IDNUMBER NOT NULL,
PROMO_IDNUMBER NOT NULL,
QUANTITY_SOLDNUMBER(10,2)NOT NULL,
AMOUNT_SOLDNUMBER(10,2)NOT NULL,
PROD_TYPENUMBER(1)AS (TO_NUMBER(SUBSTR(TO_CHAR(PROD_ID),1,1)))
)
TABLESPACE USERS
PARTITION BY RANGE (PROD_TYPE) INTERVAL (1)
(PARTITION p1 VALUES LESS THAN (1));
-- Showing the table partitions created
SELECTpartition_position,partition_name,high_value
FROMuser_tab_partitions
WHEREtable_name='SALES';
-- Populating the table with 500,000 rows
INSERT /*+ APPEND */ INTO sales NOLOGGING (PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD)
SELECT PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD
FROMSH.SALES
WHEREROWNUM<=500000;
COMMIT;
-- Querying the table using the PROD_TYPE virtual column
SELECTNVL(TO_CHAR(prod_type),'Total:') AS PRODUCT_TYPE,COUNT(*)
FROMSALES
GROUP BY ROLLUP(prod_type);
-- Gathering statistics on the SALES table
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES')
-- Showing SALES table partitions and statistics
SELECTpartition_position,partition_name,high_value,last_analyzed,num_rows
FROMuser_tab_partitions
WHEREtable_name = 'SALES'
ORDER BY 1;
-- Querying a partition without knowing the partition name
SELECTCOUNT(*)
FROM sales PARTITION FOR (4);
-- Showing the execution plan for the above query
EXPLAIN PLAN FOR
SELECTCOUNT(*)
FROM sales PARTITION FOR (4);
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
------
-- Simple REF Partitioning
------
-- Creating MANAGE_HD_PACK.sql
------
-- MANAGE_HD_PACK
------
SET TERMOUT OFF
-- Creating package specification for managing HD tables/partitiones/statistics
CREATE OR REPLACE PACKAGE MANAGE_HD_PACK IS
-- Procedure for gathering statistics on HD tables
PROCEDURE GATHER_HD_PARTITION_STATS
(p_hd_levelIN NUMBER,
p_source_dateIN DATEDEFAULT SYSDATE-1,
p_target_dateIN DATE DEFAULT SYSDATE);
-- Function for converting HIGH_VALUE column to DATE
FUNCTIONCONVERT_HIGH_VALUE_TO_DATE
(p_partition_positionIN NUMBER,
p_table_nameIN VARCHAR2)
RETURNDATE;
END MANAGE_HD_PACK;
/
-- Creating package body for managing HD tables/partitiones/statistics
CREATE OR REPLACE PACKAGE BODY MANAGE_HD_PACK IS
-- Function for converting HIGH_VALUE column to DATE
FUNCTIONCONVERT_HIGH_VALUE_TO_DATE
(p_partition_positionIN NUMBER,
p_table_nameIN VARCHAR2)
RETURN DATE
IS
v_high_valueVARCHAR2(4000);
BEGIN
SELECThigh_value
INTOv_high_value
FROMuser_tab_partitions
WHEREpartition_position = p_partition_position
ANDtable_name = p_table_name;
RETURN(TO_DATE(SUBSTR(v_high_value,11,10),'YYYY-MM-DD'));
END CONVERT_HIGH_VALUE_TO_DATE;
-- Procedure for gathering statistics on HD tables
PROCEDURE GATHER_HD_PARTITION_STATS
(p_hd_levelIN NUMBER,
p_source_dateIN DATEDEFAULT SYSDATE-1,
p_target_dateIN DATE DEFAULT SYSDATE)
IS
v_source_partition_nameVARCHAR2(30);
v_target_partition_nameVARCHAR2(30);
BEGIN
-- Find source partition name (partition to gather statistics)
SELECTpartition_name
INTOv_source_partition_name
FROMuser_tab_partitions
WHEREtable_name = 'HISTORICAL_DATA_'||TO_CHAR(p_hd_level)
ANDTO_CHAR(MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name),'DD-MON-YY')=TO_CHAR(p_source_date,'DD-MON-YY');
-- Find target partition name (partition to copy statistics)
SELECTpartition_name
INTOv_target_partition_name
FROMuser_tab_partitions
WHEREtable_name = 'HISTORICAL_DATA_'||TO_CHAR(p_hd_level)
ANDTO_CHAR(MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name),'DD-MON-YY')=TO_CHAR(p_target_date,'DD-MON-YY');
-- Gather statistics on source partition
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'HISTORICAL_DATA_'||TO_CHAR(p_hd_level),partname=>v_source_partition_name,estimate_percent=>10,block_sample=>TRUE,granularity=>'ALL',cascade=>FALSE);
-- Gather statistics from source partition to target partition
DBMS_STATS.COPY_TABLE_STATS(ownname=>USER,tabname=>'HISTORICAL_DATA_'||TO_CHAR(p_hd_level),srcpartname=>v_source_partition_name,dstpartname=>v_target_partition_name);
END GATHER_HD_PARTITION_STATS;
END MANAGE_HD_PACK;
/
SET TERMOUT ON
-- Creating the parent table with range partitions
CREATE TABLE ORDERS
(
order_idNUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY (order_id)
)
PARTITION BY RANGE (order_date)
(
PARTITION ORDERS_2009_Q1 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')),
PARTITION ORDERS_2009_Q2 VALUES LESS THAN (TO_DATE('01-JUL-2009','DD-MON-YYYY')),
PARTITION ORDERS_2009_Q3 VALUES LESS THAN (TO_DATE('01-OCT-2009','DD-MON-YYYY')),
PARTITION ORDERS_2009_Q4 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
-- Creating the child table with REF partitioning
CREATE TABLE ORDER_ITEMS
(
order_idNUMBER(12)NOT NULL, -- MUST
line_item_id NUMBER(3),
product_id NUMBER(6),
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk FOREIGN KEY (order_id) REFERENCES ORDERS (order_id)
)
PARTITION BY REFERENCE (order_items_fk);
-- Querying data dictionary tables
BREAKON table_name
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value
FROMuser_tab_partitions
WHEREtable_name IN ('ORDERS','ORDER_ITEMS')
ORDER BY 1,2;
-- Dropping an old partition
ALTER TABLE ORDERS DROP PARTITION ORDERS_2009_Q1;
-- Querying data dictionary tables
BREAKON table_name
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value
FROMuser_tab_partitions
WHEREtable_name IN ('ORDERS','ORDER_ITEMS')
ORDER BY 1,2;
------
-- REF Partitioning with HASH Sub-Partitioning
------
-- Creating the parent table with RANGE partitions and HASH sub-partitions
CREATE TABLE REF_PARTITION_PARENT
(
owner VARCHAR2(30),
table_nameVARCHAR2(30),
num_rows NUMBER,
tablespace_nameVARCHAR2(30),
CONSTRAINT pk_ref_parentPRIMARY KEY (owner,table_name)
)
TABLESPACE USERS
PARTITION BY RANGE (num_rows) SUBPARTITION BY HASH (tablespace_name) SUBPARTITIONS 4
(
PARTITION p1_num_rows100 VALUES LESS THAN (100) ,
PARTITION p2_num_rows1000 VALUES LESS THAN (1000) ,
PARTITION p3_num_rows10000VALUES LESS THAN (10000) ,
PARTITION p4_num_rows_maxVALUES LESS THAN (MAXVALUE)
);
-- Creating the child table with REF partitioning based on the parent table
CREATE TABLE REF_PARTITION_CHILD
(
ownerVARCHAR2(30) NOT NULL,-- MUST
table_name VARCHAR2(30) NOT NULL,-- MUST
index_name VARCHAR2(30) NOT NULL,
tablespace_nameVARCHAR2(30) ,
CONSTRAINT fk_ref_child_parentFOREIGN KEY (owner,table_name) REFERENCES REF_PARTITION_PARENT (owner,table_name)
)
PARTITION BY REFERENCE (fk_ref_child_parent);
-- Populating the parent table
INSERT /*+ APPEND */ INTO REF_PARTITION_PARENT NOLOGGING
SELECTowner,
table_name,
num_rows ,
tablespace_name
FROM dba_tables
WHERE num_rows IS NOT NULL;
COMMIT;
-- Populating the child table
INSERT /*+ APPEND */ INTO REF_PARTITION_CHILD NOLOGGING
SELECTowner,
table_name,
index_name,
tablespace_name
FROM dba_indexes
WHERE(owner,table_name) IN (SELECT owner,table_name FROM REF_PARTITION_PARENT);
COMMIT;
-- Gathering statistics on both tables
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'REF_PARTITION_PARENT')
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'REF_PARTITION_CHILD')
-- Querying data dictionary tables
BREAKON table_name
SELECTtable_name,
partitioning_type,
subpartitioning_type,
partition_count,
partitioning_key_count,
ref_ptn_constraint_name
FROMuser_part_tables
ORDER BY 1;
SELECTtable_name,
partition_name,
subpartition_count,
high_value ,
last_analyzed,
num_rows
FROM user_tab_partitions
WHERE table_name IN ('REF_PARTITION_PARENT','REF_PARTITION_CHILD')
ORDER BY 1,2;
-- Drop a single partition in the parent table and query the data dictionary tables
ALTER TABLE REF_PARTITION_PARENT DROP PARTITION P1_NUM_ROWS100;
BREAKON table_name
SELECTtable_name,
partitioning_type,
subpartitioning_type,
partition_count,
partitioning_key_count,
ref_ptn_constraint_name
FROMuser_part_tables
ORDER BY 1;
SELECTtable_name,
partition_name,
subpartition_count,
high_value ,
last_analyzed,
num_rows
FROM user_tab_partitions
WHERE table_name IN ('REF_PARTITION_PARENT','REF_PARTITION_CHILD')
ORDER BY 1,2;
------
-- Demo: System Partitioning
------
-- Create system partitioned table
CREATE TABLE SALES_SYSTEM
(
sales_idNUMBER,
product_codeNUMBER,
state_codeNUMBER
)
PARTITION BY SYSTEM
(
PARTITION P1 TABLESPACE PARTITIONS_TBS_1,
PARTITION P2 TABLESPACE PARTITIONS_TBS_2,
PARTITION P3 TABLESPACE PARTITIONS_TBS_3
);
-- Query data dictionary table
SELECTpartitioning_type,partition_count
FROMuser_part_tables
WHEREtable_name='SALES_SYSTEM';
-- Populate table
INSERT INTO SALES_SYSTEM PARTITION (P1) VALUES (1,101,1);
INSERT INTO SALES_SYSTEM PARTITION (P2) VALUES (2,202,2);
INSERT INTO SALES_SYSTEM PARTITION (P3) VALUES (3,303,3);
COMMIT;
-- Populate table error
INSERT INTO SALES_SYSTEM VALUES (4,404,4);
-- Gather table statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES_SYSTEM')
-- Query data dictionary table
SELECTpartition_name,last_analyzed,high_value,num_rows
FROMuser_tab_partitions
WHEREtable_name='SALES_SYSTEM';
-- Deleting from the table
DELETE SALES_SYSTEM PARTITION (P2) WHERE state_code = 2;
DELETE SALES_SYSTEM WHERE state_code = 1;
COMMIT;
-- Showing execution plan
EXPLAIN PLAN FOR DELETE SALES_SYSTEM WHERE state_code = 3;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN FOR DELETE SALES_SYSTEM PARTITION (P3) WHERE state_code = 3;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
-- Aternity HISTORICAL_DATA_TABLES Real-Life Example
-- Creating HD1/2/3 tables
-- TABLE FOR AGGREGATION LEVEL 1
CREATE TABLE HISTORICAL_DATA_1
(
TIMEFRAMEDATE,
MA_IDNUMBER(10),
EP_COMBO_IDNUMBER(10),
CTX_COMBO_IDNUMBER(10),
ORIGINATING_PATH_INDEXNUMBER(2),
VARIANCENUMBER(37,10),
AVERAGENUMBER(37,10),
VALUE_COUNTNUMBER(10,0)
)
TABLESPACE ATERNITY_HD
PARTITION BY RANGE (TIMEFRAME) INTERVAL(NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY HASH (MA_ID) SUBPARTITIONS 16
(
PARTITION HD1_p1 VALUES LESS THAN (TO_DATE('01-01-2000','DD-MM-YYYY'))
);
-- TABLE FOR AGGREGATION LEVEL 2
CREATE TABLE HISTORICAL_DATA_2
(
TIMEFRAMEDATE,
MA_IDNUMBER(10),
EP_COMBO_IDNUMBER(10),
CTX_COMBO_IDNUMBER(10),
ORIGINATING_PATH_INDEXNUMBER(2),
VARIANCENUMBER(37,10),
AVERAGENUMBER(37,10),
VALUE_COUNTNUMBER(10,0)
)
TABLESPACE ATERNITY_HD
PARTITION BY RANGE (TIMEFRAME) INTERVAL(NUMTODSINTERVAL(7,'DAY')) SUBPARTITION BY HASH (MA_ID) SUBPARTITIONS 16
(
PARTITION HD2_p1 VALUES LESS THAN (TO_DATE('01-01-2000','DD-MM-YYYY'))
);
-- TABLE FOR AGGREGATION LEVEL 3
CREATE TABLE HISTORICAL_DATA_3
(
TIMEFRAMEDATE,
MA_IDNUMBER(10),
EP_COMBO_IDNUMBER(10),
CTX_COMBO_IDNUMBER(10),
ORIGINATING_PATH_INDEXNUMBER(2),
VARIANCENUMBER(37,10),
AVERAGENUMBER(37,10),
VALUE_COUNTNUMBER(10,0)
)
TABLESPACE ATERNITY_HD
PARTITION BY RANGE (TIMEFRAME) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH (MA_ID) SUBPARTITIONS 16
(
PARTITION HD3_p1 VALUES LESS THAN (TO_DATE('01-01-2000','DD-MM-YYYY'))
);
-- Showing HD1/2/3 partitions
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value
FROMuser_tab_partitions
WHEREtable_name LIKE 'HISTORICAL_DATA__'
ORDER BY 1,2;
-- Loading data into HD1/2/3
HOST SQLLDR aternity/aternity@db11g CONTROL=C:\OOW2009\HD1.CTL LOG=C:\OOW2009\HD1.LOG DATA=C:\OOW2009\HD1.dat DIRECT=TRUE PARALLEL=TRUE SKIP_INDEX_MAINTENANCE=TRUE
HOST SQLLDR aternity/aternity@db11g CONTROL=C:\OOW2009\HD2.CTL LOG=C:\OOW2009\HD2.LOG DATA=C:\OOW2009\HD2.dat DIRECT=TRUE PARALLEL=TRUE SKIP_INDEX_MAINTENANCE=TRUE
HOST SQLLDR aternity/aternity@db11g CONTROL=C:\OOW2009\HD3.CTL LOG=C:\OOW2009\HD3.LOG DATA=C:\OOW2009\HD3.dat DIRECT=TRUE PARALLEL=TRUE SKIP_INDEX_MAINTENANCE=TRUE
-- Showing HD1/2/3 partitions
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value
FROMuser_tab_partitions
WHEREtable_name LIKE 'HISTORICAL_DATA__'
ORDER BY 1,2;
-- Inserting new rows into HD1
INSERT /*+ APPEND */ INTO historical_data_1 NOLOGGING
SELECT TO_DATE('03-OCT-09','DD-MON-RR'),MA_ID,EP_COMBO_ID,CTX_COMBO_ID,ORIGINATING_PATH_INDEX,VARIANCE,AVERAGE,VALUE_COUNT
FROM historical_data_1
WHERE ROWNUM<=100000
UNION ALL
SELECT TO_DATE('26-SEP-09','DD-MON-RR'),MA_ID,EP_COMBO_ID,CTX_COMBO_ID,ORIGINATING_PATH_INDEX,VARIANCE,AVERAGE,VALUE_COUNT
FROM historical_data_1
WHERE ROWNUM<=100000
UNION ALL
SELECT SYSDATE,MA_ID,EP_COMBO_ID,CTX_COMBO_ID,ORIGINATING_PATH_INDEX,VARIANCE,AVERAGE,VALUE_COUNT
FROM historical_data_1
WHERE ROWNUM<=100000
UNION ALL
SELECT SYSDATE+1,MA_ID,EP_COMBO_ID,CTX_COMBO_ID,ORIGINATING_PATH_INDEX,VARIANCE,AVERAGE,VALUE_COUNT
FROM historical_data_1
WHERE ROWNUM<=100000;
COMMIT;
-- Showing HD1 partitions
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value
FROMuser_tab_partitions
WHEREtable_name = 'HISTORICAL_DATA_1'
ORDER BY 1,2;
-- Gathering statistics on 20-SEP-09 and copying to 21-SEP-09
EXEC MANAGE_HD_PACK.GATHER_HD_PARTITION_STATS(1,'20-SEP-09','21-SEP-09')
-- Showing HD1 partitions and statistics
SELECTtable_name,partition_position,partition_name,MANAGE_HD_PACK.CONVERT_HIGH_VALUE_TO_DATE(partition_position,table_name) AS high_value,last_analyzed
FROMuser_tab_partitions
WHEREtable_name = 'HISTORICAL_DATA_1'ORDER BY 1,2;