Re: Optimisation issues.

Craig Burtenshaw wrote:

> Hi,
>
> We have a TEST database which was a copy of a PROD database.
> In the TEST database there are 8 records in 39065 that are 'Active' records.
> In the PROD database there are 16 records in 39497 that are 'Active'
> records.
>
> Both scripts to access this information are
> select * from table
> where status = 'A';
>
> PROD uses the Index and has a cost average of 3.
> TEST does a FULL table scan and therefore has a cost average of 29.
>
> I have rebuilt the indexes in both systems, even dropped and created the
> index in both systems.
> However, this hasn't fixed the problem.
>
> If I do a
> SQL > select status, count(status) from table
> 2 > group by status;
>
> Both systems use the same indexing.
> In fact if I do
> SQL > select status from table
> 2 > where status = 'A';
>
> Then the indexing is being used.
>
> So why doesn't it use the indexing on the (select * from table where status
> = 'A';) in the TEST database.
>
> Cheers
> Craig.
> Unix Systems/Oracle Database Administrator
> Australian Maritime Safety Authority

Lets see ... no hardware, no operating system, no version, no edition, no init parmeters, no indication of RBO or CBO, no indication as to whether statistics were created using DBMS_STATS, no EXPLAIN PLAN.

So you want someone to guess?

I guess it is the humidity. ;-)

Seriously ... give us something to work with.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)