| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why does this query need a table access by rowid?
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D
Sent: Friday, July 23, 2004 2:59 PM
To: 'oracle-l_at_freelists.org'
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
Sent: Friday, July 23, 2004 2:41 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Friday, July 23, 2004 2:25 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Karen Morton
Sent: Friday, July 23, 2004 2:10 PM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Friday, July 23, 2004 10:56 AM
To: oracle-l_at_freelists.org
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"
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jul 23 2004 - 14:06:40 CDT
 |
 |