Actually, it looks like the ultimate solution will be: Select :b1 from documents doc where decode(doc.doc_hold_status,'Y',doc_id,null) =3D :b1; Since the input is a single doc_id, if a row is returned, I *know* it's the doc_id I asked for, and this avoids the table access. Thanks again for the help, all. -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Post Hoc Ergo Propter Hoc" -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark W. Farnham Sent: Friday, July 23, 2004 2:25 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Why does this query need a table access by rowid? So: SELECT /*+ index(doc doc_on_hold) */ DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) FROM DOCUMENTS doc WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D:B1; should either work as you wish(ie. skip the table access) or not. Since you're about to try that, I'll refrain from testing it myself.... mwf -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Karen Morton Sent: Friday, July 23, 2004 2:10 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Why does this query need a table access by rowid? I believe the table is accessed because the index contains doc_id per the FBI definition. DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) is not the same as DOC_ID. Therefore, the table access is required to get plain old DOC_ID. Karen Morton Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events at http://www.hotsos.com/education/schedule.html -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark Sent: Friday, July 23, 2004 10:56 AM To: oracle-l@xxxxxxxxxxxxx Subject: Why does this query need a table access by rowid? Hi, 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=3D3D'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=3D20 FROM DOCUMENTS doc=3D20 WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D3D :B1; I get this execution plan: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D1 = Card=3D3D1430947 Bytes=3D3D10016629) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTS' (Cost=3D3D1 Card=3D3D1430947 Bytes=3D3D10016629) 2 1 INDEX (RANGE SCAN) OF 'DOC_ON_HOLD' (NON-UNIQUE) = (Cost=3D3D3 Card=3D3D1430947) 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? Thanks, -Mark 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: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------