Re: INDEX Question

I'd guess that you have an index that starts with time_stamp. Each separate is using the index to drive the query, therefore separately the two queries return rows in order; however this does not mean that concatenating the results will produce an order set.

e.g.

        set 1    :        1,    5,    12
        union all
        set 2    :        3,    8,    15

The two sets individually are in order, but the only way to guarantee that the union is ordered is to sort all 6 items.

It would be nice to have some sort of merge hint for this type of situation. Be nicer if the optimiser recognised what was going on. Roll on Oracle 9

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

meinrad.teufel_at_frqnet.de wrote in message <79u8j7$f01$1_at_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 - 15:49:56 CST