Re: Query Optimization

In article <355A7DE1.C77D5423_at_spacestar.com>,   John Flipse <jflipse_at_spacestar.com> wrote:
>
> 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> _at_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>
>

How many rows do u expect to be returned by this query?

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu May 14 1998 - 00:00:00 CEST