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

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0309110101.79e9ffb9_at_posting.google.com>...
> ...
>
>
> 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.

Hi, I am still alive for a while. So I did some more tests, no time to do any reading yet.

The timing for each of the mentioned hints or no-hint is as follows:

/*+ full(tab_t) */
Elapsed: 00:05:48.54

/*+ full(tab_t) parallel(tab_t) */
Elapsed: 00:01:38.47

/* no hint */
Elapsed: 00:00:22.53

/*+ index(tab_t,the_indexed_field) */
Elapsed: 00:00:19.36

And the table consists of hundreds of millions of rows, in which I counted only for an around 8-9 million portion.

I sense that index(t,pk) doesn't help much, meanwhile full(t) or parallel(t) are truly not good in this case.

And more often I have to do sum() on some other fields (of course by the same indexed field for condition clause), that takes even longer -- understood, may be sum() just cost more than count().

Any more hints from Billy or others?

ColoC Received on Thu Sep 11 2003 - 09:25:04 CDT