Indexed Table DML Performance when you dont use index

Question

Hi All,

I am running some performance tests about indexes and DMLs. I could not understand some test result in the case of when you do not use indexes on an indexed table DMLs perform slower than the non-indexed tables. I was expecting to run the both in similar time intervals. The plsql block i used is below:(TINDEX_PERFORMANCE_TEST_1 table have a primary key column 'ID', i did not used it in update statement so as to no update required on the index when i update using the statement below. Am i right?)

So why Indexed table performs slower when the index is not used? Thanks all...

$BEGIN

    RUNSTATS_PKG.RS_START;

    FOR i IN 1..10000 
    LOOP

        --INSERT INTO TINDEX_PERFORMANCE_TEST_1 VALUES(i,i);

        UPDATE TINDEX_PERFORMANCE_TEST_1 
        SET vtext = TO_CHAR(i)
        WHERE vtext = TO_CHAR(i);

        --DELETE FROM TINDEX_PERFORMANCE_TEST_1 WHERE ID = i;

    END LOOP;

    RUNSTATS_PKG.RS_MIDDLE;

    FOR i IN 1..10000 
    LOOP

        -- INTO TINDEX_PERFORMANCE_TEST_2 VALUES(i,i);

        UPDATE TINDEX_PERFORMANCE_TEST_2 
        SET vtext = TO_CHAR(i)
        WHERE vtext = TO_CHAR(i);

        --DELETE FROM TINDEX_PERFORMANCE_TEST_2 WHERE ID = i;

    END LOOP;

    RUNSTATS_PKG.RS_STOP;
END;

Result:

Run1 ran in 165 hsecs
Run2 ran in 93 hsecs
run 1 ran in 177,42% of the time

Answer 1

Could have been anything (eg other jobs hogging the CPU, disk performing other operations, memory management).

Elapsed time is pretty variable when you get into tenths of a second.