Re: 8.1.5 concatenated index problem

If you are trying to force the use of the index created only for column A, preventing the use of the concatenated index, then I believe you will need to perform some operation on Col_B in your query. Any operation will do. I use concat->||or NVL() for varchar2s and the veritable +0 for numerics.

On second thought if you are using rule based optimization, and specifying A is not null, you should not be using any index involving column A. The 'is not null' will preclude use of that column for indexing.

So either this was a trick question(for me at least), or you are looking squarely at a bug.

Steve McClure
----- Original Message -----
From: Kathy Duret <kathy.duret_at_isearch.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, August 25, 2000 10:25 AM
Subject: 8.1.5 concatenated index problem

> 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
>
> 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 Fri Aug 25 2000 - 16:32:42 CDT