• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » how to find indexes on a table in oracle

how to find indexes on a table in oracle

December 8, 2019 by techgoeasy 4 Comments


how to find indexes on a table in oracle

It is very common that you want to Find indexes status and assigned columns for a oracle table
And also you sometime want to know the columns which are assigned to the indexes.
Following views would be helpful in these cases and to find indexes on a table in oracle

dba_ind_columns : This is to used if login with user having DBA role
all_ind_columns : This is to used if login with user having normal role
user_ind_columns : This is to used if login with user having normal role

Table of Contents

  • How to check index on table in oracle
  • how to check index status in oracle
  • Query to check Index Associated with a Constraint
  • List all indexes in a schema
  • Query to find the index statistics for the table
  • Query to find index column definition
  • Recommended Courses

How to check index on table in oracle

We can use below query on how to check index column on table in oracle and how to check index on table in oracle. Here is the query on how to find indexes on a table in oracle

set pagesize 50000 verify off echo off

col table_name head 'Table Name' format a20
col index_name head 'Index Name' format a25
col column_name head 'Column Name' format a30

break on table_name on index_name

select    table_name, index_name, column_name
from    all_ind_columns
where    table_name like upper('&Table_Name')
order by table_name, index_name, column_position
/

how to check index status in oracle

Normal Index

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes;

how to check unusable indexes in oracle

SELECT owner, index_name, tablespace_name,status
FROM dba_indexes
WHERE status = 'UNUSABLE';

Index partitions:

SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS;

If you want to know unusable ind partitions, then we  can use below query

SELECT index_owner, index_name, partition_name, tablespace_name,status
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';

Index sub partitions:

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS;

If you want to know unusable ind sub partitions, then we  can use below query

SELECT index_owner, index_name, partition_name, subpartition_name, tablespace_name,status
FROM dba_ind_SUBPARTITIONS
WHERE status = 'UNUSABLE';

Query to check Index Associated with a Constraint

SELECT index_name, table_name, uniqueness
  FROM DBA_INDEXES WHERE table_name = '&1';

List all indexes in a schema

SELECT index_name, table_name, uniqueness
  FROM DBA_INDEXES WHERE owner = '&1';

Query to find the index statistics for the table

set linesize 200
set pages 250
set verify off
col blevel format 99
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
       decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
       i.index_name,i.last_analyzed, c.column_position, c.column_name, c.column_length
  from sys.dba_ind_columns c, sys.dba_indexes i
 where (i.table_owner,i.table_name) in ('&1','&2')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_owner, i.table_name, i.index_name, c.column_position
/

Query to find index column definition

ttitle 'Index Column Definitions'
rem
set linesize 100
set pages 250
set verify off
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a20 heading 'INDEX NAME'
col column_name format a25 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 999 heading 'LEN'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name,
       decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
       i.index_name, c.column_position, c.column_name, c.column_length
  from sys.dba_ind_columns c, sys.dba_indexes i
 where i.table_owner like upper('&owner')
   and i.table_name like upper('&table')
   and i.index_name like upper('&index')
   and c.column_name like upper('&column')
   and i.owner = c.index_owner
   and i.index_name = c.index_name
 order by i.table_owner, i.table_name, i.index_name, c.column_position
/

With 12c, Oracle has introduced the concept of Partial indexes in Partitioned table.The *_INDEXES view has been modified to include an INDEXING column, which indicates if the index is FULL or PARTIAL.

Check the indexing status of the index.

SELECT index_name,
indexing
FROM dba_indexes
ORDER BY 1;

INDEX_NAME    INDEXING
------------------------- -------
EXP_INDEX_N1 PARTIAL

This happened when we have indexing partial tag in the create index statement.
CREATE INDEX EXP_INDEX_NI ON tab_name(col1) Local INDEXING PARTIAL;

Related Articles
Virtual Index in Oracle : What is Virtual Index in Oracle? Uses,limitation ,advantage and how to use to check explain plan in Oracle database, Hidden parameter _USE_NOSEGMENT_INDEXES
Oracle Index clustering factor : How Oracle Index clustering factor is calculated and how it impacts the explain plan
Oracle Partitioned Table : WHAT IS oracle partitioned table?,How to move the existing object to Partitioned object?,Partitioning for Performance – Partition Pruning
Oracle Partition Index : Understanding Oracle partition index ,What is Global Non partitioned Indexes?, What is local prefixed indexes, non prefixed local index
Types of indexes in oracle :This page consist of oracle indexes information,different types of indexes in oracle with example,how to create/drop/alter the index in oracle

I hope you like this compilation of oracle index related queries and it will help in finding answer to various queries like how to find index on a table, list all indexes in the schema, index status on a table and many more. Please do share with me what else i can add to this list to add more value to this post. Please do provide me the feedback also

Also Read
How to list all tables in Oracle : we can get the List All Tables in Oracle by either querying all_tables or user_tables or dba_tables. we can select column and where clause as per the need
How to check table size in Oracle :Find out the Query to check table size in Oracle database, top ten big tables in particular schema or particular tablespace in oracle
How to manage indexes

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins,Creating Tables and modifying its structure,Create View, Union ,Union -all and many other stuff. A great course and must have course for SQL starter
The Complete Oracle SQL Certification Course : This is good course for any body who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developer. This course gives us tricks and lesson on how to effectively use it and become productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skill. This is good course to learn about it and start doing sql performance tuning


Filed Under: Oracle, Oracle Database Tagged With: index, indexes status

Reader Interactions

Comments

  1. olivia says

    May 28, 2020 at 9:52 am

    Great…. now its easy to check index on a table in Oracle.

    Reply
    • techgoeasy says

      May 28, 2020 at 10:18 am

      Thanks Olivia

      Reply
  2. Ritu says

    November 8, 2020 at 2:46 am

    Hi techgoeasy: Can I have your email id and contact info because I am not able to find

    Reply
    • techgoeasy says

      November 9, 2020 at 3:06 pm

      hi

      Please send mail at [email protected]

      thx

      Reply

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • How to check Tablespace in Oracle -Size ,Free space,datafiles ,growth
  • how to check all constraints on a table in oracle
  • Query to check table size in Oracle database
  • Oracle dba interview questions and answers
  • Oracle Indexes and types of indexes in oracle with example



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to run Autoconfig on Patch system in R12.2
  • Admin scripts in R12.2 Ebuisness Suite
  • Timezone settings in Oracle database
  • how to enable trace in oracle apps r12
  • Transparent Data Encryption Queries

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us