like为什么会这样？

生成表的代码

CODE:

SQL> create table test as select * from dba_objects;

Table created

SQL> insert into test select * from test;

50397 rows inserted

SQL> commit;

Commit complete

SQL> create index test_object_id on test(object_id);

Index created然后测试一个等值的没有问题

CODE:

SQL> explain plan for
  2  (
  3  select * from test where object_id='536'
  4  );

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2401865018
--------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     2 |   186 |     3   (
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     2 |   186 |     3   (
|*  2 |   INDEX RANGE SCAN          | TEST_OBJECT_ID |     2 |       |     1   (
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=536)但是like就出问题了

CODE:

SQL> explain plan for
  2  (
  3  select * from test where object_id like '536%'
  4  );

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5040 |   457K|   406   (3)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST |  5040 |   457K|   406   (3)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("OBJECT_ID") LIKE '536%')同时把object_id换为其他值，执行计划一样

CODE:

SQL> explain plan for
  2  (
  3  select * from test where object_id like '998%'
  4  );

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5040 |   457K|   406   (3)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST |  5040 |   457K|   406   (3)| 00:00:05 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("OBJECT_ID") LIKE '998%')

13 rows selected按理说不应该存在数据倾斜，重新统计分析
execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'xxx', TABNAME => 'TEST',ESTIMATE_PERCENT =>100 , BLOCK_SAMPLE => TRUE, METHOD_OPT => 'FOR ALL COLUMNS SIZE skewonly', DEGREE => 16, GRANULARITY => 'ALL', CASCADE => TRUE);
问题依旧
然后通过gather_plan_statistics

CODE:

select /*+ gather_plan_statistics */ * from test where object_id like '536%';
SQL> SELECT t.SQL_ID,t.SQL_TEXT FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%gather_plan%'
  2  /

SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
4h9sj1k8rqmpa select /*+ gather_plan_statistics */ * from test where object_id like '536%'
769qmt2w9rgpw SELECT t.SQL_ID,t.SQL_TEXT FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%gather_plan%
1skawz8cpmu8r SELECT t.SQL_ID,t.SQL_TEXT FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%gather_plan%

SQL> select * from table(dbms_xplan.display_cursor('4h9sj1k8rqmpa', null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4h9sj1k8rqmpa, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test where object_id like '536%'
Plan hash value: 1357081020
--------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffe
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| TEST |      1 |   5040 |     22 |00:00:00.15 |    14
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("OBJECT_ID") LIKE '536%')还做了基于object_name的执行计划

CODE:

SQL> explain plan for
  2  (
  3  SELECT * FROM test WHERE object_name LIKE 'I\_USER1%' ESCAPE '\'
  4  );

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1741503239
--------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     4 |   372 |     6   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     4 |   372 |     6   (0)|
|*  2 |   INDEX RANGE SCAN          | OBJECT_NAME |     4 |       |     3   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_NAME" LIKE 'I\_USER1%' ESCAPE '\')
       filter("OBJECT_NAME" LIKE 'I\_USER1%' ESCAPE '\')

15 rows selected

SQL> 一切正常
难道object_id异常的原因是因为filter(TO_CHAR("OBJECT_ID") LIKE '536%')？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10805681/viewspace-614687/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10805681/viewspace-614687/