Like I explained before, our fields are DATETIME ie 12/31/1971 11:59:59 pm. If I just do a [tDob = to_date("12/31/1971","MM/DD/YYYY")] I get 0 records. Because for what ever reason, oracle wants to filter on the TIME too. So, i have to do the BETWEEN. With that said, I created an INDEX on the TDOB field as follows TO_CHR(tDob, "MM/DD/YYYY"). When I create my sql as follows [TO_CHAR(tDob, "MM/DD/YYYY") = "12/31/1971" it works, but the problem I have there is ORACLE doesn't use the INDEX I created w/ out being told to do so with the HINTs clause.

We have been seeing a lot of issue where oracle will, for what ever reason, not use the most optimized index to get data. Using the orginal example, we have an index on TDOB and an index on cSTATUS. TDOB, as you can tell, is a Date Of Birth Field. A single date would be a very small subset, anywhere between 20 to 50 records. However, cStatus can have only 3 possible values "A", "I" or "P". "A" - about 95% of the database, "I" about 4.9% of the database and "P" about .1% of the database. However when you run the following sql:

SELECT * FROM voter WHERE tDob >= TO_DATE("12/31/1971 12:00:00 am","MM/DD/YYYY HH:MI:SS AM") and tDob <= TO_DATE("12/31/1971 11:59:59 pm","MM/DD/YYYY HH:MI:SS AM") AND cStatus = "A"

Oracle uses the cSTATUS index and does a index scan. This is so stupid when the TDOB is going to get only 37 records of the 650,000 records in table, where cStatus is scanning 620,000 records. If I take the expression of cStatus = "A" and change it to UPPER(cStatus) = "A" then my query runs quickly using the index for TDOB.

We are new at orcale. So any DETAILED explinations would be greatly helpful.

Thanks