SQL Query problem

From: <kal121_at_my-deja.com>
Date: Thu, 09 Nov 2000 19:04:19 GMT
Message-ID: <8uesfe$94a$1@nnrp1.deja.com>

Can anyone please tell me why the CBO is choosing to do a full table scan on this query?

1. There is a PRIMARY KEY on site_id
2. The table and primary key are both recently analyzed
3. Optimizer is CBO
4. Every single site_id is UNIQUE
5. 1,420,000 rows in the table

I tried forcing the using of the primary key using a hint. Well, it used the primary key, but it did a FULL SCAN on the index, not a UNIQUE SCAN. This took even longer than the full table scan (9 seconds)!

(I am baffled, since I have a UNIQUE key on another column in this same table and the CBO is doing UNIQUE SCANS on that index. That query returns in a fraction of a second.)

SQL> select site_url from tab1 where site_id = 384579;

SITE_URL

---

---

http://www.usernm384579.com

 real: 8812

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10412 Card=14200 Byt
          es=2385600)

   1    0   TABLE ACCESS (FULL) OF 'TAB1' (Cost=10412 Card=14200
          Bytes=2385600)

Statistics

---

          0  recursive calls
          4  db block gets
      68601  consistent gets
      68529  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        497  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 09 2000 - 13:04:19 CST