Skip to content
January 10, 2013 / doganay

COLUMN USAGE OF INDEXES WHICH ARE BIGGER THAN THEIR TABLES

sqlplus / as sysdba

create view dba_column_usage
as
select oo.name owner,
o.name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol#
/

create public synonym dba_column_usage for dba_column_usage
/

select
c.owner "TABLE_OWNER",c.tablename "TABLE_NAME",c.tablesize "TABLE SIZE (MB)",c.indexname "INDEX_NAME",c.indexsize "INDEX SIZE (MB)",c.indexsize/c.tablesize "INDEX/TABLE",
d.column_name, d.equality_preds, d.equijoin_preds, d.nonequijoin_preds, d.range_preds, d.like_preds, d.null_preds
from
(
with
tabs as (select owner, segment_name tablename,sum(bytes/1024/1024) tablesize from dba_segments where segment_type='TABLE' group by owner, segment_name),
inds as (select i.owner, i.index_name indexname, i.table_name tablename, sum(s.bytes/1024/1024) indexsize from dba_indexes i join dba_segments s on (i.owner=s.owner and i.index_name=s.segment_name) group by i.owner, i.index_name, i.table_name)
select * from tabs natural join inds where indexsize > tablesize and indexsize>100
) c, dba_column_usage d, dba_ind_columns e
where
c.owner=d.owner and c.tablename=d.name and d.column_name=e.column_name and
d.owner=e.index_owner and c.indexname=e.index_name and e.column_position=1
order by c.indexsize/c.tablesize desc
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: