Re: Simple Oracle Query Taking Too Long

Hi,

After creating the indexing on abc, you reindex the existing rows. the you can apply the query as of your indexing order. For ex: you have index on companycd,divisioncd,locationcd,finyearcd,empcd.. In sql query you have to apply in where caluse like..

where companycd = v_companycd,
and divisoncd = v_divisioncd,
and finyearcd = v_finyearcd
and empcd = v_empcd..

if you mis this order it will scan the full table scan for each join filed.

Thanks,

Mohan Reddy G

Bryan Hunter wrote:
> I have oracle 8.16 running on a sun enterprise 450 lots of ram and
> processor.
> 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. I then applied and
> index on the leading column and re ran the same query, still take 42
> seconds. 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
Received on Thu Sep 07 2006 - 12:37:32 CDT