SELECT with hint and Client Specified, 7.40

[Former Member]

a SELECT in ABAP 7.40:

I need to add an Oracle hint, and also"CLIENT SPECIFIED";

yet, when adding "CLIENT SPECIFIED" then the hint is gone (is not run on the database anymore.

Basically I need to run a full-table-scan select on a database table with more than 5.000.000.000 datasets, and field MANDT has one value. I need the Oracle-hint to parallelize this. Plus I need to get rid of the WHERE MANDT EQ 'NNN' addition, because there is only one MANDT.

Has anyone done this? (Will it be possible in 7.50?)

Going of a tangent, I would like to add a hint to parallelize a DELETE DB-Table command, but there seem to be no hints allowed there in ABAP (Will 7.50 allow it?)

AHGA

[horst_keller]

Regarding the last question, in DELETE the hint is placed behind WHERE:

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abenosql_db_hints....

Since many releases.

Docu with apparent translation error, correction triggered ...

[horst_keller]

"when adding "CLIENT SPECIFIED" then the hint is gone (is not run on the database anymore."

What do you mean with that? Is the hint missing in the ST05 trace?

[Former Member]

It is gone(missing) in the Execution-Plan of the select, as shown in st04.

I would not be able to use st05, because the select has not finished. I'm using st04 because this shows the execution plan already while the select is still running. I am calculating the speed on datasets-processed-per-second, and thus estimating how long it would take to finish (knowing there are 5,3 billion datasets). Usually whatever solution I envisage, has to be finished within Monday-Saturday, since weekends are candidates for reboots of the system.

TIA

[Former Member]

FWIW, here's how I get the execution plan in st04:

http://blaupause.dsag.de/pfadfinder-fuer-das-select

[Former Member]

example:
>>
PARAMETERS P_DB_PAR type n length 2 default '02'.
perform test1.
"---------------------------------------------
form test1.
constants lc_size type i value 1000000.
types: begin of lt_1,
mandt type konp-MANDT,
knumh type konp-KNUMH,
loevm_ko type konp-LOEVM_KO,
end of lt_1.
data li_1 type standard table of lt_1 with empty key
initial size lc_size.
data lw_cursor type cursor.
data lw_c80 type c length 80.
"---

".......................................................
lw_c80 = 'FULL(K) PARALLEL(' && P_DB_PAR && ')'.
OPEN CURSOR with hold lw_cursor for SELECT
mandt
knumh
loevm_ko
FROM konp as k ""key:mandt;knumh;Kopos;
CLIENT SPECIFIED "Where-Bedingung verhindern (mandt)
%_hints oracle lw_c80.
".......................................................
write / lw_c80.
DO.
FETCH NEXT CURSOR lw_cursor into table li_1 package size lc_size.
IF sy-SUBRC <> 0.
EXIT. "enddo
ENDIF.
write /'test only'.
EXIT.
ENDDO.
CLOSE CURSOR lw_cursor.
write /'done'.
endform. " test1.
"---------------------------------------------
<<

gives execution plan (display as text in st04)
>>
SELECT "MANDT","KNUMH","LOEVM_KO" FROM "KONP" "K"

Plan hash value: 4129392858

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2668K(100)| |
| 1 | TABLE ACCESS FULL| KONP | 741M| 11G| 2668K (2)| 00:01:45 |
--------------------------------------------------------------------------
<<


whereas, changing the above source code to:
>>
PARAMETERS P_DB_PAR type n length 2 default '03'.
perform test1.
"---------------------------------------------
form test1.
constants lc_size type i value 1000000.
types: begin of lt_1,
mandt type konp-MANDT,
knumh type konp-KNUMH,
loevm_ko type konp-LOEVM_KO,
end of lt_1.
data li_1 type standard table of lt_1 with empty key
initial size lc_size.
data lw_cursor type cursor.
data lw_c80 type c length 80.
"---

".......................................................
lw_c80 = 'FULL(K) PARALLEL(' && P_DB_PAR && ')'.
OPEN CURSOR with hold lw_cursor for SELECT
mandt
knumh
loevm_ko
FROM konp as k ""key:mandt;knumh;Kopos;
%_hints oracle lw_c80.
".......................................................
write / lw_c80.
DO.
FETCH NEXT CURSOR lw_cursor into table li_1 package size lc_size.
IF sy-SUBRC <> 0.
EXIT. "enddo
ENDIF.
write /'test only'.
EXIT.
ENDDO.
CLOSE CURSOR lw_cursor.
write /'done'.
endform. " test1.
<<


gives that execution plan in st04:
>>
SELECT /*+ FULL(K) PARALLEL(04) */ "MANDT","KNUMH","LOEVM_KO" FROM
"KONP" "K" WHERE "MANDT"=:A0

Plan hash value: 1842418084

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 739K(100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 741M| 11G| 739K (2)| 00:00:29 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 741M| 11G| 739K (2)| 00:00:29 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| KONP | 741M| 11G| 739K (2)| 00:00:29 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
<<

So it seems that adding "client specified" prevents the Hint to be
transmitted to the database
?

AHGA

[horst_keller]

Just a hunch:

Are the database tables buffered?

A SELECT statement with CLIENT SEPCIFIED and without a condition for the client in the WHERE condition circumvents the buffer.

[Former Member]

thanks for pointing out.
though, in this case no.
(table KONP is not SAP-Table-buffer buffered here)

but, I could narrow it down:

Given that the hint is behind the WHERE, I guessed,
that "ABAP" passes on the hint when it passes on the WHERE-clause.
Then, when there is no WHERE clause, what does "ABAP" do?
(no longer passes the hint ?)

Reasoning:
adding "client specified" also meant there is no longer a WHERE condition
(as per example source code from april 6th)

This is also the case with this test-coding:

>>

START-OF-SELECTION.

perform test_hint_without_where.

form test_hint_without_where.
data li_kunnr type table of kunnr with empty key.

CLEAR li_kunnr.
SELECT kunnr "nur f Test
into table li_kunnr
from kna1 "key:mandt;kunnr;
%_hints ORACLE 'INDEX("KNA1" "KNA1~0")'.
"^pseudo-hint, nur f Test, ob Hint übertragen wird
write: / |#{ lines( li_kunnr ) number = user }|.

CLEAR li_kunnr.
SELECT kunnr "nur f Test
into table li_kunnr
from kna1 "key:mandt;kunnr;
CLIENT SPECIFIED "f ohne: where mandt eq sy-mandt
%_hints ORACLE 'INDEX("KNA1" "KNA1~0")'.
"^pseudo-hint, nur f Test, ob Hint übertragen wird
write: / |#{ lines( li_kunnr ) number = user }|.

CLEAR li_kunnr.
SELECT kunnr "nur f Test
into table li_kunnr
from kna1 "key:mandt;kunnr;
CLIENT SPECIFIED "f ohne: where mandt eq sy-mandt
WHERE KUNNR like '0002%'
%_hints ORACLE 'INDEX("KNA1" "KNA1~0")'.
"^pseudo-hint, nur f Test, ob Hint übertragen wird
write: / |#{ lines( li_kunnr ) number = user }|.

endform. " test_hint_without_where.

<<

(st04, display execution plan as text, excerpts from there)

The first select gets this execution plan:
>>
SELECT /*+ INDEX("KNA1" "KNA1~0") */ "KUNNR" FROM "KNA1" WHERE "MANDT"=:A0
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1646 (100)| |
|* 1 | INDEX RANGE SCAN| KNA1~0 | 1683K| 24M| 1646 (1)| 00:00:01 |
---------------------------------------------------------------------------
<<
we have a WHERE clause - hint is being transmitted.



The second select gets this:
>>
SELECT "KUNNR" FROM "KNA1"
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 234 (100)| |
| 1 | INDEX FAST FULL SCAN| KNA1~0 | 1683K| 17M| 234 (2)| 00:00:01 |
-------------------------------------------------------------------------------
<<
no WHERE clause - hint is gone (not being transmitted)
((FWIW, we switched from index-range to index-fast-full scan on the DB,
so we might get different performance when doing these things on big data,
say, 300 to 6000 million datasets and so on - which is what this is all about - checking DB options))


The third select gets this:
>>
SELECT /*+ INDEX("KNA1" "KNA1~0") */ "KUNNR" FROM "KNA1" WHERE "KUNNR" LIKE :A0
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1648 (100)| |
|* 1 | INDEX FULL SCAN | KNA1~0 | 84195 | 904K| 1648 (1)| 00:00:01 |
---------------------------------------------------------------------------
<<
we have a WHERE clause - hint is being transmitted.
Yet, because we also have the "client specified",
then it seems to me that the real reason is
that the hint is gone when there is no where.
(rather than blaming it on "client specified";
"client specified" being just the reason for not having a WHERE - in the second select)

HTH

[horst_keller]

Your drill down in the comment above was helpful. Now I see it too.

"New Open SQL
SELECT *
       FROM scarr
       WHERE carrid = 'LH'
       %_HINTS HDB 'NO_USE_OLAP_PLAN'
       INTO TABLE @DATA(result1).
SELECT *
       FROM scarr
       %_HINTS HDB 'NO_USE_OLAP_PLAN'
       INTO TABLE @DATA(result2).
SELECT *
       FROM scarr CLIENT SPECIFIED
       %_HINTS HDB 'NO_USE_OLAP_PLAN'
       INTO TABLE @DATA(result3).

"Old Open SQL
SELECT *
       INTO TABLE result1
       FROM scarr
       WHERE carrid = 'LH'
       %_HINTS HDB 'NO_USE_OLAP_PLAN'.
SELECT *
       INTO TABLE result2
       FROM scarr
       %_HINTS HDB 'NO_USE_OLAP_PLAN'.
SELECT *
       INTO TABLE result3
       FROM scarr CLIENT SPECIFIED
       %_HINTS HDB 'NO_USE_OLAP_PLAN'.

STO05:

SELECT WHERE "MANDT" = '000' AND "CARRID" = N'LH' WITH HINT(NO_USE_OLAP_PLAN)

SELECT WHERE "MANDT" = '000' WITH HINT(NO_USE_OLAP_PLAN)

SELECT

SELECT WHERE "MANDT" = '000' AND "CARRID" = N'LH' WITH HINT(NO_USE_OLAP_PLAN)

SELECT WHERE "MANDT" = '000' WITH HINT(NO_USE_OLAP_PLAN)

SELECT

Indeed, without WHERE condition the hint is gone, in old Open SQL as well as with new Open SQL.

Now it's time to ask the Open SQL team about that. I'll be back ...

[horst_keller]

Have to backpedal.

A closer look at St05 shows that for all above examples the hint is transported to the database.