REM gfc_desc_timestamp_index.sql
REM (c)Go-Faster Consultancy 2013
REM look for indexes with descending columns that need to converted from dates to timestamp
REM could have several columns in one index or several indexes with the same column

set echo on pause off timi on trimspool on pages 99 lines 131 long 32767 serveroutput on
column index_ddl format a131
column column_expression format a20 
column column_position heading 'Col|Pos' format 999
column data_type format a10
spool gfc_desc_timestamp_index0

lock table gfc_desc_timestamp_index in exclusive mode
/
pause

drop table gfc_desc_timestamp_index purge
/

CREATE TABLE gfc_desc_timestamp_index
(recname VARCHAR2(15) NOT NULL
,table_name VARCHAR2(18) NOT NULL
,index_name VARCHAR2(18) NOT NULL
,column_position INTEGER NOT NULL
,column_name VARCHAR2(18) 
,column_expression CLOB
,index_ddl CLOB
,insert_timestamp TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL 
,CONSTRAINT gfc_desc_timestamp_index PRIMARY KEY (table_name, index_name, column_position)
)
/

TRUNCATE TABLE gfc_desc_timestamp_index
/


--this procedure finds the columns
DECLARE

PROCEDURE alterit
(p_recname           VARCHAR2
,p_table_name        VARCHAR2
,p_index_name        VARCHAR2
,p_column_position   INTEGER
,p_column_name       VARCHAR2
,p_data_type         VARCHAR2
,p_column_expression LONG DEFAULT NULL
) IS
  l_column_name VARCHAR2(18);
  l_ddl CLOB;
  l_stattype_locked VARCHAR2(5);
BEGIN
  dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss')||'>'||p_recname||'/'||p_table_name||'/'||p_index_name||'/'||p_column_position||'/'||p_column_name||'/'||p_column_expression);
  BEGIN
    IF p_column_expression IS NULL THEN --check the column in peoplesoft
      dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss')||'>Testing column '||p_column_name);
      SELECT f.fieldname
      INTO   l_column_name
      FROM   psrecfielddb r
      ,      psdbfield f
      WHERE  f.fieldname = r.fieldname
      AND    f.fieldtype IN(5,6) --time/datetime column in peoplesoft
      AND    r.recname = p_recname
      AND    r.fieldname = p_column_name
      AND    p_data_type = 'DATE'; --date column in the database
    ELSE  --interpret column expression to get column
      dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss')||'>Testing expression '||p_column_expression);
      SELECT f.fieldname
      INTO   l_column_name
      FROM   psrecfielddb r
      ,      psdbfield f
      ,      user_tab_columns c
      WHERE  f.fieldname = r.fieldname
      AND    f.fieldtype IN(5,6) --time/datetime column in peoplesoft
      AND    r.recname = p_recname
      AND    INSTR(UPPER(p_column_expression),UPPER('"'||r.fieldname||'"'))>0
      AND    c.table_name = p_table_name
      AND    c.column_name = r.fieldname
      AND    c.data_type = 'DATE'; --date column in the database
    END IF;
    dbms_output.put_line('Column '||p_column_name||' is date and should be a timestamp');

    l_ddl := dbms_metadata.get_ddl('INDEX',p_index_name); --extract create index DDL 

    BEGIN --check if table stats are locked
      SELECT stattype_locked
      INTO l_stattype_locked
      FROM   user_tab_statistics s
      WHERE  s.table_name = p_table_name
      AND    s.partition_name IS NULL
      AND    s.subpartition_name IS NULL;

      IF l_stattype_locked = 'ALL' THEN --remove compute stats on index DDL if stats locked on table
        l_ddl := REPLACE(l_ddl,'COMPUTE STATISTICS','');
      END IF;

    EXCEPTION WHEN no_data_found THEN --table stats not locked
      l_stattype_locked := '';
    END;


    IF INSTR(UPPER(l_ddl),'NOLOGGING') > 0 THEN
      NULL; --already contains LOGGING
    ELSIF INSTR(UPPER(l_ddl),' LOGGING') > 0 THEN
      l_ddl := REPLACE(l_ddl,' LOGGING',' NOLOGGING');
    ELSE
      l_ddl := l_ddl||' NOLOGGING';
    END IF;

    IF INSTR(UPPER(l_ddl),'NOPARALLEL') > 0 THEN
      l_ddl := REPLACE(l_ddl,' NOPARALLEL',' PARALLEL');
    ELSIF INSTR(UPPER(l_ddl),' PARALLEL') > 0 THEN
      NULL; --already contains PARALLEL
    ELSE 
      l_ddl := l_ddl||' PARALLEL';
    END IF;

    --so we only insert the row if we can find a row in preceding query
    INSERT INTO gfc_desc_timestamp_index
    (recname, table_name, index_name, column_position, column_name, column_expression, index_ddl)
    VALUES
    (p_recname, p_table_name, p_index_name, p_column_position, l_column_name, p_column_expression, l_ddl);
    COMMIT;

  EXCEPTION 
    WHEN no_data_found THEN NULL; --not an error but jump insert if column lookup fails
  END;

END alterit;


  END LOOP;

  FOR i IN ( --2nd query looks for NORMAL (ie not function based) GLOBAL partitioned indexes partitioned on DATE columns that should become TIMESTAMPs
    WITH r AS ( 
           SELECT r.recname
           ,      DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) table_name
           FROM   sysadm.psrecdefn r
           WHERE  r.rectype=0 --regular records only
    )
    SELECT /*+LEADING(r pi kc)*/ r.recname, r.table_name, i.index_name, ic.column_position, ic.column_name, tc.data_type
    FROM   r
    ,      user_indexes i
    ,      user_part_indexes pi
    ,      user_part_key_columns kc
    ,      user_ind_columns ic
    ,      user_tab_columns tc
    WHERE  i.table_name = r.table_name
    AND    i.index_type = 'NORMAL'
    AND    pi.table_name = r.table_name
    AND    pi.index_name = i.index_name
    AND    pi.locality = 'GLOBAL' --global partitioned index
    AND    ic.table_name = r.table_name
    AND    ic.index_name = i.index_name
    AND    tc.table_name = ic.table_name 
    AND    tc.column_name = ic.column_name
    AND    tc.data_type = 'DATE'
    AND    kc.object_type = 'INDEX'
    AND    kc.name = i.index_name
    AND    kc.name = pi.index_name
    AND    kc.column_name = ic.column_name
--  AND    r.recname = 'TL_EXCEPTION'
  ) LOOP
    alterit(i.recname
           ,i.table_name        
           ,i.index_name        
           ,i.column_position   
           ,i.column_name       
           ,i.data_type         
           );
  END LOOP;
END;
/

pause
spool gfc_desc_timestamp_index1
set lines 80
ttitle 'Number of Tables / Indexes / Columns to convert to Oracle Timestamp'
select count(distinct table_name) tables
,      count(distinct index_name) indexes
,      count(*) columns
from gfc_desc_timestamp_index x
/

ttitle 'Columns to be converted to Oracle Timestamp that appear in more than one index'
select table_name, column_name, count(*) indexes
from gfc_desc_timestamp_index x
group by table_name, column_name
having count(*)>1
/

ttitle 'Indexes that have more than one column to convert to Oracle Timestamp'
select table_name, index_name, count(*) columns
from gfc_desc_timestamp_index x
group by table_name, index_name
having count(*)>1
/

set lines 131
ttitle 'Summary of Index Rebuilds'
select *
from gfc_desc_timestamp_index
order by table_name, index_name
/
	ttitle off

pause
set serveroutput on
spool gfc_desc_timestamp_index2

ALTER TRIGGER psft_ddl_lock ENABLE;
EXECUTE psft_ddl_lock.ddl_permitted(TRUE);

DECLARE 
  l_sql CLOB;

  PROCEDURE exec_sql(p_sql CLOB) IS
    l_cursor_name    INTEGER;
    l_rows_processed INTEGER;

    l_sql           dbms_sql.varchar2s;
    l_chunk_size     INTEGER := 256; --default chunk size
    l_counter        INTEGER := 0; --chunk counter
    l_offset         INTEGER := 1; --starting position of current chunk
    l_sql_length     INTEGER := 0; --overall SQL length
  BEGIN
    l_sql_length := LENGTH(p_sql);
    WHILE l_offset < l_sql_length LOOP
      l_counter := l_counter + 1;
      l_sql(l_counter) := SUBSTR(p_sql,l_offset,l_chunk_size);
      dbms_output.put_line('['||l_counter||']'||l_sql(l_counter));
      l_offset := l_offset + l_chunk_size;
    END LOOP;
    dbms_output.put_line('['||l_sql_length||','||l_counter||']');
    --EXECUTE IMMEDIATE ''||p_sql; --cannot pass clob to EXECUTE IMMEDIATE in 10g

    l_cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(c => l_cursor_name
                  , statement => l_sql
                  , lb => 1
                  , ub => l_counter
                  , lfflg => false
                  , language_flag => dbms_sql.native);
    l_rows_processed := dbms_sql.execute(l_cursor_name);
    dbms_sql.close_cursor(l_cursor_name);
  END exec_sql;

BEGIN
  FOR i IN(
    SELECT DISTINCT table_name
    FROM   gfc_desc_timestamp_index
--  WHERE  table_name = 'PS_ORG_COMM' --just for testing
    ORDER BY 1
  ) LOOP

    FOR j IN (
      SELECT DISTINCT i.index_name
      FROM   gfc_desc_timestamp_index x
      ,      user_indexes i
      WHERE  x.table_name = i.table_name
      AND    x.table_name = i.table_name
      AND    x.index_name = i.index_name
      ORDER BY 1
    ) LOOP
      l_sql := 'DROP INDEX '||j.index_name;
      exec_sql(l_sql);
    END LOOP;

    FOR j IN (
      SELECT DISTINCT column_name
      FROM   gfc_desc_timestamp_index
      WHERE  table_name = i.table_name
      ORDER BY 1
    ) LOOP
      l_sql := 'ALTER TABLE '||i.table_name||' MODIFY ('||j.column_name||' TIMESTAMP)';
      exec_sql(l_sql);
    END LOOP;

    FOR j IN (
      SELECT row_number() over (partition by index_name order by column_name) seq
      ,      index_name
      ,      index_ddl
      FROM   gfc_desc_timestamp_index
      WHERE  table_name = i.table_name
      ORDER BY 1
    ) LOOP
      IF j.seq = 1 THEN --cannot do distinct on a clob
        exec_sql(j.index_ddl);
        exec_sql('ALTER INDEX '||j.index_name||' NOPARALLEL LOGGING');
      END IF;
    END LOOP;

  END LOOP;
END;
/

spool off


