Answer the question
In order to leave comments, you need to log in
What is the sequence in parsing select unique ... at oracle?
Imagine there are 2 tables.
table1
id {1,2,3,4,5,6} unique
key {0,1,0,5,4,1} any values
table2
id {3,4,5,6}
If I make a
select query in oracle unique t1.key from tabl1 t1, tabl2 t2 where t1.id = t2.id
what will be the result: (field key = 0 id = 1 was checked and other fields key = 0 are not checked, since select unique key)
5
4
1
or (key=0 id=1 was not included in the selection, so we check another field with key = 0)
0
5
4
1
Answer the question
In order to leave comments, you need to log in
CREATE TABLE table1 ( ID NUMBER, KEY NUMBER ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 1, 0 ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 2, 1 ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 3, 0 ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 4, 5 ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 5, 4 ) ;
INSERT INTO table1 ( ID, KEY ) VALUES ( 6, 1 ) ;
CREATE TABLE table2 ( ID NUMBER ) ;
INSERT INTO table2 ( ID ) VALUES ( 3 ) ;
INSERT INTO table2 ( ID ) VALUES ( 4 ) ;
INSERT INTO table2 ( ID ) VALUES ( 5 ) ;
INSERT INTO table2 ( ID ) VALUES ( 6 ) ;
COMMIT ;
SELECT UNIQUE t1.key FROM table1 t1, table2 t2 WHERE t1.id = t2.id ;
Plan Hash Value : 1501084449
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE | | | | | |
| 2 | MERGE JOIN | | | | | |
| 3 | SORT JOIN | | | | | |
| 4 | TABLE ACCESS FULL | TABLE2 | | | | |
| * 5 | SORT JOIN | | | | | |
| 6 | TABLE ACCESS FULL | TABLE1 | | | | |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - access("T1"."ID"="T2"."ID")
* 5 - filter("T1"."ID"="T2"."ID")
SQL> SELECT UNIQUE t1.key FROM table1 t1, table2 t2 WHERE t1.id = t2.id ;
KEY
----------
0
1
4
5
SQL>
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question