Have you tried an index hint on the first query yet?
Have you tried NVL(cmm_id,101) = 101,
NVL(cust_id,101) = 101
NVL(Friendly_session_id,100) = 100
loc_to_utc(NVL(creation_date,21-jun-2001')) between <your dates>?
-----Original Message-----
From: Vikas Kawatra [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, August 22, 2001 13:12
To: Multiple recipients of list ORACLE-L
Subject: RE: linux & Oracle -- best combination
We're trying to re-write some SQL such that the index on columns in the
where clause is used.Logically ,the query is as below .But this query does
not use the Index .However ,if we re-write it as below(see 2nd query) - the
index is used :but the query returns incorrect results .Can you suggest any
changes to the original query?
note :
1)we have a index on cmm_id,cust_id,friendly_session_id,creation_date
2)We want to return rows from the table even if all columns in where clause
are NULL
Thanks
vikas
SELECT friendly_session_id,
cmm_id,
cust_id,
hold_amount,
virtual_balance_amount,
creation_date,
session_id
FROM sals_session
WHERE ( CMM_ID = '101' or cmm_id IS NULL )
AND ( CUST_ID =101 or cust_id IS NULL )
AND ( FRIENDLY_SESSION_ID = 100 or friendly_session_id IS NULL )
AND (CREATION_DATE is NULL OR loc_to_utc(creation_date) between
'21-JUN-2001' and '21-AUG-2001');
SELECT friendly_session_id,
cmm_id,
cust_id,
hold_amount,
virtual_balance_amount,
creation_date,
session_id
FROM sals_session
WHERE cmm_id = nvl(cmm_id,0)
AND cust_id = nvl(cust_id,0)
AND friendly_session_id =nvl(friendly_session_id ,0)
AND (creation_date is NULL OR loc_to_utc(creation_date) between
'21-JUN-2001' and '21-AUG-2001');
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vikas Kawatra
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
