运维首页 > 数据运维 > 测试验证一下字段有空值的时候，是否字段+常量的方式可优化

测试验证一下字段有空值的时候，是否字段+常量的方式可优化

2024年 7月 4日 37.4k 0

测试验证一下字段有空值的时候，是否字段+常量的方式可优化-1
解析：
索引中不保存索引列全为空的记录，所以，仅仅在C1列上创建索引是没有作用的。创建由（C1,‘1’)构建的复合索引，由于存在常量‘1’，所以，无论C1列的值是否为空，其均可出现在索引中，并且该索引会被该SQL所使用。另外几种方法，虽然也可以达到类似避免全表扫的，使用相对高效的索引的目的，但要么需要改写SQL，要么需要修改表中的记录值，要么索引的体积较大，且还存在其它列在其中，当该其它列更新时，该索引也需要同步维护。代价均较“创建由（C1,‘1’)构建的复合索引”的方法更大。

以下测试验证一下字段有空值的时候，是否字段+常量的方式可优化？

测试表100万记录，其中有100条空值。

在创建C1,'0’复合索引后，确认可以走索引，速度提升5-7倍。

注意查看实际的执行计划，使用SQL：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

测试操作记录如下：

SQL> create table T1 as
2 select rownum as id,
3 to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
4 trunc(dbms_random.value(0, 100)) as C1,
5 dbms_random.string('x', 20) random_string
6 from dual
7 connect by level DECLARE
2 v_count NUMBER := 0;
3 BEGIN
4 FOR rec IN (
5 SELECT ROWID
6 FROM t1
7 ORDER BY DBMS_RANDOM.VALUE
8 ) LOOP
9 UPDATE t1
10 SET c1 = NULL
11 WHERE ROWID = rec.ROWID;
12
13 v_count := v_count + 1;
14
15 EXIT WHEN v_count >= 100;
16 END LOOP;
17 END;
18 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.41
SQL> select count(*) from t1 where c1 is null;

COUNT(*)
----------
100

Elapsed: 00:00:00.08
SQL> select * from t1 where rownum explain for select * from t1 where c1 is null;
explain for select * from t1 where c1 is null
*
ERROR at line 1:
ORA-00905: missing keyword

Elapsed: 00:00:00.03
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM t1 WHERE c1 IS NULL;

Explained.

Elapsed: 00:00:00.12
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.60
SQL> CREATE INDEX idx.t1 ON t1 (c1,'0');^[[D^H^C

SQL> CREATE INDEX idx_t1 ON t1(c1,'0');

Index created.

Elapsed: 00:00:04.01
SQL> EXPLAIN PLAN FOR SELECT * FROM t1 WHERE c1 IS NULL;

Explained.

Elapsed: 00:00:00.02
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.03
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> drop index idx_t1;

Index dropped.

Elapsed: 00:00:00.25
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.07
SQL>
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.05
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.05
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.07
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.07
SQL> CREATE INDEX idx_t1 ON t1(c1,'0');

Index created.

Elapsed: 00:00:04.22
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.00
SQL> EXPLAIN PLAN FOR SELECT * FROM t1 WHERE c1 IS NULL;

Explained.

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ff5spha9pjuyu, child number 1
-------------------------------------
select * from table(dbms_xplan.display)

Plan hash value: 2137789089

--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 34 |

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

Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

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

20 rows selected.

Elapsed: 00:00:00.26
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL

Plan hash value: 1970818898

---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 84 |
---------------------------------------------

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

2 - access("C1" IS NULL)

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

26 rows selected.

Elapsed: 00:00:00.07
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID ff5spha9pjuyu, child number 1
-------------------------------------
select * from table(dbms_xplan.display)

Plan hash value: 2137789089

--------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 34 |

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

Note
-----
- cardinality feedback used for this statement
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

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

20 rows selected.

Elapsed: 00:00:00.03
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL

Plan hash value: 1970818898

---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 84 |
---------------------------------------------

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

2 - access("C1" IS NULL)

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

26 rows selected.

Elapsed: 00:00:00.03
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.03
SQL> drop INDEX idx_t1;

Index dropped.

Elapsed: 00:00:00.20
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.05
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL

Plan hash value: 3724264953

--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS FULL| T1 | 84 |
--------------------------------------------

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

2 - filter("C1" IS NULL)

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

26 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.08
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.02
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.07
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.06
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.06
SQL> CREATE INDEX idx_t1 ON t1(c1,'0');

Index created.

Elapsed: 00:00:04.14
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.00
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.00
SQL> /

COUNT(*)
----------
100

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 167K| 2064 (1)| 00:00:25 |
|* 1 | TABLE ACCESS FULL| T1 | 84 | 167K| 2064 (1)| 00:00:25 |
--------------------------------------------------------------------------

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

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

1 - filter("C1" IS NULL)

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

17 rows selected.

Elapsed: 00:00:00.03
SQL> SELECT count(*) from t1 WHERE c1 IS NULL;

COUNT(*)
----------
100

Elapsed: 00:00:00.01
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g8a861jv5d0bj, child number 0
-------------------------------------
SELECT count(*) from t1 WHERE c1 IS NULL

Plan hash value: 1970818898

---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 84 |
---------------------------------------------

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

2 - access("C1" IS NULL)

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

26 rows selected.

Elapsed: 00:00:00.03

向阳逐梦
数据运维
0 0

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论

向阳逐梦

这个人很懒，什么都没有留下～

文章8411 评论0