Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> why rule based optimizer performs better than cost based?

why rule based optimizer performs better than cost based?

From: Guang Mei <gmei_at_incyte.com>
Date: Fri, 25 Jun 2004 10:55:49 -0400
Message-ID: <NJEDKDKJDGAKAEKKNEEJAEKADFAA.gmei@incyte.com>


Oracle 8173 on Sun Solaris 2.8

All application tables and indexes have been analyzed using:

execute DBMS_STATS.GATHER_TABLE_STATS('MT','TABLE_NAME',cascade => FALSE); execute DBMS_STATS.GATHER_INDEX_STATS('MT','INDEX_NAME');

I found one query that runs faster when hint /*+ rule */ is added. It basically uses indexes on table diseasemodels and observationdetails. When cost based (default) optimizer is used, oracle does full table scan on these two tables, which make the query run slower (from Elapsed time measurement). I am curious why here the "rule based" optimizer performas better than "cost based".

TIA. Guang

Here are the query (with results) and the definition of the tables:

   select /*+ rule */

        distinct name, evidence, seeflag, referenceid, category2gene.id cid,
        d.mnemonic meshid

    from diseasemodels, category d,
    observationdetails, category2gene where geneid = 156005 and     diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid     and category2gene.id = category2geneid and name = d.string(+) and     (d.categorytype = 'MESH' or d.categorytype is null)     order by name asc;

NAME




E S REFERENCEID CID MESHID
- - ----------- ---------- ----------------
Cataract
E N       58814     843529 D002386

Cataract
E N       58814     843532 D002386


Elapsed: 00:00:00.43

Execution Plan


   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 SORT (UNIQUE)

   2    1     FILTER
   3    2       NESTED LOOPS (OUTER)
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE'
   7    6               INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_IN
          DEX' (NON-UNIQUE)

   8    5             TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONDET
          AILS'

   9    8               INDEX (RANGE SCAN) OF 'OBSDETAILS_C2GID_INDEX'
           (NON-UNIQUE)

  10    4           TABLE ACCESS (BY INDEX ROWID) OF 'DISEASEMODELS'
  11   10             INDEX (RANGE SCAN) OF 'DMODELS_PDID_INDEX' (NON-
          UNIQUE)

  12    3         TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY'
  13   12           INDEX (RANGE SCAN) OF 'CATEGORY_STRING_INDEX' (NON
          -UNIQUE)


Statistics


          0  recursive calls
          0  db block gets
         78  consistent gets
          0  physical reads
          0  redo size
        752  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed


    select

        distinct name, evidence, seeflag, referenceid, category2gene.id cid,
        d.mnemonic meshid

    from diseasemodels, category d,
    observationdetails, category2gene where geneid = 156005 and     diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid     and category2gene.id = category2geneid and name = d.string(+) and     (d.categorytype = 'MESH' or d.categorytype is null)     order by name asc;

NAME




E S REFERENCEID CID MESHID
- - ----------- ---------- ----------------
Cataract
E N       58814     843529 D002386

Cataract
E N       58814     843532 D002386


Elapsed: 00:00:00.64

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=153 Card=5 Bytes=665
          )

   1    0   SORT (UNIQUE) (Cost=100 Card=5 Bytes=665)
   2    1     FILTER
   3    2       NESTED LOOPS (OUTER)
   4    3         HASH JOIN (Cost=32 Card=5 Bytes=250)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE' (
          Cost=11 Card=13 Bytes=247)

   6    5             INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_INDE
          X' (NON-UNIQUE) (Cost=3 Card=13)

   7    4           HASH JOIN (Cost=20 Card=16107 Bytes=499317)
   8    7             TABLE ACCESS (FULL) OF 'DISEASEMODELS' (Cost=4 C
          ard=5765 Bytes=132595)

   9    7             TABLE ACCESS (FULL) OF 'OBSERVATIONDETAILS' (Cos
          t=14 Card=42946 Bytes=343568)

  10    3         TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=3
          Card=75859 Bytes=6296297)

  11   10           INDEX (RANGE SCAN) OF 'CATEGORY_STRING_INDEX' (NON
          -UNIQUE) (Cost=2 Card=75859)

Statistics


          0  recursive calls
          8  db block gets
        140  consistent gets
          0  physical reads
          0  redo size
        752  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed


MT_at_max-SQL> desc diseasemodels
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

----
PHENOTYPEDETAILSID NOT NULL NUMBER NAME VARCHAR2(2000) MT_at_max-SQL> desc observationdetails; Name Null? Type ----------------------------------------- -------- ------------------------
----
CATEGORY2GENEID NOT NULL NUMBER PHENOTYPEDETAILSID NOT NULL NUMBER MT_at_max-SQL> desc category2gene Name Null? Type ----------------------------------------- -------- ------------------------
----
ID NOT NULL NUMBER CATEGORYID NOT NULL NUMBER GENEID NOT NULL NUMBER CURID NOT NULL NUMBER NOTFLAG CHAR(1) CCOMMENT VARCHAR2(300) EVIDENCE CHAR(1) SEEFLAG NOT NULL CHAR(1) REFERENCEID NUMBER METHOD VARCHAR2(128) SEQTABID NUMBER PROPDATE DATE MT_at_max-SQL> desc category; Name Null? Type ----------------------------------------- -------- ------------------------
----
ID NOT NULL NUMBER STATUS NOT NULL VARCHAR2(16) CATEGORYTYPE NOT NULL VARCHAR2(16) STRING VARCHAR2(240) MNEMONIC VARCHAR2(16) NUMERIC NUMBER



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
Received on Fri Jun 25 2004 - 09:53:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US