SQL> create table test as select * from dba_objects;

Table created

SQL> create index object_id on test(object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'TEST');

PL/SQL procedure successfully completed

SQL> select * from test where object_id =122;

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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 93 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | OBJECT_ID | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=122)

SQL> select * from test where object_id like '122%';

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      | 2520 |   228K|   197   (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 2520 |   228K|   197   (3)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter(TO_CHAR("OBJECT_ID") LIKE '122%')

SQL> set autot off
SQL> SELECT T.NUM_ROWS*0.05 FROM Dba_TabLES t WHERE t.table_name='TEST';

T.NUM_ROWS*0.05
---------------
2520.45

由于发生了隐式转换,导致执行计划中rows评估出现问题.转换后的列无统计信息,故采取总行数的0.05作为评估.(test一共50409行,5%就是2520).

这时候有两种方法去做.1 修改optimizer_dynamic_sampling=3,row评估不会那么多,但是仍然是全表扫描;2创建函数索引.

SQL> alter session set optimizer_dynamic_sampling=3;

会话已更改。

SQL> set autot trace exp
SQL> select * from test where object_id like '122%';

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    16 | 1488 |   197   (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST |    16 | 1488 |   197   (3)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter(TO_CHAR("OBJECT_ID") LIKE '122%')

Note
-----
- dynamic sampling used for this statement

使用第二点:

SQL> drop index object_id;

索引已删除。

SQL> create index object_id on test(to_char(object_id));

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'TEST');

PL/SQL 过程已成功完成。

SQL> set autot trace exp
SQL> select * from test where object_id like '122%';

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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 98 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 98 | 3 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | OBJECT_ID | 1 | | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

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

2 - access(TO_CHAR("OBJECT_ID") LIKE '122%')
filter(TO_CHAR("OBJECT_ID") LIKE '122%')

但是请注意,在这种条件下,等值连接的执行计划就会出现问题

SQL> select * from test where object_id=122;

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    98 |   196   (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST |     1 |    98 |   196   (2)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=122)

SQL> select * from test where object_id='122';

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

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    98 |   196   (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST |     1 |    98 |   196   (2)| 00:00:03 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=122)

所以需要衡量两种情况下等值于like哪种出现的几率更多些.

SQL> create table test2(object_name varchar2(7));

SQL> create table test3(object_name char(7));

SQL> insert into test2 select substr(object_name,1,7) from test;

已创建50409行。

SQL> commit;

提交完成。

SQL> insert into test3 select substr(object_name,1,7) from test;

已创建50409行。

SQL> commit;

提交完成。

SQL> create index test2_object_name on test2(object_name);

索引已创建。

SQL> create index test3_object_name on test3(object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'TEST2');

PL/SQL 过程已成功完成。

SQL> exec dbms_stats.gather_table_stats(user,'TEST3');

PL/SQL 过程已成功完成。

SQL> set autot trace exp
SQL> select * from test2 where object_name like '/440831';

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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 70 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| TEST2_OBJECT_NAME | 10 | 70 | 1 (0)| 00:00:01 |

--------------------------------------------------------------------------------------

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

1 - access("OBJECT_NAME" LIKE '/440831')

SQL> select * from test3 where object_name like '/440831';

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

--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 110 | 1 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| TEST3_OBJECT_NAME | 10 | 110 | 1 (0)| 00:00:01 |

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

1 - access("OBJECT_NAME" LIKE '/440831')

对于这种没有%的like语句,oracle将like转换为等值连接,从时间上看,char的时间要少于varchar2的时间;当然,实际使用中很少有人这么使用

ITPub博客

oracle的like

最新文章