Re: Need ideas for "proof test"

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 1 Mar 2004 09:40:12 -0000
Message-ID: <404304fc$0$10334$ed9e5944@reading.news.pipex.net>

"Ed Stevens" <nospam_at_noway.nohow> wrote in message news:atgu30tvstujr33jagkcvfp0f27a17eddn_at_4ax.com...
> On Thu, 26 Feb 2004 11:28:48 -0800, Daniel Morgan
> <damorgan_at_x.washington.edu> wrote:
> >CREATE TABLE pkfirst (
> >id NUMBER(10),
> >col1 VARCHAR2(10),
> >col2 VARCHAR2(10),
> >col3 VARCHAR2(10),
> >col4 VARCHAR2(10));
> >
> >CREATE TABLE pklast (
> >col1 VARCHAR2(10),
> >col2 VARCHAR2(10),
> >col3 VARCHAR2(10),
> >col4 VARCHAR2(10),
> >id NUMBER(10));
> >
> >ALTER TABLE pkfirst
> >ADD CONSTRAINT pk_pkfirst
> >PRIMARY KEY (id)
> >USING INDEX;
> >
> >ALTER TABLE pklast
> >ADD CONSTRAINT pk_pklast
> >PRIMARY KEY (id)
> >USING INDEX
> >TABLESPACE indx_sml;
> >
> >BEGIN
> > FOR i IN 1..200000
> > LOOP
> > INSERT INTO pkfirst
> > VALUES
> > (i, 'AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC', 'DDDDDDDDDD');
> >
> > INSERT INTO pklast
> > VALUES
> > ('AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC', 'DDDDDDDDDD', i);
> > END LOOP;
> > COMMIT;
> >END;
> >/
> >
> >set serveroutput on
> >
> >DECLARE
> > i PLS_INTEGER;
> > s pkfirst.col1%TYPE;
> >BEGIN
> > i := dbms_utility.get_time();
> >
> > FOR i IN REVERSE 1 .. 200000
> > LOOP
> > SELECT col1
> > INTO s
> > FROM pkfirst
> > WHERE id = i;
> > END LOOP;
> >
> > dbms_output.put_line('First: ' || TO_CHAR(dbms_utility.get_time()-i));
> >
> > i := dbms_utility.get_time();
> >
> > FOR i IN REVERSE 1 .. 200000
> > LOOP
> > SELECT col1
> > INTO s
> > FROM pklast
> > WHERE id = i;
> > END LOOP;
> >
> > dbms_output.put_line('Last: ' || TO_CHAR(dbms_utility.get_time()-i));
> >END;
> >/
> >
> >First: 3731
> >Last: 5366
> >
> >The PK in the first column consistently performs better at both 20,000
> >and 200,000 rows.

> Daniel,

> Thanks for the code. This will give me a good framework to jump start
> several tests I'd like to try.

> In the specific example you provided, I'm puzzled as to why there
> should be consistent performance preference of one over the other.

My *guess* (and maybe hope) would be that the example Dan provides has a constant component, how long it takes to read the index, but varies in the table access by rowid component (because Oracle doesn't have to read the whole row to answer the query).

I'd like to extend the test as follows though.

Stick in some randomized inserts. (I like your idea of loading the table in random order as well, though presumably if the test still comes out in favour of ordering then all loads would be done in sequence.)
Modify the select statements to reflect range scans and non-key access to data.
Time a reorg and associated index builds at various volumes of data. add this to the elapsed time of the ordered method.

In other words try and reflect real life data access in a bit more detail and factor in the cost of regular reorgs. The tests that dan proposes seem to be a bit biased in favour of highly organized data.

My guess is that you may save some small amount of time but at an unacceptably high cost. (unless you are paid overtime by the hour in which case of course the cost may well be acceptable :) ).

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK

Received on Mon Mar 01 2004 - 03:40:12 CST