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

kenny_60559_at_yahoo.com (KENNY L. CHEN) wrote in message news:<83f59352.0401310243.7257905e_at_posting.google.com>...
> flado_at_imail.de (Vladimir Andreev) wrote in message news:<7b208869.0401300612.7ba6b9e0_at_posting.google.com>...
> > Sorry about the delay -- I was a bit busy...
> > Anyway,here I am now:
> > If you read my initial proposal carefully, you'll notice that the
> > where-clauses are *inside* the subqueries. That way, you'd first limit
> > the intermediate result sets, and then sort them. There comes your
> > performance gain:
> > SELECT * from
> > (SELECT COL1 , COL2 ,REC_NO FROM TEST
> > WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
> > UNION ALL
> > SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST
> > WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3
> > ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
> > )
> > where ROWNUM < 2
> >
> > Now you should expect to see two PK index range scans, concatenated,
> > and sorted with a STOPKEY option in the execution plan.
> >
> > Some notes on the results: If two identical rows exist in the tables
> > (one in each table), and they both pass the where-clause criteria, and
> > they have the minimum values in col1, col2, and rec_no, this query
> > will return only one of them. Which one, is undefined. You may not
> > care about this, since the records are identical, but you might want
> > to make this distinction, if in your *production* version you select
> > more than the key columns of these tables.
> > IOW, the question answered by this query is: I want at most one record
> > from either of the tables TEST and TMP_TEST. This record should have
> > the minimum values in col1, col2, and rec_no among all records that
> > pass my predicates. I don't care from which table that record comes.
> > As you notice, this is not exactly "the top record". It's only one of
> > the (potentially two, because of the unique constraints) top records.
> >
> > If you decide that you actually want both such records, you need
> > another solution. Using rownum<3 will not do the trick.
> > Let me know if this is the case. Or better yet, read Tom Kyte's
> > "Effective Oracle by Design"
> >
> > Regards,
> > Flado
>
> ======================
> Thank you Flado.
>
> My problem is I need to know exactly "the top" record then perform
> sequential update later. That is why the original design is like this:
> (SELECT COL1 , COL2 ,REC_NO FROM TEST
> UNION
> SELECT 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
>
> And it causes performance problem when the records is getting huge.
> (In my case, millions of records in each table.)
>
> I think if I can get "the top" record from one table and the other
> separately,
> then get the exactly "top one" record by comparing the 2 records.
> However,
> just like "UNION", sorting seems can't be avoided if I need to get the
> "top one" from each table. Again, that generates performance problem
> when the records is huge.
>
> May be like what you said , read the book and see if can get some good
> ideas......

(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. Received on Mon Feb 02 2004 - 14:12:17 CET