Re: Simple Oracle Query Taking Too Long

comments in-line.

Bryan Hunter wrote:
> I have oracle 8.16 running on a sun enterprise 450 lots of ram and
> processor.

Irrelevant and once of the worst versions of Oracle ever released. Wholly unsupported. Get something that isn't paleolithic.

> I have a table abc with no index on it and 40 million rows in it.
> I am running the query "select * from abc where rownum = 1" and that takes
> 42 seconds to run.

> The table was analyzed this morning.

Essentially irrelevant with 8.1.6 (hopefully patched to .2 or .3)

> I then applied and
> index on the leading column and re ran the same query, still take 42
> seconds.

As I would expect.

> I then ran the same query but on table xyz with 80 million rows,
> two indexes and a primary key, it took .03 seconds to run. I looked at the
> explain plan on both and they are both table access full with a count
> stopkey on it. I am confused as to why this would be so slow. Any ideas?
>
> Bryan

Rebuild the table and see what happens:

CREATE TABLE new AS
SELECT * FROM slow_table;

-- 
Puget Sound Oracle Users Group