2011 May 05 8:17 AM
Hi Experts,
I use an FAE to retrieve rows from the cluster table CDPOS (residing in physically table CDCLS)
using the hints
%_HINTS ORACLE '&prefer_in_itab_opt 1&&max_in_blocking_factor 500&'
to allow an IN list and specifing the blocksize of the IN list.
The source snippet:
* l_order table with only 1 column objectid
* fill l_order table with the objectid: 25.117 entries)
...
SELECT objectid changenr tabname tabkey fname value_new value_old
INTO TABLE lt_cdpos
FROM cdpos
FOR ALL ENTRIES IN l_order
WHERE objectclas = 'VERKBELEG'
AND objectid = l_order-objectid
AND tabname = 'VBEP'
AND fname = 'KEY'
%_HINTS ORACLE '&prefer_in_itab_opt 1&&max_in_blocking_factor 500&' .
...
So I want to force an IN List to reduce the # of statements send to the database.
But when I lookup the SQL trace (ST05) the statements that were generated are not converted to an IN List
Obj. name Statement |
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021473'
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021474'
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021475'
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021476'
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021477'
CDCLS SELECT WHERE .... "OBJECTCLAS" = 'VERKBELEG' AND "OBJECTID" = '0000021478'
....
Hence, the # of executions ( 25.117) ( = # of lines in l_order table) were not reduced because it uses the single statements for the objectid.
What could be the problem ?
Are the hints ignored? (If I don't use them I got the same result in SQL Trace).
Is it problem with CDPOS is used by a cluster table ? But it uses CDCLS as the real table using an INDEX SCAN
So it should be possible to execute an IN list on the table CDCLS
Here's the EXPLAIN PLAN:
SELECT
"MANDANT" , "OBJECTCLAS" , "OBJECTID" , "CHANGENR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA"
FROM
"CDCLS"
WHERE
"MANDANT" = :A0 AND "OBJECTCLAS" = :A1 AND "OBJECTID" = :A2
ORDER BY
"MANDANT" , "OBJECTCLAS" , "OBJECTID" , "CHANGENR" , "PAGENO"
Execution Plan
SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 1 )
|
--- 2 TABLE ACCESS BY INDEX ROWID CDCLS
| ( Estim. Costs = 1 , Estim. #Rows = 1 )
| Estim. CPU-Costs = 7.219 Estim. IO-Costs = 1
|
------1 INDEX RANGE SCAN CDCLS~0
( Estim. Costs = 1 , Estim. #Rows = 1 )
Search Columns: 3
Estim. CPU-Costs = 5.737 Estim. IO-Costs = 1
Access Predicates
My system is: SAP ECC 6.0 Basis SAPKB70013 on Oracle 10.2.0.4.0
Edited by: YukonKid on May 5, 2011 9:23 AM
2011 May 05 11:15 AM
You read the following note [Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement|https://service.sap.com/sap/support/notes/48230]
Control over FOR ALL ENTRIES hints
But not [Note 129385 - Database hints in Open SQL|https://service.sap.com/sap/support/notes/129385]
Open SQL hints are only evaluated for transparent tables or views on these tables. They are not evaluated for a statement on the R/3 database buffer or for pool or cluster tables.
Regards,
Raymond
2011 May 05 10:56 AM
Hi YukonKid,
> What could be the problem ?
> Are the hints ignored? (If I don't use them I got the same result in SQL Trace).
> Is it problem with CDPOS is used by a cluster table ? But it uses CDCLS as the real table using an INDEX SCAN
> So it should be possible to execute an IN list on the table CDCLS
> Edited by: YukonKid on May 5, 2011 9:23 AM
yes. As far as i know the pool and cluster table access does not allow (or better ignore) FAE hints... . They are
treated in a (one) specific way.
Kind regrds,
Hermann
2011 May 05 1:07 PM
Hi Herman,
so I had to implement my own packaging with , say, PACKAGE SIZE 500,
fill the l_order table with this entries as a RANGE and SELECT on cluster table with an IN Clause
until I'm finished.
This would do, I guess...?
Bye
Yk
2011 May 06 8:10 AM
Hi Yk,
> so I had to implement my own packaging with , say, PACKAGE SIZE 500,
> fill the l_order table with this entries as a RANGE and SELECT on cluster table with an IN Clause
> until I'm finished.
> This would do, I guess...?
i never tried something like this and therefore i'm not sure.
Please give us a feedback if you try it.
Kind regards,
Hermann
2011 May 05 11:15 AM
You read the following note [Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement|https://service.sap.com/sap/support/notes/48230]
Control over FOR ALL ENTRIES hints
But not [Note 129385 - Database hints in Open SQL|https://service.sap.com/sap/support/notes/129385]
Open SQL hints are only evaluated for transparent tables or views on these tables. They are not evaluated for a statement on the R/3 database buffer or for pool or cluster tables.
Regards,
Raymond