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: 

Use of Statement

0 Kudos

Can anyone please explain me the following statement role in the select statement

%_HINTS ORACLE '&max_blocking_factor 300& &max_in_blocking_factor 300&'.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

That HINTS parameter is an indication to the underlying Oracle database on how SELECT with FOR ALL ENTRIES is to be distributed over several SELECT statements at database level

max_blocking_factor 300 will convert the SELECT.. FOR ALL into multiple SELECTs at database level with OR operator each returning upto 300 records.

max_in_blocking_factor 300 on the other hand will convert SELECT... FOR ALL into multiple SELECTs with IN clause lists similarly

See [Note 881083 - Blocking factors on Oracle-based systems|https://service.sap.com/sap/support/notes/881083]

The above note talks about corresponding system parameter that is defaulted when one doesn't use HINTS explicitly, but the same rules hold true when one explicitly specifies values using SELECT with HINT

Also read [Note 772497 - FAQ: Oracle Hints|https://service.sap.com/sap/support/notes/772497]

4 REPLIES 4

Former Member
0 Kudos

That HINTS parameter is an indication to the underlying Oracle database on how SELECT with FOR ALL ENTRIES is to be distributed over several SELECT statements at database level

max_blocking_factor 300 will convert the SELECT.. FOR ALL into multiple SELECTs at database level with OR operator each returning upto 300 records.

max_in_blocking_factor 300 on the other hand will convert SELECT... FOR ALL into multiple SELECTs with IN clause lists similarly

See [Note 881083 - Blocking factors on Oracle-based systems|https://service.sap.com/sap/support/notes/881083]

The above note talks about corresponding system parameter that is defaulted when one doesn't use HINTS explicitly, but the same rules hold true when one explicitly specifies values using SELECT with HINT

Also read [Note 772497 - FAQ: Oracle Hints|https://service.sap.com/sap/support/notes/772497]

0 Kudos

That HINTS parameter is an indication to the underlying Oracle database on how SELECT with FOR ALL ENTRIES is to be distributed over several SELECT statements at database level

Just small correction. This hint is not for the Oracle Database itself, but for the SAP Database Interface.

0 Kudos

Thanks a lot

former_member194613
Active Contributor
0 Kudos

>into multiple SELECTs at database level with OR operator each returning upto 300 records.

Not returning, how should that work ???

it is the blocksize send to the database, that determines the complexity of the statement! It can return millions of records.