In order to avoid nested select statements we can go
for "FOR ALL ENTRIES".First fetch values from database
table to internal table and only for the values in the
internal table we can fetch values from other database
table based on conditions in the where clause.
it is mandetory that we have to declare allthe primary keys
in either select Stmt or in where condition. orelse
duplicate records will not get selected.
All abap programers and most of the dba's that support abap
programmers are familiar with the abap clause "for all
entries". Most of the web pages I visited recently, discuss
3 major drawbacks of the "for all entries" clause:
1. duplicate rows are automatically removed
2. if the itab used in the clause is empty , all the rows in
the source table will be selected .
3. performance degradation when using the clause on big tables.
In this post I'd like to shed some light on the third issue.
Specifically i'll discuss the use of the "for all entries"
clause as a means to join tables in the abap code instead of
Say for example you have the following abap code:
Select * from mara
For all entries in itab
Where matnr = itab-matnr.
If the actual source of the material list (represented here
by itab) is actually another database table, like:
select matnr from mseg
into corresponding fields of table itab
Then you could have used one sql statement that joins both
From mara t1, mseg t2
Where t1.matnr = t2.matnr
So what are the drawbacks of using the "for all entires"
instead of a join ?
At run time , in order to fulfill the "for all entries "
request, the abap engine will generate several sql
statements (for detailed information on this refer to note
48230). Regardless of which method the engine uses (union
all, "or" or "in" predicates) If the itab is bigger then a
few records, the abap engine will break the itab into parts,
and rerun an sql statement several times in a loop. This
rerun of the same sql statement , each time with different
host values, is a source of resource waste because it may
lead to re-reading of data pages.
returing to the above example , lets say that our itab
contains 500 records and that the abap engine will be forced
to run the following sql statement 50 times with a list of
10 values each time.
Select * from mara
Where matnr in ( ...)
Db2 will be able to perform this sql statement cheaply all
50 times, using one of sap standard indexes that contain the
matnr column. But in actuality, if you consider the wider
picture (all 50 executions of the statement), you will see
that some of the data pages, especially the root and
middle-tire index pages have been re-read each execution.
Even though db2 has mechanisms like buffer pools and
sequential detection to try to minimize the i/o cost of such
cases, those mechanisms can only minimize the actual i/o
operations , not the cpu cost of re-reading them once they
are in memory. Had you coded the join, db2 would have known
that you actually need 500 rows from mara, it would have
been able to use other access methods, and potentially
consume less getpages i/o and cpu.
In other words , when you use the "for all entries " clause
instead of coding a join , you are depriving the database of
important information needed to select the best access path
for your application. Moreover, you are depriving your DBA
of the same vital information. When the DBA monitors & tunes
the system, he (or she) is less likely to recognize this
kind of resource waste. The DBA will see a simple statement
that uses an index , he is less likely to realize that this
statement is executed in a loop unnecessarily.
In conclusion I suggest to "think twice" before using the
"for all entries" clause and to evaluate the use of database
views as a means to:
a. simplify sql
b. simplify abap code
c. get around open sql limitations.
FOR ALL ENTRIES IN IS NOTHING BUT INNER JOINS, INNER JOINS IS EXTRACTING A DATA FROM TWO DEPEND TABLES( PRIMARY AND FOREIGN) BUT IN FOR ALL ENTRIES IN EXTRACTING FROM MORE THAN TWO TABLES . MAIN ADVANTAGE OF FOR ALL ENTRIES IN IS IMPROVING PERFORMANCE OF SYSTEM (HOW)..
WE ARE SUPPURATION THE TABLES, AND USING THE INNER TABLE WITH REFERENCE OF FIELD STRINGS TYPE...AND CREATING A FINAL TABLE, IT CONTAINING A TOTAL FIELDS WHICH ARE YOU SELECTED IN
DEPENDENT TABLES ...
For ALL Entries is used to join two different tables. By
tables i mean the internal tables. For All Entries has an
internal where condition in it. which makes the selection
process much faster. But still it is always better to use
inner joins when you have to join two database tables.
In a report prg there is an event called :
at SELECTION-SCREEN on VALUE-REQUEST FOR <fieldname>.
pls tell me that when i am using a module pool prg how do I
call the above event.
In other words what is the module pool equivalent for the
above event which is used in a report prg.
Hope I am able explain my query.
For the past 1 year, I have been trying for job in SAP as a
fresher, but no calls,no interviews.I know showing fake
experience is unethical, but i dont have any
alternative.After showing fake experience now iam getting
calls,but still i feel guilty.Friends i have some questions.
1. If suppose i tell the truth in interview that i have
shown fake experience,what would be the interviewer's
reaction? Will accepting the fake go negative against me?
2.Whether i should accept the fake in technical round
itself? (or) Maintain the fake,prove myself strong in
technical round &then accept the fake in HR round?
3.Some of my friends(who have shown fake and well settled
in MNC's ) are telling that during interview u should never
accept as fake i.e., u should act confidently as a real
experienced guy.Even if the interviewer come to know that
u r a fake guy, but seeing ur confidence level ,he/she will
select u ? Is it true?
Friends, particularly interviewer's , HR's please give me
ur valuabe solutions.Other's suggestions are also welcome.