CODEWITHSUNDEEP
sqlperformanceoracle-databaseplsql
Amir
Amir

Reputation: 1979

check about performance of two queries

Would you mind help me which query performing better and which one is more recommended in oracle database?

first query:

Select t2_c1,t1_c3,t1_c4,t3_c1

from t1,t2,t3

where   t1_c1 = t2_c1   and    t1_c2=t3_c2

Plan:

ID  PID Operation   Name    Rows    Bytes   Cost    CPU Cost    IO Cost Temp space  IN-OUT  PQ Dist PStart  PStop
0       SELECT STATEMENT        22362   829K    82      82                    
1   0     HASH JOIN     22362   829K    82      82                    
2   1       TABLE ACCESS FULL   CUSTOMER    87  2349    2       2                     
3   1       TABLE ACCESS FULL   MASTER  22362   240K    79      79

Second query:

select (select t2_c2 from t2 where t1_c1 = t2_c1),  t1_c3,  t1_c4,  t3_c1

from t1, t3

where t1_c2=t3_c2

Plan:

ID  PID Operation   Name    Rows    Bytes   Cost    CPU Cost    IO Cost Temp space  IN-OUT  PQ Dist PStart  PStop
0       SELECT STATEMENT        22362   240K    79      79                    
1   0     TABLE ACCESS BY INDEX ROWID   CUSTOMER    1   27  2       2                     
2   1       INDEX RANGE SCAN    SYS.IDX_CUSTOMER_V_CUST 1       1       1                     
3   0     TABLE ACCESS FULL MASTER  22362   240K    79      79

Upvotes: 1

Views: 83

Answers (1)

Matheus Segalotto
Matheus Segalotto

Reputation: 49

if you would like return less than 4% of line on table MASTER I recommend you the second one, but if you will return all lines on table MASTER the first plan is much better.

Any question just let me know.

Thanks.

Upvotes: 1

Related Questions