topic Re: Calling a index using %_HINTS ORACLE in Application Development and Automation Discussions

Calling a index using %_HINTS ORACLE

Former Member — Thu, 29 May 2008 18:14:02 GMT

Hi gurus,

I've created an index , but my problem is the right way to call it. i've allready seen several ways here in the forums and dont know witch one is right.

My index has the Z04 name and i'm calling this way :


SELECT t1~bukrs t1~belnr t1~dmbtr
        APPENDING CORRESPONDING FIELDS OF TABLE tab_dados
            FROM bsak AS t1 INNER JOIN bkpf AS t2
            ON t1~bukrs = t2~bukrs AND
               t1~belnr = t2~belnr AND
               t1~gjahr = t2~gjahr AND
               t1~blart = t2~blart
            WHERE t1~bukrs IN r_bukrs AND
                  t1~zuonr IN r_economica AND
                  t1~gjahr = ano  AND
                ( t1~budat IN so_date OR t1~augbl IN so_date )  and
                  T1~BLART in R_TIPOS_DOC AND
                  t1~hkont IN r_contas AND
                  t1~xref1 LIKE p_fundo AND
                  t1~xref3 IN r_fictr AND
                  t2~stblg = ''
                  AND t1~monat IN so_perio
                  %_HINTS ORACLE 'INDEX(BSAK BSAK~Z04)'..

the compiler didn't gave any error but just in case...

Is this right??

thanks in advance,

Regards,

João Martins

Re: Calling a index using %_HINTS ORACLE

Former Member — Fri, 30 May 2008 02:24:22 GMT

hi,

Since you are not getting error, i think it is right. but as far as i know the way to mention is:

%_HINTS ORACLE 'INDEX("HRP1001" "HRP1001~Z05")'.

jus check if the optimizer is picking the Z04.

regards,

madhu

Re: Calling a index using %_HINTS ORACLE

Former Member — Fri, 30 May 2008 06:25:57 GMT

if you use a hint then you should use the SQL trace to see whether the hint works.

Indexhints are easily tested, the explain of the SQL shows you the used index.

Test also wiithout hint.

about SQL Trace, see here last exection

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

> the compiler didn't gave any error but just in case...

that tells you nothing, unfortunately

Siegfried