SQL 语句中对于like 的调优

最新推荐文章于 2023-05-10 11:06:29 发布

最新推荐文章于 2023-05-10 11:06:29 发布 · 117 阅读

本文介绍如何通过使用函数索引优化包含LIKE操作的SQL查询，降低查询成本并提高效率。

SQL> create table test as select * from dba_objects;

表已创建。

SQL> select owner,object_name from test where owner like 'ROBINSON%';

OWNER OBJECT_NAME
------------------------------------------------------------ ------------------------------
ROBINSON TEST
ROBINSON T2
ROBINSON T1
ROBINSON T3
ROBINSON T4
ROBINSON HEX_TO_BIN

已选择6行。

SQL> create index i_owner on test(owner);

索引已创建。

SQL> update test set owner='SYSMAN' where owner!='ROBINSON';

已更新50353行。

SQL> update test set owner='SYSTEM' where object_id>1000 and object_id<1020;

已更新19行。

SQL> commit;

提交完成。

SQL> update test set owner='SCOTT' where object_id>2000 and object_id<2005;

已更新4行。

SQL> select distinct owner from test;

OWNER
------------------------------------------------------------
ROBINSON
SYSTEM
SCOTT
SYS

SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',
3 tabname => 'TEST',
4 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
5 method_opt => 'for all columns size repeat',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /

PL/SQL 过程已成功完成。

SQL> set autot traceonly
SQL> select owner,object_name from test where owner not like 'SYS%';

已选择10行。

执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25180 | 737K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 25180 | 737K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("OWNER" NOT LIKE 'SYS%')

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

从此处可以看到选择了10行，但是却走了全表扫描，现在对其加HINT提示

SQL> select /*+ INDEX (test I_OWNER) */ owner,object_name from test where owner not like 'SYS%';

已选择10行。

执行计划
----------------------------------------------------------
Plan hash value: 469605390

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25180 | 737K| 490 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 25180 | 737K| 490 (1)| 00:00:06 |
|* 2 | INDEX FULL SCAN | I_OWNER | 25180 | | 140 (3)| 00:00:02 |
---------------------------------------------------------------------------------------

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

2 - filter("OWNER" NOT LIKE 'SYS%')

我们发现，强制加HINT提示所花的成本比全表扫描成本更高，这里要想优化这条SQL 必须改like

SQL> drop index i_owner;

索引已删除。

SQL> create index f_owner on test(instr(owner,'SYS',1,1)) ;

索引已创建。

SQL> select owner,object_name from test where instr(owner,'SYS',1,1)=0;

已选择10行。

执行计划
----------------------------------------------------------
Plan hash value: 535182827

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 504 | 15120 | 52 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 504 | 15120 | 52 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | F_OWNER | 201 | | 49 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

2 - access(INSTR("OWNER",'SYS',1,1)=0)

当使用函数索引后，COST由原来的142下降到现在的52

总结：对于LIKE语句，我们可以使用instr函数来进行SQL调优