RE: 8.1.5 concatenated index problem

From: Kathy Duret <kathy.duret_at_isearch.com>
Date: Fri, 25 Aug 2000 13:12:25 -0700
Message-Id: <10600.115602@fatcity.com>

Perfect!

Thank you! I totally forgot about this. I have always worked on CBO's this is my first RBO database.

Unfortunately, I have other concatenated indexes - so I have to see what other
queries I have that could be having problems. Hopefully, Oracle will send have a patch for me so
I don't have to pour though all our queries, do a fix and then have to change it back when we go to 8.1.6

Kathy

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of MTPConsulting_at_aol.com
Sent: Friday, August 25, 2000 12:50 PM
To: Multiple recipients of list ORACLE-L Subject: Re: 8.1.5 concatenated index problem

Kathy,
Use the old rules-based tricks to suppress the index. If column A is character, use:

select column A, C from tablex where column A ||'' is Null

   (that's 2 bars/pipes and 2 single quotes)

or if column A is numeric, use:

select column A, C from tablex where column A + 0 is Null

That should work for you.

Marc Perkowitz
MTP Systems Consulting

In a message dated 8/25/00 12:24:51 PM Central Daylight Time, kathy.duret_at_isearch.com writes:

<< Dear Gurus;

 Have any of you come across this. Oracle Support is still researching this  (3 days now)

 Oracle 8.1.6 - this is not a problem
 Oracle 8.1.5 - this is a problem

 I have a concatenated index on two columns (A, B)

 select column A, C from tablex where column A is Null

                8.1.5 database returns  0 rows
                8.1.6 database returns 3207 rows

 select column A from tablex where column A is Null

                8.1.5 database returns 3207 rows
                8.1.6 database returns 3207 rows

 My problems are:
                 1)  I can't upgrade my customers 8.1.5 to 8.1.6 we get new
 machines which maybe a month or two
                 2)  We are using rule based so hints in the query will not
 force the select to use another index
                       I tried to build an additional index using just

column
 A and the query still chooses the concatenated index

                 3)  I can't drop the concatenated index as I have many

other
 queries which rely on it for performance.

 Any suggestions?

 Kathy

 --
 Author: Kathy Duret
   INET: kathy.duret_at_isearch.com
>>

--
Author:
  INET: MTPConsulting_at_aol.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

Received on Fri Aug 25 2000 - 15:12:25 CDT