Home » RDBMS Server » Performance Tuning » how to force parallel to subquery of select statement ?
how to force parallel to subquery of select statement ? [message #333582] Sun, 13 July 2008 03:41 Go to next message
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 #333588 is a reply to message #333582] Sun, 13 July 2008 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read forum guide and post the requested information for performances question.

Regards
Michel
Re: how to force parallel to subquery of select statement ? [message #333589 is a reply to message #333588] Sun, 13 July 2008 05:36 Go to previous messageGo to next message
eyuval26
Messages: 3
Registered: March 2008
Junior Member
which kind of information ?
i talk about oracle 8.1.7 64 bit. i don't think it matters to a specicif version of oracle.

Re: how to force parallel to subquery of select statement ? [message #333602 is a reply to message #333589] Sun, 13 July 2008 08:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
which kind of information ?

The one that are in the forum guide and in the sticky at top of this forum.

Regards
Michel
Re: how to force parallel to subquery of select statement ? [message #333606 is a reply to message #333582] Sun, 13 July 2008 09:51 Go to previous messageGo to next message
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 #333607 is a reply to message #333606] Sun, 13 July 2008 10:05 Go to previous messageGo to next message
eyuval26
Messages: 3
Registered: March 2008
Junior Member
Hi again,

it's more complicated than i told you.
i used the index but still, using the index 100000 times on big table takes a lot of time (i think that's because reading blocks from side to side, the mechanical issue).
that's why i want to do that in parallel to reduce total time.
the one way i found is to insert the rows which are getting before exist checking to temporary table.
then, create a function that includes exist checking.
afterwards, select from the temporary table with parallel hint and using function in where clause. that seems the only solution for now. but it's more complicated.

Thanks anyway... Smile
Yuval...


Re: how to force parallel to subquery of select statement ? [message #333630 is a reply to message #333582] Sun, 13 July 2008 16:27 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Create Index ?
Next Topic: query performance
Goto Forum:
  


Current Time: Mon Nov 11 02:21:05 CST 2024