RE: unexpected query plan
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'Christian Antognini'" <Christian.Antognini@xxxxxxxxxxxx>, "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- Date: Mon, 22 Jan 2007 18:25:15 -0000
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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: Christian Antognini [mailto:Christian.Antognini@xxxxxxxxxxxx]
Sent: Monday, January 22, 2007 12:57 PM
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
