oracle raw类型 索引,为什么RAW数据类型可以建立索引，但是不走索引

情况描述：

业务sql：

select count(*)

from px_apply t

where t.SITE_ID = '2C9146A12760F52A01276124EFB90016'

and t.CLASS_ID = '93E7EDF83B13CA6FE040007F01005BD5'

and t.IS_RESERVED = 0

and t.STATUS in (3, 4, 5)

操作系统信息：

LSB Version:    :core-3.0-ia32:core-3.0-noarch:graphics-3.0-ia32:graphics-3.0-noarch

Distributor ID: RedHatEnterpriseAS

Description:    Red Hat Enterprise Linux AS release 4 (Nahant Update 5)

Release:        4

Codename:       NahantUpdate5

数据库信息：

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

其中一个测试：

索引建立：

create index ix_px_apply_SITE_ID on hz_bsp.px_apply(SITE_ID) tablespace XXX;

具体sql：

select count(*)

from px_apply t

where t.SITE_ID = '2C9146A12760F52A01276124EFB90016'

执行计划:

SQL> set linesize 1000

SQL> set autotrace traceonly

SQL> set timing on

SQL> select count(*)

2    from px_apply t

3   where t.SITE_ID = '2C9146A12760F52A01276124EFB90016';

已用时间:  00: 00: 00.31

执行计划

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

Plan hash value: 1051324080

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

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

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

|   0 | SELECT STATEMENT   |          |     1 |    17 |  2361   (2)| 00:00:29 |

|   1 |  SORT AGGREGATE    |          |     1 |    17 |            |          |

|*  2 |   TABLE ACCESS FULL| PX_APPLY |   398K|  6608K|  2361   (2)| 00:00:29 |

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

Predicate Information (identified by operation id):

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

2 - filter(RAWTOHEX("T"."SITE_ID")='2C9146A12760F52A01276124EFB90016'

)

统计信息

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

0  recursive calls

0  db block gets

10620  consistent gets

0  physical reads

0  redo size

410  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)

1  rows processed

今天遇到一个数据库里面有很很多RAW(16)字段。目前数据库cpu占用率很高，通过操作系统物理进程找到相关问题sql，sql里面有三个RAW字段，并且三个RAW字段是查询条件。

我的解决办法：

1、a、期望在3列字段建立复合索引

b、dbms_stats.gather_table_stats

结果：全表扫描

2、a、分别在三个RAW建立index

b、dbms_stats.gather_table_stats

结果：全表扫描

问题：google很多下，说Long Raw不可以建立索引，RAW可以建立索引，但是为什么不走索引呢。

有兄弟遇到过类似的问题吗？给我一些信息。万分感谢。

注：Oracle® Database PL/SQL User's Guide and Reference

10g Release 2 (10.2)

RAW Datatype

You use the RAW datatype to store binary data or byte strings. For example, a RAW variable might store a sequence of graphics characters or a digitized picture. Raw data is like VARCHAR2 data, except that PL/SQL does not interpret raw data. Likewise, Oracle Net does no character set conversions when you transmit raw data from one system to another.

The RAW datatype takes a required parameter that lets you specify a maximum size up to 32767 bytes. The syntax follows:

RAW(maximum_size)

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

You cannot insert RAW values longer than 2000 bytes into a RAW column. You can insert any RAW value into a LONG RAW database column because the maximum width of a LONG RAW column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG RAW column into a RAW variable. Note that the LONG RAW datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

[本帖最后由 mahanso 于 2011-5-26 15:36 编辑]