Re: INDEX Question

From: Virag Saksena <vsaksena_at_sg.oracle.com>
Date: Sun, 21 Feb 1999 23:43:45 +0800
Message-ID: <36D029B1.6BD8@sg.oracle.com>

Meinrad,

        There are two problems here ...

#1. The union-all will cause you a sort (as described earlier in

    the thread)
#2. Your data access paths are not optimized

To take care of #1, we need to replace the union-all with an equivalent SQL statement. What you are trying to do is : If there are matching rows in md and d - return 1, else return 0 this can be accomplished very elegantly using an outer join ...

You can re-write it as :

select decode(nvl(d.rowid,0),0,0,1)) as status,

       time_stamp, msg_id
  from message m, messagedisplay md, display d  where m.time_stamp >=98456712
   and m.msg_id = md.msg_id(+)
   and md.dp_id = d.dp_id(+)
   and d.dp_name(+) = 'DISPLAY1'
order by time_stamp;

Since it is an outer joing, it'll return rows from m even when there are no corresponding rows in md and/or d. For the no rows cases, the columns from md/m will be returned as NULL, so your decode will return you a 0. In other cases status will return a 1.

The execution plan you should be trying to get is .. Execution Plan

---

  NESTED LOOPS OUTER
    NESTED LOOPS OUTER

      TABLE ACCESS BY INDEX ROWID MESSAGE
        INDEX RANGE SCAN MESSAGE...
      TABLE ACCESS BY INDEX ROWID MESSAGEDISPLAY
        INDEX RANGE SCAN MESSAGEDISPLAY...
    TABLE ACCESS BY INDEX ROWID DISPLAY
      INDEX RANGE SCAN DISPLAY...

The indexes here being used should be
 message(timestamp)
 messagedisplay(msg_id)
 display(dp_id)

The tuning you'd have to do would involve # Ensure the correct indexes are being used # Ensure the join methods are NESTED LOOP OUTER and   not MERGE JOIN OUTER or HASH JOIN OUTER.

There won't be any sort because you'd be driving using the index

What are the indexes MESSAGEDISPLAY_PK and MESSAGE_PK ?

Display seems like a small table, and it seems that MESSAGEDISPLAY_PK includes MSG_ID and DP_ID. This is causing the optimizer to go to MD after D. When you test the outer joins make sure

Happy Tuning.

Regards,  

Virag  

---

sg.oracle.com
Consulting Manager 6 Temasek Boulevard mobile: +65 9830 0889
Mission Critical Systems Suntec Tower Four, #12-01 direct: +65 431 1628
South Asia Region Singapore 038986 fax: +65 333 8806

---

meinrad.teufel_at_frqnet.de wrote:
>
> Hi,
> Thank you very much for your interest.
>
> The result can be of 250000 sets and it needs about 3 minutes (only
> selecting, not fetching). If I take the same statement without order by it
> takes about 3s.
>
> One index is the attribute time_stamp which I use it in order by.
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1824 Card=6546 B
> ytes=477858)
> 1 0 SORT (ORDER BY)
> 2 1 UNION-ALL
> 3 2 FILTER
> 4 3 TABLE ACCESS (FULL) OF 'MESSAGE' (Cost=1117 Card=5455
> Bytes=283660)
> 5 3 NESTED LOOPS (Cost=1 Card=1 Bytes=126)
> 6 5 TABLE ACCESS (FULL) OF 'DISPLAY' (Cost=1 Card=1 Bytes=100)
> 7 5 INDEX (UNIQUE SCAN) OF 'MESSAGEDISPLAY_PK' (UNIQUE)
> 8 2 NESTED LOOPS (Cost=4 Card=1091 Bytes=194198)
> 9 8 NESTED LOOPS (Cost=2 Card=1 Bytes=126)
> 10 9 TABLE ACCESS (FULL) OF 'DISPLAY' (Cost=1 Card=1 Bytes=100)
> 11 9 TABLE ACCESS (FULL) OF 'MESSAGEDISPLAY'
> 12 8 TABLE ACCESS (BY ROWID) OF 'MESSAGE'
> 13 12 INDEX (UNIQUE SCAN) OF 'MESSAGE_PK' (UNIQUE)
>
> Statistics
> ----------------------------------------------------------
> 1079 recursive calls
> 528520 db block gets
> 548322 consistent gets
> 25731 physical reads
> 22484 redo size
> 6523499 bytes sent via SQL*Net to client
> 193438 bytes received via SQL*Net from client
> 17505 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 262543 rows processed
>
> I tried to rewrite the statement, but I didn't find a solution without UNION.
> So I enlarged the SORT_AREA_SIZE to a size with the best performance, but it
> is not good enough. Are there more Parameters which supports the sorting?
>
> Thanks,
> Meinrad
>
> In article <7a9se1$vqd$1_at_nnrp1.dejanews.com>,
> andrewf_at_jaredgroup.com wrote:
> > Can you provide more detail? How big is the result set? How slow is the
> > execution? What's the index and what's the plan?
> >
> > From the info available, if a SORT is the reason of slowness, you may want to
> > tune the system to better support SORT. But a better solution may be to
> > rewrite your SQL to get rid of Union.
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

-- Received on Sun Feb 21 1999 - 09:43:45 CST