| how to force parallel to subquery of select statement ? [message #333582] |
Sun, 13 July 2008 03:41  |
eyuval26
Messages: 3
Registered: March 2008
|
Junior Member |
|
|
Hi there,
i have table with 100000 records and sub-select which need to be checked for each record.
something like that...
select customer_id
from customers c1 (100000 records)
where exists (select 1
from customers_status c2 (100000000 records)
where c1.customer_id = c2.customer_id
and c1.date > c2.date
and c2.status = 'A')
i must using nested loops because logical reasons.
if i add a hint of parallel on customers, it doesn't do parallel to the sub-select, but gets all rows of customers to one process and than running the sub-select for each row (which causing a long run of the whole statement).
how can i force oracle to do the sub-select in parallel too ?
Thanks !
Yuval
|
|
|
|
|
|
|
|
|
|
| Re: how to force parallel to subquery of select statement ? [message #333606 is a reply to message #333582] |
Sun, 13 July 2008 09:51  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
SELECT Customer_Id
FROM Customers c1
WHERE EXISTS (SELECT 1
FROM Customers_Status c2
WHERE c1.Customer_Id = c2.Customer_Id
AND c1.DATE > c2.DATE
AND c2.Status = 'A')
You query seems reasonably formulated. I would replace the 1 with the keyword null but it should not matter.
If you are saying your database is some release of 8 then it is time to upgrade as I believe 8 is or will soon no longer be supported.
Additionally, I do not see this query as anything that would not be workable in parallel query as Oracle would optimize it as such given the correct hint, if you were on a 10g or 11g database version.
That said, there is little you can do on a 8 database. AT the moment I cannot think of an alternative re-write you can use. I was thinking of a negative outer join but that won't work here because you are using EXISTS rather than NOT EXISTS.
However, I would suggest that maybe you should reconsider your comment about full table scan on customer. This query is going to look at all rows on customer so one would expect a modified merge/hash join operation to be performed.
Try this and see how it looks. At least your lookup will no do a table access by rowid operation as everything needed to answer the exists can be found in the index.
Again also, I do not see anything in this query that should inhibit parallel query, and seeing that you are accessing all data in the table at least once suggests that at least one full table scan might not be a bad thing.
create index Customers_Status_i1 on Customers_Status
(customer_id,status,date)
/
SELECT Customer_Id
FROM Customers c1
WHERE EXISTS (SELECT null
FROM Customers_Status c2
WHERE c1.Customer_Id = c2.Customer_Id
AND c1.DATE > c2.DATE
AND c2.Status = 'A')
Of course one can always go back to old school methods with this re-write but I do not see how this would be superior. Still, it might be worth checking out. Again pay attention to indexing. On a 10g or 11g database I might expect two INDEX FAST FULL SCANS followed by a merge operation which should be real quick on 100,000/100,000,000 rows. In all situations you should make sure you statistics are up-to-date to reflect the correct one hundred thousand/one hundred million rows in your tables.
select distinct a.customer_id
from customer a
,customers_status b
where a.customer_id = b.customer_id
and a.date > b.date
and b.status = 'A'
/
Lastly notice how I have formatted your query. I did this using the formatter on OraFAQ's home page. Look for it and use it in the future. A link on the home page is found on the right side column about half way down.
Good luck, Kevin
|
|
|
|
|
|
| Re: how to force parallel to subquery of select statement ? [message #333630 is a reply to message #333582] |
Sun, 13 July 2008 16:27 |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
Index Range/Unique Scans are not possible with parallel query in oracle.
Only Table and Index full scans (and join/sort operations) are possible in parallel - this is a common misconception.
If you need the power of parallel query, I would unnest the query into an outerjoin, with parallel operations on both tables (to negate buffer sorting). Youc an also throw in a FULL hint for good measure to ensure oracle doesnt try and steer you back down the index lookup path. (although check the plans each time, as you might be losing a better index full scan in favour of the table scan).
This may or not be quicker, and if it is quicker, it will be to the detrement of other processes running parallel used up way more SGA, so be careful.
Consider Increasing your SORT_AREA_SIZE and HASH_AREA_SIZE to cater for converting the query to FULL/HASH from INDEX Lookup.
All the best.
Dan
|
|
|
|
| Re: how to force parallel to subquery of select statement ? [message #333691 is a reply to message #333630] |
Mon, 14 July 2008 03:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
8i does not unnest automatically; you need to tell it. Try:
SELECT Customer_Id
FROM Customers c1
WHERE EXISTS (SELECT /*+UNNEST*/ null
FROM Customers_Status c2
WHERE c1.Customer_Id = c2.Customer_Id
AND c1.DATE > c2.DATE
AND c2.Status = 'A')
If that doesn't work, try a HASH_SJ hint instead.
The optimiser may not be able to unnest the quesry because of the > predicate, so you could also try:
SELECT Customer_Id
FROM Customers c1
, ( SELECT Customer_Id, MIN(DATE) as DATE
FROM Customers_Status c2
WHERE Status = 'A'
GROUP BY Customer_Id
) c2
WHERE c1.Customer_Id = c2.Customer_Id
AND c1.DATE > c2.DATE
It should be easier to force parallel with this one, but it will perform a sort, which will slow it down.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
