Re: Index question

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 25 Aug 2009 19:42:29 +0100
Message-ID: <CvednTlsZ7sOrQnXnZ2dnUVZ8hudnZ2d_at_bt.com>

<stevedhoward_at_gmail.com> wrote in message news:759c8035-99e5-466c-9677-78619bcc6b55_at_34g2000yqi.googlegroups.com... On Aug 25, 12:48 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> Trace them and post execution plans.
> Maybe an index corruption.
>
> Regards
> Michel

Thanks, Michel. Below is the 10046 trace of both (no errors in the trace file).

---

select min(jrn_seq) from xwc.xwc_change_history where jrn_seq between 1019514060 and 1019514080

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.01       0.01          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.01          1          4
0           1

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

---

total        4      0.01       0.02          1          4
0           1

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation

-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4 pr=1 pw=0 time=14558 us)
      1   FIRST ROW  (cr=4 pr=1 pw=0 time=14532 us)
      1    PARTITION HASH ALL PARTITION: 1 8 (cr=4 pr=1 pw=0
time=14526 us)
      1     INDEX RANGE SCAN (MIN/MAX) XWC_CHG_HIST_JRN_GBL PARTITION:

1 8 (cr=4 pr=1 pw=0 time=14509 us)(object id 40380)

---

select jrn_seq from xwc.xwc_change_history where jrn_seq between 1019514060 and 1019514080 order by 1

call     count       cpu    elapsed       disk      query
current        rows

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

---

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0          0
0           0
Fetch        2      0.00       0.02          2         32
0           9

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

---

total        4      0.00       0.03          2         32
0           9

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: 36

Rows Row Source Operation

-------  ---------------------------------------------------
      9  SORT ORDER BY (cr=32 pr=2 pw=0 time=26677 us)
      9   PARTITION HASH ALL PARTITION: 1 8 (cr=32 pr=2 pw=0 time=98
us)
      9    INDEX RANGE SCAN XWC_CHG_HIST_JRN_GBL PARTITION: 1 8 (cr=32

pr=2 pw=0 time=26562 us)(object id 40380)

Guessing a little bit from the index name and the partitioning clause I'd suspect a bug in the combination of partition iteration and min/max range scans.

I think the wrong answer you got from the first query is probably the min value in the first partition of the globally hash partitioned index.

The plan probably ought to be more like;

stopkey
  sort order by
    partition hash all

        first_row
          index full scan (min/max)

The intent being to find the min value in each partition then find the minimum of the minima.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Tue Aug 25 2009 - 13:42:29 CDT