Hi,

The following query is taking 11 secs. If I run this as 2 seperate
queries it is faster.


SELECT PR.product_nm, PB.publisher_nm, AR.author_nm, PR.id, PR.closing
FROM product PR JOIN publisher PB ON ( PR.company_id = PB.company_id )
AND ( PR.publisher_id = PB.id )
JOIN author AR ON ( PR.company_id = AR.company_id )
AND ( PR.author_id = AR.id )
WHERE ( PR.company_id = 1 )
AND ( PR.author_id IN ( SELECT id FROM author AR
WHERE ( AR.company_id = 1 )
AND ( AR.author_nm LIKE 'OSCAR%' ) ) )
ORDER BY PR.product_nm


Key details
-----------
product (114,000 rows)
PK - company_id, id
FK - company_id, author_id >> to author
FK - company_id, publisher_id >> to publisher
UNQ - company_id, publisher_id, product_nm


author (44,000 rows)
PK - company_id, id
UNQ - company_id, author_nm


publisher (1,500 rows)
PK - company_id, id
UNQ - company_id, publisher_nm


Can I improve this query ? Please help.

One more question, Can we specify 2 seperate plan for the above query,
one plan for the main query and another for the sub-query ?

Thank you.

Sudheer Palaparambil