Re: Query Optimization

Hi,

    We use oracle 7.3.2 and encoutered the same problem where we use the cost base that we can't force the optimizer to use the indexes we want. Therefore we switch to rule base. However, before you switch to rule base, you can first analyse the table again to whether this will help.

Man

John Flipse ¼¶¼g©ó¤å³¹ <355A7DE1.C77D5423_at_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 Fri May 15 1998 - 00:00:00 CDT