Re: Need ideas for "proof test"

Daniel,

Your test case surprised me, because the results were "the wrong way round". I would have predicted the opposite, and not to such an extreme.

Theory: Oracle finds the rowid in the index and gets to the table block using the same amount of CPU regardless of the location of the PK in the table row. The CPU required to locate the correct row is also not affected by the column position, but the CPU required to extract the column of interest from the row is larger if the PK column has to be walked past.

Fact: When I first ran your test case, my results were similar to yours.

However, I discovered a variety of reasons why the results were so extreme, and reduced your test case to two scripts which both started in the same empty tablespace, using the same session id (as the tablespace was ASSM, and it made a difference) and came up with the following outputs on 9.2.0.4 with four consecutive runs on the select loops.

Last:

        548, 564, 547, 562
First

        547, 568, 548, 563

On average, the pklast was the slightly faster option - but the variation between tests in the same circumstances was so large, that I wouldn't claim any valid conclusion.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

March 2004 Hotsos Symposium - The Burden of Proof
  Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June  2004      UK - Optimising Oracle Seminar


"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1077823690.467896_at_yasure...


>

> First: 3731

> Last: 5366

>

> The PK in the first column consistently performs better at both 20,000

> and 200,000 rows.

>