RE: Why does this query need a table access by rowid?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2004 14:25:26 -0400
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) =: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=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?
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
-----------------------------------------------------------------
Other related posts:
