OraFAQ Forum: Performance Tuning » Order of executing indexes (2 merged)

Home » RDBMS Server » Performance Tuning » Order of executing indexes (2 merged) (oracle 10.2.0,4,0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order of executing indexes (2 merged) [message #478394]

Fri, 08 October 2010 08:07

Marnickxs
Messages: 9
Registered: August 2010
Location: The Netherlands

Junior Member

SELECT sim.quan_shipped
- NVL ( (SELECT SUM (rim1.quantity)
FROM lsm_return_items rim1
WHERE rim1.id <> :b3
AND rim1.loi_id = oim.loi_id
AND rim1.smt_id = sim.smt_id),
0
)
quan_shipped,
sim.smt_id,
oim.loi_id
FROM ldr_shipment_items sim, ldr_order_items oim
WHERE oim.id = sim.oim_id AND oim.loi_id = :b2
ORDER BY DECODE (SIGN (sim.quan_shipped - :b1),
0, 1000, sim.quan_shipped) DESC

whe expect that this query first uses the index on
ldr_order_items but instead of that he first uses the index

Plan
SELECT STATEMENT  ALL_ROWS Cost: 3  			
3 SORT AGGREGATE  
Bytes: 22  Cardinality: 1  		
 2 TABLE ACCESS BY INDEX ROWID TABLE CB.LSM_RETURN_ITEMS 
 Cost: 1  Bytes: 22  Cardinality: 1  			
  1 INDEX RANGE SCAN INDEX CB.LSM_RIM_LOI_FK_I 
  Cost: 1  Cardinality: 1  		

9 SORT ORDER BY  
Cost: 3  Bytes: 27  Cardinality: 1  	
 8 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_SHIPMENT_ITEMS 
 Cost: 1  Bytes: 15  Cardinality: 1  			
  7 NESTED LOOPS  
  Cost: 2  Bytes: 27  Cardinality: 1  		
   5 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_ORDER_ITEMS 
   Cost: 1  Bytes: 12  Cardinality: 1  	
    4 INDEX RANGE SCAN INDEX CB.LDR_OIM_LOI_ID_I 
    Cost: 1  Cardinality: 1  
     6 INDEX RANGE SCAN INDEX CB.LDR_SIM_OIM_ID_I 
     Cost: 1  Cardinality: 1

Does anyone have a clou, nothing was changed.

Report message to a moderator

Order of executing indexes [message #478396 is a reply to message #478394]

Fri, 08 October 2010 08:16

Marnickxs
Messages: 9
Registered: August 2010
Location: The Netherlands

Junior Member

SELECT sim.quan_shipped
  - NVL ( (SELECT SUM (rim1.quantity)
             FROM lsm_return_items rim1
             WHERE     rim1.id <> :b3
                   AND rim1.loi_id = oim.loi_id
                   AND rim1.smt_id = sim.smt_id),
           0
      )
       quan_shipped,
    sim.smt_id,
    oim.loi_id
FROM ldr_shipment_items sim, ldr_order_items oim
WHERE oim.id = sim.oim_id AND oim.loi_id = :b2
ORDER BY DECODE (SIGN (sim.quan_shipped - :b1), 
0, 1000, sim.quan_shipped) DESC

whe expect that this query first uses the index on
ldr_order_items but instead of that he first uses the index

[pre]
Plan
SELECT STATEMENT  ALL_ROWS Cost: 3  			
3 SORT AGGREGATE  
Bytes: 22  Cardinality: 1  		
 2 TABLE ACCESS BY INDEX ROWID TABLE CB.LSM_RETURN_ITEMS 
 Cost: 1  Bytes: 22  Cardinality: 1  			
  1 INDEX RANGE SCAN INDEX CB.LSM_RIM_LOI_FK_I 
  Cost: 1  Cardinality: 1  		

9 SORT ORDER BY  
Cost: 3  Bytes: 27  Cardinality: 1  	
 8 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_SHIPMENT_ITEMS 
 Cost: 1  Bytes: 15  Cardinality: 1  			
  7 NESTED LOOPS  
  Cost: 2  Bytes: 27  Cardinality: 1  		
   5 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_ORDER_ITEMS 
   Cost: 1  Bytes: 12  Cardinality: 1  	
    4 INDEX RANGE SCAN INDEX CB.LDR_OIM_LOI_ID_I 
    Cost: 1  Cardinality: 1  
     6 INDEX RANGE SCAN INDEX CB.LDR_SIM_OIM_ID_I 
     Cost: 1  Cardinality: 1 
[/pre]

Does anyone have a clou, nothing was changed.

[Updated on: Fri, 08 October 2010 09:38] by Moderator

Report message to a moderator

Re: Order of executing indexes [message #478397 is a reply to message #478396]

Fri, 08 October 2010 08:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

Report message to a moderator

Re: Order of executing indexes [message #478449 is a reply to message #478397]

Fri, 08 October 2010 17:02

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

That looks pretty strange to me. Assuming index LDR_SIM_OIM_ID_I is on the table ldr_shipment_items, and there are no access predicates on that table, then what could it be scanning on?

Suggest you use Oracles more modern Explain Plan, which includes predicate information.

EXPLAIN PLAN FOR
SELECT sim.quan_shipped
  - NVL ( (SELECT SUM (rim1.quantity)
             FROM lsm_return_items rim1
             WHERE     rim1.id <> :b3
                   AND rim1.loi_id = oim.loi_id
                   AND rim1.smt_id = sim.smt_id),
           0
      )
       quan_shipped,
    sim.smt_id,
    oim.loi_id
FROM ldr_shipment_items sim, ldr_order_items oim
WHERE oim.id = sim.oim_id AND oim.loi_id = :b2
ORDER BY DECODE (SIGN (sim.quan_shipped - :b1), 
0, 1000, sim.quan_shipped) DESC;

SELECT * FROM table(dbms_xplan.display());

Ross Leishman

Report message to a moderator

Re: Order of executing indexes (2 merged) [message #478599 is a reply to message #478394]

Mon, 11 October 2010 04:20

Marnickxs
Messages: 9
Registered: August 2010
Location: The Netherlands

Junior Member

Hereby the predicates

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(("RIM1"."SMT_ID"=:B1 AND "RIM1"."ID"<>:B3))
3 - access("RIM1"."LOI_ID"=:B1)
8 - access("OIM"."LOI_ID"=:B2)
9 - access("OIM"."ID"="SIM"."OIM_ID")

Hopefully this helps.

Report message to a moderator

Re: Order of executing indexes (2 merged) [message #478672 is a reply to message #478394]

Mon, 11 October 2010 16:56

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

In order for people to help, you can help them by putting all of the information together so they don't have to scroll through 3 different posts.

Also, the line numbers on the predicates you just posted are related to the Explain Plan that they were part of, not the one you posted previously, which shows hierarchical step numbers.

Lucky for you I love a challenge. But until you post the ENTIRE results of what I requested, my help is the ONLY help you will get, because nobody else is stupid enough to bother trying to untangle the partial information you've provided.

SELECT sim.quan_shipped
  - NVL ( (SELECT SUM (rim1.quantity)
             FROM lsm_return_items rim1
             WHERE     rim1.id <> :b3
                   AND rim1.loi_id = oim.loi_id
                   AND rim1.smt_id = sim.smt_id),
           0
      )
       quan_shipped,
    sim.smt_id,
    oim.loi_id
FROM ldr_shipment_items sim, ldr_order_items oim
WHERE oim.id = sim.oim_id AND oim.loi_id = :b2
ORDER BY DECODE (SIGN (sim.quan_shipped - :b1), 
0, 1000, sim.quan_shipped) DESC;


SELECT STATEMENT  ALL_ROWS Cost: 3  			
3 SORT AGGREGATE  
Bytes: 22  Cardinality: 1  		
 2 TABLE ACCESS BY INDEX ROWID TABLE CB.LSM_RETURN_ITEMS 
 Cost: 1  Bytes: 22  Cardinality: 1  			
  1 INDEX RANGE SCAN INDEX CB.LSM_RIM_LOI_FK_I 
  Cost: 1  Cardinality: 1  		

9 SORT ORDER BY  
Cost: 3  Bytes: 27  Cardinality: 1  	
 8 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_SHIPMENT_ITEMS 
 Cost: 1  Bytes: 15  Cardinality: 1  			
  7 NESTED LOOPS  
  Cost: 2  Bytes: 27  Cardinality: 1  		
   5 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_ORDER_ITEMS 
   Cost: 1  Bytes: 12  Cardinality: 1  	
    4 INDEX RANGE SCAN INDEX CB.LDR_OIM_LOI_ID_I 
    Cost: 1  Cardinality: 1  
     6 INDEX RANGE SCAN INDEX CB.LDR_SIM_OIM_ID_I 
     Cost: 1  Cardinality: 1 

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(("RIM1"."SMT_ID"=:B1 AND "RIM1"."ID"<>:B3))
3 - access("RIM1"."LOI_ID"=:B1)
8 - access("OIM"."LOI_ID"=:B2)
9 - access("OIM"."ID"="SIM"."OIM_ID")

I think I see what's going on here. You have somehow indented the Explain Plan incorrectly. It should be:

SELECT STATEMENT  ALL_ROWS Cost: 3  			
3 SORT AGGREGATE  
Bytes: 22  Cardinality: 1  		
 2 TABLE ACCESS BY INDEX ROWID TABLE CB.LSM_RETURN_ITEMS 
 Cost: 1  Bytes: 22  Cardinality: 1  			
  1 INDEX RANGE SCAN INDEX CB.LSM_RIM_LOI_FK_I 
  Cost: 1  Cardinality: 1  		

9 SORT ORDER BY  
Cost: 3  Bytes: 27  Cardinality: 1  	
 8 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_SHIPMENT_ITEMS 
 Cost: 1  Bytes: 15  Cardinality: 1  			
  7 NESTED LOOPS  
  Cost: 2  Bytes: 27  Cardinality: 1  		
   5 TABLE ACCESS BY INDEX ROWID TABLE CB.LDR_ORDER_ITEMS 
   Cost: 1  Bytes: 12  Cardinality: 1  	
    4 INDEX RANGE SCAN INDEX CB.LDR_OIM_LOI_ID_I 
    Cost: 1  Cardinality: 1  
   6 INDEX RANGE SCAN INDEX CB.LDR_SIM_OIM_ID_I 
   Cost: 1  Cardinality: 1

Now this is telling us something more sensible. We see that it is driving off the LDR_OIM_LOI_ID_I index (oim.loi_id = :b2) and then joining to ldr_shipment_item on the LDR_SIM_OIM_ID_I index (oim.id = sim.oim_id).

So, it turns out that the query is doing exactly what you expected it to: it is starting with ldr_order_items, not ldr_shipment_item as was implied by the incorrectly indented plan.

Ross Leishman

Report message to a moderator

Previous Topic:	Query Problem
Next Topic:	Need performance tuning in delete statement

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 30 06:27:49 CDT 2024