Anyone care to vet / explain this test result?

From: Ed Stevens <nospam_at_noway.nohow>
Date: Tue, 16 Mar 2004 13:57:27 -0600
Message-ID: <ogle50pj2bis1me3tpj340et1dra5j07t8@4ax.com>

Platform: Oracle 8.1.7 on Win2k

Goal: demonstrate effect (or lack of) of the placement of the primary key within a row def.

Test method:
1) create table with key in first column:

CREATE TABLE EDS_TEST_KEY_FIRST
(

    SUPL_KEY NUMBER(7) NOT NULL,
    CLM_KEY CHAR(8) NOT NULL,
<snip>

CREATE UNIQUE INDEX EDS_TEST_KEY_FIRST_PK     ON EDS_TEST_KEY_FIRST(SUPL_KEY)
<snip>

---

2) create table with key in last column:

CREATE TABLE EDS_TEST_KEY_LAST
(

    CLM_KEY char(8) NOT NULL,
<snip>

    SUPL_KEY NUMBER(7) NOT NULL
)
<snip>

CREATE UNIQUE INDEX EDS_TEST_KEY_LAST_PK     ON EDS_TEST_KEY_LAST(SUPL_KEY)
<snip>

---

3) Load tables with data:

   truncate table EDS_TEST_KEY_FIRST;
   truncate table EDS_TEST_KEY_LAST;

BEGIN
   DBMS_RANDOM.INITIALIZE (12345);    FOR i IN 1..50000
   LOOP

     INSERT INTO EDS_TEST_KEY_FIRST 
     VALUES
       (i,
	substr(to_char(dbms_random.random),1,8),

<snip>

     INSERT INTO EDS_TEST_KEY_LAST
     VALUES
       (substr(to_char(dbms_random.random),1,8),


<snip>

	i);

   END LOOP;
   COMMIT;
END;
/
ANALYZE TABLE EDS_TEST_KEY_FIRST
   ESTIMATE STATISTICS
  SAMPLE 25 PERCENT
/
ANALYZE TABLE EDS_TEST_KEY_LAST
  ESTIMATE STATISTICS
  SAMPLE 25 PERCENT
/

Then test with 20 executions of the following

set serveroutput on

DECLARE
   i PLS_INTEGER;
   kf_tme char(7);
   kl_tme char(7);
   s EDS_TEST_KEY_FIRST.clm_key%TYPE;
   rn PLS_INTEGER;
BEGIN
   SELECT COUNT(*)

	INTO rn 
     FROM EDS_TEST_KEY_FIRST;

   dbms_output.put_line('Key First       Key Last');

	

   i := dbms_utility.get_time();

   FOR i IN REVERSE 1 .. rn
   LOOP

     SELECT clm_key
     INTO s
     FROM EDS_TEST_KEY_FIRST 
     WHERE SUPL_KEY = i;

   END LOOP;    kf_tme := to_char(dbms_utility.get_time()-i);

   i := dbms_utility.get_time();

   FOR i IN REVERSE 1 .. rn
   LOOP

     SELECT clm_key
     INTO s
     FROM EDS_TEST_KEY_LAST
     WHERE SUPL_KEY = i;

   END LOOP;    kl_tme := to_char(dbms_utility.get_time()-i);

   dbms_output.put_line(kf_tme || ' ' || kl_tme);

END;
/

Then, just to make things easier, I wrap the above test code in an outer loop: (note the loop controlled by the variable 'x')

set serveroutput on

DECLARE
   i PLS_INTEGER;
   x PLS_INTEGER;
   kf_tme char(7);
   kl_tme char(7);
   s EDS_TEST_KEY_FIRST.clm_key%TYPE;
   rn PLS_INTEGER;
BEGIN
   SELECT COUNT(*)

	INTO rn 
     FROM EDS_TEST_KEY_FIRST;

   dbms_output.put_line('Key First       Key Last');

	

   i := dbms_utility.get_time();

   for x in 1 .. 20
   loop

     FOR i IN REVERSE 1 .. rn
     LOOP
       SELECT clm_key
       INTO s
       FROM EDS_TEST_KEY_FIRST 
       WHERE SUPL_KEY = i;
     END LOOP;

     kf_tme := to_char(dbms_utility.get_time()-i);

     i := dbms_utility.get_time();

     FOR i IN REVERSE 1 .. rn
     LOOP
       SELECT clm_key
       INTO s
       FROM EDS_TEST_KEY_LAST
       WHERE SUPL_KEY = i;
     END LOOP;

     kl_tme := to_char(dbms_utility.get_time()-i);

     dbms_output.put_line(kf_tme || '       ' || kl_tme);

   END LOOP;
END;
/

Now, here's where it gets weird. On the first trials, performing the test code 20 separate times, I got these results: (trimming out the extraneous sqlplus msgs)

Key First Key Last

157           178
180           181
178           185
180           181
180           178
181           183
180           175
172           173
169           176
177           181
179           182
180           183
175           171
180           181
180           184
180           178
181           179
181           183
180           181
181           181

Notice that the numbers are close, sometimes First is faster, sometimes Last. No clear winner. But look at the results with the second version, where the test itself is wrapped in a loop:

161           184
366           182
364           184
365           185
366           184
366           184
365           183
364           185
366           183
364           184
366           184
364           184
365           185
366           184
365           185
365           184
365           185
366           183
364           184
365           185

Here, we have a clear preference for the Key_Last table. The first set of tests is what I would expect, with no clear pattern; this definite pattern in the second test seems rather bizarre. Does anyone have an explanation for this?

Sign me -- mumbling and drooling. Received on Tue Mar 16 2004 - 13:57:27 CST