Re: SQL Joins Optimization

If you want Oracle to examine ONLY
the rows where field2 = 'test', then you need to have a rapid access path into
table1 on that field - i.e. an index that starts with field2.

You then need to persuade Oracle that
it is more efficient to use that index than it is to do a full tablescan (assuming
you are using cost based optimisation),
which means
a) generating useful statistics about the two tables and
b) not setting the init.ora parameters at levels which

    produced spurious I/O costs.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



geenstones_at_my-deja.com wrote in message <8vuhmg$ooe$1_at_nnrp2.deja.com>...


>Given the followin SQL statement:

>

>Select * from table1,table2

>where table1.field1(pk) = table2.field1(fk)

>and table1.field2 = 'test'

>

>How an I get oracle to only search the

>joined resultset where field2 = 'test'.

>

>Meaning if table1 has 1000000 records and

>the joined resultset returns only 100 records

>I don't want the optimizer to query the text

>search ('test') on 1 million record but only

>on the joined resultset of 100 records.

>

>It seems to be always querying the 1 million record resultset

>before the join.

>

>Hope this makes sense.

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.