I'm running a COBOL program that runs on HPUX PA-RISC and is reading from an Oracle 10g database. One of the SELECT statements appears to be running inefficiently and I hope a hint will help. I tried using Oracle's SQL*Plus to see how the SELECT performs with and without the hint, and the results are pretty convincing that the hint makes a significant difference. I added the hint as follows:

EXEC SQL

DECLARE EQ0201_CONTRACT_SPAN CURSOR FOR

SELECT /* INDEX (CONTRACT_SPAN (CONTRACT_NBR)) */

   IMAGE_RECNBR

   , CONTRACT_NBR

   , BENEFIT_PKG

   , BILL_TO

   , CONTRACTYPE

   , DIVISION_NBR

   , TIER

   , TRANSCODE

   , VOID

   , YMDEFF

   , YMDELIG

   , YMDEND

   , YMDTRANS

FROM CONTRACT_SPAN

WHERE CONTRACT_NBR = :S-CONTRACT-NBR

   AND IMAGE_RECNBR > :S-IMAGE-RECNBR

ORDER BY YMDEFF , IMAGE_RECNBR

END-EXEC

I'm not getting any performance improvement from this approach. With hints you never get syntax errrors, the hint is simply ignored. Does anyone see anything wrong with my code? Has anyone been able to add a hint to a COBOL program and see a significant performance improvement? If so, please share your code with us.