Re: 8.1.5 concatenated index problem

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Fri, 25 Aug 2000 14:45:20 -0400
Message-Id: <10600.115589@fatcity.com>

Not having anywheres the amount of knowledge some og the particitants on = this list have, I would try the following: drop the concatenated index and create the new single column index. Test = your query.=20
Recreate the concatenated index with the single column index in place and = test your query.
Reasoning: If the concatenated index is the first choice in the list of =

indexes and the column in the select is the first column in the concatenate=
d index it fill the criteria of "use the index". By dropping the concatenat=
ed index and creating the single index and then recreating the concatenated=

 index the single index should be the first index it finds when it looks = for an index that matches the column.

Like I said, I don't understand a lot but logically it sounds valid. The = indexes are not listed alphabetically from the dba_ind_columns table. I = have found that they are listed by creation time. 2bits shot in the dark.
ROR
>>> kathy.duret_at_isearch.com 08/25/00 01:25PM >>>
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

--=20
Author: Kathy Duret
  INET: kathy.duret_at_isearch.com=20

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 - 13:45:20 CDT