Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

3 way join optimization

760601Mar 16 2010 — edited Mar 17 2010
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 14 2010
Added on Mar 16 2010
2 comments
538 views