当前位置:操作系统 > Unix/Linux >>

让优化器用上可为空的索引

让优化器用上可为空的索引
 
首先创建一张测试表:
CREATE TABLE FOO(
  USERID NUMBER(20) NULL,
  USERNAME VARCHAR2(100),
  USERDEPT NUMBER(20),
  USERDEPTNAME VARCHAR2(100)
)
/
模拟上10万条数据进去:
BEGIN
  FOR I IN 0..100000 LOOP
    INSERT INTO FOO
    VALUES
    (I,'ZIWEN'||i,MOD(I,30),'USERDEPTNAME'||MOD(I,30));
  END LOOP;
  IF MOD(I,100) =0 THEN
    COMMIT;
  END IF;
END;
/
在可以为NULL的USERID上面增加一个索引:
CREATE INDEX INDEX_FOO_USERID ON FOO(USERID);
 
进行表分析:
ANALYZE TABLE FOO
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL INDEXED COLUMNS;
 
执行下面的语句,按照道理而言,应该走INDEX,INDEX RANGE SCAN,实际上也是走的索引范围扫描。
SELECT USERID FROM FOO WHERE USERID = 100;
 
 
修改其中的某些USERID为空再试一下,仍旧走的是索引范围扫描。
UPDATE FOO SET FOO.USERID = NULL WHERE MOD(USERID,1000)=0;
 
给查询的SQL增加HINT提示,看一下执行计划:
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
 
执行计划如下:
Plan hash value: 1245013993
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  3515K|   171   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| FOO  |   100K|  3515K|   171   (1)| 00:00:03 |
--------------------------------------------------------------------------
 
为什么即使给了HINT也不走索引INDEX_FOO_USERID呢?是因为USERID允许为空么?
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO WHERE USERID IS NOT NULL;
 
执行计划如下:
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   100K|  3515K|   756   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FOO              |   100K|  3515K|   756   (1)| 00:00:10 |
|*  2 |   INDEX FULL SCAN           | INDEX_FOO_USERID |   100K|       |   224   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------
增加了IS NOT NULL之后,索引走了INDEX_FOO_USERID。
 
换一个角度,如果让USERID列,设置为非空的话,会不会走索引呢?
UPDATE FOO SET FOO.USERID =990000 WHERE USERID IS NULL;
ALTER TABLE FOO MODIFY(USERID NOT NULL); 
 
然后再执行刚刚的语句,只是这个时候不加IS NOT NULL:
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
这个时候的执行计划:
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |   100K|  3515K|   756   (1)| 00:00:10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FOO              |   100K|  3515K|   756   (1)| 00:00:10 |
|   2 |   INDEX FULL SCAN           | INDEX_FOO_USERID |   100K|       |   224   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------
发现通过HINT,优化器走了索引。
 
将索引设置为NULL,但是里面不存在NULL值的时候:
ALTER TABLE FOO MODIFY(USERID NULL); 
 
SELECT /*+INDEX(FOO INDEX_FOO_USERID)*/* FROM FOO;
执行计划如下:
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  3515K|   171   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| FOO  |   100K|  3515K|   171   (1)| 00:00:03 |
--------------------------------------------------------------------------
 
这里得到的一个结论就是:
索引能不为空就不为空,也可以通过在索引列加上IS NOT NULL让优化器使用为空的索引。
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,