11GR1中的参数'SKIP_UNUSABLE_INDEXES'

最新推荐文章于 2021-02-16 00:23:05 发布

原创最新推荐文章于 2021-02-16 00:23:05 发布 · 902 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#object #table #access #integer #sql #caching

oracle 专栏收录该内容

90 篇文章

订阅专栏

本文介绍Oracle数据库中如何通过设置参数skip_unusable_indexes为TRUE来跳过不可用索引，避免全表扫描，提高查询效率。并演示了创建表、建立索引、查询计划及更改系统参数的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT VALUE FROM SYS.LOADER_SKIP_UNUSABLE_INDEXES，查找后是一张视图：

CREATE OR REPLACE FORCE VIEW SYS.LOADER_SKIP_UNUSABLE_INDEXES (VALUE)
AS
   SELECT COUNT (*) AS VALUE
     FROM v$parameter
    WHERE UPPER (name) = 'SKIP_UNUSABLE_INDEXES' AND VALUE = 'TRUE';

涉及到参数：'SKIP_UNUSABLE_INDEXES'

show parameter SKIP_UNUSABLE_INDEXES

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
skip_unusable_indexes boolean TRUE

默认为TRUE

show parameter index

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_index_caching              integer                          0
optimizer_index_cost_adj             integer                          100
optimizer_use_invisible_indexes      boolean                          FALSE
skip_unusable_indexes                boolean                          TRUE

测试：

create table tcyang tablespace users as select * from dba_objects where rownum<=10;

Table created.

Elapsed: 00:00:00.08

create index idx_object_name on tcyang(object_name) tablespace users;

Index created.

Elapsed: 00:00:00.01

select * from tcyang where object_name='I_COL2';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS I_COL2 49 49 INDEX 2007-11-07 13:53:13 2007-11-07 13:53:13 2007-11-07:13:53:13 VALID N N N 4

Execution Plan
----------------------------------------------------------

Plan hash value: 62112992

-----------------------------------------------------------------------------------------------
| Id | Operation                   | Name            | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |   207 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TCYANG          |     1 |   207 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_OBJECT_NAME |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

2 - access("OBJECT_NAME"='I_COL2')

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

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

alter index idx_object_name unusable;

select * from tcyang where object_name='I_COL2';

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3869935700

----------------------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |   207 |     2   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TCYANG |     1 |   207 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME"='I_COL2')

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

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

select * from user_indexes where index_name='IDX_OBJECT_NAME';

INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH TABLESPACE_NAME                 INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS   PCT_FREE LOG     BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS     NUM_ROWS SAMPLE_SIZE
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- --------- -------- ------------- ------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- --- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- -------- ---------- -----------
LAST_ANALYZED       DEGREE                                   INSTANCES                                PAR T G S BUFFER_ USE DURATION        PCT_DIRECT_ACCESS ITYP_OWNER                     ITYP_NAME
------------------- ---------------------------------------- ---------------------------------------- --- - - - ------- --- --------------- ----------------- ------------------------------ ------------------------------
PARAMETERS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO VISIBILIT DOMIDX_MANAGEM
--- ------------ ------ -------- --- --- --- --------- --------------
IDX_OBJECT_NAME                NORMAL                      SYS                            TCYANG                         TABLE       NONUNIQUE DISABLED               USERS                                   2        255          65536                       1 2147483645                                                                              10 YES          0           1            10                       1                       1                 1 UNUSABLE         10          10
2012-06-09 11:48:16 1                                        1                                        NO N N N DEFAULT NO

NO NO NO NO VISIBLE

alter system set skip_unusable_indexes=false scope=memory;

再次查询：

select * from tcyang where object_name='I_COL2';

报错。