Following query give us the status of Index which will be the right candidate to rebuild the index in poor index quality.
set linesize 300
spool index_info.txt
SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor,
case when nvl(i.clustering_factor,0) = 0 then 'No Stats'
when nvl(t.num_rows,0) = 0 then 'No Stats'
when (round(i.clustering_factor/t.num_rows *100)) < 6 then 'Excellent'
when (round(i.clustering_factor/t.num_rows *100)) between 7 and 11 then 'Good'
when (round(i.clustering_factor/t.num_rows *100)) between 12 and 21 then 'Fair'
else 'Poor'
end Index_Quality,
i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from user_indexes i, user_objects o, user_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
order by 1;
Share this:
X Press This Email Facebook LinkedIn WhatsApp Print Reddit Tumblr Pinterest Pocket Telegram Mastodon Nextdoor
Customize buttons
Related
Day 7: Working with Indexes
Day 7: Working with Indexes In Day 7 of our PostgreSQL learning series, we'll explore working with indexes in PostgreSQL. Indexes are essential for optimizing query performance by enabling faster data retrieval. We'll cover various aspects of indexes, including their types, creation, and usage, with detailed explanations and examples. Understanding…
April 6, 2024
In "PostgreSQL"
Rebuild Index offline and online in Oracle
Rebuild Index offline and online in Oracle Index is used in select query to fetch the data faster but due to insert, update and delete commands index is fragmented to remove the fragmentation of index we need to rebuild the index. Syntax --Offline rebuildALTER INDEX REBUILD; (default)-- Online rebuild used in…
September 5, 2014
In "Oracle"
Check fragmentation index script for schema by analyze command
Check fragmentation of indexes for schema by analyze command in Oracle We need to create script for analyze the command and check health from INDEX_STATS because INDEX_STATS contain only one row after each analyze command. So after every analyze command we need to run the select on INDEX_STATS table. INDEX_STATS…
February 25, 2020
In "Oracle"
This entry was posted in Oracle and tagged candidate for rebuild index, Check index is used, Fragmentation of index, Monitor index usage on January 15, 2013 by SandeepSingh. Edit
Unknown's avatar
About SandeepSingh
Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.
View all posts by SandeepSingh →
Post navigation← ORA-01591: lock held by in-doubt distributed transactionORA-609 , ORA-12170 and TNS-12537 in Oracle →
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Search
Search
Blog Stats
7,481,775 hits
Check the Undo tablespace Usage in Oracle
Exclude/Include option in EXPDP and IMPDP Datapump
Stop the EXPDP/IMPDP datapump Job in Oracle
Generate DDL for the User including grants in Oracle
Archive generated per day or hour in Oracle database
Check the Size of Oracle Database and PDB database
Oracle Database Management Services
Find the temp usage by sessions in Oracle
Check status, enable and disable the Audit in Oracle
Enable or disable the supplemental logging in Oracle
contactus@smarttechways.com
Blog at WordPress.com.