| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: 3 table query question
A copy of this was sent to spam_at_spam.sucks.com
(if that email address didn't require changing)
On 23 Jun 98 15:47:47 GMT, you wrote:
>Hi everyone:
>
>I have a question for the Oracle gurus in this forum.
>
>Situation:
>When I query from 2 tables, the explain plan table shows
>the index path is found and used, resulting in a good quick
>query. But when I add a third table, the index on the first
>table is no longer used, and the query takes much more time.
>
>Environment: Oracle 7.3.3 (mainframe) SQL*Net, SQLCoder.
>Oracle is using cost based optimization.
>
>Here is the detail.
>
>/* query 1 */
>
>select count(*)
>from table1,
> table2
>where (table1.field1 = table2.field1 );
>
> SELECT STATEMENT Cost = 102
> SORT AGGREGATE
> NESTED LOOPS
> INDEX FULL SCAN table1_alternate_index
> INDEX UNIQUE SCAN table2_primary_key
>
With this query, it can use the FULL SCAN on the INDEX and never goto the table since table1.field1 is in the index and we need no other columns.
So, it full scans the index and does an index probe into table2 to do the join and counts the rows...
>/* query 2 */
>
>select count(*)
>from table1,
> table2,
> table3
>WHERE (table1.field1 = table2.field1 )
>and (table1.field2 = table3.field2 );
>
> SELECT STATEMENT Cost = 408
> SORT AGGREGATE
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL table1
> INDEX UNIQUE SCAN table2_primary_key
> INDEX UNIQUE SCAN table3_primary_key
>
>table1.field2 is also indexed, and table3.field2 is the
>primary key for that table.
>
With this one, I'm am assuming that you have an index on table1(field1) and ANOTHER, SEPARATE index on table1(field2).....
So, if it full scanned one index, the data it needed (field2 or field1) would not be present. We would have to goto the base table (table access by rowid) to pick it up, in order to join. Thats alot of scattered IO.
So, since there isn't one index it can full scan to get the answer and it knows it will read the entire table anyway using either of the existing indexes, it full scans the table to take advantage of multi-block reads and contigous IOs (as opposed to reading the table a block at a time in a scattered fashion).
>If I force query 2 to use the index it used in query 1,
>I get an even greater cost; = not as efficient.
>
thats the scattered reads (table access by rowid) kicking in..
>select /* index (table1 table1_alternate_index) */
>count(*)
>from table1,
> table2,
> table3
>etc.
>
>SELECT STATEMENT Cost = 2248
> SORT AGGREGATE
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY ROWID table1
> INDEX FULL SCAN table1_alternate_index
> INDEX UNIQUE SCAN table2_primary_key
> INDEX UNIQUE SCAN table3_primary_key
>
>
>If I force the use of the index on table1.field2, it will use
>it, but it will yield me a cost of 696.
>
>So it appears the best cost is when the optimizer determines
>the access paths itself; however it uses a full table scan on
>a relatively large table. (48k rows.)
>
>So, can anyone tell me why it the optimizer would find it more
>optimal in this case to not use the index?
>
>Note: the data is very normalized. I can not join table3.field2
>to table2.
>
>Thanks for any help on this.
>
>Bonus question: since the optimizer has to parse the statement
>to determine the best access paths anyway, why would it take
>noticeably longer to parse a query when you do a "select field1
>from ..." as opposed to a "select count(*) from ..."? (since the
>tables and join conditions are identical.)
>
>
>Bill
>
>in an effort to keep from getting spammed, please manually reply
>to: bill_at_NOSPAM.gpfn.sk.ca (remove NOSPAM.)
>
>Many thanks!
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 23 1998 - 12:20:38 CDT
 |
 |