Re: BAD PERF. for single row access with unique index

From: <kerbiquet_at_hotmail.com>
Date: 2000/06/28
Message-ID: <8jcdkt$s71$1@nnrp1.deja.com>#1/1

Thank you very much for you help,

For both statement Oracle is using the unique index, that looks to me the faster way to get back the result as there is a predicate on the 3 columns of the UNIQUE INDEX. But It's so slow for MESSAGE! I rebuild all the index partitions, I got the same perf.

I create an another table MESSAGE_TEST with the same structure, partitions, unique index and same data (insert into select from). I get much faster result on my test table. What could be the reason? Not index fragmentation, I done rebuild. Not table fragmentation I only access 1 single row. Any idea? Looks like Oracle is not looking at all the 3 columns of the unique index...

Here are the trace for MESSAGE, MESSAGETXT and MESSAGE_TEST:

---

---

select count(1) from message
WHERE source = 'A'
AND day=to_date('16/05/2000','DD/MM/YYYY') AND number = 900

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      1.39       1.37          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.00          0          3
0           1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      1.39       1.37          0          3
0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (OPS$ICB2G)

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT AGGREGATE
      1   INDEX UNIQUE SCAN (object id 2185)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      1    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESSAGE_1'

(UNIQUE)

---

---

---

---

select count(1) from message_test
WHERE source = 'A'
AND day=to_date('16/05/2000','DD/MM/YYYY') AND number = 900

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.06       0.06          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.00          0          3
0           1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.06       0.06          0          3
0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (OPS$ICB2G)

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT AGGREGATE
      1   INDEX UNIQUE SCAN (object id 12195)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      1    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'IU_MESSAGE_TEST_1'

(UNIQUE)

---

---

---

---

select count(1) from ops$caa.messagetxt
WHERE source = 'A'
AND day=to_date('16/05/2000','DD/MM/YYYY') AND number = 900

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.11       0.15          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.01       0.06          3          3
0           1

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.12       0.21          3          3
0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 25 (OPS$ICB2G)

Rows Row Source Operation

-------  ---------------------------------------------------
      0  SORT AGGREGATE
      5   INDEX RANGE SCAN (object id 2357)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      5    INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'IU_MESSAGETXT_1'
               (UNIQUE)



************************************************************************

---

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 28 2000 - 00:00:00 CDT