EcalCondDB/sql/partition_splitter.sql

0001 /******************************************************************************
0002
0003 This script is used to create the scripts that actually splits partitions
0004 on CMS_ECAL_COND account
0005
0006 ******************************************************************************/
0007
0008 column pind format a2
0009 column table_name format a30
0010 column column_name format a30
0011 column pname format a30
0012
0013 DROP TABLE SPLITTER;
0014
0015 /*
0016    Create a table which lists all the partitioned tables.
0017    For each partitioned table stores the name, the column on which
0018    it has been partitioned, the partition name and the index of the
0019    partition. Our partition names are of the form XXXX_n, where n
0020    is a progressive number. This number is the index of the
0021    partition. The current partition is the one with the highest
0022    index
0023 */
0024 CREATE TABLE SPLITTER AS
0025 select table_name, column_name, pname, max(pind) pind, num_rows
0026         from (select t.table_name,
0027         column_name,
0028         regexp_replace(partition_name, '_[0-9]+$', '') pname,
0029         regexp_replace(partition_name, '.*_', '') pind, u.num_rows num_rows
0030         from
0031         user_tab_partitions t join user_part_key_columns c on t.table_name =
0032         c.name join user_tables u on t.table_name = u.table_name)
0033         group by pname, table_name, column_name, num_rows order by num_rows asc;
0034
0035 /*
0036    Add a column to that table to store the maximum value assigned
0037    to the partitioning column.
0038 */
0039 ALTER TABLE SPLITTER ADD MAXVAL INT DEFAULT 0;
0040
0041 COLUMN A FORMAT A30
0042
0043 SET HEAD OFF
0044 SET ECHO OFF
0045 /*
0046    Create a script to generate SQL commands to generate another
0047    script to update the MAXVAL column of the SPLITTER table. Use
0048    the COALESCE function to fight against empty tables.
0049 */
0050 column a format a35
0051 SPOOL UPDATESPLITTER1.sql
0052 SELECT 'SELECT ''UPDATE SPLITTER SET MAXVAL = '', COALESCE(MAX(',
0053         COLUMN_NAME, '),0), '' WHERE TABLE_NAME = ',
0054         REGEXP_REPLACE(REGEXP_REPLACE(TABLE_NAME, '^ *', ''''''),
0055         ' *$', '''''') A,
0056         ';'' FROM ', TABLE_NAME, ';'
0057         FROM SPLITTER;
0058 SPOOL OFF
0059
0060 /*
0061    Run the generated script, to generate the script to actually
0062    update SPLITTER.
0063 */
0064 SPOOL UPDATESPLITTER2.sql;
0065 @UPDATESPLITTER1;
0066 SPOOL OFF
0067
0068 /*
0069    update SPLITTER with the current values for partitioning columns
0070 */
0071 @UPDATESPLITTER2;
0072
0073 /*
0074    generate the sql instructions to partition tables
0075 */
0076 SET LINE 400
0077 SPOOL SPLITPARTITIONS.sql
0078 SELECT 'ALTER TABLE ' || TABLE_NAME || ' SPLIT PARTITION ' || PNAME || '_' ||
0079         PIND || ' AT (' || MAXVAL || ') INTO (PARTITION ' || PNAME || '_' ||
0080         (PIND) || ' TABLESPACE CMS_ECAL_COND_20' || PIND || '_DATA' ||
0081         ', PARTITION ' || PNAME || '_' || (PIND + 1) ||
0082         ' TABLESPACE CMS_ECAL_COND_20' || (PIND+1) ||
0083         '_DATA) UPDATE GLOBAL INDEXES;'
0084         FROM SPLITTER WHERE MAXVAL > 0 ORDER BY NUM_ROWS ASC;
0085 SPOOL OFF
0086
0087 /* do the splitting (to be done by hand)
0088 @SPLITPARTITIONS;
0089 */
0090
0091 /*
0092 SELECT table_name, partition_name, high_value
0093 FROM user_tab_partitions;
0094 */