RE: linux & Oracle -- best combination

From: Koivu, Lisa <lisa.koivu_at_efairfield.com>
Date: Wed, 22 Aug 2001 09:07:30 -0700
Message-ID: <F001.0037429D.20010822092656@fatcity.com>

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:VKawatra_at_innoventry.com]
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: VKawatra_at_innoventry.com



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: ListGuru_at_fatcity.com (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).

Received on Wed Aug 22 2001 - 11:07:30 CDT