Khi nào cần tạo index trong cơ sở dữ liệu Oracle cũng như cơ sở dữ liệu khác?

1. Khi nào cần tạo index

Để phát huy hiệu quả của việc sử dụng index, ta tạo index cho từng cột hoặc nhóm cột tham gia trong mệnh đề WHERE của câu lệnh truy vấn (chú ý điều kiện WHERE này phải hay dùng, ít dùng thì không nên tạo vì sẽ làm chậm câu lệnh DML và tốn dung lượng lưu trữ)

Ví dụ:

1. Tạo index tăng tốc độ truy vấn tên nhân viên:

SELECT * FROM emp WHERE emp_id=100;

Lệnh tạo index

CREATE INDEX idx ON emp (emp_id) tablespace INDX;

Nếu câu lệnh

SELECT * FROM emp WHERE emp_id=100, city_id=10;

Lệnh tạo index 2 cột emp_id, city_id:

CREATE INDEX idx ON emp (emp_id,city_id) tablespace  INDX;

2. Tạo index tăng tốc độ truy vấn tên nhân viên chữ hoa:

SELECT * FROM emp WHERE UPPER(emp_name) LIKE 'JOH%';

Lệnh tạo index

CREATE INDEX idx ON emp (UPPER(emp_name)) tablespace INDX;

3. Với câu lệnh truy vấn trên biểu thức:

SELECT a FROM t WHERE a + b * (c - 1) < 100;

Lệnh tạo index

CREATE INDEX idx ON t (a + b * (c - 1), a, b) tablespace  INDX;

4. Tạo index hỗ trợ sắp xếp chuỗi ký tự dựa trên ngôn ngữ của từng quốc gia:

SELECT * FROM t_table ORDER BY name;

Lệnh tạo index

CREATE INDEX nls_index

ON t_table NLSSORT(name, 'NLS_SORT = German')) tablespace   INDX;

5. Sử dụng index trên nhiều cột khác nhau:

SELECT * FROM emp

WHERE UPPER(emp_name) LIKE 'JOH%'

ORDER BY name;

Lệnh tạo index

CREATE INDEX empi

ON emp UPPER ((ename), NLSSORT(ename)) tablespace   INDX;

Đây là khuyến cáo của Oracle cũng như mọi cơ sở dữ liệu khác:

Các lưu ý khi tạo index:

• Index làm tăng tốc độ của các câu lệnh truy vấn nhưng làm chậm tốc độ của các câu lệnh Insert. Vì thế, cần giảm tối thiểu index trên các bảng hay xảy ra thay đổi dữ liệu cột đánh Index.
• Đặt index trong các tablespace riêng biệt, không đặt index trong tablespace chứa rollback segment, temporary segment và table.
• Để làm giảm sự phân mảnh trong các tablespace dùng chứa index sử dụng chuẩn kích thước extent là bội số của 5*DB_BLOCK_SIZE.
• Hiệu năng có thể tăng lên nếu không sử dụng mệnh đề LOGGING. Vì vậy, cần xem xét khi sử dụng mệnh đề LOGGING khi tạo các index lớn.
• Vì các điểm vào cho index là nhỏ hơn đối với các rows được index nên index block sẽ có nhiều điểm vào cho một block. Vì vậy, giá trị của INITRANS đối với index nói chung nên đặt lớn hơn là giá trị của tham số này trên table sử dụng index.

2. KHÔNG PHẢI LÚC NÀO CŨNG TẠO INDEX VÌ ĐÔI LÚC QUÉT FULL NHANH HƠN INDEX KHI DỮ LIỆU TRẢ VỀ  NHIỀU (>4%)

Index chậm hơn quét full bảng, chỉ hiệu quả khi lượng dữ liệu trả về < 4%

Type	Số bản ghi	%/Tổng	FULL	Dùng index I7
0	45304503	15.26	Nhanh hơn
1	63454748	21.37	Nhanh hơn
2	123116203	41.46	Nhanh hơn
3	760951	0.26		Nhanh hơn
4	745957	0.25		Nhanh hơn
5	1917303	0.65		Nhanh hơn
6	442206	0.15		Nhanh hơn
7	3124481	1.05		Nhanh hơn
8	34567	0.01		Nhanh hơn
1	6	0.00		Nhanh hơn
10	464908	0.16		Nhanh hơn
11	56955192	19.18	Nhanh hơn
55	596007	0.20		Nhanh hơn
Tổng	296.917.032

select type, count(*) from test_table group by type;
(có 1 INDEX theo trường TYPE test_table_I7)

--Plan: dùng index

--SELECT STATEMENT  ALL_ROWSCost: 51,365  Bytes: 3  Cardinality: 1             

--    3 SORT AGGREGATE  Bytes: 3  Cardinality: 1         

--        2 PARTITION RANGE ALL  Cost: 51,365  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 - #208   

--            1 INDEX STORAGE FAST FULL SCAN INDEX test_table_I7 Cost: 51,365  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 - #208

--36.766s khi dùng index test_table_I7

select count(*) from test_table where type not in  ('10', '11', '55');

--Plan: Quét full

--SELECT STATEMENT  ALL_ROWSCost: 433,385  Bytes: 3  Cardinality: 1             

--    3 SORT AGGREGATE  Bytes: 3  Cardinality: 1         

--        2 PARTITION RANGE ALL  Cost: 433,385  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 - #208   

--            1 TABLE ACCESS STORAGE FULL TABLE test_table Cost: 433,385  Bytes: 238,824,735  Cardinality: 79,608,245  Partition #: 2  Partitions accessed #1 - #208

--13.81s khi quét FULL bảng

select  /*+ FULL(a)*/   count(*) from test_table a where type not in  ('10', '11', '55');

Đã thử nghiệm và kết quả như trên: Với dữ liệu trả về < 1% thì dùng index nhanh hơn, dữ liệu chiếm nhiều như  15,19,21,41% như trên dùng full nhanh hơn index

--> Dữ liệu trả về (select * from table_a where …) <4% thì quyết định tạo index và thường xuyên sử dụng (không thường xuyên sử dụng thì tạo, chạy xong là xóa)