Home » RDBMS Server » Performance Tuning » Pl. clear my douts of nested loop join.
Pl. clear my douts of nested loop join. [message #65542] Thu, 21 October 2004 01:11 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Dear frends,

I have some dout in nested loop join pl. clarify me.

SELECT a.unit_price * a.quantity
FROM order_items a,orders b
WHERE b.customer_id = :b1
AND a.order_id = b.order_id

Plan
-------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN CUSTOMER_ID
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID

In this case Order table is outer table and order_items table is inner table.

Actually How the Oracle Execute this plan..My mean to say is first it will keep block of first
row of outer table in buffer cache and then all rows of inner table in buffer cache.

Is inner table is scanned every time for every row of outer table ??

Pl.clear my douts.

Thax in adv.

Bhavin Shah
Re: Pl. clear my douts of nested loop join. [message #65544 is a reply to message #65542] Thu, 21 October 2004 03:03 Go to previous messageGo to next message
kri
Messages: 5
Registered: October 2004
Junior Member
Your question:
Is inner table is scanned every time for every row of outer table?
Answer: yes
Re: Pl. clear my douts of nested loop join. [message #65545 is a reply to message #65544] Thu, 21 October 2004 03:05 Go to previous messageGo to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Means if a table is large and if it's a full table scan then it's affecting badly on performance..am i right??

bhavin
Re: Pl. clear my douts of nested loop join. [message #65546 is a reply to message #65545] Thu, 21 October 2004 03:13 Go to previous messageGo to next message
kri
Messages: 5
Registered: October 2004
Junior Member
Yes it is right. But in your example, the outher table(orders) use access by index rowid, which is fast.

Your database use CBO, or RBO?
Re: Pl. clear my douts of nested loop join. [message #65556 is a reply to message #65542] Tue, 26 October 2004 03:05 Go to previous message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
Hi,
MY optimizer is using CBO OPTIMIZER.

Bhavin
Previous Topic: What is a effecting a analyzing a table for CBO.
Next Topic: Index not used by SQL
Goto Forum:
  


Current Time: Thu Mar 28 04:24:54 CDT 2024