Difference between revisions of "Index Maintenance"
Jump to navigation
Jump to search
(Created page with "Size of all indexes: <source lang="sql"> with idx as ( select * from dba_indexes where index_type = 'NORMAL') , seg as ( select owner, segment_name, round(sum(bytes)) bytes...") |
|||
| Line 6: | Line 6: | ||
where index_type = 'NORMAL') | where index_type = 'NORMAL') | ||
, seg as ( | , seg as ( | ||
| − | select owner, segment_name, | + | select owner, segment_name, sum(bytes) bytes |
from dba_segments | from dba_segments | ||
group by owner, segment_name) | group by owner, segment_name) | ||
| − | select idx.index_name, idx.segment_created, seg.bytes / 1024 / 1024 MB | + | select idx.index_name, idx.segment_created, round(seg.bytes / 1024 / 1024) MB |
from idx | from idx | ||
, seg | , seg | ||
Latest revision as of 14:32, 26 April 2016
Size of all indexes:
with idx as (
select *
from dba_indexes
where index_type = 'NORMAL')
, seg as (
select owner, segment_name, sum(bytes) bytes
from dba_segments
group by owner, segment_name)
select idx.index_name, idx.segment_created, round(seg.bytes / 1024 / 1024) MB
from idx
, seg
where seg.owner(+) = idx.owner
and seg.segment_name(+) = idx.index_name
and seg.bytes is not null
order by seg.bytes;