DB 10: Execution Plans

Trước khi DB thực thi 1 câu lệnh sql, thì optimizer phải tạo 1 Execution Plans cho câu lệnh sql đó.
Sau khi có Execution Plans thì DB sẽ thực thi Execution Plans này step by step.
-> vì vậy, để tìm nguyên nhân câu query bị chậm thì Execution Plans là nơi cần xem đầu tiên.
Oracle DB:
- EXPLAIN PLAN FOR select * from dual
- "EXPLAIN PLAN FOR" ko show execution plan mà save vào bảng PLAN_TABLE
- select * from table(dbms_xplan.display) - câu lệnh này sẽ show execution plan cho câu query vừa thực thi ở trên


- Phân tích các operations:
1. Index and Table Access
1.1. INDEX UNIQUE SCAN
sẽ chỉ thực thi duyệt B-tree. DB sẽ thực thi operation này nếu một ràng buộc duy nhất đảm bảo rằng các tiêu chí tìm kiếm sẽ không khớp với nhiều hơn một entry.
1.2. INDEX RANGE SCAN
DB sẽ thực thi duyệt B-tree và follows the leaf node chain to find all matching entries
1.3. INDEX FULL SCAN
Reads the entire index—all rows—in index order
1.4. INDEX FAST FULL SCAN
Reads the entire index—all rows—as stored on the disk, This operation is typically performed instead of a full table scan if all required columns are available in the index. Similar to TABLE ACCESS FULL, the INDEX FAST FULL SCAN can benefit from multi-block read operations
1.5. TABLE ACCESS BY INDEX ROWID
Retrieves a row from the table using the ROWID retrieved from the preceding index lookup
1.6. TABLE ACCESS FULL
Reads the entire table—all rows and columns—as stored on the disk


2. Join
2.1. NESTED LOOPS JOIN
2.2. HASH JOIN
2.3. MERGE JOIN


3. Sorting and Grouping
3.1. SORT ORDER BY
3.2. SORT ORDER BY STOPKEY
3.3. SORT GROUP BY
3.4. SORT GROUP BY NOSORT
3.5. HASH GROUP BY


4. Top-N Queries
4.1. COUNT STOPKEY
4.2. WINDOW NOSORT STOPKEY

DB9: NULL in the Oracle Database

1. Indexing NULL

[The OAuth 2.1 Authorization Framework] - Giới thiệu

Mô hình xác thực truyền thống Client-Server:
client gửi y/c truy cập vào protected resource trên server bằng cách sd the resource owner's credentials
Để mà có thể cho phép bên thứ 3 truy cập vào resource của mình thì server phải share credential của mình, điều này sẽ gây ra vài vấn đề và hạn chế:
- ứng dụng bên thứ 3 phải lưu trữ credential của resource server để sd trong tương lai, thường là clear text password .
- server phải support việc xác thực mật khẩu.
- ứng dụng bên thứ 3 thoải mái truy cập vào protected resources.
- resource owners ko thể thu hồi quyền truy cập của 1 bên thứ 3 riêng lẻ mà phải thu hồi quyền truy cập đối với all bên thứ 3 (bằng cách change password).

Với OAuth, client y/c truy cập resource đc hosted bởi resource server. thay vì sd resource owner's credentials để truy cập protected resources, thì client nhận 1 access token - là 1 credential biểu diễn 1 set các thuộc tính truy cập như là: scope, lifetime.

1. Roles
OAuth định nghĩa 4 roles:
- resource owner - là 1 thực thể có thể gán quyền truy cập protected resource. (RO)
- resource server- là server mà hosted cái protected resources. (RS)
- client - là ứng dụng truy vấn protected resource
- authorization server - là server issuing (phát hành) access token tới client (AS)
-> resource server và authorization server có thể cùng run trên 1 con server or tách ra.
1 authorization server có thể issue (phát hành) access token đc chấp nhận bởi nhiều resource server.

2. Protocol Flow

	+--------+                               +---------------+
        |        |--(1)- Authorization Request ->|   Resource    |
        |        |                               |     Owner     |
        |        |<-(2)-- Authorization Grant ---|               |
        |        |                               +---------------+
        |        |
        |        |                               +---------------+
        |        |--(3)-- Authorization Grant -->| Authorization |
        | Client |                               |     Server    |
        |        |<-(4)----- Access Token -------|               |
        |        |                               +---------------+
        |        |
        |        |                               +---------------+
        |        |--(5)----- Access Token ------>|    Resource   |
        |        |                               |     Server    |
        |        |<-(6)--- Protected Resource ---|               |
        +--------+                               +---------------+
 

Refresh Token - issue bởi authorization server: là cái credentials dùng để xin cấp lại access token khi mà access token hết hạn.
Nếu mà authorization server phát hành refresh token thì nó sẽ đc phát hành cùng với access token.
ko giống như access token, refresh token chỉ dùng với authorization server, và sẽ ko bao giờ gửi đến resource server.

 
  +--------+                                           +---------------+
  |        |--(1)------- Authorization Grant --------->|               |
  |        |                                           |               |
  |        |<-(2)----------- Access Token -------------|               |
  |        |               & Refresh Token             |               |
  |        |                                           |               |
  |        |                            +----------+   |               |
  |        |--(3)---- Access Token ---->|          |   |               |
  |        |                            |          |   |               |
  |        |<-(4)- Protected Resource --| Resource |   | Authorization |
  | Client |                            |  Server  |   |     Server    |
  |        |--(5)---- Access Token ---->|          |   |               |
  |        |                            |          |   |               |
  |        |<-(6)- Invalid Token Error -|          |   |               |
  |        |                            +----------+   |               |
  |        |                                           |               |
  |        |--(7)----------- Refresh Token ----------->|               |
  |        |                                           |               |
  |        |<-(8)----------- Access Token -------------|               |
  +--------+           & Optional Refresh Token        +---------------+

 
 

Access Token - là credentials sd để truy cập protected resources.

DB8: Partial Indexes (index 1 phần)

Nguồn: use-the-index-luke
Partial index hữu ích trong case sd phổ biến là điều kiện where là giá trị hằng số (kiểu như status code)

  	
    	SELECT message
        FROM messages
        WHERE processed = 'N'
        AND receiver  = ?
    
  

có thể tối ưu câu query trên bằng a two-column index như sau:
thứ tự cột đánh index trong case này ko quan trọng vì ko có điều kiện range condition.

  	
    	CREATE INDEX messages_todo
          ON messages (receiver, processed)
    
  

với cách đánh index ở trên thì index đã làm rất tốt vai trò của mình, tốc độ search sẽ rất nhanh nhưng cũng khá là tốn dung lượng ổ cứng
tại sao: tại vì có rất nhiều row sẽ ko bao giờ cần tìm (những row mà message đã processed - processed = 'Y')
-> cần dùng partial index - với partial index thì index có thể chỉ chứa những tin nhắn chưa xử lý (unprocessed messages)
cú pháp đánh index 1 phần:

  	
    	CREATE INDEX messages_todo
          ON messages (receiver)
       		WHERE processed = 'N'
    
  

bạn thấy là bây giờ ko cần đánh index trên cột "processed" luôn, vì sao?
vì là index này: messages_todo đánh lên cột receiver và luôn có processed = 'N'
-> bằng việc làm như vậy thì index bây giờ giảm size đc cả chiều dọc và chiều ngang (số row ít hơn, số column cũng ít hơn)

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.