RE: Why does this query need a table access by rowid?
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2004 15:09:49 -0400
Mark,
That's true, but it would require the index to be much larger. The idea
behind the function based index was to reduce the size of the index, to
make it for cache-friendly.
-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 Powell, Mark D
Sent: Friday, July 23, 2004 2:59 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Why does this query need a table access by rowid?
If you only need to find out if a document is in hold status on a case
by
case basis then couldn't you also accomplish this same goal by using a
nonunique index built on (doc_id,doc_hold_status) to support the PK
constraint on doc_id. Only an index access would be necessary to verify
the
status of the document and you could dispense with use of a function
based
index. This would not work as well for finding all documents on hold
but it
should work for testing individual documents.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark
Sent: Friday, July 23, 2004 2:41 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why does this query need a table access by rowid?
Actually, it looks like the ultimate solution will be:
Select :b1
from documents doc
where decode(doc.doc_hold_status,'Y',doc_id,null) =3D3D :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) =3D3D: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=3D3D3D'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=3D3D20
FROM DOCUMENTS doc=3D3D20
WHERE DECODE(DOC_HOLD_STATUS,'Y',DOC_ID,NULL) =3D3D3D :B1;
I get this execution plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3D3D3DCHOOSE (Cost=3D3D3D1 =3D
Card=3D3D3D1430947
Bytes=3D3D3D10016629)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'DOCUMENTS' (Cost=3D3D3D1
Card=3D3D3D1430947 Bytes=3D3D3D10016629)
2 1 INDEX (RANGE SCAN) OF 'DOC_ON_HOLD' (NON-UNIQUE) =3D
(Cost=3D3D3D3
Card=3D3D3D1430947)
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
-----------------------------------------------------------------
Other related posts:
