Re: SQL query

Glenn wrote:
> Hi All,
>
> I am using a function (GET_CURRENT_DATE) to get the sysdate back from
> the database. I do this because the other application is in SYBASE
> which doesn't recognize sysdate.
>
> The function is basically this statement.
>
> Select sysdate INTO tmpVar from dual;
> RETURN tmpVar;
>
> Anyway I get a MERGE_CARTESIAN JOIN when I try this query.
>
> Select firstname, surname, p.fin FROM PARTICIPANTS P,
> LICENCE_PARTICIPANTS LP,
> GEAR_TAG_ISSUANCES GTI, LICENCE_AREAS LA, AREAS A WHERE
> p.fin = lp.fin and
> gti.licence_id = lp.licence_id and
> lp.licence_id = la.licence_id and
> la.area_id = a.area_id and a.area = '33' and
> 33010251 between gti.tag_start and
> gti.tag_end and GET_CURRENT_YEAR = gti.LICENCE_YEAR
> and GET_CURRENT_DATE between lp.START_DATE
> and lp.end_date ;
>
> Operation
>
> SELECT STATEMENT Optimizer Mode=CHOOSE
> TABLE ACCESS BY INDEX ROWID
> NESTED LOOPS
> NESTED LOOPS
> HASH JOIN
> MERGE JOIN CARTESIAN
> TABLE ACCESS BY INDEX ROWID
> INDEX RANGE SCAN
> BUFFER SORT
> TABLE ACCESS FULL
> INDEX FAST FULL SCAN
> TABLE ACCESS BY INDEX ROWID
> INDEX UNIQUE SCAN
> INDEX RANGE SCAN
>
>
> But when I use sysdate in the last line, then no more CARTESIAN.
>
> Select firstname, surname, p.fin FROM PARTICIPANTS P,
> LICENCE_PARTICIPANTS LP,
> GEAR_TAG_ISSUANCES GTI, LICENCE_AREAS LA, AREAS A WHERE
> p.fin = lp.fin and
> gti.licence_id = lp.licence_id and
> lp.licence_id = la.licence_id and
> la.area_id = a.area_id and a.area = '33' and
> 33010251 between gti.tag_start and
> gti.tag_end and GET_CURRENT_YEAR = gti.LICENCE_YEAR
> and sysdate between lp.START_DATE
> and lp.end_date ;
>
> Operation
>
> SELECT STATEMENT Optimizer Mode=CHOOSE
> NESTED LOOPS
> HASH JOIN
> HASH JOIN
> HASH JOIN
> TABLE ACCESS BY INDEX ROWID
> INDEX RANGE SCAN
> INDEX FAST FULL SCAN
> TABLE ACCESS FULL
> TABLE ACCESS FULL
> TABLE ACCESS BY INDEX ROWID
> INDEX UNIQUE SCAN
>
>
> Anybody know why this may happen? I've used the function successfully
> with other SQL statements and didn't have any problems. I'm using
> Oracle 9.2.0.7.0.
>
> Thanks for any help.
>
> Glenn
>
Do not use functions in your where clause, Oracle is terrible at this, [Quoted] just check your cpu when this query is running. Store the values in a context variable and retrieve it with sys_context, performance will be up to par. Received on Fri Mar 17 2006 - 22:47:14 CET