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:
Post a Comment