Re: table join problem

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 12 Jan 2004 07:35:47 -0800
Message-ID: <1073921669.338762@yasure>

Perica Milosevic wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073668017.378941_at_yasure>...
> 

>>Perica Milosevic wrote:
>>
>>
>>>Hello!
>>>
>>>I have master-detail relationship between two tables, for example:
>>>Company (companyid, companyname), Worker(companyid, workerid,
>>>workername, workerage) where the 'companyid' field is connection
>>>between this two tables.
>>>
>>>I want to select ALL workers from all companies that employ workers
>>>older than 60 (workerage > 60).
>>>
>>>For example:
>>>COMPANY
>>>companyid 1, companyname ORACLE
>>>companyid 2, companyname SUN
>>>companyid 3, companyname IBM
>>>
>>>WORKER
>>>companyid 1, workerid 1, workername 'John', workerage 32
>>>companyid 1, workerid 2, workername 'Smith', workerage 61
>>>companyid 2, workerid 1, workername 'Peter', workerage 27
>>>companyid 2, workerid 2, workername 'Jenet', workerage 33
>>>companyid 2, workerid 3, workername 'Michael', workerage 38
>>>companyid 3, workerid 1, workername 'Sarah', workerage 41
>>>companyid 3, workerid 2, workername 'Nick', workerage 63
>>>
>>>I would like to get:
>>>1, 1, 'John', 32
>>>1, 2, 'Smith', 61
>>>3, 1, 'Sarah', 41
>>>3, 2, 'Nick', 63
>>>
>>>Thanks in advance,
>>>Perica Milosevic
>>
>>Since this is school work ... post your best attempt to solve it and we
>>will give you hints.

> 
> 
> 
> No this is not a school work, real problem is much more complicated. I
> have 3 connected tables (Ticket->TicketRowOdds->MatchOdds) and each
> table has more than 100000 rows. I need the fastest possible solution,
> everything I've already tried is very slow. My fastest solution is:
> 
> SELECT 'everything i need'
> FROM Ticket t, TicketRowOdds tro, MatchOdds mo
> WHERE (tro.ticketId = t.id) AND 
>       (mo.matchId = tro.matchid) AND 
>       (exists (select * 
>                from TicketRowOdds tro2, MatchOdd mo2 
>                where (tro2.ticketId = t.id) AND 
>                      (mo2.matchid = tro2.MATCHID) AND 
>                      (mo2.something > t.something)))
> 
> Because of "(mo2.something > t.something)" I need to perform this
> join.
> I use Oracle 8.1
> 
> Thanks in advance,
> Perica Milosevic

Given the number of records involved indexes will definitely help? So the first questions, before you start changing your SQL are "Do they exist?" and "Are they being used?"

You should check in user_ind_columns to make sure indexes exist for the columns in your WHERE clauses. Then ... to make sure they are being used run EXPLAIN PLAN on your query.

When you are done ... post the EXPLAIN PLAN.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Jan 12 2004 - 09:35:47 CST