Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 11g Invisible Index

By Saurabh Gupta | on May 27, 2011 |
Oracle 11g Tutorials

Oracle 11g release has introduced a new property of an Index i.e. Visibility. Before the world saw 11g release, an index was always created and existed in visible mode. But now, as per the fresh feature in Oracle 11g, an index may exist in Invisible Mode too. The optimizer may (may not) use the index during query execution.

In this tutorial, I shall familiarize you with the Visibility mode of an Index, its impact and analysis in sync with my findings.

Invisible Index: The Cause and Introduction

SQL tuning is a hit and trial method with majority of cases. Indexing is one of the primary actions in SQL tuning. If a complex SQL query has to be fine tuned, multiple indexing options follow different access path and can yield varied performance. Prior to Oracle 11g, multiple indexing options have to be created, tested and dropped before moving to the nest option. This ‘create and drop index’ approach appeared a crude solution for complex query tuning.

With 11g release, Invisible Index provides a discrete solution to handle scenarios of above sort. Multiple indexing options can be created in INVISIBLE mode. At a time, one (or more) out of many can be made visible to the optimizer. Now, let the optimizer use the available visible indexes and arrive at the performance matrix. The approach can be practiced until the best indexing option is identified.

A new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEX has been introduced to govern the usage of invisible index by the optimizer.

Invisible Index: Syntax and Illustration

By default, an index is always created in VISIBLE mode. The syntax for the creation of an invisible index shows the inclusion of a new INVISIBLE keyword.

Syntax [1]
CREATE INDEX [INDEX NAME] ON [TABLE NAME (COLUMN NAME)] INVISIBLE;
Code [1]

The statement below creates an index T_INV_IDX on the EMPLOYEE_ID column of EMPLOYEES table.

SQL> CREATE INDEX T_INV_IDX ON EMPLOYEES (EMPLOYEE_ID) INVISIBLE;
 
INDEX created.

An existing index can be altered to toggle over the visibility modes.

Syntax [2]
ALTER INDEX [INDEX NAME] [VISIBLE | INVISIBLE]

A VISIBLE index T_INV_IDX is altered to switch to INVISIBLE mode.

SQL> ALTER INDEX T_INV_IDX INVISIBLE; 
 
INDEX altered.

OPTIMIZER_USE_INVISIBLE_INDEX is a boolean parameter which can be set as either TRUE or FALSE. By default, the new initialization parameter OPTIMIZER_USE_INVISIBLE_INDEX is set to FALSE.

If the parameter is set as FALSE, optimizer would ignore the invisible index and use only the visible index while designing the query access path. If it is TRUE, optimizer would considerate all the indexes on a table, irrespective of their visibility modes. Vocally, this setting (OPTIMIZER_USE_INVISIBLE_INDEX as TRUE) nullifies the purpose of Invisible Indexes.

Its value can be changed using ALTER [SYSTEM | SESSION] command.

Invisible Index: Demonstration and Impact analysis

I shall demonstrate the impact and usage of Invisible index with the help of below illustration.
For dedicated testing, I shall create a fresh table.

Code [3a]
SQL> CREATE TABLE T_INV_IND
    (COL1 NUMBER,
     COL2 NUMBER,
     COMM VARCHAR2(100)
     );
 
TABLE created.

Now, we insert huge volume of test data to increase the scalability and precision of index usage.

Code [3b]
SQL> INSERT INTO T_INV_IND
SELECT ROWNUM * 2, ROWNUM * 3, ‘Comment – ‘||ROWNUM
FROM DUAL
CONNECT BY ROWNUM < 1000000;
 
1000000 ROWS inserted.

Adding the primary key constraint on COL1 of the table

Code [3c]
SQL> ALTER TABLE T_INV_IND ADD PRIMARY KEY(COL1);
 
TABLE altered.

Adding a B-tree Invisible Index on COL2 column of the table

Code [3d]
SQL> CREATE INDEX T_INV_IDX1 ON T_INV_IND(COL2) INVISIBLE;
 
INDEX created.

A new column ‘VISIBILITY’ has been added to USER_INDEXES dictionary view to maintain the visibility mode of the index. For the above index T_INV_IDX1, the status is updated as INVISIBLE.

Example Code [3e]

SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      INVISIBLE

Case 1: OPTIMIZER_USE_INVISIBLE_INDEX = FALSE and INVISIBLE Index

In this case, the optimizer would ignore the index during query access path optimization. This case efficiently demonstrates the impact of visible mode on a query.

The below query has been executed and its explain plan can be generated

Code [4a]
SQL> SELECT * FROM T_INV_IND WHERE COL2 < 50;

Inference: The explain matrix shows that the optimizer has undergone full scan of the table T_INV_IND to retrieve the query result set. The optimizer has ignored the invisible index on COL2 of the table.

Using hints to enforce invisible index

Alternatively, in the situations as in Case 1, an invisible index can be enforced in a query using /*+INDEX*/ hint. The optimizer forcibly uses the index in the query access path.

The index T_INV_IDX1 was altered to INVISIBLE mode.

Code [4b]
ALTER INDEX T_INV_IDX1 INVISIBLE 
 
INDEX altered.

Now, explain plan for the below query can be generated to demonstrate the usage of hints with invisible indexes.

Code [4c]
SELECT /*+ INDEX(T_INV_IDX1) */ *
FROM T_INV_IND
WHERE COL2 < 50;

Inference: Oracle hint overrides the invisible effect of an index. Note the explain plan matrix is same as that in Case 1.

Case 2: OPTIMIZER_USE_INVISIBLE_INDEX = FALSE and VISIBLE Index

Now, we make the index visible to infer the impact of visible mode.

Code [5a]
SQL> ALTER INDEX t_inv_idx1 visible;
 
INDEX altered.

The status changes to VISIBLE mode in the USER_INDEXES view.

Code [5b]
SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      VISIBLE

Now, we shall query the T_INV_IND table with COL2 column to generate the explain plan of the query. The PLAN_TABLE_OUTPUT shows the performance matrix of the query.

Inference: As per the explain plan, the optimizer uses the index T_INV_IDX1 to perform Index Range Scan. This implies that an Invisible Index behaves like a normal index in visible state.

Case 3: OPTIMIZER_USE_INVISIBLE_INDEX = TRUE and INVISIBLE Index

The initialization parameter was changed to TRUE. This setting can be done by DBA or privileged users. The change directs optimizer to use indexes in invisible mode, while deciding query access path.

After Case 2, the index T_INV_IDX1 still remains in INVISIBLE mode.

Code [6a]
SQL> SELECT INDEX_NAME, TABLE_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = ‘T_INV_IDX1';
 
INDEX_NAME    TABLE_NAME                     VISIBILIT
------------- ------------------------------ ---------
T_INV_IDX1    T_INV_IND                      INVISIBLE

Now, we shall execute the same query as in Case 1 to study the impact of parametric change on the optimizer.

Inference: The explain plan matrix shows the index range scan and usage of T_INV_IDX1 index. If the parameter OPTIMIZER_USE_INVISIBLE_INDEX is TRUE, visibility mode of an index is nullified.

« « How to Improve Communication Skills
Do you know When to be Assertive » »

Author Description

Avatar

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Read Only Tables

    June 16, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT