Home -> Usenet -> comp.databases.oracle -> Re: Help! SQL tuning: using ALL indexes

Re: Help! SQL tuning: using ALL indexes

From: Gerard H. Pille <ghp_at_infosoft.be>
Date: 1996/07/18
Message-ID: <4sm4b4$g2n_at_news.Belgium.EU.net> 








In article <4sh0qj$i0j_at_steadfast.teradyne.com!, Bonnie Torkko
(torkko_at_std.teradyne.com) says...


!

!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-

!within-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 cost-

!based 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)


!*****************************************************************************

!




Would you mind trying a single-column index on SYN.ID and defining DSUM as follows:
 create view DSUM (ID, HNUM, SNUM, TNUM, COUNT) as

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


Maybe the brackets were hiding something from the optimiser.  You shouldn't have put them, 
should you?  But the "SYN.ID id" might also prove usefull.  Views do hurt somewhere, they are a 
pain ...



I'd like to know if any of this did help.

-- 
Kind reGards
     \ /   |
      X    |
     / \   s
     Gerard


Received on Thu Jul 18 1996 - 00:00:00 CEST












Original text of this message