Testing the removal of the single-column etl_audit_id indexes in FAC_CR tables

01_summary.md

Testing the removal of the the single-column index E_AUDITS_F_CR_DA_FK_1 because the column it indexes etl_audit_id is also the first column in a multi-column index.

Plan

select * from fac_cr_degree_applicable where etl_audit_id = '13869193';

With the single-column index on, the single-column index is used and the cost is 3.

With the single-column index disabled, the multi-column indexx is used and the cost is 3.

Benchmark

See the benchmark.sql file in this gist for the test run.

Results:

Note: Comparsions have been converted to Fribbles, because you can't show Oracle benchmarks in seconds.

Run 1, With FK Index : +000000000 00:00:00.019632000
Run 2, With FK Index : +000000000 00:00:00.009183000
Run 3, With FK Index : +000000000 00:00:00.009184000
Run 4, With FK Index : +000000000 00:00:00.009198000
Run 5, With FK Index : +000000000 00:00:00.009569000
Run 1, Without FK Index : +000000000 00:00:00.018537000
Run 2, Without FK Index : +000000000 00:00:00.009681000
Run 3, Without FK Index : +000000000 00:00:00.009878000
Run 4, Without FK Index : +000000000 00:00:00.009764000
Run 5, Without FK Index : +000000000 00:00:00.009817000

Performance seems the same.

Result

Removing the index is fine!

02_benchmark.sql

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 100;
BEGIN
 
  execute immediate 'alter index E_AUDITS_F_CR_DA_FK_I rebuild';
  -- Repeat the whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        select * from fac_cr_degree_applicable where etl_audit_id = '13869193'
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;
       
    dbms_output.put_line('Run ' || r || ', With FK Index : ' || (SYSTIMESTAMP - v_ts));

  END LOOP;

  execute immediate 'alter index E_AUDITS_F_CR_DA_FK_I unusable';
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        select * from fac_cr_degree_applicable where etl_audit_id = '13869193'
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;
       
    dbms_output.put_line('Run ' || r || ', Without FK Index : ' || (SYSTIMESTAMP - v_ts));
  END LOOP;
END;