Oracle 颠覆认知的无函数处理限定条件字段也可以用上函数索引（2）

 

以前遇到过一次：Oracle 颠覆认知的无函数处理限定条件字段也可以用上函数索引

现在遇到了新的情况：nvl函数处理的可以用上普通索引。

 

SQL语句如下：

SELECT A.*, A."ROWID"
 FROM X_XXXXXXXXX_LOG A
WHERE 1 = 1
  AND NVL(A.IS_XXXX, '0') = '0'
  AND ROWNUM <= 40
  AND A.XXXXXX_TIME > SYSDATE - 30; 

 

表X_XXXXXXXXX_LOG的索引情况：

INDEX_NAME                     UNIQUENESS COLUMN_NAMES                                       STATUS
------------------------------ ---------- -------------------------------------------------- ------------------------
PK_X_XXXXXXXXX_LOG             UNIQUE     XXX_NO                                             VALID
IDX_TOL_IS_XXXX                NONUNIQUE  IS_XXXX                                            VALID

 

 

SQL执行情况：

可以用上普通索引。

14:25:47 SYS@xxxxxxx(1438)> SELECT A.*, A."ROWID"
14:26:02   2    FROM X_XXXXXXXXX_LOG A
14:26:02   3   WHERE 1 = 1
14:26:02   4     AND NVL(A.IS_XXXX, '0') = '0'
14:26:02   5     AND ROWNUM <= 40
14:26:02   6     AND A.XXXXXX_TIME > SYSDATE - 30;

no rows selected

Elapsed: 00:00:00.01
14:26:03 SYS@xxxxxxx(1438)> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  ghh7gfcjtvx41, child number 0
-------------------------------------
SELECT A.*, A."ROWID"   FROM X_XXXXXXXXX_LOG A  WHERE 1 = 1    AND
NVL(A.IS_XXXX, '0') = '0'    AND ROWNUM <= 40    AND A.XXXXXX_TIME >
SYSDATE - 30

Plan hash value: 3775263249

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |      0 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY               |                 |      1 |        |      0 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| X_XXXXXXXXX_LOG |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | IDX_TOL_IS_XXXX |      1 |      1 |      0 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=40)
   2 - filter("A"."XXXXXX_TIME">SYSDATE@!-30)
   3 - access("A"."IS_XXXX"='0')

 

仔细看执行计划发现谓词条件里边并没有IS_XXXX IS NULL的过滤。

于是，DESC发现表的该字段为not null限制：

14:29:21 SYS@xxxxxxx(1438)> set line 80
14:29:25 SYS@xxxxxxx(1438)> desc X_XXXXXXXXX_LOG
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 XXX_NO                                    NOT NULL VARCHAR2(36)
 XXXX_CODE                                          VARCHAR2(20)
 XXXXX_NO                                           VARCHAR2(32)
 XXXX_NO                                            VARCHAR2(60)
 XXXX_TYPE                                          VARCHAR2(2)
 XXXX_ID                                            VARCHAR2(20)
 XXXX_TIME                                          DATE
 REXXXX                                             VARCHAR2(1500)
 IS_XXXX                                   NOT NULL VARCHAR2(1)
 XXXX_TIME                                          DATE
 XXXXXX_TIME                               NOT NULL DATE

 

实际上CBO把NVL(A.IS_XXXX, '0') = '0'等价为了IS_XXXX='0'了，所以用了索引。

 

大呼一声：Oracle牛逼。