CREATE TABLE index_test(
   anumber        Number(10),
   avarchar2        Varchar2(100),
   adate        Date);

BEGIN
   FOR x IN 1..1000 LOOP
      INSERT INTO index_test 
         VALUES (x, RPAD('0',100,'0'), SYSDATE);
   END LOOP;
END;
/

COMMIT;

CREATE INDEX idx_invisible ON index_test (anumber) INVISIBLE;

EXEC DBMS_STATS.gather_table_stats(USER, 'INDEX_TEST’, cascade => TRUE);

SELECT index_name, visibility
  FROM user_indexes
 WHERE index_name = 'IDX_INVISIBLE';

SET AUTOTRACE ON

SELECT adate 
  FROM index_test
 WHERE anumber = 567;

SELECT /*+ INDEX(Index_Test idx_invisible) */ * 
  FROM index_test
 WHERE anumber = 567;

ALTER INDEX idx_invisible VISIBLE;

SELECT index_name, visibility
  FROM user_indexes
 WHERE index_name = 'IDX_INVISIBLE';

SELECT adate 
  FROM index_test
 WHERE anumber = 567;

ALTER INDEX idx_invisible INVISIBLE;

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

SELECT adate 
  FROM index_test
 WHERE anumber = 567;

-- CLEAN UP

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE;

DROP TABLE Index_Test;