Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

RE: Why does this query need a table access by rowid?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 23 Jul 2004 15:09:49 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660C2B@bosmail00.bos.il.pqe>


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"



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
-----------------------------------------------------------------
----------------------------------------------------------------
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US