Skip to Content
author's profile photo
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Posted on May 05, 2011 at 10: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

    Add comment
    10|10000 characters needed characters exceeded

  • Posted on May 05, 2011 at 09: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

    Add comment
    10|10000 characters needed characters exceeded

    • 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