Indexes: Strange behaviour !!

From: Roy Varghese <rvarghese_at_ibm.net>
Date: 1998/01/23
Message-ID: <34c910f8.0@news1.ibm.net>#1/1

Hey folks, please help me out with this one:

I have a journal detail table JRNL_LN which has more than 5 million rows. There are 5 indexes on this table, only one of which is unique. The unique index also acts to enforce the primary key. All indexes have an average of 5 fields in the key.

Now, I have a query that joins this detail table to the header (containing only 100,000) record and selects some records. Something like:

SELECT A.Journal_Date, A.Journal_ID,

                  A.Group_Code, B.Account , B.Amount

FROM JRNL_HEADER A, JRNL_LN B
WHERE A.Journal_date = B.Journal_Date AND

                 A.Journal_Id = B.Journal_Id AND
                 A.Group_Code = B.Group_Code
                 AND
                 B.Account = '23343'

Now funny part is on examining execution plan, Oracle seems to be ignoring Index1 with key - Journal_date, Journal_id, Group_Code, Account, Year_Code -, and instead is using UNIQUE Index2 with key - Journal_date, Journal_id, Group_Code, Year_Code, User_Code. Note that there are
statistics available for either the tables or the indexes in the data dictionary
at this stage, and the optimizer is running in CHOOSE mode. Now, if I gather statistics for Index2 using ANALYZE INDEX then it begins to use Index1 and query executes significantly faster.

What could be reason for this behaviour! Anybody?

All answers are appreciated. Thanks in advance !!!

Roy Varghese Received on Fri Jan 23 1998 - 00:00:00 CST