ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle的like

oracle的like

原创 Linux操作系统 作者:fulzu 时间:2009-09-24 11:46:32 0 删除 编辑

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

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

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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

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

| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   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博客 ” ,链接:http://blog.itpub.net/10805681/viewspace-615459/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-28

  • 博文量
    68
  • 访问量
    71573