Resolution:
Description: For RAW datatypes the Explain Plan of the server SQL shows the index not being used and a full table scan being done.

An example of a bad Explain Plan is below:
********************************************************************************

select STR_VALUE into :b0:b1  
from
ATTRIBUTES where ((OBJECT_ID=:b2 and ATTRIBUTE_NAME=:b3) and
ATTRIBUTE_TYPE=
  :b4)


call     count       cpu    elapsed       disk      query  
current        rows
------- ------  -------- ---------- ---------- ---------- ----------
----------
Parse        5      0.00       0.00          1          0        
1           0
Execute     34      0.00       0.00          0          0        
0          36
Fetch       34      0.00       0.00         12        102        
0          33
------- ------  -------- ---------- ---------- ---------- ----------
----------
total       73      0.00       0.00         13        102        
1          69

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 16  (ICDBOWN)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
     10   TABLE ACCESS (FULL) OF 'ATTRIBUTES'

Customers have reported that Explain Plan reports that InConcert is not
using indices.
Resolution:

In fact testing and Oracle documentation reveals that despite what Explain Plan
reports, InConcert is using indices appropriately.

Facts:

This is from the online documentation for 7.3.4

http://inconcert.xait.xerox.com/~crs/734oradoc/DOC/server/doc/A48506/strace.htm

In which is says:

"Note: TKPROF cannot tell the TYPE of the bind variables simply by
looking at the text of the SQL statement. It assumes that TYPE is
CHARACTER; if this is not the case, you should
put appropriate type conversions in the SQL statement."

Additional test results are described by Gerard:

"Michael A. Behr" wrote:

> > According to Mike the customer tried the operation with the ATTRIBUTES_KEY
> > index removed and then it *really* ran slow.  This also supports the
> > contention that the index was being used despite what tkprof says.
>
> That was actually our own John Bedell, not a customer

Today, we ran this so as to eliminate (as best we could) the effects of caching
by restarting Oracle between each test run.  The following material shows the
result of running tkprof over the trace files.  Only the material  relevant to
the query we were in doubt about is included here.  Note that in spite of the
clear evidence to the contrary, the  "explain plan" indicates a full table scan
in all three cases, while only the case where the index was removed were a
reasonable number of bffer gets and disk i/o perfromed.


// After we drop the constraint which created the primary index and bounced
// the Oracle instance

********************************************************************************

select STR_VALUE into :b0:b1
from
ATTRIBUTES where ((OBJECT_ID=:b2 and ATTRIBUTE_NAME=:b3) and ATTRIBUTE_TYPE=
  :b4)


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.01       0.03          1          0          1           0

Execute      4      0.00       0.00          0          0          0           3

Fetch        4     10.91      24.68      12156      12156         28           4

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

total        9     10.92      24.71      12157      12156         29           7

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 110  (ICDBUSERQA)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   TABLE ACCESS (FULL) OF 'ATTRIBUTES'


// After the primary key constraint (and supporting index get restored
// and the Oracle instance gets bounced.

********************************************************************************

select STR_VALUE into :b0:b1
from
ATTRIBUTES where ((OBJECT_ID=:b2 and ATTRIBUTE_NAME=:b3) and ATTRIBUTE_TYPE=
  :b4)


call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.03       0.04          1          0          1           0

Execute      4      0.00       0.00          0          0          0           3

Fetch        4      0.00       0.07          5         16          0           4

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

total        9      0.03       0.11          6         16          1           7

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 110  (ICDBUSERQA)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   TABLE ACCESS (FULL) OF 'ATTRIBUTES'

********************************************************************************