View and index

[Former Member]

Hi all, I have a view that I am reading from, but would like to know if I can create and index over that view and how?

Thanks

Mike

[Former Member · Accepted solution]

No, you cannot. Views do not store any data.

[Former Member · Accepted solution]

Thanks, I have tried to do the select seperating the two tables to get the result I need but it is still very slow. I have a view of MKPF/MSEG but when I run my report with the view or separately it is still slow on the select. Do you have any hints/tricks that might help?

Thanks

Mike

[markus_doehr2 · Accepted solution]

Did you try to use ST05 for an SQL trace and see which statement is causing that much time? Do you select qualified? You're doing "select *"?

Markus

[Former Member · Accepted solution]

Yes I have looked at ST05 and SE30 and the biggest data base hit is this view between MKPF/MSEG. And I am doing a 'select *' as we use all of the fields in the program. I am not sure if there is a faster way to get what we need. Thanks for the input.

Mike

[Former Member · Accepted solution]

To optimize a view you have to consider ...

1. Join condition fields, which of course the more selective and indexed the better.

2. The where condition used to select from the view.

Take a look at the fields used in the where statement and look at which table the field applies to. For example:

select * from view1 where field1 = x, field2 = y, field3 = x

-- view 1 composes of tables 1 and 2.

field 1 is in both tables.

field 2 is in table 1 only.

field 3 is in table 2 only.

Then, on table 1 you would want an index containing fields 1 and 2. On table 2 you would want index containing fields 1 and 3.