Re: Using hints in Pro*C

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 22 Oct 1998 20:58:25 GMT
Message-ID: <36399b26.31890025@192.86.155.100>

A copy of this was sent to L.B.Vanting_at_t-online.de (if that email address didn't require changing) On 20 Oct 1998 21:48:47 GMT, you wrote:

>I'm trying to use hints in a Pro*C cursor but when I examine the sql trace
>file, it seems that the hint (probably seen as a comment) is removed by
>Pro*C.
>
>Has anyone experienced this?
>Any input would be appreciated.
>
>I'm thinking of some work-arounds, but I'm not keen on rewriting the whole
>Pro*C program, i.e. in OCI.
>
>Thanks in advance!
>
>Regards
>Lars Bo Vanting, COREBIT TPI AG, Switzerland
>LarsBoV_at_corebit.dk
>
>

Can you post an example please (and specify versions, platforms, etc)... I just tried the following pro*c and got the trace file with the hints carefully preserved.... It shows that pro*c will rewrite your query, the hints are rewritten ( the --+ is rewritten as /*+ and such)

I used proc 8.0.3 on solaris...

void process() {

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR dummy[25];
EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE C1 CURSOR FOR
    SELECT --+ full

         *
      FROM DUAL where 1 = 3;
   

EXEC SQL DECLARE C2 CURSOR FOR
    SELECT /*+ full */ *
      FROM DUAL where 1 = 2;

    EXEC SQL ALTER SESSION SET SQL_TRACE=TRUE;     EXEC SQL OPEN C1;     for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C1 INTO :dummy;

    }

    EXEC SQL OPEN C2;     for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C2 INTO :dummy;

    }
    printf( "Ok\n" );
}

select /*+ full +*/ *
from
 DUAL where 1=3

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 4848 (TKYTE)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   FILTER
      0    TABLE ACCESS (FULL) OF 'DUAL'

********************************************************************************

select /*+ full +*/ *
from
 DUAL where 1=2

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           0

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 4848 (TKYTE)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   FILTER
      0    TABLE ACCESS (FULL) OF 'DUAL'

 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 22 1998 - 15:58:25 CDT