Ed Stevens <nospam_at_noway.nohow> wrote in message news:<ogle50pj2bis1me3tpj340et1dra5j07t8_at_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.
i := dbms_utility.get_time(); should be inside outer loop, not before loop
Received on Tue Mar 16 2004 - 19:09:41 CST
