From: kenny_60559@yahoo.com (KENNY L. CHEN) Newsgroups: comp.databases.oracle Subject: Re: "Union all" and "UNION" ???? Return the same result or not ?? Date: 2 Feb 2004 05:12:17 -0800 Organization: http://groups.google.com Lines: 83 Message-ID: <83f59352.0402020512.342a6e52@posting.google.com> References: <83f59352.0401230234.210b7b56@posting.google.com> <7b208869.0401230947.44caa3a@posting.google.com> <83f59352.0401232325.61c22285@posting.google.com> <83f59352.0401272344.446e3e14@posting.google.com> <7b208869.0401300612.7ba6b9e0@posting.google.com> <83f59352.0401310243.7257905e@posting.google.com> NNTP-Posting-Host: 211.79.0.4 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1075727537 20848 127.0.0.1 (2 Feb 2004 13:12:17 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 2 Feb 2004 13:12:17 +0000 (UTC) kenny_60559@yahoo.com (KENNY L. CHEN) wrote in message news:<83f59352.0401310243.7257905e@posting.google.com>... > flado@imail.de (Vladimir Andreev) wrote in message news:<7b208869.0401300612.7ba6b9e0@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...... ========================== 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) then use hint in my SQL command... (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.