RE: unexpected query plan

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Mon, 22 Jan 2007 18:25:15 -0000
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BB99@ENYC11P32005.corpny.csfb.com>

Thanks Chris. I have since educated myself on this optimizer technique. In retrospect it's a bit of a surprise that I had never run across it before.

Wolfgang Breiting correctly pointed out that the optimizer is estimating 1.3M rows to be returned by this query; in fact it returns 492, so my job now is to figure out why the estimate is so far off. I have Jonathan's Cost-Based Oracle Fundamentals open to chapter 3, Single-Table Selectivity...now if I could just get a few minutes to read it!

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini_at_trivadis.com] Sent: Monday, January 22, 2007 12:57 PM
To: paul.baumgartel_at_credit-suisse.com
Cc: oracle-l_at_freelists.org
Subject: RE: unexpected query plan

Paul

> What is this indexjoin operation?

To save a table access the query optimizer is able to get the data by joining two indexes on the same table (based on the ROWID that can be found in both segments). Of course this is only possible when all referenced attributes are indexed.

> I have never seen it.

FYI: this is not something "new". Here a link to the 8.1.5 where it is documented...
http://download-uk.oracle.com/docs/cd/F49540_01/DOC/server.815/a67781/c2 0b_ops.htm#1164

HTH
Chris

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 22 2007 - 12:25:15 CST