INDEX Question

From: <meinrad.teufel_at_frqnet.de>
Date: Thu, 11 Feb 1999 09:40:57 GMT
Message-ID: <79u8j7$f01$1@nnrp1.dejanews.com>

Hi,

I'm having problems with a union statement, where the result should be ordered by an indexed attribute. The query is very slow if the result is very big. The Query Plan tells me, that the last operation is SORT ORDER BY. If I execute each part of the Union-Statement seperate, the optimizer do not operate with SORT ORDER BY and so the each query is very fast.

What can I do against this performance problem??

The statement looks like this:

select 0 as status, time_stamp, msg_id from message where

       time_stamp >=98456712 and
       not exists( select msg_id from messagedisplay where
                       message.msg_id=messagedisplay.msg_id and
                       exists (select dp_id from display where
                                  messagedisplay.dp_id=dislay.dp_id and
                                  dp_name='DISPLAY1'))

union all
select 1 as status, time_stamp, msg_id from message m, messagedisplay md, display d where

       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;

Thanks,
Meinrad

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Feb 11 1999 - 03:40:57 CST