Forum Stats

  • 3,781,146 Users
  • 2,254,484 Discussions
  • 7,879,595 Comments

Discussions

3 way join optimization

760601
760601 Member Posts: 1
edited Mar 17, 2010 1:53AM in SQL & PL/SQL
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

Answers

This discussion has been closed.