我有这样一个索引，什么时候会用上呢

anlinew · 发表于 2008-4-14 09:32

set autotrace traceonly explain
select /*+ index (t processed_flag_idx) */ *
from t where ( case temporary when 'N' then 'N' end ) = 'N';
set autotrace off

执行计划看看

foxmile · 发表于 2008-4-14 09:50

原帖由 xiaodong_1567 于 2008-4-13 12:50 发表

set autotrace traceonly explain
select /*+ index (t processed_flag_idx) */ *
from t where temporary = 'N';
set autotrace off

原代码上条件加了

select /*+ index (s processed_flag_idx) */ *
from t s where temporary = 'N';
set autotrace off
试试

zergduan_TT · 发表于 2008-4-14 11:23

SQL> create table AA as select * from dba_objects;

Table created

SQL> commit;

Commit complete

SQL> select count(*) , temporary from AA group by temporary;

  COUNT(*) TEMPORARY
---------- ---------
   103 Y
   50221 N

SQL> create index ind_AA on AA ( case temporary when 'Y' then 'Y' end );

Index created

[php]
SQL> explain plan for
  2  select * from AA where temporary='Y';

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3285055242
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 124 | 11532 | 157 (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| AA | 124 | 11532 | 157 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TEMPORARY"='Y')

13 rows selected

SQL> explain plan for
  2  select  * from AA where (CASE TEMPORARY WHEN 'Y' THEN 'Y' END)='Y';

Explained

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1326281477
--------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |       | 503 | 46779 | 24 (0)| 00:0
| 1 |  TABLE ACCESS BY INDEX ROWID| AA    | 503 | 46779 | 24 (0)| 00:0
|*  2 | INDEX RANGE SCAN       | IND_AA | 103 |    |    1 (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "TEMPORARY" WHEN 'Y' THEN 'Y' END ='Y')

14 rows selected

[/php]

函数索引~条件要加函数~
你索引建错了，case在建立索引的时候用法用问题~

mihawk · 发表于 2008-4-14 15:25

原帖由 zergduan_TT 于 2008-4-14 11:23 发表
SQL> create table AA as select * from dba_objects;

函数索引~条件要加函数~
你索引建错了，case在建立索引的时候用法用问题~

顶. 不知道楼主为什么要建这么一个奇怪的函数索引, 这个问题用普通索引就可以解决了.

xiaodong_1567 · 发表于 2008-4-14 17:04

原帖由 foxmile 于 2008-4-14 09:50 发表

select /*+ index (s processed_flag_idx) */ *
from t s where temporary = 'N';
set autotrace off
试试

SQL> select /*+ index (s processed_flag_idx) */ *
  2  from t s where temporary = 'N';

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 705 | 99405 |  3271 (1)| 00:00:40 |
|*  1 |  TABLE ACCESS FULL| T | 705 | 99405 |  3271 (1)| 00:00:40 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TEMPORARY"='N')

Note
-----
- dynamic sampling used for this statement

还是不行的

xiaodong_1567 · 发表于 2008-4-14 17:09

回zergduan_TT

SQL> create table AA as select * from dba_objects;

表已创建。

SQL> select count(*) , temporary from AA group by temporary;

执行计划
----------------------------------------------------------
Plan hash value: 2807095694

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    | 51863 | 101K| 158 (3)| 00:00:02 |
| 1 |  HASH GROUP BY    |    | 51863 | 101K| 158 (3)| 00:00:02 |
| 2 | TABLE ACCESS FULL| AA | 51863 | 101K| 155 (1)| 00:00:02 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      48  recursive calls
      0  db block gets
      758  consistent gets
      678  physical reads
      0  redo size
      512  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

SQL> create index ind_AA on AA ( case temporary when 'Y' then 'Y' end );

索引已创建。

SQL> set autotrace traceonly explain
SQL> select * from AA where temporary='Y';

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 78 | 13806 | 155 (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| AA | 78 | 13806 | 155 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TEMPORARY"='Y')

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off

你的方法我这也行不通啊

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

xiaodong_1567 · 发表于 2008-4-14 17:10

SQL> select count(*) , temporary from AA group by temporary;

  COUNT(*) T
---------- -
      96 Y
   49219 N

xiaodong_1567 · 发表于 2008-4-14 17:12

原帖由 mihawk 于 2008-4-14 15:25 发表

顶. 不知道楼主为什么要建这么一个奇怪的函数索引, 这个问题用普通索引就可以解决了.

是个试验，但实际中也有这种情况
如：重点为尚未处理的订单建索引

zergduan · 发表于 2008-4-14 17:59

原帖由 xiaodong_1567 于 2008-4-14 17:09 发表
回zergduan_TT

SQL> create table AA as select * from dba_objects;

表已创建。

SQL> select count(*) , temporary from AA group by temporary;

执行计划
----------------------------------------------------------
Plan hash value: 2807095694

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    | 51863 | 101K| 158 (3)| 00:00:02 |
| 1 |  HASH GROUP BY    |    | 51863 | 101K| 158 (3)| 00:00:02 |
| 2 | TABLE ACCESS FULL| AA | 51863 | 101K| 155 (1)| 00:00:02 |
---------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      48  recursive calls
      0  db block gets
      758  consistent gets
      678  physical reads
      0  redo size
      512  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      2  rows processed

SQL> create index ind_AA on AA ( case temporary when 'Y' then 'Y' end );

索引已创建。

SQL> set autotrace traceonly explain
SQL> select * from AA where temporary='Y';

执行计划
----------------------------------------------------------
Plan hash value: 3285055242

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    | 78 | 13806 | 155 (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| AA | 78 | 13806 | 155 (1)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("TEMPORARY"='Y')

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off

你的方法我这也行不通啊

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

我那个只是个试验，说明你这么建立索引，是一个函数索引，如果你查询的语句条件中没有使用这个函数，是不可能使用索引的，hints也没用。

zergduan · 发表于 2008-4-14 18:04

我听说这种case语句建立索引是为了保证唯一性而不是用来使用的~
比如table A有3个字段 a , b, c
如果想保证a 为Null时 b唯一且 a not null时 c唯一，可以用case来建立唯一索引。
记得在某人的blog上看到过~

我有这样一个索引，什么时候会用上呢

浏览过的版块