Re: From 1second to 2 minutes in a single column...

"tore" <TBostrup_at_teamia.com> wrote in message news:0DIP6.39$yH.2438_at_client...
> I have a very simple query:
>
> SELECT P1.InstrumentNumber,
> P1.FixedLastName
> FROM
> Instruments I,
> PartyNames P1
> WHERE I.InstrumentNumber = P1.InstrumentNumber
> AND P1.PartyIndex = 2
> AND P1.LastName >= 'A'
> AND P1.LastName <= 'ACzzzzzzzzzzz'
>
> InstrumentNumber is the primary key of Instruments. Instruments has just
> over 600,000 entries, while PartyNames has over 1.8 million entries. I
 have
> indexes for all the columns referenced in this query.
>
> This query executes in 1 second and returns less than 30 rows.
>
> If I change the select list to
>
> SELECT Count(*)
>
> the time explodes to 2 minutes and 17 seconds!
>
> If I add a column from Instrument to the select list (also indexed), I get
 a
> similar jump in execution time.
>
> How on earth is Oracle spending 2 minutes in order to obtain information
> about 30 rows???????
>
> Help !!
> Tore.
>
>

This is not yet completely sufficient background. Are you using the Rule Based Optimizer (not recommended) or the Cost Based Optimizer. Looks like it is rule based.
You would of course also *always* need to specify, *which version* of Oracle you are using. Many questions have a version specific answer.

So here are some educated guesses.
Your use of count(*) is ambigous, as count(*) will refer to *both* tables in your query, not to PartyNames only.
The jump in execution time probably has to do with the fact in the first statement, you don't select the instruments *table* at all, as you only need the index.
Evidently PartyNames is the driving table, as you don't have any specific condition on Instruments.
So this means the *larger* table of the two is drving. This is bad, unless the larger table is returning a smaller subset, using a correct index.
As you state you indexed *all* columns involved, the optimizer may well choose an incorrect index.

Evidently you need to study and/or to post explain plan results. In a correctly configured database you can already get them by set autotrace on explain stat

You might want to rephrase your query and transform the instruments part to a where exists correlated subquery.
Doing so, the optimizer will be less easily fooled into choosing an incorrect driving table.

If you are using CBO, try to create histograms for the PartyNames indexed columns.

Hth,

Sybrand Bakker, Oracle DBA
remove verwijder-dit to reply Received on Sat May 26 2001 - 02:35:16 CDT