按快了表中大概10000条数据,id is not null 9990 条 is null 10 条,当查询条件为is null 的时候,应该怎样加索引 。 create table test (name varchar2(20) ) ; insert into test select level from dual connect by level <=9990 ; commit ; insert into test select null from dual connect by level <=10 ; select * from test where name is null ;
create table test (name varchar2(20) ) ;
insert into test select level from dual connect by level <=9990 ;
commit ;
insert into test select null from dual connect by level <=10 ;
select * from test where name is null ;
一般情况下,where条件中和null比较将会导致full table scan,实际上,如果table中索引建列的值都为null,那么该行在索引(此处指b*tree,位图索引和聚簇索引可以有空值)中就不会存在,因此oracle为了保证查询结构的准确性,就会用full table scan代替index scan,这样理解,不走索引也就在情理之中。
当然,如果某个索引列上有定义为not null,在这种情况下,不存在所有索引列都为空的情况,所以此种情况下,是可以走index scan的,因此,对于where条件中含有类似is null,=null的情况,是否走索引,还是要看索引建中是否有某个列定义为not null。
一般查询条件中出现了这样的条件,都会进行全表扫描~~
create index xxx on xxx(id , 0)
或者和其他索引合并。dex@ORCL> create table test (name varchar2(20) ) ;Table created.dex@ORCL> insert into test select level from dual connect by level <=9990 ;9990 rows created.dex@ORCL> commit ;Commit complete.dex@ORCL> insert into test select null from dual connect by level <=10 ;10 rows created.dex@ORCL> commit ;Commit complete.dex@ORCL> create index idx_test_name on test(name) ;Index created.dex@ORCL> analyze table test compute statistics ;Table analyzed.dex@ORCL> select * from test where name is null ;10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 10 | 40 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("NAME" IS NULL)dex@ORCL> select segment_name , bytes/1024 from user_segments where segment_name='IDX_TEST_NAME' ;SEGMENT_NAME BYTES/1024
-------------------- ----------
IDX_TEST_NAME 256
dex@ORCL> drop index idx_test_name ;Index dropped.dex@ORCL> create index idx_test_name on test(name,0) ;Index created.dex@ORCL> analyze table test compute statistics ;Table analyzed.
dex@ORCL> select * from test where name is null ;10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1829724555----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST_NAME | 10 | 40 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - access("NAME" IS NULL)dex@ORCL> select /*+FULL(test)*/* from test where name is null ;10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 10 | 40 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------dex@ORCL> select segment_name , bytes/1024 from user_segments where segment_name='IDX_TEST_NAME' ;SEGMENT_NAME BYTES/1024
-------------------- ----------
IDX_TEST_NAME 256