Why does using two indexes slow down my query?

I'm having a problem with Oracle that I have not seen in other DBMSs. I'm no

Oracle expert, so I am hoping there is a simple solution...

I have a table
of about 5 million rows. Each record is relatively wide (300 bytes  or so).
I am using Oracle 7.3 for NT.

When I do the following query:

SELECT   count(*)
FROM
  my_table
WHERE
  indexed_colum1 = '101'

I get a quick
"9,000"

Or when I do
SELECT
  count(*)
FROM
  my_table
WHERE   indexed_colum2 = 'C'

I get a quick "80,000"

But God help me if I put BOTH
indexed columns in the query such as this: SELECT
  count(*)
FROM   my_table
WHERE
  indexed_colum1 = '101
AND
  indexed_colum2 = 'C'

This
takes upwards of 15 minutes. My past expereince with DBMSs (Informix, RedBrick, SQLServer) has been that the more narrow I make my constraints, the faster the query returns values...

I have estimated statistics using
analyze.
I have tried all the optimizer goals.

Any help is greatly
appreciated.

--Patrick

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Jun 09 1998 - 00:00:00 CDT