Re: "Union all" and "UNION" ???? Return the same result or not ??

>
> ==========================
> What if I create unique index on those table
> create unique index idx_test_k1 on test (col1 ,col2, rec_no)
> and create unique index idx_tmp_test_k1 on tmp_test (col1,col2,rec_no)

I've assumed you already have unique indexes on these columns (you said in your initial post that they were unique).

>
> (SELECT /*+ INDEX(TEST idx_test_k1) */ COL1 , COL2 ,REC_NO FROM TEST
> UNION ALL
> SELECT /*+ INDEX(TEST idx_tmp_test_k1) */ COL1 , COL2 ,REC_NO FROM TMP_TEST )
> WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 )
> AND ROWNUM < 2
> ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
>
> I tried several time seems work as I expected... But not sure it is exactly right.

It isn't. Your WHERE clause is applied only to the second select (from tmp_test).
So, if I understand correctly, you want this result: (col1, col2, rec_no, t_name), where t_name is either 'TEST' or 'TMP_TEST'.
If you are on 8i Enterprise or Personal Edition, or on 9i (any edition), use analytics:

select col1,col2,rec_no,t_name
from (

    select t.*, rank() over (order by col1,col2,rec_no) rn     from (

        select test.*, 'TEST' t_name from test
        union all
        select tmp_test.*, 'TMP_TEST' t_name from tmp_test) t

Otherwise (if you cannot use analytics), you'll need to pack and unpack the three columns, and use MIN(). You don't really want to go there, unless it is absolutely necessary.

HTH,
Flado Received on Tue Feb 03 2004 - 10:27:29 CET