EcalCondDB/sql/partition_db.sql

0001 /*
0002  * This script will partition the whole database, based on IOV_IDs
0003  *
0004  * It creates a script that contains the actual commands to partition
0005  * all the tables that contain some key to an IOV_ID.
0006  *
0007  * Partitions are created based on a division of IOV_IDs in blocks
0008  * of N, up to a maximum value M. Adjust these values using variables
0009  * step and imax in the DECLARE block below.
0010  *
0011  * Usage:
0012  * 1. Run this script with the command @partition_db
0013  * 2. Check that a new MKPARTITION.sql script and a fresh SPLIT.sql script
0014  *    are created and they contain reasonable values
0015  * 3. Run @MKPARTITION, crossing your fingers and spelling verses
0016  *    from any ORACLE tutorial
0017  *
0018  * Giovanni.Organtini@roma1.infn.it 2009
0019  *
0020  */
0021
0022 /* basic steup */
0023 SET NEWPAGE 0
0024 SET SPACE 0
0025 SET LINESIZE 80
0026 SET PAGESIZE 0
0027 SET ECHO OFF
0028 SET FEEDBACK OFF
0029 SET HEADING OFF
0030 SET MARKUP HTML OFF
0031
0032 /* generate a script that in turn generate the commands to split the tables */
0033 SPOOL SPLIT.sql
0034
0035 CREATE TABLE DUMMY (
0036         MYQUERY VARCHAR(1000)
0037 )
0038 /
0039
0040 BEGIN
0041 DECLARE
0042     step NUMBER :=100;
0043     imax NUMBER :=1000;
0044     i NUMBER :=step;
0045     q VARCHAR(1000) := '';
0046  BEGIN
0047   LOOP
0048   q := 'SELECT "ALTER TABLE ", REGEXP_REPLACE(TNAME, "$", ""),
0049           " SPLIT PARTITION ", REGEXP_REPLACE(TNAME, "$", "_0"),
0050           " AT (", i, ") INTO (PARTITION ", REGEXP_REPLACE(TNAME, "$", "_1"),
0051           ", PARTITION ", REGEXP_REPLACE(TNAME, "$", "_0"),
0052           ") UPDATE GLOBAL INDEXES;" FROM
0053             (SELECT T.TABLE_NAME TNAME, T.COLUMN_NAME COLNAME
0054              FROM USER_TAB_COLS T, USER_ALL_TABLES A
0055              WHERE T.TABLE_NAME = A.TABLE_NAME AND
0056                (T.COLUMN_NAME LIKE "%IOV%" AND T.DATA_TYPE LIKE "%NUMBER%")
0057             ) WHERE TNAME NOT LIKE "BIN%";';
0058         q := REGEXP_REPLACE(q, 'i', i);
0059         q := REGEXP_REPLACE(q, '"', '''');
0060         insert into dummy values (q);
0061    i := i+step;
0062    EXIT WHEN i > imax;
0063    END LOOP;
0064  END;
0065 END;
0066 /
0067
0068 SELECT * FROM DUMMY;
0069
0070 DROP TABLE DUMMY;
0071
0072 SPOOL OFF
0073
0074 /* generate a script that in fact create partitions */
0075 SPOOL MKPARTITIONS.sql
0076
0077 /* first of all create new tables from existing ones, with a single partition */
0078 SELECT 'CREATE TABLE ',REGEXP_REPLACE(TNAME, '$', '_2'),
0079         ' AS SELECT * FROM ', REGEXP_REPLACE(TNAME, '$', ''),
0080         ' PARTITION BY RANGE (', REGEXP_REPLACE(COLNAME, ' +$', ''),
0081         ') (PARTITION ', REGEXP_REPLACE(TNAME, '$', '_0'),
0082         ' VALUES LESS THAN (MAXVALUE));'
0083             FROM
0084             (SELECT T.TABLE_NAME TNAME, T.COLUMN_NAME COLNAME
0085              FROM USER_TAB_COLS T, USER_ALL_TABLES A
0086              WHERE T.TABLE_NAME = A.TABLE_NAME AND
0087                (T.COLUMN_NAME LIKE '%IOV%' AND T.DATA_TYPE LIKE '%NUMBER%')
0088             ) WHERE TNAME NOT LIKE 'BIN%';
0089
0090 /* then drop the existing tables and rename the new ones */
0091
0092 SELECT 'DROP TABLE ', REGEXP_REPLACE(TNAME, '$', '') NEWTNAME,
0093         '; RENAME', REGEXP_REPLACE(TNAME, '$', '_2'), ' TO ',
0094         REGEXP_REPLACE(TNAME, '$', '')  FROM
0095             (SELECT T.TABLE_NAME TNAME, T.COLUMN_NAME COLNAME
0096              FROM USER_TAB_COLS T, USER_ALL_TABLES A
0097              WHERE T.TABLE_NAME = A.TABLE_NAME AND
0098                (T.COLUMN_NAME LIKE '%IOV%' AND T.DATA_TYPE LIKE '%NUMBER%')
0099             ) WHERE TNAME NOT LIKE 'BIN%';
0100
0101 /* finally call the script that will create partitions, in fact */
0102
0103 @SPLIT
0104
0105 SPOOL OFF
0106
0107