08-12-2006 10:26 AM
I have a scenario where when we run a SELECT SINGLE query on a database view , the system hangs and subsequently doesn't give us a response.
The View is a Z.. view and is made of 4 base tables , which are ADRC,VBPA,VTTP and VTTK. The database view has approximately 8 million records.
When we run a SELECT SINGLE * INTO WA WHERE (CONDITION) query on the database view the system behaves randomly i.e. for few entries in the WHERE clause the query runs without any hic-ups. And on occasions the system hangs up and because of time-out , the program terminates.
Can some one please let me know why is the view and SELECT query behaving is such a way ?
Will appreciate anyones help !!!!
08-12-2006 10:29 AM
Hi,
Couple of things to note.
Select single should be specified with all the key fields. Are you specifying all the key fields while using select single?
Why are you using select single * ? do you want all the fields of the view? Choose only those fields which are really required.
r u using the select single within a Loop?
Run a SE30 analysis and see where the problem is.
Cheers
VJ
08-12-2006 10:29 AM
Hi,
Couple of things to note.
Select single should be specified with all the key fields. Are you specifying all the key fields while using select single?
Why are you using select single * ? do you want all the fields of the view? Choose only those fields which are really required.
r u using the select single within a Loop?
Run a SE30 analysis and see where the problem is.
Cheers
VJ
08-12-2006 10:50 AM
Hi,
Instead of a view, you can also INNER JOIN statements.
In your select queries make sure all the key fields meet the where condition and be more specific on your conditions.
Reward if helpful.
Regards
08-12-2006 10:58 AM
hi
welcome to SDN.
Well,while using SELECT SINGLE you need to specify all the key fields inorder to fetch the record uniquley..
You can also use INNER JOIN instead of Views in your scenario...
Cheers,
Abdul Hakim
Mark all useful answers..
08-12-2006 11:44 AM
Hi All,
Thanks for your responses.
But I still have an issue here. Let me answere your queries.. :
1. Select single is not being used in a loop.
2. The actual query is as follows :
select single * from ZMM_PENANG_1B
into general_info
where tknum = shipping.
Now the DATABASE VIEW contains multiple entries against one "shipping" number. Hence when the select query runs it picks up the first entry from the view.
The problem is , that the query and the code have been running nicelly for the last 2 years in our production system. But suddenly for the last 2 days it is hanging up, that also NOT ALWAYS. Issue happens Intermittentlly. As i said earlier the View contains 8 million records.
Hope this info helps.
Please advice.
07-18-2007 9:30 PM
Hi,
Check with your Basis administrator to see if there are any automated update stats job running on the base tables that are part of the view.
We faced somewhat similar condition when the (DB13) daily automated update stats job in SAP (4.7 x110) ran and it collected stats on the table and its "primary index" only. This led the (Oracle) cost based optimizer to choose a different path when executing the same query and resulted in longer run-times.
Reward points if helpful.
Yuva.