Query Optimization

From: John Flipse <jflipse_at_spacestar.com>
Date: 1998/05/14
Message-ID: <355A7DE1.C77D5423@spacestar.com>

Hello -

I am trying to optimize an Oracle query, but am running into issues with

the optimizer. We are running on Oracle v7.1, using the cost based optimizer.

I have put indices on all three tables involved in the query, but there always seems to be a full table scan on at least one of the tables. I have
executed the query plan on the tables both empty and full of data, with statistics and without, but none of these factors seem to make any difference.

I have played around with the order of the tables in the FROM clause, which
effects the plan, but not in the way I had hoped - it turns out that which
ever table is last in the FROM clause, that is the table which gets the full table scan. I've even tried including a dummy table containing one row
as the last table in the FROM clause, purposely not joining to it to form
a cart-prod on the one table, which presumably would get the full table scan,
thus causing the optimizer to use indexes on the other three tables as I'd like, but this causes TWO of the tables to then have full table scans!
Very frustrating to say the least.

Also, I've tried applying hints to force the optimizer to use the index I
want, but it just causes the full table scan to migrate to another of the
three tables. A guy I work with said he recalls reading somewhere that there
is some special odd-ball situation in Oracle where, for whatever reason,

the optimizer gets goofy and insists on a full table scan no matter what

gyrations are tried. An option which might possibly help resolve the issue
is to get away from the cost based optimizer, but unfortunately that is not
an option, as it would mean reconfiguring Oracle and impacting many other
applications other than just my own.

I have included the cre scripts for all the tables involved, as well as the
query in question below for reference.

My appologies for rambling, but I hope this gives you an idea of where I am and what I've tried up until now. Any ideas or help anyone has would
be greatly appreciated!!! Thanks!

jlf

drop table mr_element_relation;
create table mr_element_relation
(

        elem_id number  not null,
        parent_elem_id  number  not null,
        dflt_seq_no     number(4,0)     not null,
        last_updt_date  date    null,
        last_userid     varchar2(20)    null

);
CREATE UNIQUE INDEX idx_mr_element_relation

       ON mr_element_relation
          (elem_id, parent_elem_id)
       TABLESPACE multirdbidx;

drop table mr_element;
create table mr_element
(

        elem_id number  not null,
        elem_type_cd    char(4) not null,
        name    varchar2(255)   null,
        iakb_elem_name  varchar2(255)   null,
        description     varchar2(250)   null,
        culture_type_cd char(4) null,
        source_elem_id  number  null,
        system_no       number  null,
        dm_system_no    number  null,
        legacy_db       number  null,
        last_updt_date  date    null,
        last_userid     varchar2(20)    null

);
CREATE UNIQUE INDEX idx_mr_element

       ON mr_element
          (elem_id)
       TABLESPACE multirdbidx;

DROP SEQUENCE elem_id;

CREATE SEQUENCE elem_id

     INCREMENT BY 1
     START WITH 200000
     NOMAXVALUE NOCYCLE CACHE 20 ORDER;

drop table mr_model_explode;
create table mr_model_explode
(

        explode_id      number  not null,
        explode_dt      date    not null,
        model_id        number  not null,
        ks_id   number  not null,
        elem_id number  not null,
        elem_type_cd    char(4) not null,
        parent_elem_id  number  not null,
        name    varchar2(255)   null,
        description     varchar2(120)   null,
        seq     varchar2(2000)  not null,
        level_no        number  not null,
        explode_ind     char(1) not null,
        last_updt_date  date    null,
        last_userid     varchar2(20)    null

);
CREATE UNIQUE INDEX idx_mr_model_explode

       ON mr_model_explode
          (explode_id, elem_id, parent_elem_id)
       TABLESPACE multirdbidx;

DROP SEQUENCE explode_id;

CREATE SEQUENCE explode_id

     INCREMENT BY 1
     START WITH 1
     NOMAXVALUE NOCYCLE CACHE 20 ORDER;

SQL> explain plan set statement_id = 'modex3' for 2 select distinct
3 mme.explode_id,

  4          sysdate,
  5          mme.model_id,
  6          mme.ks_id,
  7          me.elem_id,
  8          me.elem_type_cd,
  9          mer.parent_elem_id,
 10          me.name,
 11          mme.seq || to_char(mer.dflt_seq_no, '09999'),
 12          1,
 13          'X'
 14    from mr_element_relation mer,
 15         mr_element me,
 16         mr_model_explode mme
 17   where mme.explode_ind = 'Y'
 18         and mme.explode_id = 1
 19         and mer.elem_id = me.elem_id
 20      and mer.parent_elem_id = mme.elem_id;

Explained.

SQL> @g:\data\mis\proj\mris\ddl\optimize\showplan Enter Plan Statement Id: modex3

Query Plan

---- SELECT STATEMENT Cost = SORT UNIQUE NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL MR_ELEMENT

TABLE ACCESS BY ROWID MR_MODEL_EXPLODE

          INDEX                RANGE SCAN          IDX_MR_MODEL_EXPLODE

      TABLE ACCESS             BY ROWID            MR_ELEMENT_RELATION

        INDEX                  UNIQUE SCAN

IDX_MR_ELEMENT_RELATION 9 rows selected.

SQL> Received on Thu May 14 1998 - 00:00:00 CDT