频道栏目
IT货架 > > 正文
关于index_ffs使用索引的一点问题.
网友分享于:Jun 13, 2018 12:02:04 AM    来源: IT货架   

SQL> desc test_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NUMBER
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(19)
SQL> select count(*) from test_objects;
COUNT(*)
----------
68387
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_OBJECTS' (TABLE) (Cost=137
SQL> select count(*) from test_objects where object_id is not null;
COUNT(*)
----------
68387
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST_OBJECTS' (INDEX) (Co
SQL> alter table test_objects modify object_id not null;
Table altered.
SQL> desc test_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(19)
SQL> select count(*) from test_objects;
COUNT(*)
----------
68387
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IND_TEST_OBJECTS' (INDEX) (

 

----------------------------------------------------------------------------------

c.索引访问路径oracle并没有考虑
主要讨论index fast full scan
index fast full scan类似全表扫描,只是把索引当作表来处理,支持并行和多块读
index fast full scan的前提是,索引必须满足这个查询,同时必须保证非空
(通过非空字段或者是在where条件中声明非空),最为典型的就是count(*)
SQL> create table test as select * from dba_objects;
表已创建。
SQL> create index i_test_1 on test(object_id);
索引已创建。
SQL> analyze table test compute statistics;
表已分析
SQL> select count(*) from test;
执行计划
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 154 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 49883 | 154 (2)| 00:00:02 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
690 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
这里虽然有索引,但是oracle并不知道索引中是否有null值,所以无法走索引
SQL> select count(*) from test where object_id is not null;
执行计划
----------------------------------------------------------
Plan hash value: 1366347385
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 4 | 27 (4)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 4 | |
|
|* 2 | INDEX FAST FULL SCAN| I_TEST_1 | 49882 | 194K| 27 (4)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
117 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

。。。说白了一句话:index 不存null
你让人家走索引,问题你得告诉他,你要的数据都得在索引中~

广告服务联系QQ:1134687142 | 网站地图

版权所有: IT货架- 内容来自互联网,仅供用于技术学习,请遵循相关法律法规. 京ICP备11030978号-1