HỌC ORACLE DATABASE CƠ BẢN TỪ A-Z - BÀI 15: QUẢN LÝ CÁC INDEXES

15.1.PHÂN LOẠI INDEXES

Index là cấu trúc hình cây cho phép truy xuất trực tiếp một row trong table. Indexes có thể chia ra làm hai loại chính là logic và vật lý. Index theo kiểu logic dẫn xuất từ ứng dụng, còn index theo kiểu vật lý thì được phân chia theo cách thức mà index được lưu trữ.

15.1.1.  Index trên một column và Index trên nhiều columns

Một index trên một column thì chỉ có một column đó tham gia vào INDEX KEY. Ví dụ index trên  trường EMPNO của bảng EMP chỉ có một cột tham gia vào khoá của index.

Index trên nhiều columns hay còn gọi là index phức hợp, những indexes này được tạo thành từ nhiều columns trong table, các columns tạo thành index không cần phải ở cạnh nhau. Ví dụ index tạo nên từ hai cột DEPNO và JOB trong bảng EMP.

Số columns cực đại cho một index trên nhiều columns là 32. Tuy nhiên kích thước kết hợp của tất cả các columns không vượt quá 1/3 kích thước của một Block.

15.1.2.  Unique index và Non-unique index

Một unique index (index duy nhất) đảm bảo rằng không có hai rows nào thuộc table có cùng một giá trị trên các columns có trong index. Khoá của unique index chỉ có thể trỏ đến một row duy nhất trong table.

Khác với unique index, với non-unique index (index không duy nhất), một giá trị khoá của index sẽ tương ứng với một nhóm các rows.

15.1.3.  Partitioned index và non-partitioned index

Các table thông thường trong database đều thuộc loại non-partitioned table.

Partitioned index (index phân khu) dùng cho các table lớn, lưu các mục index (index entries) của index này có thể nằm trên nhiều segments.

Việc phân khu sẽ cho phép một index có thể trải rộng trên nhiều tablespaces, giảm bớt tình trạng quá tải khi index được truy xuất và quản lý.

Các partitioned index hay được sử dụng cùng với các partitioned table (bảng được phân khu) để tăng cường hiệu năng và dễ quản lý. Partitioned index sẽ được tạo ra ứng với mỗi partitioned table.

Tài liệu này đề cập tới hai loại index hay được sử dụng là B-TREE index và BIPMAP index.

15.2.TỔ CHỨC INDEX

15.2.1.  B-TREE index

Mặc dù hầu hết các indexes đều sử dụng B-TREE index, song thuật ngữ B-TREE thường sử dụng kết hợp với một index có lưu trữ danh sách ROWID tại mỗi khoá của index đó.

Cấu trúc của B_TREE

Đỉnh của index hay còn gọi là gốc (root). Gốc chứa các điểm vào (entry) trỏ đến mức tiếp theo của index. Ở mức tiếp theo là các block nhánh (branch). Block nhánh này lại trỏ đến các block tiếp theo của index. Ở mức thấp nhất là lá (leaf). Lá sẽ chứa thông tin điểm vào trỏ đến các rows trong table.

Hình vẽ 1.    B_TREE index

Các khối lá là kết nối kép thuận tiện cho việc truy xuất index trong trật tự giảm hay tăng của giá trị khoá.

Định dạng của lá index

Một điểm vào của index sẽ được tạo thành bởi các thành phần sau đây:

§  Entry header: thông tin lưu trữ số column và thông tin khoá của các hàng trong bảng.

§  Key column length_value pair: chứa thông tin về kích thước column tham gia vào khoá và tiếp theo là kích thước của đó.

§  ROWID: là giá trị của ROWID  chứa giá trị khoá của index.

Đặc tính của index leaf entry

Một B-TREE index trong một non-partitioned table:

§  Giá trị khoá bị lặp lại nếu như có nhiều hàng có cùng giá trị khoá.

§  Không có index entry tương ứng với các rows mà giá trị của tất cả các cột khoá đều bằng NULL.

§  ROWID được giới hạn sử dụng để trỏ đến các rows của table, bởi vì tất cả các rows đều thuộc về cùng một segment.

Ảnh hưởng của việc thực thi câu lệnh DML đối với Index.

Oracle server xem xét index của table mỗi khi có một câu lệnh DML thực thi trên table đó. Dưới đây là một số ảnh hưởng của câu lệnh DML đối với index:

§  Kết quả của câu lệnh insert (thêm mới) dòng dữ liệu sẽ chèn thêm một index entry vào index tương ứng.

§  Việc delete (xoá) các rows trong table sẽ dẫn tới việc xoá các index entries tương ứng trong block. Không gian sử dụng bởi các rows bị xoá sẽ không dùng được cho các entries (điểm vào mới) cho tới khi toàn bộ các entries (điểm vào) của block bi xoá.

§  Việc cập nhật các cột khoá là kết quả của quá trình delete hay insert. Giá trị PCTFREE không ảnh hưởng đến index ngoại trừ vào thời điểm tạo index. Một điểm vào mới có thể được thêm vào block của index ngay cả khi không đủ không gian chỉ định bởi PCTFREE.

15.2.2.  Reverse Key Index

Trái ngược với B-TREE index, reverse key index (khoá index ngược) sử dụng cấu trúc khoá index theo thứ tự các bytes ngược với thứ tự trong B-TREE (ngoại trừ ROWID). Tuy nhiên, trật tự của các columns trong khoá vẫn được giữ nguyên.

Hình vẽ 2.    Reverse Key Index

Khi chèn một bản ghi trong trật tự tăng dần của khoá, ví dụ như việc hệ thống sinh ra số empno cho bảng EMP, thì có thể xảy ra hiện tượng thắt cổ chai  trên các index vì tất cả các index được cập nhật xảy ra ở cùng một vị trí trong cây index, phương pháp dùng khoá ngược sẽ trải và phân tán các index cập nhật qua nhiều cây index.

Ví dụ: khi insert mã nhân viên 7698 vào  trong bảng, một khoá 8967 (khoá ngược với 7698) sẽ được lưu trong index, và nhân viên tiếp theo 7782 được nhập và thì một khoá 2877 (khoá ngược với 7782) sẽ được lưu vào của index. Vì vậy các mục (entries) của index được lưu trên trên nhiều bock khác nhau của index.

Như vậy việc sắp xếp đó có ý nghĩa đặc biệt trong việc tránh giảm hiệu năng trong index môi trường Oracle Paralell.

Index dùng khoá ngược hay được sử dụng cho các query mà có các giá trị giống nhau, bởi vì các khoá về mặt từ vựng cạnh nhau sẽ không được lưu trữ gần nhau khi sử dụng khoá ngược.

15.2.3.  Bitmap Index

Hình vẽ 3.    Bitmap index

Bitmap Index (Index theo kiểu ánh xạ bits) là một kiểu index hay được sử dụng trong một số trường hợp sau:

§  Khi table có nhiều rows và các cột khoá có giá trị khác nhau rất ít. Điều đó có nghĩa là có rất ít sự khác nhau trong giá trị của các cột. Ví dụ Bitmap Index thích hợp hơn đối với các cột giới tính (Nam hay Nữ).

§  Khi truy vấn có kết hợp sử dụng nhiều mệnh đề trong phần điều kiện WHERE. Mệnh đề truy vấn sử dụng các phép toán logic OR.

§  Khi các cột khoá là read-only (chỉ đọc) hay có rât ít hoạt động cập nhật các cột khoá.

Cấu trúc của Bitmap Index

Một Bitmap index cũng được tổ chức như là B-TREE index, nhưng phần lá của mỗi node lưu một dãy các bit cho mỗi khoá thay vì danh sách các ROWID. Mỗi bit trong danh sách Bitmap đó tương ứng với một ROWID, và nếu giá trị bit đó được khởi tạo, điều đó có nghĩa là hàng có ROWID tương ứng sẽ chứa giá trị khoá.

Sử dụng Bitmap index

Bitmap-TREE index sử dụng để thiết lập phần lá của các node, phần này sẽ chứa đoạn bitmap được sử dụng để xác định hàng chứa giá trị khoá.

Khi có thay đổi trên các cột khoá trong table, các chuỗi bitmap cần được thay đổi theo. Kết quả là sẽ sinh ra các khoá trên các bitmap segment liên quan do quá trình phân đoạn các khoá này đòi hỏi thực hiện trên toàn bộ bitmap segment. Một row quản lý bởi bitmap sẽ không thể cập nhật bởi các transaction khác đến khi transaction đầu kết thúc.

15.2.4.  So sánh giữa B-TREE index và Bitmap index

Bảng so sánh giữa B-TREE và Bitmap index

B-tree	Bitmap
Thích hợp với các cột dữ liệu trên tập giá trị lớn	Thích hợp với các cột dữ liệu trên tập giá trị nhỏ
Việc cập nhật dựa trên khoá quan hệ nên không đắt	Cập nhật dựa vào trường khoá nên khá đắt
Không hiệu quả cho các truy vấn có sử dụng mệnh đề OR	Hiệu quả cho các truy vấn có sử dụng mệnh đề OR
Hữu ích đối với OLTP (Online transsaction processing - dịch vụ xử lý trực tuyến)	Hữu ích đối với DSS (Decision support system - hệ thống hỗ trợ quyết định)

Bảng trên đây so sánh giữa B-TREE và Bitmap Index, Bitmap index được sử dụng nhiều hơn trong trường hợp các cột có giá trị khác nhau rất ít.

Việc cập nhật các cột làm khoá trong Bitmap index thì sẽ chậm hơn bởi vì Bitmap index sử dụng phương pháp khoá đoạn bitmap (bitmap segment level locking), trong khi đó trong một B-TREE index khoá thực hiện trên các điểm vào tương ứng với từng row riêng lẻ trên table.

Bitmap index có thể thực hiện các hoạt động với các toán hạng logic OR. Khi đó Oracle Server sử dụng hai phân đoạn bitmap để thực hiện việc so sánh từng bit trong toán hạng OR và trả về kết quả là một chuỗi Bitmap. Tính chất này cho phép sử dụng hiệu quả chuỗi Bitmap trong câu lệnh truy vấn có sử dụng toán hạng logic OR.

Nói chung B-TREE index thích hợp hơn trong môi trường OLTP cho việc truy vấn các bảng động. Trong khi đó, Bitmap index thích hợp hơn trong môi trường DSS có sử dụng nhiều câu lệnh truy vấn phức tạp trên các table lớn (large) và tĩnh (static).

15.3.QUẢN LÝ INDEX

15.3.1.  Tạo các index

Một index có thể tạo hoặc trên acount của user là owner của bảng hay tạo trên một acount khác.

Cú pháp:

CREATE [ UNIQUE ]INDEX [schema.] index

ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ NOSORT ]

Với:

UNIQUE                    được sử dụng chỉ định một unique index (non-unique index là mặc định).

schema                        là owner của bảng chứa index.

index               là tên của index.

table                là tên của bảng chứa index

column                        là tên cột dùng làm index

ASC/DESC                 được cung cấp để tương thích về cú pháp cho database khác.

TABLESPACE                      tên tablespace mà index sẽ được tạo trên đó

PCTFREE                   không gian dành riêng trong mỗi block, được sử dụng khi một có điểm vào mới của index (new entries) được tạo ra.

INITRANS                 chỉ định số giao dịch thiết lập ban đầu cho mỗi block

MAXTRANS             giới hạn số giao dịch có thể thiết lập cho mỗi block (giá trị mặc định là 255).

STORAGE                 tham số lưu trữ, quy định có bao nhiêu extents sẽ cấp phát cho index.

LOGGING                 chỉ định việc tạo các index và các hoạt đông tuần tự trên trên index sẽ được ghi vào trong các redo log file

NOLOGGING           chỉ định việc tạo và các hoạt động tuần tự trên index không được ghi vào các log file.

NOSORT                    chỉ định các row được lưu trong database theo trật tự tăng dấn và vì thế oracle server không cấn sắp xếp các hàng trong khi tạo index.

Ví dụ:

CREATE INDEX scott.emp_lname_idx

ON scott.employees(last_name)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx01;

Chú ý:

§  Nếu giá trị của MINIMUM EXTENT đã được định nghĩa cho tablespace, kích thước của extent dùng cho index sẽ được làm tròn lên thành một bội số lần giá trị của MINIMUM EXTENT.

§  Nếu mệnh đề [NO]LOGGING bị bỏ qua, thuộc tính log của index mặc định sẽ phụ thuộc vào thuộc tính log của tablespace mà index được tạo trên đó.

§  PCTUSED không được chỉ định cho index, vì các điểm vào index phải được lưu trữ theo một trật tự nhất định, người dùng không được phép điều khiển khi một index block được sử dụng cho việc insert.

§  Nếu từ khoá NOSORT được sử dụng khi dữ liệu không được săp xếp trong trường khoá thì câu lệnh sẽ kết thúc với một lỗi, lỗi này giống như việc thực hiện nhiều câu lệnh DML trên bảng.

§  Oracle server sẽ sử dụng index đang tồn tại để tạo bảng nếu có thể. Việc này xảy ra khi khoá cho một index mới tương ứng với phần đầu của khoá của một index đã tồn tại.

Các hướng dẫn khi tạo index:

Hãy xem xét các hướng dẫn 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 DML. Vì thế, cần giảm tối thiểu index trên các bảng hay xảy ra thay đổi.

§  Đặ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.

Để 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.

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 emp_i1 ON emp (emp_id);

Nếu câu lệnh: SELECT * FROM emp WHERE emp_id=100 and name='Binh';

Lệnh tạo index

CREATE INDEX emp_i1 ON emp (emp_id, name);

Nếu câu lệnh: SELECT * FROM emp WHERE UPPER(emp_name) LIKE 'JOH%';

Lệnh tạo index

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

2. 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);

3. 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'));

4. 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));

Index và giá trị PCTFREE

Tham số PCTFREE cho index làm việc khác với cho table. Tham số này được sử dụng chỉ trong quá trình tạo các index. Tham số này dành riêng không gian cho các điểm vào của idex. Các điểm vào của index sẽ không được cập nhật. Khi cột khoá được cập nhật, các điểm vào cũ sẽ được xoá và chèn vào đó một điểm vào mới. Sử dụng giá trị của tham số PCTFREE thấp cho index trên các cột mà giá trị của nó tăng đều đặn. Ví dụ như hệ thống sinh số hoá đơn. Khi này, điểm vào của index mới sẽ luôn luôn dựa vào điểm vào đã tồn tại trước đó và vì vậy không cần chèn thêm vào các điểm vào mới giữa hai điểm vào đã tồn tại.

Ở đây giá trị cho một cột  được index của hàng được chèn vào bảng có thể nhận bất cứ giá trị nào. Vì vậy giá trị mới có thể không nằm trong dãy giá trị đã có. Cho nên cần phải chỉ định một giá trị PTCFREE cao. Ví dụ tạo index trên trường mã khách hàng trên bảng hoá đơn. Trong trường hợp này cần sử dụng công thức tính sau để xác định giá trị của PCTFREE:

Giá trị cực đại có thể lấy trong khoảng thời gian cụ thể chẳng hạn như một năm.

15.3.2.  Một số cách tạo index

Tạo index một cách tường minh

Với cách này, người dùng chỉ việc tạo index một cách tường minh thông qua câu lệnh SQL.

Mệnh đề ON sẽ cho biết cột dữ liệu trong bảng được sử dụng index.

Ví dụ:

CREATE INDEX emp_ename ON emp(ename)

      TABLESPACE users

      STORAGE (INITIAL 20K

      NEXT 20k

      PCTINCREASE 75)

      PCTFREE 0;

Tạo Index được gắn liền ngay trong ràng buộc (constraint)

Khi tạo các ràng buộc UNIQUE hoặc PRIMARY KEY cho các cột dữ liệu trong table, Oracle sẽ tự động tạo ra các index tương ứng với cột dữ liệu này.

Tuy nhiên trong một số trường hợp, user sở hữu table muốn tạo các indexes cho table nằm trên một tablespace riêng để tiện cho việc quản trị. Khi này, việc tạo index cần được gắn liền một cách tường minh ngay trong câu lệnh tạo bảng.

Ví dụ:

CREATE TABLE emp (

     empno NUMBER(5) PRIMARY KEY, age INTEGER)

     ENABLE PRIMARY KEY USING INDEX

     TABLESPACE users

     PCTFREE 0;

Tạo Index Online

Thông thường trong khi tạo index, các câu lệnh DML tác động lên cột dữ liệu có liên quan đều tạm thời không thực hiện được cho đến khi hoàn tất việc tạo index.

Để có thể cho phép thực hiện câu lệnh DML tác động lên các cột dữ liệu được index, Oracle có hỗ trợ Index online (Index trực tuyến) khi này, ta cần bổ sung thêm mệnh đề ONLINE vào trong câu lệnh.

Ví dụ:

ALTER INDEX emp_name REBUILD ONLINE;

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;

Tạo Index theo giá trị hàm

Không chỉ cho phép thực hiện các index trực tiếp trên các cột dữ liệu trong table, Oracle còn cho phép thực hiện các index dựa vào giá trị của các hàm áp dụng trên các cột dữ liệu của table.

Ví dụ:

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

Sử dụng cho câu lệnh truy vấn:

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

Lưu ý: Loại Index này chỉ được sử dụng trong các phiên bản Oracle 8i trở lên. Để sử dụng được index này, ta cần phải thiết lập một số thông số khởi tạo trong parameter file:

§  QUERY_REWRITE_INTEGRITY đặt là TRUSTED

§  QUERY_REWRITE_ENABLED đặt là TRUE

§  COMPATIBLE phải được đặt là 8.1.0.0.0 hoặc lớn hơn

Để sử dụng được index này, table cần được phải được thực hiện cấu trúc (ANALYZE) sau khi đã tạo xong index.

15.3.3.  Tạo Index khoá ngược (reverse key index)

Ta có thể tạo các reverse key index bằng câu lện CREATE INDEX:

Cú pháp:

CREATE [ UNIQUE ]INDEX [schema.] index

ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

REVERSE

Ví dụ:

CREATE UNIQUE INDEX scott.ord_ord_no_idx

ON scott.ord(ord_no) REVERSE

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx01;

Câu lệnh này tạo index khoá ngược tương tự như cho index thông thường ngoại trừ việc thêm mệnh đề REVERSE.

Chú ý: từ khoá NOSORT không được sử dụng trong câu lệnh tạo index khoá ngược.

15.3.4.  Tạo Bitmap index

Cú pháp:

CREATE BITMAP INDEX [schema.] index

ON [schema.] table

(column [ ASC | DESC ] [ , column [ASC | DESC ] ] ...)

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ NOSORT ]

Ví dụ :

CREATE BITMAP INDEX scott.ord_region_id_idx

ON scott.ord(region_id)

PCTFREE 30

STORAGE(INITIAL 200K NEXT 200K

PCTINCREASE 0 MAXEXTENTS 50)

TABLESPACE indx01;

Chú ý: Bitmap index không thể là Unique.

Tham số khởi tạo CREATE_BITMAP_AREA_SIZE quyết định không gian sẽ được sử dụng cho việc lưu trữ các bitmap segment trong bộ nhớ, giá trị mặc định của nó là 8MB, một giá trị lớn hơn có thể làm cho việc tạo index nhanh hơn.

15.3.5.  Thay đổi tham số lưu trữ cho index

Một số tham số lưu trữ và tham số sử dụng block có thể thay đổi bằng cách sử dụng câu lệnh ALTER INDEX.

Cú pháp:

ALTER INDEX [schema.]index

[ storage-clause ]

[ INITRANS integer ]

[ MAXTRANS integer ]

Ví dụ:

ALTER INDEX scott.emp_lname_idx

STORAGE(NEXT 400K

MAXEXTENTS 100);

Ảnh hưởng của việc thay đổi tham số lưu trữ cho một index giống như việc thay đổi tham số cho bảng, cách sử dụng chung nhất của việc thay đổi tham số là tăng giá trị của MAXEXTENTS cho index.

15.3.6.  Cấp phát và thu hồi không gian sử dụng của index

Thiết lập không gian sử dụng bằng tay

Công việc này cần thiết khi thêm các extent cho một index trước khi một quá trình chèn một lượng lớn các hàng vào trong bảng . Thêm các extent bằng tay ngăn việc tự động thêm các extent của index.

Lấy lại không gian cấp phát cho index bằng tay

Sử dụng mệnh đề DEALLOCATE của câu lệnh ALTER INDEX để giải phóng không gian không được sử dụng ở trên mức High Water Mark trong một index.

Cú pháp:

ALTER INDEX [schema.]index

{ALLOCATE EXTENT ([SIZE integer [K|M]]

[ DATAFILE ‘filename’ ])

| DEALLOCATE UNUSED [KEEP integer [ K|M ] ] }

Ví dụ:

ALTER INDEX scott.ord_region_id_idx

ALLOCATE EXTENT (SIZE 200K

DATAFILE ‘/DISK6/indx01.dbf’);

ALTER INDEX scott.ord_ord_no_idx

DEALLOCATE UNUSED;

Chú ý:

Không gian index được thu hồi khi bảng trên đó index được xây dựng bị truncate(xoá). Khi thực hiện việc xoá bảng bằng lệnh Truncate thì các index trên bảng đó cũng bị xoá theo.

15.3.7.  Xây dựng lại (Rebuild) các index

Các index được xây dựng lại nhằm mục đích:

§  Một index mới được xây dựng trên cơ sở một index đã tồn tại .

§  Quá trình săp xếp không cần thiết khi một index được xây dựng trên một index đã tồn tại, kết quả là quá trình sẽ tạo ra hiệu năng cao hơn.

§  Index cũ bị xoá đi sau khi một index mới được tạo. Trong quá trình xây dựng lại Index không gian cần thiết là không gian cho cả index cũ và index mới khi được tạo thành.

§  Các truy vấn có thể tiếp tục sử dụng các index đang tồn tại trong khi các index mới đang được xây dựng.

Các tình huống có thể phải xây dựng lại index

§  Các index đang tồn tại cần được chuyển tới một tablespace mới,công việc này cần thiết khi các index ở trong cùng một  tablespace vì các bảng hay các object khác cần phân tán trên nhiều đĩa.

§  Một index chứa nhiều điểm vào bi xoá, hiện tượng này xảy ra với các index trượt, ví dụ như index trên trường số thứ tự đặt hàng của bảng đặt hàng. Khi các đơn đặt hàng đã hoàn thành sẽ bị xoá đi và một đơn đặt hàng mới được thêm vào trong bảng với số đặt hàng lớn hơn.

§  Một index thông thường cần chuyển đổi sang index với khoá ngược.

Cú pháp:

ALTER INDEX [schema.] index REBUILD

[ TABLESPACE tablespace ]

[ PCTFREE integer ]

[ INITRANS integer ]

[ MAXTRANS integer ]

[ storage-clause ]

[ LOGGING| NOLOGGING ]

[ REVERSE | NOREVERSE ]

Câu lệnh Rebuild index không thể dùng để chuyển đổi một Bitmap index thành một B-Tree và ngược lại. Các mệnh đề REVERSE và NOREVERSE chỉ sử  dụng với B-tree index.

Ví dụ:

ALTER INDEX scott.ord_region_id_idx

REBUILD

TABLESPACE indx02;

15.3.8.  Kiểm tra tính hợp lệ của index

Hình vẽ 4.    Kiểm tra tính hợp lệ của Index

Câu lệnh phân tích index thực hiện các công việc sau:

§  Kiểm tra tất cả index blocks và tìm xem có block hỏng không. Câu lệnh này không kiểm tra xem index có tương ứng với dữ liệu trong bảng hay không.

§  Thiết lập view INDEX_STATS với thông tin về index

Ở phiên bản Oracle 9i, ta có thể thực hiện lệnh ANALYZE VALIDATE STRUCTURE  để tối ưu ngay cả khi đang có lệnh DML thực hiện trên table.

Cú pháp :

ANALYZE INDEX [schema.]index VALIDATE STRUCTURE

Sau khi thực hiện câu lệnh ANALYZE INDEX truy vấn view INDEX_STATS để lấy thông tin về index như trong ví dụ dưới đây:

SVRMGR> SELECT blocks, pct_used, distinct_keys

2> lf_rows, del_lf_rows

3> FROM index_stats;

BLOCKS        PCT_USED    LF_ROWS      DEL_LF_ROWS

------                ---------            --------             ------------

25                    11                    14                    0

1 row selected.

Tổ chức lại index nếu nó có tỷ lệ các hàng bị xoá cao, ví dụ khi tỷ lệ DEL_LF_ROWS với LF_ROWS vượt quá 30% .

15.3.9.  Xoá các index

Một index cần đựơc xoá đi trong những trường hợp sau đây:

§  Một index không cấn thiết cho ứng dụng nữa.

§  Một index có thể được xoá đi khi thực hiện load nhiều dữ liệu, và tạo lại sau khi đã load xong dữ liệu.

§  Một index có thể được đánh dấu không hợp lệ (INVALID) khi có một instance hỏng trong quá trình nào đó ví dụ như load dữ liệu. Trong trường hợp đó index cần được xoá đi và tạo lại.

§  Index bị hỏng.

Cú pháp:

DROP INDEX [schema.]index;

15.4.THÔNG TIN VỀ CÁC INDEX

15.4.1.  Xem thông tin về các index

Data dictionary có các view dùng để xem thông tin về index, hai view thông dụng nhất hay sử dụng là DBA_INDEXES và DBA_IND_COLUMNS.

Hình vẽ 5.    Thông tin về Index

Sử dụng câu lệnh sau đây kiểm tra tên kiểu và trạng thái của index của user SCOTT:

SVRMGR> SELECT index_name, tablespace_name, index_type,

2> uniqueness, status

3> FROM dba_indexes

4> WHERE owner='SCOTT';

INDEX_NAME               TABLESPACE_NAME                        INDEX_TYPE UNIQUENES STATUS

------------                          ---------------                ----------              ---------            ------

EMP_LNAME_IDX        INDX01                      NORMAL          NONUNIQUE   VALID

ORD_ORD_NO_IDX      INDX01                      NORMAL          UNIQUE        VALID

ORD_REGION_ID_IDX INDX02                      BITMAP            NONUNIQUE   VALID

3 rows selected.

Cột INDEX_TYPE chỉ định index là Bitmap hay Normal, sử dụng câu lệnh sau liệt kê tên của tất cả các index khoá ngược:

SVRMGR> SELECT o.object_name

2> FROM dba_objects o

3> WHERE owner='SCOTT'

4>        AND o.object_id IN (SELECT i.obj#

5>        FROM ind$ i

6>        WHERE BITAND(i.property,4) = 4);

OBJECT_NAME

-----------------

ORD_ORD_NO_IDX

1 row selected.

15.4.2.  Tìm các cột trong một index

Câu lệnh truy vấn sau đây liệt kê các index của user SCOTT và chỉ ra các bảng và cột trên đó index được xây dựng:

SVRMGR> SELECT index_name, table_owner, table_name, column_name

2> FROM dba_ind_columns

3> WHERE index_owner = 'SCOTT'

4> ORDER BY index_name, column_position;

INDEX_NAME               TABLE_OWNER                                    TABLE_NAME                                           COLUMN_NAME

----------------- -------------                               ---------------                 ----------

EMP_LNAME_IDX        SCOTT                 EMP                            LAST_NAME

ORD_ORD_NO_IDX      SCOTT                 ORD                            ORD_NO

ORD_REGION_ID_IDX SCOTT                 ORD                            REGION_ID

3 rows selected.

=============================
Website không bao giờ chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn muốn tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ kinh nghiệm, thủ tục, quy trình, bí kíp thực chiến mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin1_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================

HỌC ORACLE DATABASE CƠ BẢN TỪ A-Z - BÀI 15: QUẢN LÝ CÁC INDEXES, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty