topic Re: Database Hints in Application Development and Automation Discussions

Database Hints

Former Member — Wed, 26 Oct 2011 09:46:35 GMT

Hi All,

I am working on performance issue..The requirement is like : The user can enter the table and Index name in the Selection screen.

select query should pick the data from Database table based on the index.....

In where condition i have to incorporate the Oracle hints.((%_HINTS ORACLE )...

Can any one suggest me how to proceed.....

Jayan.

Re: Database Hints

ThomasZloch — Wed, 26 Oct 2011 10:10:54 GMT

You can send both the DB table name and also Oracle hints syntax dynamically (as variable contents) to the Open SQL statement, so this should be possible.

Please read ABAP online documentation for SELECT and search for additional information on "%_HINTS ORACLE", I've found some interesting threads after a quick search.

Thomas

Re: Database Hints

Former Member — Wed, 26 Oct 2011 10:22:28 GMT

You could do something like the following to build your dynamic select statement:

concatenate 'INDEX("' p_table '", "' p_index '")' into lv_hint.

  select single *
    from (p_table)
    into ls_result
    %_HINTS ORACLE lv_hint.

This is a similar thread and was solved:

Re: Database Hints

Former Member — Wed, 26 Oct 2011 16:46:53 GMT

?? a user is supplying a table and index name? Why would you do that? If they don't supply values for the fields named in the chosen index, you're going to read the table end-to-end anyway.... Why would a user even be reading a database table?

Re: Database Hints

ThomasZloch — Wed, 26 Oct 2011 18:34:19 GMT

Good point actually. Maybe he's planning a little "don't trust the CBO" kind of analysis program, to compare runtimes of different access paths?

Thomas