Index Size Query
Query to check the size of indexes
With the article “Index Size Query,” we quickly examine the indexes and their sizes.
Indexes are database objects that allow fast access to data. If there were no index on the table, queries would be very slow because they would read all the rows sequentially, generating a lot of read I/O on the disk and a significant CPU consumption.
Therefore, the presence and maintenance of indexes are very important factors to keep the database performing well.
If a table undergoes frequent DELETE operations, it will surely have fragmented space inside, which can lead to significant slowdowns.
Therefore, in these situations, it is necessary to perform data reorganization, i.e., MOVE TABLE, REBUILD INDEX, and final statistics.
So, with the query provided below, you can identify: the index type, the partition name, the originating tablespace, and the index status.
The selected fields are:
OWNER: Owner of the index.
INDEX_NAME: Name of the index.
INDEX_TYPE: Type of the index.
PARTITION_NAME: Name of the partition.
TABLESPACE_NAME: Name of the tablespace where the index resides.
BYTES: Size of the index converted to MB.
STATUS: Status of the index.
Additionally, we extract information from the views DBA_INDEXES and DBA_SEGMENTS.
Finally, we relate the data by the JOIN OWNER=OWNER and INDEX_NAME=SEGMENT_NAME.
Query to check the size of indexes:
set lines 200
set pages 99
col OWNER for a16
col INDEX_NAME for a30
col INDEX_TYPE for a21
col TABLESPACE_NAME for a24
col PARTITION_NAME for a24
select i.OWNER, i.INDEX_NAME, i.INDEX_TYPE, s.PARTITION_NAME, s.TABLESPACE_NAME, s.BYTES/1024/1024 as "Size MB", i.STATUS
from dba_indexes i, dba_segments s
where i.OWNER not in ('SYS','SYSTEM') and
i.OWNER = s.OWNER and
i.INDEX_NAME = s.SEGMENT_NAME
order by 1,2,4;To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.
Stay updated on our activities by joining our community (make sure to like our pages, let’s connect, and stay updated on each of our new guides!):
Facebook
Youtube
Linkedin
For those who haven’t done so yet, we recommend saving this site among your bookmarks, so you’ll always have our solutions to the most common database problems at your fingertips, especially Oracle databases.
0 Comments