Re: SQL query

"Glenn" <glen_mackay_at_yahoo.com> wrote in message news:1142614283.205768.195460_at_u72g2000cwu.googlegroups.com...
: 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
:

1. it would be helpful to include the table and index names in your plan
2. you don't need to select from dual to get sysdate:

create or replace function GET_CURRENT_DATE return date
is
begin

     return sysdate;
end;

see if that makes a difference (i'm not sure if Oracle9i will do a SELECT FROM DUAL behind the scenes, but you certainly won't be explictly telling it to do an unnecessary select

++ mcs Received on Fri Mar 17 2006 - 18:21:58 CET