• 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

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

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

Recent Posts

  • Oracle tkprof utility
  • What is Skew in Database?
  • how to find file based on size in linux
  • SQL trace, 10046 event in Oracle
  • How to use sed replace string in file

Copyright © 2023 : TechGoEasy

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