Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

For all entries: %_HINTS are not used for the statement ?

Former Member
0 Kudos
2,065

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

1 ACCEPTED SOLUTION

raymond_giuseppi
Active Contributor
0 Kudos
305

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

4 REPLIES 4

former_member192616
Active Contributor
0 Kudos
305

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

0 Kudos
305

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

0 Kudos
305

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

raymond_giuseppi
Active Contributor
0 Kudos
306

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