Table size plus Index Size Combined

The_Cute_DBA

We have this DW test environment, one request is to list all table sizes for specific schemas. The table size in the report should already include the size of all it's index. We only need to report tables having 1Million rows or more based on num_rows estimate. (These tables will then be reviewed for size usage and are candidate for rows purging).

I have made two separate queries, one for table size and one for index sizes.

--Table Size
select seg.owner, seg.segment_name, seg.bytes/1024/1024 as table_size_mb, tab.num_rows
from dba_segments seg join dba_tables tab
on (seg.segment_name = tab.table_name)
where seg.owner in
(
'JAMES',
'JOHN',
'MARIA'
)
and NVL(tab.NUM_ROWS,0) > 1000000;

---Index Size
SELECT seg.owner, idx.table_name, idx.index_name, SUM(seg.bytes)/1024/1024 as index_size_mb
  FROM dba_segments seg,
  dba_indexes idx
  WHERE idx.table_owner in
(
'JAMES',
'JOHN',
'MARIA'
)
  AND idx.table_name IN
  (
  select table_name from dba_tables where nvl(num_rows,0) > 1000000
  )
  AND idx.owner = seg.owner
  AND idx.index_name = seg.segment_name
  GROUP BY seg.owner, idx.index_name, idx.table_name;

I cannot figure out a way of combining the query results so that I can have a report that is something like this:

TABLE_OWNER         TABLE_NAME         SIZE_MB          NUM_ROWS
James               Customer_Tb1         120             2000000
James               Customer_Tb2         100             1100000
James               Customer_Tb3         300             3200000
John                Orders_Tb1           545             1300000
John                Orders_Tb2           900             1500000
Maria               Recept_Tb1           750             6700000

SIZE_ MB is the table size plus the size of all its indexes

NUM_ROWS is the row estimate of the table that should be 1M or more in order to be qualified in the report

Jonathan Lewis

There's a thread on this topic at https://community.oracle.com/tech/developers/discussion/4299814/find-largest-table-including-indexes-and-lobs but I wrote a note based on the requirement explaining the steps that you might need to get to the right answer here: https://jonathanlewis.wordpress.com/2019/11/06/table-space/

The answer may be more complex than you need, but there may be components (IOTs, LOBs, nested tables) that you have in your system that you've overlooked.

For a very straightforward case, if you've produced statement for the tables and indexes, and you've included the table name in the query for indexes, you can align the two sets of results (by adding a NULL column to the query for tables and labelling it index_name) and doing a UNION ALL.

Regards

Jonathan Lewis

BEDE

Better use sys.dba_objects instead. There you have object_type.

Something like:

select sum(sg.bytes), sum(sg.blocks),ob.owner, ob.object_name, ob.object_type
from sys.dba_objects ob
left join sys.dba_segments sg on ob.owner=sg.owner and ob.object_name=sg.segment_name and ob.object_type=sg.segment_type
where object_type in ('TABLE','INDEX') and ob.owner not in ('SYS','SYSTEM','XDB')
group by ob.owner, ob.object_name, ob.object_type
;

Still, until now, whenever I have looked for the space used I've queried only sys.dba_sagments or all_segments, without joining it with anything else, seeing that sys_dba_segments tells about the object type in the segment_type column. For tables you have segment_type like 'TABLE%', while for indexes you have segment_type like 'INDEX%'.

Jonathan Lewis

There's a thread on this topic at https://community.oracle.com/tech/developers/discussion/4299814/find-largest-table-including-indexes-and-lobs but I wrote a note based on the requirement explaining the steps that you might need to get to the right answer here: https://jonathanlewis.wordpress.com/2019/11/06/table-space/

The answer may be more complex than you need, but there may be components (IOTs, LOBs, nested tables) that you have in your system that you've overlooked.

For a very straightforward case, if you've produced statement for the tables and indexes, and you've included the table name in the query for indexes, you can align the two sets of results (by adding a NULL column to the query for tables and labelling it index_name) and doing a UNION ALL.

Regards

Jonathan Lewis

The_Cute_DBA

Thank you, the link is very helpful.

I was able to use the reference code to come up with a better solution.

Jonathan Lewis

Thanks for the acknowledgement, glad it helped.

Regards

Jonathan Lewis