Oracle LEADING Tip - Why Is It Required?

Question

Suddenly (but, unfortunately, I do not know when it was “suddenly”, I know that at some point it was normal), one of my requests started from 7 seconds instead of milliseconds. I have 1 local table and 3 tables that are accessed through a DB link. 3 remote tables are merged together and one of them is connected to my local table.

A local table where a sentence requires only a few milliseconds to execute on its own and returns only a few (10 or 100) records. Three deleted tables have many hundreds of thousands, possibly millions of records between them, and if I join them accordingly, I get tens or hundreds of thousands of records.

I am only joining remote tables so that I can pull out several pieces of data associated with each record in my local table.

What seems to be happening is that Oracle first joins the remote tables together, and then my local table with this clutter at the end. This will always be a bad idea, especially considering the dataset that exists right now, so I added a tooltip /*+ LEADING(local_tab remote_tab_1) */to my query, and now it returns in milliseconds.

I compared the explanation plans, and they are almost identical, with the exception of one BUFFER SORTin one of the deleted tables.

I am wondering what could make Oracle get this wrong? Is this an index issue? What should I look for?

Answer 1

. - . Oracle , "", , , ; .

- , , . ​​

, , . dbms_stats. .

Answer 2

, , , , , :

SELECT *
FROM   tableA, tableB, tableC, tableD, tableE
WHERE  tableA.ID0 = :bind1
AND    tableA.ID1 = tableB.ID1
AND    tableB.ID2 = tableC.ID2
AND    tableC.ID3 = tableD.ID3
AND    tableD.ID4 = tableE.ID4
AND    tableE.ID5 = :bind2;

, A (, ID0 ) E ( tableE.ID5 ).

; ( ), , , , .

LEADING - (.. A), (.. - ).

Answer 3

. , , . // , . ( , .)

, Oracle . 10053 ( " Oracle" 8i-10.1) , LEADING .

DRIVING_SITE , , , , ; , , LEADING.

Answer 4

, , . , "", . , , .

. .

Answer 5

RI,

, SQL.

Oracle , , :

A) . , , ... .

B) /.

10 Oracle . , , ?

, , , Oracle . , Oracle , , , (.. ; -).

, :

• INSERT sub-SELECT .
• SELECT , .

1 , . , . , .

Oracle, , , . .