Re: 8.1.5 concatenated index problem

From: <MTPConsulting_at_aol.com>
Date: Fri, 25 Aug 2000 14:39:35 EDT
Message-Id: <10600.115588@fatcity.com>

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 Received on Fri Aug 25 2000 - 13:39:35 CDT