This oracle function will help you quickly check if an index is used or not, you have to enable his monitoring before
CREATE OR REPLACE FUNCTION is_index_used (owner VARCHAR,
table_name VARCHAR,
index_name VARCHAR)
RETURN VARCHAR
IS
is_used VARCHAR (3);
cnt NUMBER;
BEGIN
is_used := 'NO';
SELECT COUNT (*) CNT
INTO cnt
FROM SYS."_CURRENT_EDITION_OBJ" io,
SYS."_CURRENT_EDITION_OBJ" T,
sys.ind$ i,
sys.USER$ iu,
sys.USER$ tu,
sys.object_usage ou
WHERE io.owner# = iu.USER#
AND i.obj# = io.obj#
AND io.obj# = ou.obj#(+)
AND T.obj# = i.bo#
AND T.owner# = Tu.USER#
AND i.type# NOT IN (4, 8, 9)
AND BITAND (io.flags, 128) <> 128
AND iu.name = owner
AND io.name = index_name
AND T.name = table_name
AND DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') = 'YES'
AND DECODE (BITAND (ou.flags, 1), 0, 'NO', NULL, NULL, 'YES') =
'YES';
IF cnt > 0
THEN
is_used := 'YES';
ELSE
is_used := 'NO';
END IF;
RETURN is_used;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN is_used;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END;
/
I recommend to wait minimum one week after enabling monitoring before you check it with a simple call like this ...
select INDEX_NAME,is_index_used(OWNER,TABLE_NAME,INDEX_NAME) from dba_indexes where owner = 'FOMI' and table_name = 'ACCOUNTS'
No comments:
Post a Comment