Re: Slow query

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
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:

  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 - 16:58:22 CEST

Original text of this message