Re: Different index usage by Oracle* optimizer

If you look at the CARD entry for the 2 databases you will note that the optimiser expects to return 275,000 rows in one, and 340,000 rows in the other. That suggests a significant difference in the underlying stats on the two sets of data, especially since you say you have ca. 200,000 rows in the table.

What are the min and max values for the customer id ? What are the clustering factors on the index ?

One strategy to deal with this problem is to create a histogram on the customer_id column so that the optimiser is better able to deal with odd skews in the data.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

broeni_at_my-deja.com wrote in message <7rt7lm$1q4$1_at_nnrp1.deja.com>...
>I'm having 2 similar databases on 2 different UNIX servers, both
>`OSF1 V4.0 1091 alpha'.
>
>The database on server 1 is:
>Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
>
>while the database on server2 is running with the partitioning option:
>Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
>With the Partitioning option
>
>The table CUSTOMER_ALL contains ~200'000 rows in each database, but I
>observe the following difference:
>
>server 1 and server 2: select a distinct customer:
> 1* select customer_id from customer_all where customer_id = 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26) ~
> 1 0 INDEX (UNIQUE SCAN) OF 'PKCUSTOMER_ALL' (UNIQUE) (Cost=1 C ~
> ard=1 Bytes=26)
>
>The behaviour of the optimizer changes if a range of customers is
>selected.
>On server 1 the index is still used:
> 1 select customer_id from customer_all
> 2* where customer_id > 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=546 Card=275477 Byte ~
> s=7162402)
> 1 0 INDEX (RANGE SCAN) OF 'PKCUSTOMER_ALL' (UNIQUE) (Cost=546 ~
> Card=275477 Bytes=7162402)
>
>On server 2 the optimzer makes a full table scan and uses the index
>only if I include the hint FIRST_ROWS:
> 1 select customer_id from customer_all
> 2* where customer_id > 50000
> Execution Plan
> ----------------------------------------------------------
> 0 ~ SELECT STATEMENT Optimizer=CHOOSE (Cost=482 Card=342379 Byte ~
> s=8901854)
> 1 0 TABLE ACCESS (FULL) OF 'CUSTOMER_ALL' (Cost=482 Card=34237 ~
> 9 Bytes=8901854)
>
>On both databases the table customer_all and the index
>pkcustomer_all are fully analyzed with `ANALYZE ... COMPUTE STATISTICS'.
>
Received on Fri Sep 17 1999 - 06:35:22 CDT