| Pls advice about this EXECUTION PLAN [message #180088] |
Fri, 30 June 2006 02:33  |
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
|
Senior Member |
|
|
I have a query that even don't give any output after 5 hours.I am pasting the EXE PLAN. Please advice
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7559859 Card=110 Byt
es=5720)
1 0 SORT (GROUP BY) (Cost=7559859 Card=110 Bytes=5720)
2 1 NESTED LOOPS (Cost=931593 Card=498471005 Bytes=259204922
60)
3 2 TABLE ACCESS (FULL) OF 'MYTABLE' (Cost=110195 Car
d=410699 Bytes=16838659)
4 2 TABLE ACCESS (FULL) OF 'MY_SECOND_TABLE' (Cost=2 Card=12
26 Bytes=13486) |
|
|
|
|
|
| Re: Pls advice about this EXECUTION PLAN [message #180101 is a reply to message #180088] |
Fri, 30 June 2006 02:52  |
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
|
Senior Member |
|
|
And query is as follows
select sap_id, mth_id, prd_sap, sum(net_invc_amt) from MYTABLE, 'MY_SECOND_TABLE
where dw_cd = 'PK'
and csa_invc_edw.sap_nbr <> mtl_mtl_nbr
and mth_id = '200301'
group by sap_id, prd_sap, mth_id
order by sap_id;
|
|
|
|
|
|
| Re: Pls advice about this EXECUTION PLAN [message #180290 is a reply to message #180197] |
Sun, 02 July 2006 21:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| smartin wrote on Sat, 01 July 2006 02:21 |
Am I reading that right...is that 410699 full table scans of the second table?
|
Of course that is only the CBO's best estimate. It could be lots more 
@gkrishn, I stongly suspect you are editing the SQL and possibly the plan in some misguided effort to simply the problem.
- Your FROM clause includes a quote mark: 'MY_SECOND_TABLE
- Your WHERE clause includes a table name / alias (csa_invc_edw) that doesn't exist in the FROM clause (I concede that it could be a packaged function call, but I doubt it)
Just a thought, if you included the correct query, we might be able to help.
Without knowing which tables the columns DW_CD and MTH_ID belong to, it's difficult to say, but this SQL (for dw_cd = 'PK' and mth_id = '200301') joins EVERY row in mytable to EVERY row in my_second_table.
Even if the <> clause is some dubious attempt at a join condition, it still joins EVERY row in mytable to EVERY row in my_second_table with a non-matching sap_nbr / mtl_mtl_nbr. It's my guess that more rows will mis-match that will match.
I think maybe you should add a proper join condition. One with an = clause would look nice.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
