Documentation on Oracle Hints Extension ^

[Former Member]

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

[Former Member]

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

[retired_member]

DB hints are DB specific.

You have to look up Oracle's DB documentation.

[Sandra_Rossi]

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.

[pokrakam]

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.