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;
