Skip to Content
0

Documentation on Oracle Hints Extension ^

May 12, 2017 at 05:12 PM

119

avatar image

Hello everyone,

I'm trying to find some documentation/discussions/information on a particular Oracle Hints extension. Here's my SELECT statement:

SELECT a~vkont FROM fkkvkp AS a
                    INNER JOIN fkkvk  AS b
                       ON a~vkont = b~vkont
        INTO TABLE t_fkkvkp
         FOR ALL ENTRIES IN r_gpart
       WHERE a~gpart = r_gpart-partner
         AND b~vktyp IN r_vktyp[]
       %_HINTS ORACLE 'INDEX(FKKVKP "FKKVKP~1" "FKKVKP^1")'.

What I fail to understand is the very last part:

"FKKVKP^1".

Couldn't find any search results or F1 help related to the ^<index_name>. Your thoughts?

- pk

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Mike Pokraka May 12, 2017 at 09:46 PM
1

The hint tells the DB which index to use, referring to it by the name assigned in the database. Please have a look in SE11 under indexes - can't remember where to click off the top of my head, but somewhere you can get the DB index names.

The three-character SAP index name in SE11 is generic, and in the background a DB-specific name is generated by SAP, which happens to be the table name and a number for Oracle. So table MYTAB indexes ABC and DEF will be called MYTAB~1 and MYTAB~2 in Oracle.

I'm reasonably sure they are just covering the scenario that an OS/DB combination might use ^1 ^2 ^3... instead of the more common ~1 ~2.... suffixes. Oracle doesn't care whether an index specified in a hint doesn't exist, it just gets ignored. So it's no harm to put an alternate name in there.

An less likely reason might be that FKKVKP~1 and FKKVKP^1 correspond to two different indexes. Again, SE11 will tell you.

Share
10 |10000 characters needed characters left characters exceeded
Horst Keller
May 12, 2017 at 05:56 PM
2

DB hints are DB specific.

You have to look up Oracle's DB documentation.

Share
10 |10000 characters needed characters left characters exceeded
Sandra Rossi May 12, 2017 at 06:45 PM
1

Here are quotes from 2 documentations (don't know whether it can help; my opinion is that the notation FKKVKP^1 is useless if it doesn't exist in the database; check SE14 to see the actual database indexes via button "storage parameters"):

SAP note 772497 - FAQ: Oracle Hints states:

  • INDEX(<table> <index> [<index2> ... <indexN>])
  • Using one of the indexes <index>, <index2>, ... , <indexN> to access the <table> table
  • Usually only ONE index is specified so that the other indexes are omitted. However, if the exact notation of the index is unknown in R/3 (for example, MARA~0 or MARA^0 or MARA_____0), all notations must be specified in sequence to ensure that the name of the actual index is included.

.

Doc of Oracle hints (10.2 here) : http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#i35922:

  • If the INDEX hint specifies a list of available indexes, then the optimizer considers the cost of a scan on each index in the list and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes from this list and merge the results, if such an access path has the lowest cost.
Share
10 |10000 characters needed characters left characters exceeded