Skip to content

Instantly share code, notes, and snippets.

@mehdip2007
Created April 5, 2020 10:38
Show Gist options
  • Save mehdip2007/384af70ab6a7faf676c00aa95879e693 to your computer and use it in GitHub Desktop.
Save mehdip2007/384af70ab6a7faf676c00aa95879e693 to your computer and use it in GitHub Desktop.
When you table get a changes a lot your table index might get fragment, so u can use the below query to find the fragmented index.
select
'exec analyzedb.reorg_a_table4('||' '||rtrim(t.table_owner)|| ' ' ||','|| ' ' ||
rtrim(t.table_name)||' '||');',
t.table_owner||'.'||t.table_name name,
a.num_rows,
sum(t.inserts) ins,
sum(t.updates) upd,
sum(t.deletes) del,
sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,'999999.99') per_del,
round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
from DBA_TAB_MODIFICATIONS t, all_tables a
where t.timestamp >= to_date(sysdate - 200,'dd-mon-yyyy')
and t.table_owner = a.owner and t.table_owner not in ('SYS','SYSTEM')
and t.table_name=a.table_name
and t.table_name = 'GSM_SERVICE_MAST'
group by t.table_owner, t.table_name, a.num_rows
--having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
order by num_rows desc, t.table_owner, t.table_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment