Index in view

[FredericGirod]

Hi,

I have a little question, what appends with index when you make select on a view ? For example: CAUFV is a view on two tables AFKO AUFK, the two tables have different index.

Rgd

Frédéric

[Former Member]

Hello,

A veiw is just a skeleton and connects tables def. So in CAUFV there are two tables AFKO & AUFK and the data resides in them only. View is just another way of looking the data in these tables.

[manuel_bassani]

Hi,

using a view is like making a SELECT...JOIN...

so make a select from the view CAUFV is like to make a select like

SELECT ...
FROM AFKO JOIN AUFK ON AKKO-MANDT = AUFK-MANDT
                   AND AFKO-AUFNR = AUFK-AUFNR

(in terms of indexes). So depending on the where clause, the optimizer will choose the primary key to make the join and a secondary index (if it exists) for one of the tables AKKO/AFKO

Regards, Manuel

[FredericGirod]

My real question is, What about optimisation with the view ?

is it more complex ? or does SAP manage that very well ?

Fred

[Former Member]

Hello,

you can say that the View is a projection to both tables (AUFK and AFKO) with one SELECT-Statement. Example: If you need informations from both of the tables and you dont use the view, you have to program like:

  select * from aufk into wa_aufk...
        select * from afko into wa_afko 
                where aufnr eq wa_afko-aufnr ...
        endselect
  endselect

But with help of the view you have only one select:

  select * from CAUFV ....
  endselect.

The view is not a new "table" it is only a projection at runtime to this both tables.

Maybe this helps you also.

regards

Thorsten

[Former Member]

HI Frederic

View is only a window to view to the data from the database tables. data will not reside in the view, view is only used for customizing the data retrieval.

when selecting data in the view it fetches data from database table where the index is present.

if view as more than one tables the effect of index will be the same as index in the join statement.

regards

kishore

[Former Member]

SAP itself manages the view and we don't have to do anything. As this is taken care from Database side you don't have to worry it will always optimise.

[FredericGirod]

Thanks everyone for reply, I know what is a view, but, my problem is optimization.

Are you sure that SAP convert exactly a view by an inner join ?

Are you sure there is no impact on the index choice when SAP convert the ABAP code to the BD code ?

[manuel_bassani]

Hi Frédéric,

SAP converts Views in Select...join. (like native SQL does)

About the optimization problem: it depends from many variables (like existing secondary indexes, DB statistics etc..). To be sure about the index that will be choosed you should run SQL trace (ST05)

Regards, Manuel