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

Hi,
Okay, so I didn't realise what information was required.
Here it goes:

Sun Solaris 2.7 - Enterprise 450.
Oracle 8.1.7
Optimiser = CHOOSE
Statistics were with Explain Plan in TOAD.

Cheers

"Daniel Morgan" <damorgan (AT) exxesolutions (DOT) com> wrote

<SNIP

Craig Burtenshaw wrote:

Those statistics are worthless. You must run DBMS_STATS regularly as follows:

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schema_na me',
CASCADE=>TRUE);

TOAD is incapable of performing this function. Then, if the problem persists
run EXPLAIN PLAN and post the two plans.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Hi Craig,

Us Canberra guys have got to stick together

There could be a number of reasons some of which include:

- The statistics not being accurate (not sure how you've got the stats with
toad but look at the dbms_stats package)
- Histogram statistics being present in prod but not in testing if the low
cardinality of status is non-typical
- The clustering factor of the status column being somewhat different
between the 2 databases (with the values of status being deemed to be
distributed among more blocks in test than in prod)
- Key parameters such as db_file_multiblock_read_count,
optimizer_index_cost_adj, optimizer_index_caching being somewhat different
- Parallelism kicking in on test
- Rows being more compactly stored in the "fresher" test table vs the
possibly fragmented prod table resulting in somewhat different High Water
Marks
- etc, the list goes on....

The reason why selecting just the status column uses the index in test is
probably due to the fact that it doesn't need to visit the table if using
the status index hence making the index more appealing to the cbo.

Are the statistics as listed in the DD similar between the two databases?
What are the execution plans ?
What's the cost of using the index (via a hint) and how does it compare to
the FTS ?

Good Luck

Richard

"Craig Burtenshaw" <crb (AT) amsa (DOT) gov.au> wrote