-- Interval_Virtual_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 SERVEROUTPUT ON
SET LINES 180
SET PAGES 1500
SET FEEDBACK ON
SET ECHO ON
CL SCR

DROP TABLE INTERVAL_VIRTUAL_PARTITIONING PURGE;

-- Creating the interval range partitioned table with the FIRST_DIGIT virtual column
CREATE TABLE INTERVAL_VIRTUAL_PARTITIONING
(
	SOME_NUMBERS	NUMBER			NOT NULL,
	FIRST_DIGIT		NUMBER(1)		AS (TO_NUMBER(SUBSTR(TO_CHAR(SOME_NUMBERS),1,1)))
)
	PARTITION BY RANGE (FIRST_DIGIT) INTERVAL (1)
	(PARTITION p1 VALUES LESS THAN (2));

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

-- Populating the table with 1,000 rows
BEGIN	
	FOR i IN 1..1000 LOOP
		INSERT INTO INTERVAL_VIRTUAL_PARTITIONING (some_numbers) VALUES (ROUND(DBMS_RANDOM.VALUE(0,1000)));
	END LOOP;
	COMMIT;
END;
/

-- Querying the table using the FIRST_DIGIT virtual column
SELECT	NVL(TO_CHAR(FIRST_DIGIT),'TOTAL: ') AS FIRST_DIGIT,COUNT(*)
FROM	INTERVAL_VIRTUAL_PARTITIONING
GROUP BY ROLLUP(FIRST_DIGIT);

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

-- Showing INTERVAL_VIRTUAL_PARTITIONING table partitions and statistics
SELECT	partition_position,partition_name,high_value,last_analyzed,num_rows
FROM	user_tab_partitions 
WHERE	table_name = 'INTERVAL_VIRTUAL_PARTITIONING'
ORDER BY partition_position;

-- Querying a specific partition without knowing the partition name
SELECT	COUNT(*) 
FROM 	INTERVAL_VIRTUAL_PARTITIONING PARTITION FOR (4);

-- Checking execution plan for the above query
EXPLAIN PLAN FOR
SELECT	COUNT(*) 
FROM 	INTERVAL_VIRTUAL_PARTITIONING PARTITION FOR (4);
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

-- Rename all interval partitions created as SYS_P%
DECLARE
	CURSOR 	get_partitions IS
	SELECT 	partition_position 
	FROM 	user_tab_partitions 
	WHERE 	table_name='INTERVAL_VIRTUAL_PARTITIONING' 
	AND 	partition_name LIKE 'SYS_P%' 
	ORDER BY partition_position;
	sql_stmt	VARCHAR2(1000);
BEGIN
	FOR rec IN get_partitions LOOP
		sql_stmt := 'ALTER TABLE INTERVAL_VIRTUAL_PARTITIONING RENAME PARTITION FOR ('||rec.partition_position||') TO P'||rec.partition_position;
		DBMS_OUTPUT.PUT_LINE(sql_stmt);
		EXECUTE IMMEDIATE sql_stmt;
	END LOOP;
END;
/

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