Home » SQL & PL/SQL » SQL & PL/SQL » Driving table problem
Driving table problem [message #249736] Thu, 05 July 2007 13:02 Go to next message
scoorocks
Messages: 3
Registered: July 2007
Junior Member
We have 2 tables, ip having 2 indexes ipid and uid and table dest having the same 2 indexes. Table dest has 1/10 th the number of rows when compared to table ip( 1 million vs 13 million) . Basically i want to do a full scan of table dest, select ipid and uid and query for each value certain columns from table ip using the orimary index ipid and secondary index dest.

Something like:

select distinct string23,string2,uid from ipx,dest
where
ipx.ipid=dest.ipid and ipx.uid=dest.uid

the problem however is that when i ask oracle to explain the plan, it is doing a full table scan on ipx instead of dest. many combinations seem to yeild the same result, full scan of ipx instead of dest..any solutions?
Re: Driving table problem [message #249738 is a reply to message #249736] Thu, 05 July 2007 13:08 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
please read & FOLLOW ALL the posting guideline as stated on the STICKY posts at the top of this forum.
Without many more details the responses will be at best guesses.

Are statistics current on both tables & indexes?
Re: Driving table problem [message #249739 is a reply to message #249738] Thu, 05 July 2007 13:14 Go to previous messageGo to next message
scoorocks
Messages: 3
Registered: July 2007
Junior Member
Yes. They have been analyzed today
Re: Driving table problem [message #249741 is a reply to message #249739] Thu, 05 July 2007 13:26 Go to previous messageGo to next message
scoorocks
Messages: 3
Registered: July 2007
Junior Member
The oracle version that we are using is 9i and if i am correct the ordering of the table names in the where clause does not matter anymore in deciding the driving table.

As stated my basic requirement is that i want the values returned from table dest to be used to retrieve data from the larger table ipx.

Thanks
Re: Driving table problem [message #249756 is a reply to message #249736] Thu, 05 July 2007 15:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Post the stats and EXPLAIN.
2. Post index structures for both tables.
3. Did you try using hint /*+ ORDERED */ ?
4. Try creating an index on both join columns for both tables.
5. IMHO - HASH join will be probably the best choice.

Michael

[Updated on: Thu, 05 July 2007 15:10]

Report message to a moderator

Re: Driving table problem [message #249797 is a reply to message #249756] Thu, 05 July 2007 21:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Agree with Michael, Hash would probably be the best choice.

But you can test it with this SQL and find out.

select /*+ ordered use_nl(ipx)*/ distinct string23,string2,uid 
from dest, ipx
where 
ipx.ipid=dest.ipid and ipx.uid=dest.uid


Ross Leishman
Previous Topic: performance tunning
Next Topic: number of rows in all tables in a schema...
Goto Forum:
  


Current Time: Sun Dec 04 19:09:41 CST 2016

Total time taken to generate the page: 0.12728 seconds