B
B
Botu2015-05-14 07:29:07
Oracle
Botu, 2015-05-14 07:29:07

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

1 answer(s)
A
Anton, 2015-05-26
@Oraclist

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 ;

The execution plan will be as follows
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")

The plan is read from the most "right" to the most "left".
Those. the most nested row first and up the tree.
Actually SORT UNIQUE fulfills the penultimate point.
Answering the question, first there will be a connection and filtering ( line 5 of the plan ) and only then a selection of unique rows / values.
The correct answer is this (do not forget about sorting)
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 question

Ask a Question

731 491 924 answers to any question