Re: Need ideas for "proof test"

From: Ed Stevens <nospam_at_noway.nohow>
Date: Fri, 05 Mar 2004 10:36:17 -0600
Message-ID: <hsah409j1munolbf6cgh6nn3v27c30vkpf@4ax.com>

Ok, here's some preliminary results of my testing.

I started with the code that Daniel supplied, but modified it slightly to base it a real table from one of our apps, and built in some testing flexibility. However the basics are the same: create two tables that are identical except for the postion of the PK column, load the tables with an INSERT inside a loop, then loop thru SELECTS.

First my results: With tables of 200,000 rows, 5 trials of the SELECT loop provided by Daniel . . .

Key_First Key_Last Diff

704	740	36
714	739	25
683	676	-7
681	694	13
727	716	-11

This is what I would expect -- some varience between trials, but no clear trend or consistency.

And for those who want to vet the procedure, here's my code, followed by EXPLAINs on the two queries.

CREATE TABLESPACE edstevens_DATA

    DATAFILE 'E:\ORADATA\<sid>\edstevens_DATA_01.DBF'

	SIZE 1024064K 
	AUTOEXTEND ON 
	MAXSIZE UNLIMITED
	EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
	LOGGING
	ONLINE

/

-- create the tables

drop table edstevens.EDS_TEST_KEY_FIRST
/

drop table edstevens.EDS_TEST_KEY_LAST
/

CREATE TABLE edstevens.EDS_TEST_KEY_FIRST (

    SUPL_KEY NUMBER NOT NULL,

    COL_01     NUMBER(8)   NOT NULL,
    COL_02     NUMBER(9,2) NOT NULL,
    COL_03     NUMBER(5,4) NOT NULL,
    COL_04     NUMBER(5,4) NOT NULL,
    COL_05     NUMBER(5,4) NOT NULL,
    COL_06     NUMBER(9,2) NOT NULL,
    COL_07     NUMBER(9,2) NOT NULL,
    COL_08     NUMBER(9,2) NOT NULL,
    COL_09     DATE        NOT NULL,
    COL_10     DATE        NOT NULL,
    COL_11     DATE        NOT NULL,
    COL_12     DATE            NULL,
    COL_13     CHAR(1)         NULL

)
TABLESPACE edstevens_DATA
LOGGING
PCTFREE 0
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(FREELISTS 1

        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

PARALLEL(DEGREE 4 INSTANCES 1)
NOCACHE
/

CREATE UNIQUE INDEX edstevens.EDS_TEST_KEY_FIRST_PK

    ON edstevens.EDS_TEST_KEY_FIRST(SUPL_KEY) PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE edstevens_DATA
STORAGE(FREELISTS 1

        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

LOGGING
NOPARALLEL
NOCOMPRESS
/

CREATE TABLE edstevens.EDS_TEST_KEY_LAST (

    COL_01     NUMBER(8)   NOT NULL,
    COL_02    NUMBER(9,2) NOT NULL,
    COL_03 NUMBER(5,4) NOT NULL,
    COL_04 NUMBER(5,4) NOT NULL,
    COL_05 NUMBER(5,4) NOT NULL,
    COL_06 NUMBER(9,2) NOT NULL,
    COL_07 NUMBER(9,2) NOT NULL,
    COL_08 NUMBER(9,2) NOT NULL,
    COL_09     DATE        NOT NULL,
    COL_10 DATE        NOT NULL,
    COL_11     DATE        NOT NULL,
    COL_12    DATE            NULL,
    COL_13 CHAR(1)         NULL,

    SUPL_KEY NUMBER NOT NULL
)
TABLESPACE edstevens_DATA
LOGGING
PCTFREE 0
PCTUSED 1
INITRANS 1
MAXTRANS 255
STORAGE(FREELISTS 1

        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

PARALLEL(DEGREE 4 INSTANCES 1)
NOCACHE
/

CREATE UNIQUE INDEX edstevens.EDS_TEST_KEY_LAST_PK

    ON edstevens.EDS_TEST_KEY_LAST(SUPL_KEY) PCTFREE 10
INITRANS 2
MAXTRANS 255
TABLESPACE edstevens_DATA
STORAGE(FREELISTS 1

        FREELIST GROUPS 1
        BUFFER_POOL DEFAULT)

LOGGING
NOPARALLEL
NOCOMPRESS
/

-- load the tables

   truncate table edstevens.EDS_TEST_KEY_FIRST;    truncate table edstevens.EDS_TEST_KEY_LAST;

BEGIN
   FOR i IN 1..200000
   LOOP

     INSERT INTO edstevens.EDS_TEST_KEY_FIRST 
     VALUES
       (i,
	12345678,
	1234567.12,
	1.1234,
	1.1234,
	1.1234,
	1234567.12,
	123467.12,
	1234567.12,
	sysdate,
	sysdate,
	sysdate,
	sysdate,
	'x');

     INSERT INTO edstevens.EDS_TEST_KEY_LAST
     VALUES
       (12345678,
	1234567.12,
	1.1234,
	1.1234,
	1.1234,
	1234567.12,
	123467.12,
	1234567.12,
	sysdate,
	sysdate,
	sysdate,
	sysdate,
	'x',
	i);

   END LOOP;
   COMMIT;
END;
/

ANALYZE TABLE edstevens.EDS_TEST_KEY_FIRST ESTIMATE STATISTICS SAMPLE 25 PERCENT
/

ANALYZE TABLE edstevens.EDS_TEST_KEY_LAST ESTIMATE STATISTICS SAMPLE 25 PERCENT
/

• run the timing test

set serveroutput on

DECLARE
   i PLS_INTEGER;
   s edstevens.EDS_TEST_KEY_FIRST.COL_01%TYPE;    rn PLS_INTEGER;
BEGIN
   SELECT COUNT(*)

	INTO rn 
     FROM edstevens.EDS_TEST_KEY_FIRST;
	

   i := dbms_utility.get_time();

   FOR i IN REVERSE 1 .. rn
   LOOP

     SELECT COL_01
     INTO s
     FROM edstevens.EDS_TEST_KEY_FIRST 
     WHERE SUPL_KEY = 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 .. rn
   LOOP

     SELECT COL_01
     INTO s
     FROM edstevens.EDS_TEST_KEY_LAST
     WHERE SUPL_KEY = i;

   END LOOP;    dbms_output.put_line('Last: ' ||
TO_CHAR(dbms_utility.get_time()-i));
END;
/

• EXPLAIN PLAN - key first === sql> explain plan for 2 SELECT clm_key 3 FROM PUR003.EDS_TEST_KEY_FIRST 4 WHERE SUPL_KEY = 1 5 /

Explained.

sql> set echo off

OPERATION                 OPTIONS         OBJECT NAME
                rows OPT
------------------------- ---------------
---------------------------------
--------- ---------- ------
 SELECT STATEMENT                         COST = 1
                   1 CHOOSE
  TABLE ACCESS            BY INDEX ROWID  EDS_TEST_KEY_FIRST
                   1 ANALYZ
   INDEX                  UNIQUE SCAN     EDS_TEST_KEY_FIRST_PK
                   1 ANALYZ

• EXPLAIN PLAN - key last === sql> explain plan for 2 SELECT clm_key 3 FROM PUR003.EDS_TEST_KEY_LAST 4 WHERE SUPL_KEY = 1 5 /

Explained.

sql> set echo off

OPERATION                 OPTIONS         OBJECT NAME
                rows OPT
------------------------- --------------- ------------------------
--------- ---------- ------
 SELECT STATEMENT                         COST = 1
                   1 CHOOSE
  TABLE ACCESS            BY INDEX ROWID  EDS_TEST_KEY_LAST
                   1 ANALYZ
   INDEX                  UNIQUE SCAN     EDS_TEST_KEY_LAST_PK
                   1 ANALYZ

Received on Fri Mar 05 2004 - 10:36:17 CST