Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Why does this query need a table access by rowid?

Why does this query need a table access by rowid?

From: Bobak, Mark <>
Date: Fri, 23 Jul 2004 13:55:36 -0400
Message-ID: <>


I've got a table, DOCUMENTS, with lots of columns (and lots of rows). DOC_ID is the PK, and DOC_HOLD_STATUS is another column in the table, which is NOT NULL and will always have 'Y' or 'N'.

Now, I've got a query where, given the DOC_ID, I want to determine if a particular document is on hold. Note that DOCUMENTS contains on the order of 170M rows, of which approximately 200k rows are flagged as DOC_HOLD_STATUS=3D'Y', or, docs that are on hold.

So, given the large disparity in the number of docs on hold vs. not on hold, I created a function-based index defined as: Create DOC_ON_HOLD on
DOCUMENTS(DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL)); Now, that gives me a convenient index of just the docs that are on hold. (And with only around 200k DOC_IDs, it's *much* smaller than if I just had an index on DOC_HOLD_STATUS, which makes it a lot more cache friendly.)

So, finally, I get to the problem.
When I execute this query:
SELECT /*+ index(doc doc_on_hold) */ DOC_ID=20   FROM DOCUMENTS doc=20
 WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D :B1; I get this execution plan:
Execution Plan

   0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D1 Card=3D1430947 Bytes=3D10016629)

   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTS' (Cost=3D1 Card=3D1430947 Bytes=3D10016629)

   2 1 INDEX (RANGE SCAN) OF 'DOC_ON_HOLD' (NON-UNIQUE) (Cost=3D3 Card=3D1430947)

So, the index is recognized, and that's great. My question is, why is the TABLE ACCESS BY ROWID required?
Since only the DOC_ID is in the select list, why the table access?



PS Granted, this is an efficient query, and the elimination of the table access amounts to one less consistent get. However, this is a VERY heavily hit query, and reducing 4 or 5 consistent gets to 3 or 4 could potentially be a big savings on a query that gets executed as much as this one does.

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Fri Jul 23 2004 - 12:52:14 CDT

Original text of this message