Re: PARALLEL QUERY
- From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sun, 28 Oct 2007 23:07:24 +0530
Hi Jonathan,
-- The original query involves only one big table(~20M) rows.
-- There is no primary key on the table but there exists a composite index.
-- The table is list partitioned on a column into 4 partitions.
-- Columns involved in the said composite index are NOT NULL and the
partition key column is also not null.
Would be great if you can share with us the way you determine whether Oracle
will use parallel execution for a query or not.
My query is very straight forward as below.
select * from big_table bt
where
<predicates involving the composite index columns> AND
<predicate involving the partition key column>;
e.g.,
select * from big_table bt
where
c1=12 AND
c2='A' AND
c3='A123' AND
c4 in (0,1); -- c4 partition key column
If it is too time consuming to elaborate here then can you please point me
to any document which mentions the algorithm that Oracle follows to decide
whether to use parallel execution or not.
Thanks in advance.
Regards,
Deepak
On 10/28/07, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
>
>
> Looking back through the thread, the only information I have
> about the query is that it is:
>
> select /*+ parallel(bt,4) */ from big_table bt;
>
> How many tables are there in your real query, and how many indexes
> are there available, and are any of the tables partitioned and which
> columns are declared not null, and how many lines is the serial
> execution plan that you get.
>
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: "DBA Deepak" <oracle.tutorials@xxxxxxxxx>
> To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
> Cc: <oracle-l@xxxxxxxxxxxxx>
> Sent: Saturday, October 27, 2007 9:16 PM
> Subject: Re: PARALLEL QUERY
>
>
> > Hi Jonathan,
> >
> > Thanks again for your nice explanation. But I had a little difficulty in
> > understanding the following....
> >
> >> think about ANY executions that might NOT do a parallel tablescan on
> table
> > XXX and make it impossible for them to happen.
> >
> > Would be great if you can kindly elaborate on the above.
> >
> > In my original post in this thread Oracle completely ignored the
> parallel
> > scan of the table even though the explain plan showed that it had lesser
> > cost. As per your explanation I had restricted/limited Oracle optimizer
> > (through hints) to follow parallel processing approach.
> >
> > Please excuse me for any kind of ignorance.
> >
> > Regards,
> >
> > Deepak
> >
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards,
Deepak
Oracle DBA
Other related posts:
