RAW資料型別可以建立索引，但是不走索引測試（轉）

在逛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')