Issue with VIEW and SELECT statement

[Former Member]

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

[Former Member]

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

[Former Member]

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

[abdul_hakim]

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

[Former Member]

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.

[Former Member]

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.