Sunday, May 04, 2014

Invisible Indexes (Part 2)

In Oracle 11.1.0.6.0, you could not run DBMS_STATS against an invisible index or a table with an invisible index. I found 2 workarounds. You could run the old ANALYZE command or you could make the index visible on a temporary basis:

SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner) invisible
  3  /

Index created.

SQL> exec dbms_stats.gather_table_stats -
> (user,'my_table',cascade=>true);
BEGIN dbms_stats.gather_table_stats  (user,'my_table',cascade=>true); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 17806
ORA-06512: at "SYS.DBMS_STATS", line 17827
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats(user,'my_index');
BEGIN dbms_stats.gather_index_stats(user,'my_index'); END;

*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 14037
ORA-06512: at "SYS.DBMS_STATS", line 14060
ORA-06512: at line 1

SQL> analyze table my_table compute statistics
  2  /

Table analyzed.

SQL> analyze index my_index validate structure
  2  /

Index analyzed.

SQL> alter index my_index visible
  2  /

Index altered.

SQL> exec dbms_stats.gather_table_stats -
> (user,'my_table',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'my_index');

PL/SQL procedure successfully completed.

SQL>

This is bug 6344547. According to Metalink / My Oracle Support, it was fixed in version 11.1.0.7.0. I was unable to check this but did confirm that it was fixed in version 11.2.0.1.0.

********************

The initialisation parameter optimizer_use_invisible_indexes allows the optimizer to use invisible indexes. In the example below, a table is created with an invisible index. A SELECT statement, which would normally use this index, is run against the table but the index is not used because it is invisible. The initialisation parameter is set to true and the SELECT statement is run again. This time it uses the index:

SQL> create table my_table
  2  as select * from dba_tables
  3  /

Table created.

SQL> create index my_index
  2  on my_table(owner) invisible
  3  /

Index created.

SQL> set autotrace on
SQL> select count(*) from my_table
  2  where owner = 'SYSTEM'
  3  /

  COUNT(*)
----------
       154


Execution Plan
----------------------------------------------------------
Plan hash value: 228900979

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    17 |     6  (17)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| MY_TABLE |   143 |  2431 |     6  (17)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYSTEM')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter session set optimizer_use_invisible_indexes = true
  2  /

Session altered.

SQL> select count(*) from my_table
  2  where owner = 'SYSTEM'
  3  /

  COUNT(*)
----------
       154


Execution Plan
----------------------------------------------------------
Plan hash value: 4077732364

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    17 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |    17 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_INDEX |   154 |  2618 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SYSTEM')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         68  consistent gets
          1  physical reads
          0  redo size
        523  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

No comments: