DB7: Tìm kiếm trong khoảng (Searching for Ranges)

Tham khảo:use-the-index-luke
- Các toán tử như: lớn hơn (>), bé hơn (<) hay between có thể sử dụng index như là toán tử bằng (=)
- Ngay cả toán tử LIKE, trong một số case nhất định thì vẫn có thể sử dụng đc index như các toán tử trên.
- Việc sd các toán tử này sẽ giới hạn các việc order các cột trong trường hợp index trên nhiều cột.

1. Greater (>), Less (<) and BETWEEN
- yếu tố a/h lớn nhất đến hiệu suất của INDEX RANGE SCAN là việc duyệt Leaf Node leaf node traversal
Do vậy mà quy tắc vàng khi đánh index đó là: giữ cho INDEX RANGE SCAN càng nhỏ càng tốt.
Vậy việc scan index: start và end ở đâu?
-> Câu trả lời rất dễ nếu câu lệnh SQL đề cập đến điều kiện bắt đầu và điều kiện dừng một cách rõ ràng, ví dụ như sau:

	
    	SELECT first_name, last_name, date_of_birth
        FROM employees
       	WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
        AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')
    

nhìn ví dụ trên ta thấy, index chỉ scan trong 1 khoảng nhất định
Nếu chúng ta thêm 1 điều kiện where nữa, ví dụ như sau:

	
    	SELECT first_name, last_name, date_of_birth
        FROM employees
       	WHERE date_of_birth >= TO_DATE(?, 'YYYY-MM-DD')
        AND date_of_birth <= TO_DATE(?, 'YYYY-MM-DD')
        AND subsidiary_id  = ?
    

khi đó thì index range scan sẽ ko còn rõ ràng nữa.
Tất nhiên 1 index lý tưởng sẽ đánh trên cả 2 cột này, nhưng câu hỏi đặt ra là, đánh theo thứ tự như nào? cột nào trước - cột nào sau?

+ case 1: date_of_birth trước, subsidiary_id sau

+ case 2: subsidiary_id trước, date_of_birth sau

-> Để tăng hiệu năng thì nên đánh thứ tự index như sau: index for equality first—then for ranges.
Tuy nhiên, nếu range của "date_of_birth" ko lớn thì sự khác biệt về hiệu năng giữa 2 case trên cũng ko đáng kể.
2. Indexing SQL LIKE Filters
- cần lưu ý khi sd toán tử LIKE vì nó rất a/h đến hiệu năng.
- tiền tố trước % càng nhiều thì hiệu năng càng cao

-> chú ý: chỉ những ký tự đứng trước % thì mới serves as an access predicate, ký tự đằng sau % ko có ý nghĩa
-> Tip: nên tránh dùng like with leading wildcards (e.g., '%TERM').
- các database hẩu như giả thuyết là ko có case leading wildcard ở trên
nhưng nếu full text search thì nên làm xáo trộn như sau:

	WHERE text_column LIKE '%' || ? || '%'

-> để tránh sql injection
chú ý: Đối với cơ sở dữ liệu PostgreSQL, vấn đề lại khác vì PostgreSQL giả sử có một thẻ đại diện đứng đầu khi sử dụng các tham số liên kết cho một biểu thức LIKE
-> với oracle bạn có thể sd: contains thay vì LIKE
??? hãy nghĩ xem làm sao để đánh index trong case này: '%TERM'???
3. Index Combine (Index Merge)
Câu hỏi phổ biến: với mệnh đề where, tạo cho mỗi column 1 index hay 1 index cho nhiều column, cái nào hiệu quả hơn?
trong hầu hết trường hợp thì câu trả lời là: một index với nhiều column thì tốt hơn
Tuy nhiên với câu query sau thì ngược lại:

	
    	SELECT first_name, last_name, date_of_birth 
  			FROM employees
 				WHERE UPPER(last_name) < ? 
   					AND date_of_birth    < ?
   

-> One index scan is faster than two.

DB6: index bị chậm - P2

Tham khảo:use-the-index-luke
- The Query Optimizer
. The query optimizer, or query planner, is the database component that transforms an SQL statement into an execution plan
. This process is also called compiling or parsing
There are two distinct optimizer types:
1. Cost-based optimizers (CBO):
tạo ra nhiều execution plan khác nhau và tính toán chi phí cho mỗi execution plan, nó được ước tính dựa vào số thao tác cần xử lý và số lượng bản ghi. Chi phí này được dùng để chọn execution plan tốt nhất.
2. Rule-based optimizers (RBO):
tạo ra execution plan dựa vào một tập hợp rule cố định,RBO kém linh hoạt hơn và hiện tại ít được sử dụng.

DB5: Mệnh đề where - The Equality Operator (toán tử equal)

Tham khảo: use-the-index-luke
- Primary Keys
Employees table

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE           NOT NULL,
   phone_number  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)

ví dụ câu select sau lấy ra tên nhân viên

SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123

vì primary key là duy nhất nên DB ko cần phải tìm ở Leaf Node, chỉ cần duyệt index tree (INDEX UNIQUE SCAN )
sau khi tìm ra index thì DB cần làm 1 step nữa để lấy data ra
đó là thao tác: TABLE ACCESS BY INDEX ROWID
v/đ performance chậm có thể xảy ra ở bước này
Primary Keys without Unique Index
1 khóa chính ko nhất thiết phải là 1 index duy nhất (unique index)
bạn cũng có thể sd 1 non-unique index
trong case này thì Oracle DB sẽ ko sd INDEX UNIQUE SCAN thay vào đó nó sẽ dùng INDEX RANGE SCAN
một lý do cho việc sd non-unique index cho 1 primary key là Deferrable Constraints (các ràng buộc bị hoãn (bị trễ))
các Deferrable Constraints sẽ đc validate sau khi transaction commit, các ràng buộc thông thường sẽ validate luôn trong quá trình thực thi câu query.
Deferred constraints đc y/c for inserting data into tables with circular dependencies (các phụ thuộc vòng tròn).
- Concatenated Indexes
nếu table có nhiều primary key, thì DB cũng sẽ tự tạo index cho các column này
note: thứ tự index's column a/h rất lớn đến hiệu năng
ex: case sáp nhập cty, Cty tổng có bảng nhân viên với employeeID là primary key,
giờ sáp nhập thêm 1 cty con vào thì ID của nhân viên lúc này sẽ duplicate
-> ta cần thêm 1 cột ID nữa để phân biệt, subEmployeeID chẳng hạn
lúc này query:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id   = 123
   AND subsidiary_id = 30

-> DB vẫn là: INDEX UNIQUE SCAN, bất kể có bao nhiêu cột đc đánh index.
Nhưng nếu:

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20

Thì mọi chuyện lại khác: case này thì DB sẽ thực thi: TABLE ACCESS FULL
DB xét mỗi column dựa vào thứ tự của nó để sort the index entries,
column đầu tiên là tiêu chí sắp xếp chính, và column thứ hai chỉ xác định thứ tự if two entries have the same value in the first column and so on. -> A concatenated index is one index across multiple columns.
- Trong thực tế việc chọn index này trường nào nên trước nên được đánh giá cẩn thận để chọn được đúng thứ tự mà hay được sử dụng nhất
- The fewer indexes a table has, the better the insert, delete and update performance.