topic Re: Parallel Execution Hints in Application Development and Automation Discussions

Parallel Execution Hints

Former Member — Wed, 22 Sep 2010 17:52:54 GMT

Hi everyone,

I used hints in my SQL statement but when I run it and check in ST05, the hints do not seemed to work because there is no PX in the execution plan.

Here is my code:


  SELECT mkpf~mandt
         mkpf~mblnr
         mkpf~mjahr
         mseg~zeile
         mkpf~budat
         mseg~bwart
         mseg~matnr
         mseg~werks
         mseg~lgort
         mseg~shkzg
         mseg~bwtar
         mseg~menge
         mseg~meins
         mseg~smbln
         mseg~kostl
         mseg~aufnr
         mseg~bukrs
         mseg~prctr
    FROM mkpf
   INNER JOIN mseg
      ON mseg~mandt EQ mkpf~mandt
     AND mseg~mblnr EQ mkpf~mblnr
     AND mseg~mjahr EQ mkpf~mjahr
    INTO TABLE ts_mseg1
   WHERE mkpf~budat IN s_budatf
     AND mseg~werks IN s_werks
    %_HINTS
    ORACLE 'PARALLEL(MKPF,5)'.

Hope you can help me on this. Thanks!

Re: Parallel Execution Hints

HermannGahm — Wed, 22 Sep 2010 18:29:18 GMT

Hi,

parallel query can only be used with multiblock operations or partitions.

In your case you"ll still see an index range scan on mkpf which can not do

multiblock reads (can not be parallelized).

In order to get a parallel executed SQL execution you have to force a

full table scan as well.

...
WHERE mkpf~budat IN s_budatf
     AND mseg~werks IN s_werks
    %_HINTS
    ORACLE 'FULL(T_00) PARALLEL(T_00,5)'.

Note:

Parallelizing this query does probably not make sense since you probably

access only a relatively small portion out of the table i guess. In a multi

user scenario database side parallelization does not make sense as well.

Kind regards,

Hermann

Edited by: Hermann Gahm on Sep 22, 2010 8:34 PM

Re: Parallel Execution Hints

Former Member — Wed, 22 Sep 2010 19:48:52 GMT

Hi Hermann,

Is it possible to use the parallel hint if the SELECT statement has inner joins (taken from SAP Note 651060)?

Re: Parallel Execution Hints

HermannGahm — Wed, 22 Sep 2010 19:51:24 GMT

sure, as i showed in my previous post.

Instead of the table names you have to use the aliases (T_00, T_01, ...)

Kind regards,

Hermann