Index (Chỉ mục) là một cấu trúc dữ liệu được sử dụng để tăng tốc độ truy cập dữ liệu trong cơ sở dữ liệu quan hệ. Index hoạt động giống như một mục lục cho một cuốn sách, giúp cơ sở dữ liệu nhanh chóng tìm kiếm các bản ghi cụ thể mà không cần phải quét qua toàn bộ bảng.
Xem thêm Index là gì: https://datalinks.vn/index-la-gi/
Quản trị index trong Oracle Database là một phần quan trọng của quản trị cơ sở dữ liệu, giúp tăng hiệu suất truy vấn bằng cách giảm thời gian truy cập dữ liệu. Dưới đây là các hoạt động quản trị chính liên quan đến index trong Oracle:
1. Tạo Index
Bạn có thể tạo index để cải thiện hiệu suất truy vấn trên các bảng. Dưới đây là các ví dụ về cách tạo index:
Tạo index đơn giản:
CREATE INDEX idx_employee_last_name ON employees (last_name);
Tạo index duy nhất:
CREATE UNIQUE INDEX idx_employee_email ON employees (email);
Tạo index composite (đa cột):
CREATE INDEX idx_employee_name ON employees (last_name, first_name);
Tạo index chức năng (functional index):
CREATE INDEX idx_employee_upper_last_name ON employees (UPPER(last_name));
Tạo index với ONLINE tạo xong index rồi mới sử dụng sau (tối ưu tránh treo database):
create index idx_table9 on table9(id) nologging parallel 10 tablespace DUONG_IDX online;
Tạo index cho bảng lớn ở trạng thái unusable ngay từ đầu, nếu không có unusable có thể treo db
Luôn tạo index cho bảng có partition ở dạng LOCAL để tránh index bị UNUSABLE khi dữ liệu thay đổi
create index idx_table9 on table9(id) nologging parallel 10 tablespace DUONG_IDX local unusable;
2. Xem Thông Tin Index
Để xem thông tin về các index trong cơ sở dữ liệu, bạn có thể sử dụng các view như DBA_INDEXES, USER_INDEXES, và ALL_INDEXES.
Xem tất cả index trong schema hiện tại:
SELECT index_name, table_name, uniqueness FROM user_indexes;
Xem chi tiết các cột của index:
SELECT index_name, column_name, column_position FROM user_ind_columns WHERE index_name = 'IDX_EMPLOYEE_LAST_NAME';
3. Xóa Index
Bạn có thể xóa index nếu không còn cần thiết hoặc để tối ưu hóa cơ sở dữ liệu.
DROP INDEX idx_employee_last_name;
4. Rebuild Index
Rebuild index giúp tái tổ chức và tối ưu hóa không gian sử dụng của index.
ALTER INDEX idx_employee_last_name REBUILD;
Nếu insert data lớn vào table => off index để insert data vào table nhanh hơn và tránh treo DB:
alter index idx_table9 unusable;
Rebuild index online sau khi insert data
Từ khoá online sẽ tiến hành làm chậm, từ từ tránh việc treo db
alter index idx_table9 rebuild online nologging parallel 10;
Di chuyển move index sang tablespace khác
Quá trình này sẽ tạo mới index trên TBS mới và sau đó xoá index trên tbs cũ
Dữ liệu truy vấn bình thường mà không ảnh hưởng gì
ALTER INDEX IDX_DUONG_TAB REBUILD TABLESPACE TBS_DATA_IDX;
5. Kiểm Tra Hiệu Suất Index
Sử dụng các công cụ như EXPLAIN PLAN và AUTOTRACE để kiểm tra hiệu suất của các index.
Sử dụng EXPLAIN PLAN:
EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_name = 'Smith'; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Sử dụng AUTOTRACE:
SET AUTOTRACE ON; SELECT * FROM employees WHERE last_name = 'Smith'; SET AUTOTRACE OFF;
6. Quản Lý Index Ẩn (Invisible Indexes)
Index ẩn không được sử dụng bởi optimizer nhưng vẫn được duy trì bởi cơ sở dữ liệu. Điều này hữu ích khi bạn muốn kiểm tra hiệu suất mà không cần xóa index.
Xem thêm về Invisible Indexes ở đây: https://datalinks.vn/invisible-indexes-la-gi/
Tạo index ẩn:
CREATE INDEX idx_employee_phone INVISIBLE ON employees (phone_number);
Làm cho index trở nên ẩn:
ALTER INDEX idx_employee_last_name INVISIBLE;
Làm cho index hiện:
ALTER INDEX idx_employee_last_name VISIBLE;
7. Quản Lý Index Partitioned
Index partitioned giúp cải thiện hiệu suất và quản lý các bảng lớn.
Tạo index partitioned:
CREATE INDEX idx_employee_dept ON employees (department_id) LOCAL;
8. Giám Sát và Duy Trì Index
Sử dụng các view như V$OBJECT_USAGE để giám sát việc sử dụng index và đảm bảo rằng các index không bị lãng phí.
Bật giám sát index:
ALTER INDEX idx_employee_last_name MONITORING USAGE;
Kiểm tra việc sử dụng index: (tần suất sử dụng index để xem có còn sử dụng hay không, nếu không dùng thì nên drop index đi)
SELECT index_name, table_name, used FROM v$object_usage WHERE index_name = 'IDX_EMPLOYEE_LAST_NAME';
Tắt giám sát index:
ALTER INDEX idx_employee_last_name NOMONITORING USAGE;
- Giám sát việc sử dụng index: Tính năng giám sát có thể tạo ra một chút overhead, nên chỉ bật giám sát khi cần thiết và tắt khi không cần thiết nữa.
- View V$OBJECT_USAGE: Chỉ hiển thị các index đã được giám sát. Nếu một index không được giám sát, nó sẽ không xuất hiện trong view này.
- Quyền hạn cần thiết: Để thực hiện các thao tác này, bạn cần có quyền ALTER trên index và quyền truy cập vào view V$OBJECT_USAGE.
Tóm lại
Quản trị index trong Oracle bao gồm việc tạo, xem thông tin, xóa, tái xây dựng, kiểm tra hiệu suất, quản lý index ẩn và index partitioned, và giám sát việc sử dụng index. Các thao tác này giúp đảm bảo rằng cơ sở dữ liệu hoạt động hiệu quả và tối ưu hóa truy vấn.