Re: Slow query
Date: 23 Aug 2000 15:58:22 +0100
Message-ID: <u766osm3ch.fsf_at_o2-3.ebi.ac.uk>
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:
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,
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
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-24Received on Wed Aug 23 2000 - 16:58:22 CEST
+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