Geek Answers Handbook

Adding redudant join conditions in Oracle leads to a different plan

I have a common database combining the situation with three tables. One table, A, is the primary table with the primary key named id. Tables B and C contain auxiliary data records and A, and each also has a column with a name idthat is a foreign key, indicating the A. id. Now, if I want all the data from A, B and C in one query, I would write:

SELECT *
FROM A
INNER JOIN B
ON B.id = A.id
INNER JOIN C
ON C.id = A.id

which of course works great.

Recently, our database administrator told us that this is inefficient in Oracle, and you also need to combine the conditions between C and B as follows:

SELECT *
FROM A
INNER JOIN B
ON B.id = A.id
INNER JOIN C
ON C.id = A.id AND C.id = B.id

, , , . , , , . : , "" , 1 035, "" - 389 ( ). .

- , ? C B . , , - .

+5
4

.

-, SQL , ID B, C. .

. Oracle , (, , ). , . , , hair = 'bald' 10% , , gender = 'F' 50%. Oracle , , hair = 'bald' gender = 'F' 5% ( ).

"" , Oracle , , .

Oracle , , . . .

, , .....

PS. , . B.ID C.ID , A.ID - , , , A.ID = B.ID A.ID = C.ID, B.ID!= C.ID, .

+2

.

, Oracle : Transitive Closure, .

, .

+3

Oracle . , A = B A = C, B = C, Oracle , B C, WHERE JOIN.

, A, B / C ( ), - , , ). , A, B C , . Oracle FROM, WHERE . , ( , ). B = C , B, C ( ), .

+2

.

, Oracle.

B C A, ,

INNER JOIN B
ON B.id = A.id

(, ), C , , , C A, .

, , C , A B, , A C.

+1

More articles:


All Articles