3 way join optimization

760601

Hi,

I have a query that takes ages (~8 minutes) to run and i wanted to know if anyone can think of a way to optimize it...

SELECT I.ISBN13, I.image_uri, I.creation_date, I.last_modified_date, D.IS_UK, D.IS_AU, D.IS_DELETED_AU, D.IS_DELETED_UK
FROM (nielsen_images I inner join nielsen_to_do_run R on r.Isbn13 = I.ISBN13) inner join nielsen D on r.isbn13 = d.isbn13
WHERE R.status = 1
AND (D.IS_DELETED_UK=0 OR D.IS_DELETED_AU=0)


nielsen is a books data table containing 6.2 million records (ISBN13 is the PK)
images contains ~2 million records (one per ISBN13 which is also the PK)
nielsen_to_do_run is a table used fot bulk processing of the data and is also ~6 million records (size drops after every bulk) status=1 is only for 300 records every time. (ISBN13 is the PK)

Thanks,
Roy

BTW - I have full permissions on these table so adding indexes or whatever is a valid option (if it'll help)

Edited by: user9351627 on Mar 16, 2010 5:43 PM

Srini Chavali-Oracle

Welcome to the forums !

When your query takes too long:

865295

1814597

HTH
Srini

MScallion

Do you have an index on the status column in the nielsen_to_do_run table and are your statistics current?