11g的cbo中加提示不起作用了？

显示全部楼层 · 2007-9-27 21:42:58

SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE11.2.0.1.0Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production复制代码请教一下：sql中加提示指定索引不起作用了？版本11.2.0.1 linux，是cbo在11g中发生了变化？例子如下
SQL> select /* + index(INDX_T_USER_PROD_REND_TIME)*/ *
2from t_user_product
3where /*user_id=195350 AND*/ REAL_END_TIME > to_date('2030-06-01','yyyy-mm-dd');
未选定行

执行计划
----------------------------------------------------------

Plan hash value: 1476962036

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

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

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

| 0 | SELECT STATEMENT
|
| 1 | 278
| 1 (0)| 00:00:01 |

| 1 |TABLE ACCESS BY INDEX ROWID| T_USER_PRODUCT
| 1 | 278
| 1 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

2 - access("REAL_END_TIME">TO_DATE(' 2030-06-01 00:00:00', 'syyyy-mm-dd hh24:
mi:ss'))

Note

-----

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

SQL> select /* + index(INDX_T_USER_PROD_REND_TIME)*/ *
2from t_user_product
3where user_id=195350 AND REAL_END_TIME > to_date('2030-06-01','yyyy-mm-dd');
未选定行

执行计划
----------------------------------------------------------

Plan hash value: 3693321042

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

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

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

| 0 | SELECT STATEMENT
|
|1107 | 300K|
3 (0)| 00:00:01 |

|*1 |TABLE ACCESS BY INDEX ROWID| T_USER_PRODUCT |1107 | 300K|
3 (0)| 00:00:01 |

|*2 | INDEX RANGE SCAN
| INDX_T_USER_PROD_USER_ID | 424 | |
2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("REAL_END_TIME">TO_DATE(' 2030-06-01 00:00:00', 'syyyy-mm-dd hh24:
mi:ss'))

2 - access("USER_ID"=195350)

Note

-----

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

复制代码

千问 · 2007-9-27 21:42:58

格式不对
select /*+ index(tablename INDX_T_USER_PROD_REND_TIME) */*
48.2from t_user_product
49.3where user_id=195350 AND REAL_END_TIME > to_date('2030-06-01','yyyy-mm-dd');

千问 · 2007-9-27 21:42:58

/*+ index(tablename indexname) */

千问 · 2007-9-27 21:42:58

thanks
已经发现是语法问题，谢谢楼上两位

千问 · 2007-9-27 21:42:58

davidxu322 发表于 2013-02-06 10:32:32
thanks
已经发现是语法问题，谢谢楼上两位
即使是语法没问题，有时用了hints也不一定按提示走计划!