Re: Q: Oracle sqlplus way to capture all duplications and removal.

colocoloc_at_yahoo.com (ColoC) wrote i

> As I wrote in another earlier post to this thread, I had some minutes
> before I leave. I know that we are on a 4 CPU UNIX box, I did the
> following:
>
> ##################################
> > set time on
> > select count(*)
> > from tab_1
> > where one_line_condition_on_a_well_indexed_field;
>
> FIELD_NAME
> ------------
> xxxxxx
>
> Elapsed: 00:00:22.02
>
> > select /*+ FULL(tab_1) PARALLEL(tab_1,4) */
> > count(*) from tab_1
> > where one_line_condition_on_a_well_indexed_field;
>
> FIELD_NAME
> ------------
> xxxxxx
>
> Elapsed: 00:01:12.67
>
> ##################################
>
> Should this mean that I am wrong in using the parallel query or should
> this mean that it is not well configured to be using it? Thanks.

The problem in your SQL:

   where one_line_condition_on_a_well_indexed_field;

This could be done faster using a fast full index scan (Oracle CBO hint INDEX_FFS).

And likely this is what's happening - and why it is faster that a full table scan in parallel.

BTW, I do select count(*) using INDEX_FFS on large partitioned tables and get something like a 50+ million rows count result within 2 to 3 seconds.

Using a full table scan in parallel, takes 60+ seconds in not longer.

The basic criteria for using parallel query is when you are hitting a large volume of data with your query, and where that large volume can be broken up into smaller volumes and processed in parallel.

Try the following on a large table:

select /*+ full(t) */ count(*) from table t versus
select /*+ full(t) parallel(t,4) */ count(*) from table t

This should give you a basic idea of the concept of splitting the large data volume into smaller volumes for processing.

--
Billy