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

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 11 Sep 2003 22:43:43 -0700
Message-ID: <1a75df45.0309112143.19eddcd1@posting.google.com>

colocoloc_at_yahoo.com (ColoC) wrote in message

> /*+ INDEX_FFS(tab,ind_field) */
> Elapsed: 00:00:07.94
>
> /*+ INDEX(tab) */
> Elapsed: 00:00:19.81
>
> /* no hint */
> Elapsed: 00:00:08.14

Which shows that the CBO does not do a too shabbily job. Have you analysed tables and indexes for stats? What does the default "no hint" SQL's execution plan look like?

> So the INDEX_FFS() may not seem to help much (all the above were done
> in a short period of time, while other system load should be the
> same).

There is no single magic wand that makes all queries fast. Queries are diverse by nature and what works for one, does not work for another.

For example, INDEX_FFS on a bitmap index on a partition table (which is the choice made by the CBO):

SQL> select count(*) from foo;

COUNT(*)

56731865

Elapsed: 00:00:02.40

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1578 Card=1) 1 0 SORT (AGGREGATE)

   2    1     PARTITION RANGE (ALL)
   3    2       BITMAP CONVERSION (COUNT)
   4    3         BITMAP INDEX (FAST FULL SCAN) OF 'FOO_BMP_INDEX'

I'm not sure what you are trying to achieve though. Are you just looking at hints in general, or are you trying to solve a specific query's performance?

My take on hints is that there are a great method to experiment with different execution plans and data processing logic. I use it for ad-hoc stuff. However, I feel that it is very much an exception to go and hard code production code and SQL with hints.

--
Billy

Received on Fri Sep 12 2003 - 00:43:43 CDT