Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Related questions

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 a 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 a 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.