Different index usage by Oracle* optimizer

From: <broeni_at_my-deja.com>
Date: Fri, 17 Sep 1999 11:11:55 GMT
Message-ID: <7rt7lm$1q4$1@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'.

Another difference that I could find is that on server 1 -the one using the index- the table customer_all uses 5 extents, while on server 2 only 1 extent is used for the table.

My problem is not restricted to the example I've described. The programs show a very poor performance on server 2 because many of the statements cause full table scans.

Questions

---

+ Is it "normal" that when the partitioning option is used the
optimizer has a tendency to make full table scans?
+ Can I prevent this by setting `optimizer_mode = COST' in the init.ora
file?
+ Are there any other explanations for the different behaviour?

Many thanks in advance for any response, Stephan

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 06:11:55 CDT