• 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 make index invisible in oracle

How to make index invisible in oracle

November 20, 2020 by techgoeasy Leave a Comment


Table of Contents

  • What   are  Invisible Indexes?
  • How to use the Invisible Indexes at the Session level
  • How to make invisible index visible
  • How to make visible index invisible

What   are  Invisible Indexes?

Beginning with Oracle Database 11g Release 1, you can create invisible indexes or make an existing index invisible. An invisible index is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Unlike unusable indexes, an invisible index is maintained during DML statements. Although you can make a partitioned index invisible, you cannot make an individual index partition invisible while leaving the other partitions visible. Using invisible indexes, you can do the following:

  • Test the removal of an index before dropping it.
  • Use temporary index structures for certain operations or modules of an application without affecting the overall application.

How to use the Invisible Indexes at the Session level

SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
---- ---- -----
optimizer_use_invisible_indexes boolean FALSE
SQL> alter session set optimizer_use_invisible_indexes=TRUE;
Session altered.
SQL> show parameter optimizer_use_invisible_indexes;
NAME TYPE VALUE
---- ---- -----
optimizer_use_invisible_indexes boolean TRUE

How to make invisible index visible

You can just use the alter index command

ALTER INDEX index_name VISIBLE;

How to make visible index invisible

ALTER INDEX index_name INVISIBLE;

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

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

Recent Posts

  • Multi Language Support in 11i/R12
  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1

Copyright © 2021 : TechGoEasy

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