Indexing SQLs for DBA

Q: How to check if indexes are used by an application or not?

select INDEX_NAME,INDEX_TYPE,TABLE_NAME 
from user_indexes 
where TABLE_NAME='LB_LOG_BOOK'; --SYS_C007158

To reset the values in the v$object_usage view, disable index monitoring and re-enable it:
ALTER INDEX LB_SUBMITTER NOMONITORING USAGE;
ALTER INDEX LB_SUBMITTER MONITORING USAGE;

SELECT table_name, index_name, monitoring, used FROM v$object_usage;

ANALYZE INDEX LB_SUBMITTER COMPUTE STATISTICS;


Q: Find out which Indexes are workable?

First of all, gather table statistics -
exec dbms_stats.gather_table_stats( ownname => 'PROD7',tabname => 'LB_LOG_BOOK', cascade => true);

Then "Index Usage Count" column will show the index usage -
select p.object_name "Object Name",p.operation "Operation",p.options "Option"
, count(p.object_name) "Index Usage Count"
from dba_hist_sql_plan p, dba_hist_sqlstat s, dba_indexes di
where p.object_owner = 'PROD7'and p.operation like '%INDEX%'
and p.sql_id = s.sql_id and p.object_name = di.index_name and di.table_name='LOGIN'
group by p.object_name, p.operation, p.options
order by 1,2,3;


Q: which column for which index?
 
select * from user_ind_columns ic where ic.table_name='LOGIN';



Indexing on Foreign Key Referenced Columns

Non indexed Foreign key can lock child table(s) when we need to delete from parent table.
This script will generate a log file from where we can find all referenced columns of a table (like-individual), and the "create index" script.

set pages 200 lines 200
SPOOL INDIVIDUAL_FK_INDEXING.sql

SELECT 'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name 
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R' 
AND b.table_name='INDIVIDUAL';

SPOOL OFF;
 
We may need to change the index names if it crosses the maximum length for naming.
 

Finding the Non-Indexed Foreign Keys

COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'


SELECT t.table_name,c.constraint_name,c.table_name table2,acc.column_name
FROM all_constraints t, all_constraints c, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( 
  SELECT '1' 
  FROM all_ind_columns aid
  WHERE aid.table_name = acc.table_name
  AND aid.column_name = acc.column_name
  )
AND t.table_name='CLIENT'
ORDER BY c.table_name; 


Partition Index rebuild - PL/SQL 

DECLARE
tab_name VARCHAR2(30);
stmt VARCHAR2(500);
sqlstr VARCHAR2(800);
TYPE TabCurTyp IS REF CURSOR;
ind_cursor TabCurTyp;
indx_name VARCHAR2(35);
part_name VARCHAR2(35);

BEGIN
tab_name := UPPER('&tab_name');
sqlstr:='SELECT distinct uip.index_name, uip.partition_name
FROM USER_IND_PARTITIONS uip, USER_TAB_PARTITIONS utp, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name = uip.index_name
AND ui.table_name = utp.table_name
AND utp.table_name LIKE '''||tab_name||'%''';

OPEN ind_cursor FOR sqlstr;
LOOP
FETCH ind_cursor INTO indx_name,part_name;
EXIT WHEN ind_cursor%NOTFOUND;
stmt := 'ALTER INDEX ' || indx_name || ' REBUILD PARTITION ' ||part_name ||' ONLINE';
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
END LOOP;
END;
/

Here you only have to put the table name.


Single Syntax:
ALTER INDEX index_nm REBUILD PARTITION partition_name;