在逛ITubd的时候看见一列做记录：
情况描述：

业务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.

解决方案：

多半是隐式的数据类型转换 试下在常量那边加HEXTORAW SQL> select count(*)   2    from px_apply t   3   where t.SITE_ID = RAWTOHEX('2C9146A12760F52A01276124EFB90016'); 已用时间:  00: 00: 00.23 执行计划 ---------------------------------------------------------- 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")='324339313436413132373630463532413               0313237363132344546423930303136') 统计信息 ----------------------------------------------------------           0  recursive calls           0  db block gets       10620  consistent gets           0  physical reads           0  redo size         407  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 反了，是HEXTORAW 你没发现错误已经很明显了么    2 - filter(RAWTOHEX("T"."SITE_ID")='324339313436413132373630463532413               0313237363132344546423930303136')