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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Check if a table is Indexed in Oracle

by admin

Understanding ‘Why my index is not being used?’ is a big topic as there are many varied reasons for indexes not being used. The first thing you can do is to check whether your table is indexed or not.

Why SHOULD the index be used?

Oracle does not have to use an index simply because an index exists. If a query requires every row in the table to be queried (in table joins, for example), then why query all the rows in the index AND all the rows in the table? Ignoring the index in this case results in better performance. The optimizer makes decisions based on statistics regarding the suitability of various access methods, including indexes, for all queries and chooses the best one.

The table is indexed, isn’t it?

Check that the table which you believe should be accessed via an index actually has indexes defined on it. The indexes could have been dropped or could have failed to create – For example, it is possible, after importing tables, or performing loads, that indexes were not created due to errors (either software or human). The following query shows an example of checking for indexes:

SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Migrate ASM Disk Groups to another Storage Online [non ASMLIB Devices]
  2. How to shrink a Temporary Tablespace datafile in Oracle
  3. MUTATING Table Error and How to Resolve it (ORA-04091)
  4. Script To Get Tablespace Utilization In Oracle Database 12c
  5. How to Enable or Disable Veritas ODM for Oracle database 12.1.0.1
  6. ORA-14652: reference partitioning foreign key is not supported
  7. Basic SELECT Statement in PL/SQL
  8. Oracle Scheduler 12c New Features and Enhancements
  9. How to Check the Environment Variables for an Oracle Process
  10. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright