Re: Slow query

Thanks for the suggestions. Here's some clarification:

I am using Rule Based Optimization, not CBO, so no statistics have been run. Also, without the concatenated NULL, Explain Plan showed no indexes in use in the Select, and it was much slower. I do have the indexes in trans_facts disabled while I do the Insert. I am aware of the 10% rule-of-thumb. Also, this is 7.3.4.

Thanks again.

Philip Lijnzaad wrote in message ...
>
>Alan> Below is a query used to insert rows into a fact table in our
 datamart. It
>Alan> takes almost an hour to run, which strikes me as a rather long time.
>
>I assume you refer to the SELECT part, not the INSERT part?
>
>If not, the only thing I can suggest (but be extremely careful with this,
 and
>e.g. disalllow others acces to TRANS_FACTS) is to disable all the indexes
 and
>integrity checks on TRANS_FACTS; once the INSERT is complete, enable them
>again. The integrety checking and index-inserting is a rather time
 consuming
>thing, so better do it in one go, is the idea.
>
>Alan> I
>Alan> managed to speed it up to this point (it used to take much longer) by
>Alan> concatenating a NULL in the WHERE clause to force the RBO to use the
 index
>Alan> on the larger table.
>
>Oracle has a host of optimizer optins you may want to investigate. They
 look
>like
>
> SELECT /*+ HINT(s) */ col1, col2 FROM ... WHERE ....
>
>where HINT (white-space separated) is one of:
>
> ALL_ROWS # optimize throughtput
> FIRST_ROWS # optimize response time
> FULL(tablename) # full scan on tablename; ignore index
> INDEX(tablename indexname(s)) # use indexname(s)
> ORDERED # do join in order of tables given
> # (most selective first)
> AND_EQUAL(table index index ...) # scan indexes simultaneously
> USE_NL(table table ...) # use a nested loop on table
> USE_MERGE(table table ...) # opposite of nested loop
> RULE # use rule-based optimizer
>
>Be sure to get the syntax right; if not, it won't have any effect, and
>there's no messages about it.
>
>
>But anyway, I'd be surprised if the optimizer does a bad job; in general,
 it
>is extremely good at guessing what goes on. Do you use statistics-based
>optimization, and is it switched on by default? Are the statistics recent
>enough (run ANALYZE if not).
>
>Lastly, please be aware that for any query that is likely to retrieve more
>than around 10% (*yes*, that little!) of the rows of table, it probably
 pays
>to disable the usage of the index on that table (e.g. by concating '' in
 the
>query, like you did before, or use a HINT). Often, even the
 statitics-based
>optimizer won't catch this thing, so you have to do it manually.
>
>Hope this helps,
>
>
 Philip
>
>--
>When C++ is your hammer, everything looks like a thumb. (Steven Haflich)
>---------------------------------------------------------------------------
 --
>Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm
 A2-24
>+44 (0)1223 49 4639 / Wellcome Trust Genome Campus, Hinxton
>+44 (0)1223 49 4468 (fax) \ Cambridgeshire CB10 1SD, GREAT
 BRITAIN
>PGP fingerprint: E1 03 BF 80 94 61 B6 FC 50 3D 1F 64 40 75 FB 53
Received on Wed Aug 23 2000 - 19:48:16 CEST