-- Interval_Partitioning.sql

COL PARTITION_NAME FOR A30
COL HIGH_VALUE FOR A30
ALTER SESSION SET nls_date_format='DD-MON-RR HH24:MI:SS';
SET LINES 180
SET PAGES 1500
SET FEEDBACK ON
SET ECHO ON
CL SCR

DROP TABLE INTERVAL_PARTITIONING PURGE;

-- Creating an interval range partitioned table
CREATE TABLE INTERVAL_PARTITIONING
(serial_num	NUMBER,
 some_value	VARCHAR2(10))
PARTITION BY RANGE (serial_num) INTERVAL (10000)
-- STORE IN (PARTITIONS_TBS_1,PARTITIONS_TBS_2,PARTITIONS_TBS_3)
( PARTITION p1_10000 VALUES LESS THAN (10001) ,
  PARTITION p2_20000 VALUES LESS THAN (20001) ,
  PARTITION p3_30000 VALUES LESS THAN (30001) );

-- Showing table partitions created
SELECT	partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;

-- Populating the table with 50,000 rows
BEGIN	
	FOR i IN 1..50000 LOOP
		EXECUTE IMMEDIATE 'INSERT INTO INTERVAL_PARTITIONING (serial_num,some_value) VALUES ( :i , ''A'' )' USING i;
	END LOOP;
	COMMIT;
END;
/

-- Gathering statistics on the table
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'INTERVAL_PARTITIONING')

-- Showing table partitions created
SELECT	partition_position,partition_name,high_value,TO_CHAR(last_analyzed,'DD-MON-RR HH24:MI:SS') last_analyzed,num_rows
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;

-- Querying minimum and maximum values from a specific partition without knowing the partition name
SELECT	MIN(serial_num),MAX(serial_num)
FROM	INTERVAL_PARTITIONING PARTITION FOR (40000);

-- Checking execution plan for the above query
EXPLAIN PLAN FOR 
SELECT	MIN(serial_num),MAX(serial_num)
FROM	INTERVAL_PARTITIONING PARTITION FOR (40000);
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

-- Rename interval partitions using PARTITION FOR clause
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (40000) TO P4_40000;
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (50000) TO P5_50000;

-- Showing table partitions
SELECT	partition_position,partition_name,high_value,TO_CHAR(last_analyzed,'DD-MON-RR HH24:MI:SS') last_analyzed,num_rows
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;

-- Inserting 10,000 new values
INSERT /*+ APPEND */ INTO INTERVAL_PARTITIONING NOLOGGING
SELECT	serial_num+100000,some_value
FROM	INTERVAL_PARTITIONING
WHERE 	ROWNUM<=10000;
COMMIT;

-- Showing table partitions created
SELECT	partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;

-- Inserting 30,000 new values in-between
INSERT /*+ APPEND */ INTO INTERVAL_PARTITIONING NOLOGGING
SELECT	serial_num+70000,some_value
FROM	INTERVAL_PARTITIONING
WHERE 	ROWNUM<=30000;
COMMIT;

-- Showing table partitions created
SELECT	partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;

-- Rename interval partitions using PARTITION FOR clause
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (80000) TO P8_80000;
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (90000) TO P9_90000;
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (100000) TO P10_100000;
ALTER TABLE INTERVAL_PARTITIONING RENAME PARTITION FOR (110000) TO P11_110000;

-- Showing table partitions
SELECT	partition_position,partition_name,high_value
FROM	user_tab_partitions
WHERE	table_name='INTERVAL_PARTITIONING'
ORDER BY partition_position;
