如何讓table表的null列由不走索引變為可走索引

SQL> create table t_null(a int not null,b int);

Table created.

SQL> insert into t_null select level,level+2 from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_1 on t_null(a);

Index created.

SQL> create index idx_2 on t_null(b);

Index created.

SQL> explain plan for select a from t_null;

Explained.

--非空a列採用了索引全掃描
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

Plan hash value: 3375905911

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    10 |   130 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_1 |    10 |   130 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)

12 rows selected.

SQL> explain plan for select b from t_null;

Explained.
--空b列採用了全表掃描
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

Plan hash value: 2443327197

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    10 |   130 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_NULL |    10 |   130 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)

12 rows selected.

--對空b列新增where 條件b is not null,可走索引全掃描
SQL> explain plan for select a from t_null where a is not null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

Plan hash value: 3375905911

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    10 |   130 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_1 |    10 |   130 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)

12 rows selected.

--如對空b列進行聚集運算,如下count會自動filter out null value,走了索引全掃描
SQL> explain plan for select count(b) from t_null;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

Plan hash value: 1382308919

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |       |     1 |    13 |            |          |
|   2 |   INDEX FULL SCAN| IDX_2 |    10 |   130 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------

-----
   - dynamic sampling used for this statement (level=2)

13 rows selected.

--刪除b列索引
SQL> drop index idx_2;

Index dropped.
--構建b列如下的索引,即如b列為空,則轉化為0
SQL> create index idx_2 on t_null(b,0);

Index created.

SQL> explain plan for select b from t_null;

Explained.

--修正後b列可走索引全掃描
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

Plan hash value: 3964661047

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |    10 |   130 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | IDX_2 |    10 |   130 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

   - dynamic sampling used for this statement (level=2)

12 rows selected.