ITPub博客

首页 > 数据库 > Oracle > Preventing Oracle from Using an Index(zt)

Preventing Oracle from Using an Index(zt)

原创 Oracle 作者:jametong 时间:2005-04-18 09:19:38 0 删除 编辑

Preventing Oracle from Using an Index
Compliments of Oracle FAQ

In certain cases, you may want to disable the use of a specific, or all indexes for a given query. Here are some examples demonstrating several methods for doing so:

-- Adding an expression to the indexed column

SQL>select count(*) from t where empno+0=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

-- Specifying the FULL hint to force full table scan

SQL>select /*+ FULL(t) */ * from t where empno=1000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------
1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=41)

-- Specifying NO_INDEX hint

SQL>select /*+ NO_INDEX(T) */ count(*) from t where empno=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

-- Using a function over the indexed column

SQL>select count(*) from t where to_number(empno)=1000;

COUNT(*)
----------
1

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
[@more@]

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

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

注册时间:2013-11-23

  • 博文量
    47
  • 访问量
    280629