analyze table supermag.ffmaprep_ validate structure cascade;
select do.owner,do.object_name, do.object_type,sysind.flags
from dba_objects do, sys.ind$ sysind
where do.object_id = sysind.obj#
and bitand(sysind.flags,4096)=4096;
set autotrace on explain
set feedback 1
select /*+ full(t1) parallel */ sum(ora_hash(rowid))
from <TABLE NAME> t1
where <INDEXED COLUMN> is not null
minus
select /*+ index_ffs(t <INDEX NAME>) parallel */ sum(ora_hash(rowid))
from <TABLE NAME> t
where <INDEXED COLUMN> is not null;
create or replace procedure analyze_quick(owner_table varchar2, name_table varchar2, name_index varchar2 default null)
is
s varchar2(30000);
num_indexes number := 0;
sum_hash number;
begin
for i in (select a.owner, a.index_name, b.column_name
from dba_indexes a, dba_ind_columns b
where a.table_owner = upper(owner_table)
and a.table_name = upper(name_table)
and (a.index_name = upper(name_index) or name_index is null)
and a.index_type not in ('IOT - TOP'
,'LOB'
,'FUNCTION-BASED NORMAL'
,'FUNCTION-BASED DOMAIN'
,'CLUSTER')
and a.owner = b.index_owner
and a.index_name = b.index_name
and a.table_name = b.table_name
and b.column_position = 1) loop
num_indexes := num_indexes+1;
s := 'select /*+ full(t1) parallel */ sum(ora_hash(rowid)) from ';
s := s || owner_table || '.' || name_table || ' t1 where ' || i.column_name ||' is not null MINUS ';
s := s || 'select /*+ index_ffs(t '|| i.index_name||') */ sum(ora_hash(rowid)) from ';
s := s || owner_table || '.' || name_table || ' t where ' || i.column_name ||' is not null';
begin
execute immediate s into sum_hash;
if sum_hash > 0 then
raise_application_error(-20220,'TABLE/INDEX MISMATCH detected!! Table: '
||upper(owner_table)||'.'||upper(name_table)
||' Index: '
||upper(i.index_name));
end if;
exception
when no_data_found then null; -- no_data_found means that there is not inconsistency
end;
end loop;
if num_indexes = 0 and name_index is not null then
raise_application_error(-20221,'Check was not executed. Index '||upper(name_index)||' does not exist for table '||upper(name_table)|| ' or table does not exist');
elsif num_indexes = 0 then
raise_application_error(-20222,'Check was not executed. No INDEXES with index_type=NORMAL found for table '||upper(name_table)|| ' or table does not exist');
end if;
end;
/
execute analyze_quick('&TABLE_OWNER','&TABLE_NAME');
ANALYZE TABLE supermag.FFMapRep_ ESTIMATE STATISTICS SAMPLE 5 PERCENT;