EXPLAIN EXTENDED

Answering questions asked on the site.

Habib asks:

I have a table with a PRIMARY KEY defined as ConsumerID RAW(16) NOT NULL.

However, when I issue a query like this:

SELECT  *
FROM     Consumers
WHERE   ConsumerID = '1DFFC45DEDEB42B8B79B060D544C2ACB'

, it uses a TABLE ACCESS FULL and is very slow.

How can I improve this query?

Defining a PRIMARY KEY column as RAW(16) almost always means using system generated GUID's returned by SYS_GUID as PRIMARY KEY's.

Let's create a sample table and see what's going on:

CREATE TABLE t_guid (
id RAW(16) NOT NULL,
value VARCHAR2(50) NOT NULL
)
/
ALTER TABLE t_guid ADD CONSTRAINT pk_guid_id PRIMARY KEY (id)
/
INSERT
INTO    t_guid
SELECT  SYS_GUID(), 'Value ' || level
FROM    dual
CONNECT BY
level <= 1000000
/
COMMIT
/

We have a 1,000,000 rows here filled with SYS_GUID's.

If we issue the query like this:

SELECT  value
FROM    t_guid
WHERE   id = 'FA6BD2195B2D48C48A9D9D067ECDC670'

, we see that it runs for more than 1.5 seconds.

The detailed EXPLAIN PLAN for this statement shows the following:

EXPLAIN PLAN FOR
SELECT  value
FROM    t_guid
WHERE   id = 'FA6BD2195B2D48C48A9D9D067ECDC670'

SELECT  *
FROM     TABLE(DBMS_XPLAN.display())

Plan hash value: 1229846465
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |    29 |  1337   (3)| 00:00:17 |
|*  1 |  TABLE ACCESS FULL| T_GUID |     1 |    29 |  1337   (3)| 00:00:17 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(RAWTOHEX("ID")='FA6BD2195B2D48C48A9D9D067ECDC670')

We see that the filter uses the following condition: RAWTOHEX("ID")='FA6BD2195B2D48C48A9D9D067ECDC670'

This condition, of course, is not sargable.

When comparing two values of different types, one type should be cast to the other.

A RAW may be cast to a VARCHAR2 by applying function RAWTOHEX, which returns a hexadecimal representation of bytes in the RAW data.

Reverse operation is performed by function HEXTORAW, which takes a string of hexadecimal bytes represenation as an argument and returns a RAW value composed of these bytes.

But this is not possible by any VARCHAR2 value, since only the strings that contain an even number of hexadecimal digits can be supplied as an argument to this function.

That's why Oracle doesn't perform implicit conversion from VARCHAR2 to RAW.

When comparing two values, one of type VARCHAR2, the other one of type RAW, it's the RAW that is cast to VARCHAR2 (which is always possible) and not vice versa.

To make the condition sargable, we should make an explicit cast:

SELECT  value
FROM    t_guid
WHERE   id = HEXTORAW('FA6BD2195B2D48C48A9D9D067ECDC670')

SELECT STATEMENT 
 TABLE ACCESS BY INDEX ROWID, 20090520_guid.T_GUID
  INDEX UNIQUE SCAN, 20090520_guid.PK_GUID_ID

, which uses an INDEX UNIQUE SCAN and therefore is instant.

Hope that helps.

I'm always glad to answer the questions regarding database queries.

Ask me a question