topic Re: Issue with VIEW and SELECT statement in Application Development and Automation Discussions

Issue with VIEW and SELECT statement

Former Member — Sat, 12 Aug 2006 09:26:20 GMT

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 !!!!

Re: Issue with VIEW and SELECT statement

Former Member — Sat, 12 Aug 2006 09:29:25 GMT

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

Re: Issue with VIEW and SELECT statement

Former Member — Sat, 12 Aug 2006 09:50:48 GMT

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

Re: Issue with VIEW and SELECT statement

abdul_hakim — Sat, 12 Aug 2006 09:58:47 GMT

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..

Re: Issue with VIEW and SELECT statement

Former Member — Sat, 12 Aug 2006 10:44:03 GMT

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.

Re: Issue with VIEW and SELECT statement

Former Member — Wed, 18 Jul 2007 20:30:42 GMT

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.