Re: Anyone care to vet / explain this test result?

"Ed Stevens" <nospam_at_noway.nohow> a écrit dans le message de news:ogle50pj2bis1me3tpj340et1dra5j07t8_at_4ax.com...
> Platform: Oracle 8.1.7 on Win2k
>

[Snip]
>
> 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?
>

Hum, very strange. Notice how the FIRST loop iteration (with the loop version) gives you results similar to individual runs...

Look at you "i" variable, it's initialized OUTSIDE of the x loop ONCE. You do not re-init it IN the loop for subsequent loops, therefor doubling your times.

Correct would be:

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');

   for x in 1 .. 20
   loop

     i := dbms_utility.get_time(); -- GD FIX !!!!!!

     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);