/**********************************************************************************************************************************/
/*                                                                                                                                */
/* This script takes existing indexes and creates linguistic copies of the ones that have at least one non-numeric column.        */
/* It currently handles composite indexes of up to 16 columns. Additional columns can be added, i.e. col17, col18 etc., to the    */
/* table definition below and the UPDATE statement to accomodate indexes that have more than 16 columns.                          */
/*                                                                                                                                */
/* IMPORTANT NOTE!!!                                                                                                              */
/* This script needs to be run as SYSDBA.  After connecting to sqlplus as SYSDBA, execute the following command:                  */
/*      SQL> alter session set current_schema = <Remedy Schema Owner Name, e.g. ARADMIN>                                          */
/*                                                                                                                                */
/*                                                                                                                                */
/* The order to run the scripts if you are moving from Case Sensitive (CS) to Case Insensitive (CI):                              */
/*   1  li_20210505.sql (this script)                                                                                             */
/*   2. Drop_I.sql                                                                                                                */
/*   3. Create_CI.sql                                                                                                             */
/*                                                                                                                                */ 
/* The order to run the scripts if you are moving from Case Insensitive (CI) to Case Sensitive (CS):                              */
/*   1. Drop_CI.sql                                                                                                               */
/*   2. Drop_I.sql                                                                                                                */
/*   3. Recreate_NI.sql                                                                                                           */    
/*                                                                                                                                */
/*  **** You may encounter "ORA-01450: maximum key length (xxxx) exceeded" errors when creating some Linguistic Indexes. This is  */
/*       because the total key length of the columns in the index exceeds the (xxxx) value above. That value is 6398 for a        */
/*       database block size of 8K. Your best option then may be to create those indexes in a tablespace of larger block size.    */
/*                                                                                                                                */
/*                                                                                                                                */
/*  **** You may also encounter "ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found"                                     */
/*       If this occurs, you will need to identify the data that is in violation, remove or repair the duplicate data and         */
/*       manually rebuild the specific index by re-executing the SQL statement.                                                   */
/*                                                                                                                                */
/* (c) BMC Software, Inc. (2016)                                                                                                  */
/*                                                                                                                                */
/**********************************************************************************************************************************/

ACCEPT RemedySchemaOwner     char default 'ARADMIN'  prompt 'Enter the Remedy schema owner name      (Press ENTER to accept the default of ARADMIN)  : '
ACCEPT RemedyTableSpace      char default 'ARSYSTEM' prompt 'Enter the Remedy schema tablespace name (Press ENTER to accept the default of ARSYSTEM) : '

set trimspool on
set trimout   on
set termout   off
set lines     15000
set pages     0
set feedback  off
set echo      off
set verify    off

DROP TABLE linguistic_indexes
;


CREATE TABLE linguistic_indexes (tbl_name       varchar2(30)  ,
                                 idx_name       varchar2(30)  ,
                                 col1           varchar2(60)  ,
                                 col1_orig      varchar2(60)  ,
                                 col2           varchar2(60)  ,
                                 col2_orig      varchar2(60)  ,
                                 col3           varchar2(60)  ,
                                 col3_orig      varchar2(60)  ,
                                 col4           varchar2(60)  ,
                                 col4_orig      varchar2(60)  ,
                                 col5           varchar2(60)  ,
                                 col5_orig      varchar2(60)  ,
                                 col6           varchar2(60)  ,
                                 col6_orig      varchar2(60)  ,
                                 col7           varchar2(60)  ,
                                 col7_orig      varchar2(60)  ,
				 col8           varchar2(60)  ,
                                 col8_orig      varchar2(60)  ,
                                 col9           varchar2(60)  ,
                                 col9_orig      varchar2(60)  ,
                                 col10          varchar2(60)  ,
                                 col10_orig     varchar2(60)  ,
                                 col11          varchar2(60)  ,
                                 col11_orig     varchar2(60)  ,
                                 col12          varchar2(60)  ,
                                 col12_orig     varchar2(60)  ,
                                 col13          varchar2(60)  ,
                                 col13_orig     varchar2(60)  ,
                                 col14          varchar2(60)  ,
                                 col14_orig     varchar2(60)  ,
				 col15          varchar2(60)  ,
                                 col15_orig     varchar2(60)  ,
                                 col16          varchar2(60)  ,
                                 col16_orig     varchar2(60)  ,
                                 storage_clause varchar2(2000),
                                 isunique       varchar2(9)   ,
                                 create_index   varchar(1))
;

 
INSERT INTO linguistic_indexes(tbl_name,idx_name,col1,col1_orig)
 (SELECT table_name,
         index_name,
         decode(
                char_length,0,column_name,
                'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
               ),
         column_name    
  FROM   dba_ind_columns
  WHERE  index_name in (SELECT index_name FROM dba_indexes WHERE index_type = 'NORMAL' AND owner = '&&RemedySchemaOwner')
  AND    index_owner = '&&RemedySchemaOwner'
  AND    column_position = 1)
;

COMMIT
;

UPDATE linguistic_indexes a
 SET col2 =
       (SELECT decode(
                     char_length,0,column_name,
                     'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                     ) 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 2),
  col2_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name 
	AND      index_owner = '&&RemedySchemaOwner'
        AND      column_position = 2),
  col3 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name 
     AND    index_owner = '&&RemedySchemaOwner'
     AND    column_position = 3),
  col3_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 3),
  col4 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position=4),
  col4_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 4),
  col5 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 5),
  col5_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 5),
  col6 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 6),
  col6_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 6),
  col7 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 7),
  col7_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 7),
  col8 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 8),
  col8_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 8),
  col9 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 9),
  col9_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 9),
  col10 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 10),
  col10_orig =
       (SELECT   column_name 
         FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 10),
 col11 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 11),
  col11_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 11),
  col12 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 12),
  col12_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 12),
  col13 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 13),
  col13_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 13),
  col14 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 14),
  col14_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 14),
  col15 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 15),
  col15_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 15),
  col16 =
    (SELECT decode(
                   char_length,0,column_name,
                   'NLSSORT('||column_name||','||chr(39)||'NLS_SORT=BINARY_CI'||chr(39)||')'
                  )
     FROM   dba_ind_columns
     WHERE  index_name = a.idx_name
     AND    index_owner = '&&RemedySchemaOwner'	 
     AND    column_position = 16),
  col16_orig =
       (SELECT   column_name 
        FROM     dba_ind_columns
        WHERE    index_name = a.idx_name
        AND      index_owner = '&&RemedySchemaOwner'		
        AND      column_position = 16),
  isunique = (SELECT decode(uniqueness,'UNIQUE','Y','N')
              FROM   dba_indexes
			  WHERE  owner = '&&RemedySchemaOwner'
              AND    index_name = a.idx_name),
  storage_clause = ' PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE &&RemedyTableSpace;'
;

COMMIT
;

UPDATE    linguistic_indexes 
   SET    create_index      = 'Y'
 WHERE    substr(col1,1,7)  = 'NLSSORT'
    OR    substr(col2,1,7)  = 'NLSSORT'
    OR    substr(col3,1,7)  = 'NLSSORT'
    OR    substr(col4,1,7)  = 'NLSSORT'
    OR    substr(col5,1,7)  = 'NLSSORT'
    OR    substr(col6,1,7)  = 'NLSSORT'
    OR    substr(col7,1,7)  = 'NLSSORT'
    OR    substr(col8,1,7)  = 'NLSSORT'
    OR    substr(col9,1,7)  = 'NLSSORT'
    OR    substr(col10,1,7) = 'NLSSORT'
    OR    substr(col11,1,7) = 'NLSSORT'
    OR    substr(col12,1,7) = 'NLSSORT'
    OR    substr(col13,1,7) = 'NLSSORT'
    OR    substr(col14,1,7) = 'NLSSORT'
    OR    substr(col15,1,7) = 'NLSSORT'
    OR    substr(col16,1,7) = 'NLSSORT'
;

COMMIT
;


/**********************************************************************************************************************************/
/*                                                                                                                                */
/* Generate SQL statements to drop existing indexes. Indexes that are part of a table constraint will not be dropped.             */
/*                                                                                                                                */
/**********************************************************************************************************************************/

spool Drop_I.sql

SELECT 'DROP index '||'&&RemedySchemaOwner'||'.'||idx_name||';' 
FROM   linguistic_indexes a
WHERE  a.create_index = 'Y'
AND    a.idx_name not in (SELECT index_name 
                          FROM   dba_constraints 
                          WHERE  owner = '&&RemedySchemaOwner' 
                          AND    index_name IS NOT NULL)
;

spool off


/**********************************************************************************************************************************/
/*                                                                                                                                */
/* Generate SQL statements to drop existing LINGUISTIC indexes with "_CI" in their names.                                         */
/*                                                                                                                                */
/**********************************************************************************************************************************/

spool Drop_CI.sql

SELECT 'DROP index '||'&&RemedySchemaOwner'||'.'||idx_name||'_CI;' 
FROM   linguistic_indexes a
WHERE  a.create_index = 'Y'
AND    a.idx_name IN (SELECT b.index_name 
                      FROM   dba_constraints b 
                      WHERE  b.owner = '&&RemedySchemaOwner' 
                      AND    b.index_name IS NOT NULL)
;

spool off


/**********************************************************************************************************************************/
/*                                                                                                                                */
/* Generate SQL statements to recreate the original NORMAL indexes.                                                               */
/*                                                                                                                                */
/**********************************************************************************************************************************/

spool Recreate_NI.sql

SELECT 'CREATE '||decode(isunique,'Y','unique')||' index '||'&&RemedySchemaOwner'||'.'||idx_name||' on '||'&&RemedySchemaOwner'||'.'||tbl_name||'('||col1_orig||
decode(col2_orig,null,'',','   || col2_orig)   ||
decode(col3_orig,null,'',','   || col3_orig)   ||
decode(col4_orig,null,'',','   || col4_orig)   ||
decode(col5_orig,null,'',','   || col5_orig)   ||
decode(col6_orig,null,'',','   || col6_orig)   ||
decode(col7_orig,null,'',','   || col7_orig)   ||
decode(col8_orig,null,'',','   || col8_orig)   ||
decode(col9_orig,null,'',','   || col9_orig)   ||
decode(col10_orig,null,'',','  || col10_orig)  ||
decode(col11_orig,null,'',','  || col11_orig)  ||
decode(col12_orig,null,'',','  || col12_orig)  ||
decode(col13_orig,null,'',','  || col13_orig)  ||
decode(col14_orig,null,'',','  || col14_orig)  ||
decode(col15_orig,null,'',','  || col15_orig)  ||
decode(col16_orig,null,')',col16_orig||')')    ||
storage_clause
FROM   linguistic_indexes a
WHERE  a.create_index = 'Y'
AND    a.idx_name not in (SELECT index_name 
                          FROM   dba_constraints 
                          WHERE  owner = '&&RemedySchemaOwner' 
                          AND    index_name IS NOT NULL)
;

spool off

/**********************************************************************************************************************************/
/*                                                                                                                                */
/* Generate SQL statements to create LINGUISTIC indexes. The index names are the same as the original NORMAL indexes except when  */    
/* the index is part of a table constraint in which case "_CI" is appended to its name                                            */    
/*                                                                                                                                */
/**********************************************************************************************************************************/

spool Create_CI.sql

SELECT 'CREATE '||decode(isunique,'Y','unique')||' index '||'&&RemedySchemaOwner'||'.'||idx_name||' on '||'&&RemedySchemaOwner'||'.'||tbl_name||
'('||  col1                          ||
decode(col2,null,'',','  || col2)    ||
decode(col3,null,'',','  || col3)    ||
decode(col4,null,'',','  || col4)    ||
decode(col5,null,'',','  || col5)    ||
decode(col6,null,'',','  || col6)    ||
decode(col7,null,'',','  || col7)    ||
decode(col8,null,'',','  || col8)    ||
decode(col9,null,'',','  || col9)    ||
decode(col10,null,'',',' || col10)   ||
decode(col11,null,'',',' || col11)   ||
decode(col12,null,'',',' || col12)   ||
decode(col13,null,'',',' || col13)   ||
decode(col14,null,'',',' || col14)   ||
decode(col15,null,'',',' || col15)   ||
decode(col16,null,')',','|| col16)   ||
storage_clause 
FROM  linguistic_indexes a
WHERE CREATE_index = 'Y'
AND   a.idx_name NOT IN (SELECT b.index_name 
                         FROM   dba_constraints b 
                         WHERE  b.owner = '&&RemedySchemaOwner' 
                         AND    b.index_name IS NOT NULL)
UNION
SELECT 'CREATE '||decode(isunique,'Y','unique')||' index '||'&&RemedySchemaOwner'||'.'||idx_name||'_CI on '||'&&RemedySchemaOwner'||'.'||tbl_name||
'('||  col1                          ||
decode(col2,null,'',','  || col2)    ||
decode(col3,null,'',','  || col3)    ||
decode(col4,null,'',','  || col4)    ||
decode(col5,null,'',','  || col5)    ||
decode(col6,null,'',','  || col6)    ||
decode(col7,null,')',',' || col7)    ||
decode(col8,null,'',','  || col8)    ||
decode(col9,null,'',','  || col9)    ||
decode(col10,null,'',',' || col10)   ||
decode(col11,null,'',',' || col11)   ||
decode(col12,null,'',',' || col12)   ||
decode(col13,null,'',',' || col13)   ||
decode(col14,null,'',',' || col14)   ||
decode(col15,null,'',',' || col15)   ||
decode(col16,null,'',',' || col16)   ||
storage_clause 
FROM  linguistic_indexes c
WHERE c.create_index = 'Y'
AND   c.idx_name IN (SELECT d.index_name 
                     FROM   dba_constraints d 
                     WHERE  d.owner = '&&RemedySchemaOwner' 
                     AND    d.index_name IS NOT NULL)
;

spool off
