Oracle提示的优先级最高？——一次坏块处理的详细记录 (2)

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 164K| 1175M| 1985 |
| 1 | TABLE ACCESS FULL | CAT_ZONE_PRODUCT | 164K| 1175M| 1985 |
--------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;

Session altered.

看来HINT没有起左右，可是从语法上看是没有任何问题的。难道是or的问题：

SQL> EXPLAIN PLAN FOR
2 SELECT /* ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 ;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84208 | 602M| 1985 |
| 1 | TABLE ACCESS BY ROWID RANGE| CAT_ZONE_PRODUCT | 84208 | 602M| 1985 |
-------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

这回结果对了，看来果然是or出的问题，不过既然HINT的优先级最高，为什么加上一个OR的条件，就导致Oracle的执行计划改变了呢，看来不是HINT优先级最高是有疑问的，就是这里出现了bug。

问题找到了，剩下的就简单了，将查询语句简单变形，再次执行：

SQL> CREATE TABLE CAT_ZONE_PRODUCT_BAK AS
2 SELECT /* ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
3 WHERE ROWID < CHARTOROWID('AAANV5AAnAAAGGBAAA')
4 UNION ALL
5 SELECT /* ROWID(CAT_ZONE_PRODUCT) */ * FROM CAT_ZONE_PRODUCT
6 WHERE ROWID >= CHARTOROWID('AAANV5AAnAAAGGCAAA')
7 ;

Table created.

SQL> TRUNCATE TABLE CAT_ZONE_PRODUCT DROP STORAGE;

Table truncated.

www.ixdba.net

SQL> INSERT INTO CAT_ZONE_PRODUCT SELECT * FROM CAT_ZONE_PRODUCT_BAK;

157186 rows created.

SQL> COMMIT;

Commit complete.