Partitioned Indexes Oracle Query
Query to Check the Size of Partitioned Indexes
With the article “Query Partitioned Indexes Oracle Size,” we’ll be able to verify the size of partitioned indexes.
Effective and stable service in terms of performance relies on good data organization. Therefore, in large-scale environments, excellent data organization is essential to enable the database to perform optimally.
To keep data accessible in the best possible way, we need to start with tablespaces constructed based on type (TABLES, PARTITIONED TABLES, INDEXES, PARTITIONED INDEXES, LOB, PARTITIONED LOB) and the amount of data.
Tablespaces must be created according to the type and quantity of data they will host to configure the appropriate uniform size.
At a logical level, objects should be created with tables that do not exceed large sizes. If they do, they should be partitioned.
If the table is partitioned, the default index will also be partitioned with the same tablespace organization.
So, with this article, I present the query to extract information about partitioned indexes.
Executing the query, we can see the type of INDEX, the status of the INDEX, and the originating tablespace.
The selected fields are:
OWNER: Owner of the index.
INDEX_NAME: Name of the index.
INDEX_TYPE: Type of the index.
Additionally:
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.
Furthermore, we extract information from the views DBA_IND_PARTITIONS and DBA_SEGMENTS.
Finally, they are related by the JOIN OWNER=OWNER and INDEX_NAME=SEGMENT_NAME.
Partitioned Indexes Oracle Query:
set lines 200
set pages 99
col INDEX_OWNER for a16
col INDEX_NAME for a24
col INDEX_TYPE for a10
col TABLESPACE_NAME for a24
col PARTITION_NAME for a24
select i.INDEX_OWNER, i.INDEX_NAME, s.SEGMENT_TYPE, s.PARTITION_NAME, s.TABLESPACE_NAME, s.BYTES/1024/1024 as "Size MB", i.STATUS
from dba_ind_partitions i, dba_segments s
where i.INDEX_OWNER not in ('SYS','SYSTEM')
and i.INDEX_OWNER = s.OWNER
and i.INDEX_NAME = s.SEGMENT_NAME
and i.partition_name = s.partition_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