Czy wiesz ile jest indeksów w Twoim systemie?
Sprawdzić ilość indeksów w systemie można w prosty sposób za pomocą słownika ALL_INDEXES.
select table_name, count(*)
from all_indexes where owner = 'HR'
group by table_name order by count(*);
W większości systemów na tabelach jest założonych dosłownie kilka indeksów. Czasem kilkanaście. Jeden system jednak poszedł na rekord. Na tabelach było założonych po kilkadziesiąt indeksów, 30-40 indeksów! Rekord to 44. Na jednej tabeli. Niezbyt dużej zresztą, coś koło 40 kolumn właśnie. Jak się okazało, większość tabel w tym systemie miała pozakładane jednokolumnowe indeksy na wszystkie kolumny! Plus kilka indeksów wielokolumowych.
Do tego był to system back office'owy - tabele te brały udział w sporych przetwarzaniach. A taka ilość indeksów to zabójstwo dla wydajności! Nie dziwota zatem, że przetwarzania trwały godzinami - przy stosunkowo niedużej ilości przetwarzanych rekordów! Usunięcie nadmiarowych, zbędnych indeksów mogłoby przynieść znaczne korzyści!
Niestety osoba odpowiedzialna za ten system moje rewelacje skwitowała krótko:
- Indeksy nie mają wpływu na wydajność.
Z jednej strony to oczywiste, że każdy indeks podczas DML musi zostać zaktualizowany, co na logikę wydłuża czas transakcji. Z drugiej strony - faktycznie nigdy tego osobiście nie przetestowałam! Nie mam niezbitego dowodu!
No to przecież muszę to sprawdzić, nie wytrzymam :)
Jak bardzo ilość indeksów wpływa na czas zapisu rekordów do tabeli?
Na potrzeby testu skorzystam ze standardowego schematu HR na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.
Utworzyłam tabelę źródłową EMP_IDX_0 dla moich testów jako kopię tabeli EMPLOYEES. Następnie zwiększyłam ilość kolumn (na czymś muszę zakładać te indeksy) jako duble oryginalnych kolumn z suffixem _1 oraz uzupełniłam te kolumny danymi z kolumn oryginalnych.
Na potrzeby testu zainsertuję do obu tabel 107000 rekordów. Myślę, że ta ilość pozwoli zobrazować tendencję.
Załadowanie 107000 rekordów trwało ośmiokrotnie dłużej w przypadku tabeli z 25 indeksami! Czyli mam w końcu mój dowód :)
Żeby było ciekawiej, w systemie o którym wspomniałam nie używano przetwarzań masowych BULK COLLECT. Przetwarzania odbywały się metodą ROW BY ROW. W związku z tym przeprowadziłam również test, gdzie dane ładowała wiersz po wierszu.
Dwukrotnie wolniej niż w przypadku metody BULK COLLECT. I tak samo jak w pierwszym teście - większa ilość indeksów spowolniła ładowanie danych prawie pięciokrotnie.
Podsumowanie:
Niezależnie od metody duża ilość indeksów na tabeli znacznie pogarsza wydajność ładowania danych. Indeksy odgrywają ważną rolę w systemach, jednak zbyt duża ich ilość może negatywnie odbić się na wydajności przetwarzań. Oczywiście wszystko zależy od systemu i od architektury procesów niemniej warto o tym pamiętać przy dodawaniu kolejnego indeksu.
Jak bardzo ilość indeksów wpływa na czas zapisu rekordów do tabeli?
Na potrzeby testu skorzystam ze standardowego schematu HR na bazie Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.
Utworzyłam tabelę źródłową EMP_IDX_0 dla moich testów jako kopię tabeli EMPLOYEES. Następnie zwiększyłam ilość kolumn (na czymś muszę zakładać te indeksy) jako duble oryginalnych kolumn z suffixem _1 oraz uzupełniłam te kolumny danymi z kolumn oryginalnych.
Następnie utworzyłam dwie puste tabele testowe.
Jedna EMP_IDX_4 na której założyłam 4 indeksy.
I druga EMP_IDX_25 z 25 indeksami.
Do ładowania zastosowałam metodę BULK wraz z FORALL.
Załadowanie 107000 rekordów trwało ośmiokrotnie dłużej w przypadku tabeli z 25 indeksami! Czyli mam w końcu mój dowód :)
Żeby było ciekawiej, w systemie o którym wspomniałam nie używano przetwarzań masowych BULK COLLECT. Przetwarzania odbywały się metodą ROW BY ROW. W związku z tym przeprowadziłam również test, gdzie dane ładowała wiersz po wierszu.
Dwukrotnie wolniej niż w przypadku metody BULK COLLECT. I tak samo jak w pierwszym teście - większa ilość indeksów spowolniła ładowanie danych prawie pięciokrotnie.
Podsumowanie:
Niezależnie od metody duża ilość indeksów na tabeli znacznie pogarsza wydajność ładowania danych. Indeksy odgrywają ważną rolę w systemach, jednak zbyt duża ich ilość może negatywnie odbić się na wydajności przetwarzań. Oczywiście wszystko zależy od systemu i od architektury procesów niemniej warto o tym pamiętać przy dodawaniu kolejnego indeksu.
Wszystkie skrypty poniżej.
create table emp_idx_0 as select * from employees;
alter table emp add
(EMPLOYEE_ID_1 NUMBER(6,0),
FIRST_NAME_1 VARCHAR2(20 BYTE),
LAST_NAME_1 VARCHAR2(25 BYTE) ,
EMAIL_1 VARCHAR2(25 BYTE),
PHONE_NUMBER_1 VARCHAR2(20 BYTE),
HIRE_DATE_1 DATE,
JOB_ID_1 VARCHAR2(10 BYTE) ,
SALARY_1 NUMBER(8,2),
COMMISSION_PCT_1 NUMBER(2,2),
MANAGER_ID_1 NUMBER(6,0),
DEPARTMENT_ID_1 NUMBER(4,0),
SALARY_NEW_1 NUMBER,
CHRIST_BONUS_1 NUMBER);
update emp set
EMPLOYEE_ID_1 = EMPLOYEE_ID,
FIRST_NAME_1 = FIRST_NAME,
LAST_NAME_1 = LAST_NAME,
EMAIL_1 = EMAIL,
PHONE_NUMBER_1 = PHONE_NUMBER,
HIRE_DATE_1 = HIRE_DATE,
JOB_ID_1 = JOB_ID,
SALARY_1 = SALARY,
COMMISSION_PCT_1 = COMMISSION_PCT,
MANAGER_ID_1 = MANAGER_ID,
DEPARTMENT_ID_1 = DEPARTMENT_ID,
SALARY_NEW_1 = SALARY_NEW,
CHRIST_BONUS_1 = CHRIST_BONUS;
EXEC DBMS_STATS.gather_table_stats('HR', 'EMP_IDX_0');
create table emp_idx_4 as select * from emp_idx_0 where 1=2 ;
CREATE UNIQUE INDEX hr.EMP_emp_idx_4_ID_PK ON hr.emp_idx_4 (EMPLOYEE_ID) ;
CREATE INDEX hr.emp_idx_4_DEPARTMENT_IX ON hr.emp_idx_4 (DEPARTMENT_ID) ;
CREATE INDEX hr.emp_idx_4_JOB_IX ON hr.emp_idx_4 (JOB_ID) ;
CREATE INDEX hr.emp_idx_4_NAME_IX ON hr.emp_idx_4 (LAST_NAME, FIRST_NAME) ;
EXEC DBMS_STATS.gather_table_stats('HR', 'EMP_IDX_4');
create table emp_idx_25 as select * from EMP_IDX_0 where 1=2 ;
CREATE UNIQUE INDEX hr.EMP_emp_idx_25_ID_PK ON hr.emp_idx_25 (EMPLOYEE_ID) ;
CREATE INDEX hr.emp_idx_25_FIRST_NAME ON hr.emp_idx_25 ('FIRST_NAME') ;
CREATE INDEX hr.emp_idx_25_LAST_NAME ON hr.emp_idx_25 ('LAST_NAME') ;
CREATE INDEX hr.emp_idx_25_EMAIL ON hr.emp_idx_25 ('EMAIL') ;
CREATE INDEX hr.emp_idx_25_PHONE_NUMBER ON hr.emp_idx_25 ('PHONE_NUMBER') ;
CREATE INDEX hr.emp_idx_25_HIRE_DATE ON hr.emp_idx_25 ('HIRE_DATE') ;
CREATE INDEX hr.emp_idx_25_JOB_ID ON hr.emp_idx_25 ('JOB_ID') ;
CREATE INDEX hr.emp_idx_25_SALARY ON hr.emp_idx_25 ('SALARY') ;
CREATE INDEX hr.emp_idx_25_COMMISSION_PCT ON hr.emp_idx_25 ('COMMISSION_PCT') ;
CREATE INDEX hr.emp_idx_25_MANAGER_ID ON hr.emp_idx_25 ('MANAGER_ID') ;
CREATE INDEX hr.emp_idx_25_DEPARTMENT_ID ON hr.emp_idx_25 ('DEPARTMENT_ID') ;
CREATE INDEX hr.emp_idx_25_SALARY_NEW ON hr.emp_idx_25 ('SALARY_NEW') ;
CREATE INDEX hr.emp_idx_25_CHRIST_BONUS ON hr.emp_idx_25 ('CHRIST_BONUS') ;
CREATE INDEX hr.emp_idx_25_EMPLOYEE_ID_1 ON hr.emp_idx_25 ('EMPLOYEE_ID_1') ;
CREATE INDEX hr.emp_idx_25_FIRST_NAME_1 ON hr.emp_idx_25 ('FIRST_NAME_1') ;
CREATE INDEX hr.emp_idx_25_LAST_NAME_1 ON hr.emp_idx_25 ('LAST_NAME_1') ;
CREATE INDEX hr.emp_idx_25_EMAIL_1 ON hr.emp_idx_25 ('EMAIL_1') ;
CREATE INDEX hr.emp_idx_25_HIRE_DATE_1 ON hr.emp_idx_25 ('HIRE_DATE_1') ;
CREATE INDEX hr.emp_idx_25_JOB_ID_1 ON hr.emp_idx_25 ('JOB_ID_1') ;
CREATE INDEX hr.emp_idx_25_SALARY_1 ON hr.emp_idx_25 ('SALARY_1') ;
CREATE INDEX hr.emp_idx_25_COMMISSION_PCT_1 ON hr.emp_idx_25 ('COMMISSION_PCT_1') ;
CREATE INDEX hr.emp_idx_25_MANAGER_ID_1 ON hr.emp_idx_25 ('MANAGER_ID_1') ;
CREATE INDEX hr.emp_idx_25_DEPARTMENT_ID_1 ON hr.emp_idx_25 ('DEPARTMENT_ID_1') ;
CREATE INDEX hr.emp_idx_25_SALARY_NEW_1 ON hr.emp_idx_25 ('SALARY_NEW_1') ;
CREATE INDEX hr.emp_idx_25_CHRIST_BONUS_1 ON hr.emp_idx_25 ('CHRIST_BONUS_1') ;
CREATE INDEX hr.emp_idx_25_PHONE_NUMBER_1 ON hr.emp_idx_25 ('PHONE_NUMBER_1') ;
EXEC DBMS_STATS.gather_table_stats('HR', 'EMP_IDX_25');
set serveroutput on ;
declare
dStart date;
dSTop date;
bulk_errors exception;
pragma exception_init(bulk_errors, -24381);
cursor cur is
with data as (select level lvl from dual connect by level <= 1000)
select rownum,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID,
SALARY_NEW,
CHRIST_BONUS,
EMPLOYEE_ID_1,
FIRST_NAME_1,
LAST_NAME_1,
EMAIL_1,
HIRE_DATE_1,
JOB_ID_1,
SALARY_1,
COMMISSION_PCT_1,
MANAGER_ID_1,
DEPARTMENT_ID_1,
SALARY_NEW_1,
CHRIST_BONUS_1,
PHONE_NUMBER_1
from EMP_IDX_0, data ;
type tab is table of cur%rowtype;
t tab;
begin
dSTart := sysdate;
open cur;
fetch cur bulk collect into t;
close cur;
forall i in 1..t.count save exceptions
insert into emp_idx_4 values t(i);
dStop := sysdate;
dbms_output.put_line(round((dstop - dstart) * 24*60*60,2));
exception when bulk_errors then
dbms_output.put_line('ERROR');
dbms_output.put_line(round((nvl(dstop,sysdate) - dstart) * 24*60*60,2));
end;
/
set serveroutput on ;
declare
dStart date;
dSTop date;
bulk_errors exception;
pragma exception_init(bulk_errors, -24381);
cursor cur is
with data as (select level lvl from dual connect by level <= 1000)
select rownum,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID,
SALARY_NEW,
CHRIST_BONUS,
EMPLOYEE_ID_1,
FIRST_NAME_1,
LAST_NAME_1,
EMAIL_1,
HIRE_DATE_1,
JOB_ID_1,
SALARY_1,
COMMISSION_PCT_1,
MANAGER_ID_1,
DEPARTMENT_ID_1,
SALARY_NEW_1,
CHRIST_BONUS_1,
PHONE_NUMBER_1
from EMP_IDX_0, data ;
type tab is table of cur%rowtype;
t tab;
begin
dSTart := sysdate;
open cur;
fetch cur bulk collect into t;
close cur;
forall i in 1..t.count save exceptions
insert into emp_idx_25 values t(i);
dStop := sysdate;
dbms_output.put_line(round((dstop - dstart) * 24*60*60,2));
exception when bulk_errors then
dbms_output.put_line('ERROR');
dbms_output.put_line(round((nvl(dstop,sysdate) - dstart) * 24*60*60,2));
end;
/
set serveroutput on ;
declare
dStart date;
dSTop date;
begin
dSTart := sysdate;
FOR Z IN (with data as (select level lvl from dual connect by level <= 1000)
select rownum,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID,
SALARY_NEW,
CHRIST_BONUS,
EMPLOYEE_ID_1,
FIRST_NAME_1,
LAST_NAME_1,
EMAIL_1,
HIRE_DATE_1,
JOB_ID_1,
SALARY_1,
COMMISSION_PCT_1,
MANAGER_ID_1,
DEPARTMENT_ID_1,
SALARY_NEW_1,
CHRIST_BONUS_1,
PHONE_NUMBER_1
from EMP_IDX_0, data ) loop
insert into emp_idx_4 values z;
end loop;
dStop := sysdate;
dbms_output.put_line(round((dstop - dstart) * 24*60*60,2));
end;
/
set serveroutput on ;
declare
dStart date;
dSTop date;
begin
dSTart := sysdate;
FOR Z IN (with data as (select level lvl from dual connect by level <= 1000)
select rownum,
FIRST_NAME,
LAST_NAME,
EMAIL,
PHONE_NUMBER,
HIRE_DATE,
JOB_ID,
SALARY,
COMMISSION_PCT,
MANAGER_ID,
DEPARTMENT_ID,
SALARY_NEW,
CHRIST_BONUS,
EMPLOYEE_ID_1,
FIRST_NAME_1,
LAST_NAME_1,
EMAIL_1,
HIRE_DATE_1,
JOB_ID_1,
SALARY_1,
COMMISSION_PCT_1,
MANAGER_ID_1,
DEPARTMENT_ID_1,
SALARY_NEW_1,
CHRIST_BONUS_1,
PHONE_NUMBER_1
from EMP_IDX_0, data ) loop
insert into emp_idx_25 values z;
end loop;
dStop := sysdate;
dbms_output.put_line(round((dstop - dstart) * 24*60*60,2));
end;
/
Komentarze
Prześlij komentarz