Thanks for the question, Snehasish.
Asked: October 02, 2016 - 5:59 am UTC
Last updated: October 02, 2016 - 8:36 am UTC
Version: 11gR2
Viewed 1000+ times
You Asked
Hi Team,
Good day.
I have the below query, it_source_copy is populated in run time and the code is run once the number of value reaches 1000.
SELECT crm_guid
crm_usstat
crm_stsma
ch_on
ch_at
bbp_flag
FROM /bic/agdb_stat00
INTO TABLE it_gdb_stat
FOR ALL ENTRIES IN it_source_copy
WHERE crm_guid = it_source_copy-crm_ohguid
AND crm_usstat LIKE 'E%'
AND bbp_flag = SPACE
and CRM_STSMA = c_statuspr;
The query is transformed to
select .... from /bic/agdb_stat00 where crm_guid in('AB23232DEE4454') and crm_usstat like 'E%' and bbp_flag = '' and crm_stsma = 'ABCD';
I am not sure why the code is run record by record (crm_guid being run for each value).
Kindly let us know if this is default Oracle behavior, also how to make it run as a batch.
also kindly let me know how oracle internally executes bulk queries, is it per record or as a batch. Kindly send me any link where i can read more on this topic.
Regards,
Snehasish
and Connor said...
That looks like SAP ABAP to me. Yes ?
If so, then its not *Oracle* that is mapping the query into the record-by-record SQL statements you see - it is the SAP engine.
You'd need to speak to the SAP support people about why they are generating such an inefficient mapping, because you're right, it should be transformed into a simple join.
But its *not* Oracle that is doing that.
Is this answer out of date? If it is, please let us know via a Comment