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

Thanks.

The database is Oracle 8.0.5 on NT4. It does use the rule based optimizer (by its own choice - default is set to CHOOSE). We don't have a DBA, and both the server setup and the database design was our company's first foray into Oracle (we are heavy on SQL Server development). There may be something simple or obvious that a(n experienced) DBA would have known to do for optimizing the database. Don't assume everything is as it should be...

The Query Plan (when including I.UniqueDateTime in the select list) is:

SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=817210 Bytes=113492190)  MERGE JOIN (Cost=37 Card=817210 Bytes=113592190)   INDEX (FULL SCAN) OF INSTRUMENT_DIM_COVER (NON-UNIQUE) (Cost=26 Card=1433701 Bytes=55914339)
  SORT (JOIN) (Cost=11 Card=57 Bytes=5700)    TABLE ACCESS (BY INDEX ROWID) OF PARTYNAMES (Cost = 2 Card = 57 Bytes = 5700)

    INDEX (RANGE SCAN) OF PARTYNAMEPINDEX (NON-UNIQUE) (Cost=1 Card=57)

Apparently it chooses the RULE-based optimizer. I'm not sure how I can get it to use the Cost based optimizer. I have tried using INDEX hints without any (visible) success. We don't have any decent tools for working with Oracle databases, only those excuses that Oracle provides, and I just came across the freeware version of TOAD (looks very promising...).

The simple query is actually an attempt to rebuild a larger query that also includes a self-join on the partyname table, and joins with two more tables, one large (probably comparable to the Instruments table) and one small (a few hundred rows). As I am testing, I select a reasonably small subset from the PartyName table (and eventually from he Instruments table). However, in production, the query will be the basis for a very large report (easily around 1000 printed pages of tabular output per range selected). I may have different cases for the report. In one case, the range on the Instruments table will include all (or most) instruments, but with a limited range of PartyNames, and in another case, there will be a significantly limited range selected from the Instruments table as well - I think the Partyname ranges will be the same (before the join with Instruments), but I don't know that for a fact.

As a part of the query, a date range must be checked against a column (in the Instrument table) that contains a date-like STRING (MM/DD/YYYY HH24:MI:SS.nn) (I wasn't there when this design took place or it would have been different...and now everything in the system depends on it). Short of implementing a shadow column (would need two, actually) and a trigger to populate it, are there any ways of either achieving some use of an index to perform the matching, or how can I force the optimizer to perform the join from the PartyNames table to Instruments FIRST (PartyNames would need to be and remain the driving table), and then have Oracle compare a TO_DATE(SUBSTR(UniqueDateTime, 1, 10), 'MM/DD/YYYY') to a date range on only the selected rows from Instruments instead of thinking it should restrict (by the where clause) Instruments before performing the join?

Unfortunately, our mailserver has been down this weekend so I can't get (or send) the news posts at work.

Regards,
Tore.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tgun1obqet9hbb_at_beta-news.demon.nl...
>
> "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.

<snip>

> > If I add a column from Instrument to the select list (also indexed), I
 get
> > a similar jump in execution time.

<snip>

> > 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 Sun May 27 2001 - 22:39:31 CDT