-- Range_List_Template
CONN / AS SYSDBA
CL SCR
COL HIGH_VALUE 		FOR A80
COL SUB_HIGH_VALUE 	FOR A10
COL HIGH_BOUND		FOR A20
COL PART_NAME 		FOR A10
COL SUBPART_NAME 	FOR A15
COL PART_POS		FOR 999
COL SUBPART_POS		FOR 999
SET ECHO ON

DROP TABLE RANGE_LIST_TEMPLATE_TBL PURGE;

CREATE TABLE RANGE_LIST_TEMPLATE_TBL
(	ANALYSIS_TIME	DATE 	NOT NULL,
	MEASUREMENT		NUMBER	NOT NULL,
	LOCATION		VARCHAR2(20)	,
	TENANT_KEY		NUMBER	NOT NULL,
	EXCEPTION		VARCHAR2(100)
)	PARTITION BY RANGE (ANALYSIS_TIME) INTERVAL (NUMTODSINTERVAL(1,'day'))
	SUBPARTITION BY LIST (TENANT_KEY) SUBPARTITION TEMPLATE (SUBPARTITION DEF VALUES (DEFAULT))
	(PARTITION P1 VALUES LESS THAN (TO_DATE('2010-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

SELECT	utp.partition_position part_pos,utp.partition_name part_name,utp.high_value,uts.subpartition_position subpart_pos,uts.subpartition_name subpart_name,uts.high_value sub_high_value,uts.num_rows
FROM	user_tab_partitions		utp,
		user_tab_subpartitions	uts
WHERE	utp.partition_name = uts.partition_name
AND		utp.table_name = 'RANGE_LIST_TEMPLATE_TBL'
ORDER BY utp.partition_position,uts.subpartition_position;

SELECT	subpartition_name subpart_name,subpartition_position subpart_pos,high_bound 
FROM 	user_subpartition_templates 
WHERE 	table_name = 'RANGE_LIST_TEMPLATE_TBL' 
ORDER BY subpartition_position;

BEGIN
	FOR i IN 1..100000 LOOP
		INSERT INTO range_list_template_tbl (ANALYSIS_TIME,MEASUREMENT,LOCATION,TENANT_KEY,EXCEPTION)
		VALUES (SYSDATE+DBMS_RANDOM.VALUE(0,1),ROUND(DBMS_RANDOM.VALUE(0,1000)),'Unknown',ROUND(DBMS_RANDOM.VALUE(1,10)),NULL);
	END LOOP;
	COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'RANGE_LIST_TEMPLATE_TBL');

SELECT	utp.partition_position part_pos,utp.partition_name part_name,utp.high_value,uts.subpartition_position subpart_pos,uts.subpartition_name subpart_name,uts.high_value sub_high_value,uts.num_rows
FROM	user_tab_partitions		utp,
		user_tab_subpartitions	uts
WHERE	utp.partition_name = uts.partition_name
AND		utp.table_name = 'RANGE_LIST_TEMPLATE_TBL'
ORDER BY utp.partition_position,uts.subpartition_position;

SELECT	tenant_key,COUNT(*) num_rows
FROM	range_list_template_tbl SUBPARTITION (&Subpartition_Name)
GROUP BY tenant_key
ORDER BY num_rows;

DECLARE
	CURSOR 	get_big_tenants_cur IS
	SELECT	tenant_key
	FROM	range_list_template_tbl SUBPARTITION (&Subpartition_Name)
	GROUP BY tenant_key
	HAVING	COUNT(*) > &num_rows
	ORDER BY tenant_key ASC;
	sql_stmt	VARCHAR2(1000);
BEGIN
	sql_stmt := 'ALTER TABLE RANGE_LIST_TEMPLATE_TBL SET SUBPARTITION TEMPLATE (';
	FOR rec IN get_big_tenants_cur LOOP
		sql_stmt := sql_stmt || 'SUBPARTITION "'||rec.tenant_key||'" VALUES ('||rec.tenant_key||'),';
	END LOOP;
	sql_stmt := sql_stmt || 'SUBPARTITION "DEF" VALUES (DEFAULT))';
	EXECUTE IMMEDIATE(sql_stmt);
	DBMS_OUTPUT.PUT_LINE('SQL Stmt: '||sql_stmt);
END;
/

SELECT	subpartition_name subpart_name,subpartition_position subpart_pos,high_bound 
FROM 	user_subpartition_templates 
WHERE 	table_name = 'RANGE_LIST_TEMPLATE_TBL' 
ORDER BY subpartition_position;

BEGIN
	FOR i IN 1..100000 LOOP
		INSERT INTO range_list_template_tbl (ANALYSIS_TIME,MEASUREMENT,LOCATION,TENANT_KEY,EXCEPTION)
		VALUES (SYSDATE+DBMS_RANDOM.VALUE(2,3),ROUND(DBMS_RANDOM.VALUE(0,1000)),'Unknown',ROUND(DBMS_RANDOM.VALUE(1,10)),NULL);
	END LOOP;
	COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'RANGE_LIST_TEMPLATE_TBL');

SELECT	utp.partition_position part_pos,utp.partition_name part_name,utp.high_value,uts.subpartition_position subpart_pos,uts.subpartition_name subpart_name,uts.high_value sub_high_value
FROM	user_tab_partitions		utp,
		user_tab_subpartitions	uts
WHERE	utp.partition_name = uts.partition_name
AND		utp.table_name = 'RANGE_LIST_TEMPLATE_TBL'
ORDER BY utp.partition_position,uts.subpartition_position;
