Help! SQL tuning: using ALL indexes

From: Bonnie Torkko <torkko_at_std.teradyne.com>
Date: 1996/07/16
Message-ID: <4sh0qj$i0j_at_steadfast.teradyne.com>

I'm trying to find a faster way to execute my queries with views-within-views that include outer joins; I can't understand why the optimizer will not use my master index when this is the column referenced in my WHERE clause (even though it uses IN with a list of values). I read that a clause like "id in (1,2)" treats it like "id = 1 OR id = 2" and I have notes from a class that say it should use the index but I believe there is a problem when the FROM clause contains a view with an outer join. We are forced to use the view- -a-view because there is a bug in Oracle7 7.2.2 when performing a remote query using database links with table joins because the table join ends up being performed over the network rather than on the remote database. I know I can use UNION as a work-around and split up the query but this makes my application much more complex since I am already building separate queries to do a distributed query (using UNION ALL). I also tried using the costbased  optimizer by analyzing the MASTER table but that didn't change things. Any help or explanation is appreciated and I'm including too much information to clarify things. Thanks and please respond to me directly if you can,

	Bonnie
	torkko_at_std.teradyne.com



*****************************************************************************

TABLES, INDEXES and VIEWS

---

create table MASTER (

        ID           integer                 not null);

create table DESC (
        ID           integer                 not null,
        TNUM         integer                 not null);

create table SYN (
        ID           integer                 not null,
        TNUM         integer                 not null,
        HNUM         integer,
        SNUM         integer,
        COUNT        integer);

create unique index MASTER_INDEX on MASTER (ID); create index DESC_INDEX on DESC (ID, TNUM); create index SYN_INDEX on SYN (ID, TNUM, HNUM, SNUM);

create view DSUM (ID, HNUM, SNUM, TNUM, COUNT) as

       select    SYN.ID, HNUM, SNUM, SYN.TNUM, COUNT
       from      SYN, DESC
       where     COUNT is not null
         and     (SYN.ID = DESC.ID (+) and SYN.TNUM = DESC.TNUM (+));

create view MASTER_DSUM as
       select    M.ID, D.HNUM, D.SNUM, D.TNUM, D.COUNT
       from      DSUM D, MASTER M
       where     M.ID = D.ID;



*****************************************************************************

EXECUTION PLANS, RULE-BASED OPTIMIZER

---

SELECT count(*) FROM dsum WHERE id = 300

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: CHOOSE
      0   SORT (AGGREGATE)
    145    NESTED LOOPS (OUTER)
    169     TABLE ACCESS (BY ROWID) OF 'SYN'
    170      INDEX (RANGE SCAN) OF 'SYN_INDEX' (NON-UNIQUE)
    290     INDEX (RANGE SCAN) OF 'DESC_INDEX' (NON-UNIQUE)

SELECT count(*) FROM dsum WHERE id in (100,200,300,400)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: CHOOSE
      0   SORT (AGGREGATE)
    580    NESTED LOOPS (OUTER)
37334637     TABLE ACCESS (FULL) OF 'SYN'                  <<<< TERRIBLE!
   1160     INDEX (RANGE SCAN) OF 'DESC_INDEX' (NON-UNIQUE)


*****************************************************************************

SELECT count(*) FROM master_dsum WHERE id = 300

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: CHOOSE
      0   SORT (AGGREGATE)
    145    NESTED LOOPS (OUTER)
    145     NESTED LOOPS
      1      INDEX (UNIQUE SCAN) OF 'MASTER_INDEX' (UNIQUE)
    169      TABLE ACCESS (BY ROWID) OF 'SYN'
    170       INDEX (RANGE SCAN) OF 'SYN_INDEX' (NON-UNIQUE)
    290     INDEX (RANGE SCAN) OF 'DESC_INDEX' (NON-UNIQUE)
 

SELECT count(*) FROM master_dsum WHERE id in (100,200,300,400)  

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   HINT: CHOOSE
      0   SORT (AGGREGATE)
    580    NESTED LOOPS (OUTER)
    580     NESTED LOOPS
1376073      TABLE ACCESS (FULL) OF 'MASTER'                 <<<< TERRIBLE!
    676      TABLE ACCESS (BY ROWID) OF 'SYN'
    680       INDEX (RANGE SCAN) OF 'SYN_INDEX' (NON-UNIQUE)
   1160     INDEX (RANGE SCAN) OF 'DESC_INDEX' (NON-UNIQUE)


*****************************************************************************

Received on Tue Jul 16 1996 - 00:00:00 CEST