Đã đăng vào Thứ Hai, 4:05 SA 6 phút đọc

Hiểu bản chất histogram trong oracle database

1. Mở đầu

Histogram đóng vai trò như một công cụ mạnh mẽ giúp Oracle hiểu rõ hơn về cách phân phối dữ liệu. Bằng cách cung cấp cái nhìn chi tiết về các giá trị trong các cột, histogram cho phép cost-based optimizer đưa ra quyết định tốt hơn, từ đó tối ưu hóa hiệu suất truy vấn.

2. Nguyên lý hoạt động

Histogram ghi lại sự phân bố của dữ liệu khi chúng ta analyze một bảng hoặc index. Với thông tin này, cost-based optimizer có thể quyết định sử dụng index cho các điều kiện mà nó biết sẽ trả về một số lượng ít các hàng, và bỏ qua index khi điều kiện giới hạn sẽ trả về nhiều hàng. Việc sử dụng histogram không chỉ giới hạn cho các index. Bất kỳ cột nào của một bảng đều có thể xây dựng histogram trên đó.

Lý do chính để tạo ra các histogram là để giúp trình tối ưu hóa lập kế hoạch đúng đắn nếu dữ liệu trong một bảng bị lệch nhiều. Ví dụ, nếu một hoặc hai giá trị chiếm phần lớn tỷ lệ của bảng, các index liên quan có thể không giúp giảm số lượng I/O cần thiết để xử lý truy vấn. Việc tạo ra một histogram cho phép cost-based optimizer biết khi nào việc sử dụng index là phù hợp, hoặc khi 80% bảng sẽ được trả về do giá trị trong mệnh đề WHERE.

Khi tạo histogram, chúng ta cần chỉ định kích thước cụ thể. Kích thước này liên quan đến số lượng bucket cho histogram. Mỗi bucket sẽ chứa thông tin về giá trị của cột và số lượng hàng.

Truy vấn trên tạo ra một histogram gồm mười bucket trên bảng COMPANY, như được minh họa trong hình bên dưới. Các giá trị của cột COMPANY_CODE được chia vào mười bucket như hình. Ví dụ này cho thấy một số lượng lớn (80%) giá trị của COMPANY_CODE bằng 1430. Như được thể hiện trong hình, hầu hết các bucket có Width Balanced (cân bằng theo chiều rộng) chứa chỉ 3 hàng, trong khi một bucket chứa đến 73 hàng. Trong Height Balanced (cân bằng theo chiều cao) của phân phối này, mỗi bucket có cùng số lượng hàng và phần lớn các điểm kết thúc của bucket đều là 1430, phản ánh sự phân bố không đồng đều của dữ liệu.

Histograms của Oracle được cân bằng theo chiều cao thay vì cân bằng theo chiều rộng. Do đó, tất cả các bucket trong histogram chứa cùng một số lượng hàng. Điểm bắt đầu và kết thúc của một bucket được xác định bởi số lượng hàng chứa các giá trị đó. Histogram cân bằng theo chiều rộng xác định các giá trị phạm vi cho mỗi bucket, sau đó đếm số lượng hàng trong phạm vi đó, nhưng đây không phải là một tùy chọn lý tưởng.

3. Một số ví dụ thực tế:

Cột chứa dữ liệu phân bố không đồng đều

Giả sử bạn có một bảng ORDERS chứa thông tin về các đơn đặt hàng và cột ORDER_STATUS chỉ ra trạng thái của từng đơn hàng. Nếu trạng thái "Delivered" chiếm 90% dữ liệu và các trạng thái còn lại chỉ chiếm 10%, thì việc sử dụng histograms có thể giúp Oracle tối ưu hóa truy vấn tốt hơn.

Truy vấn cột có giá trị thường xuyên lặp lại

Nếu bạn có bảng CUSTOMERS với cột REGION chứa các khu vực địa lý của khách hàng, và một số khu vực có nhiều khách hàng hơn hẳn, histogram có thể cải thiện cách Oracle tối ưu hóa các truy vấn. Nếu một khu vực chứa 80% khách hàng, Oracle có thể sử dụng histogram để chọn một kế hoạch truy vấn khác (ví dụ: dùng full table scan thay vì index scan) khi lọc dữ liệu.

Truy vấn với các cột có dữ liệu phân bố khá đồng đều

Nếu cột AGE trong bảng EMPLOYEES chứa độ tuổi của nhân viên và phân phối dữ liệu khá đồng đều, việc tạo histograms trên cột này sẽ không mang lại nhiều lợi ích về hiệu suất.Ở đây, không tạo histogram vì dữ liệu phân phối đều, Oracle sẽ không thu được thêm thông tin nào hữu ích.

Histogram trong các truy vấn với nhiều giá trị DISTINCT

Cột ZIP_CODE trong bảng ADDRESSES có nhiều giá trị khác nhau, nhưng một số mã bưu điện có nhiều bản ghi hơn những mã khác. Histogram sẽ giúp Oracle hiểu rõ hơn về phân phối của các giá trị và chọn kế hoạch truy vấn phù hợp hơn.

TIP

Nếu dữ liệu trong một bảng có dữ liệu phân bố không đồng đều, histograms sẽ giúp cost-based optimizer có cái nhìn cân đối hơn về sự phân phối dữ liệu (bằng cách chia dữ liệu thành các bucket). Tuy nhiên, việc áp dụng histograms trên các cột có dữ liệu khá đồng đều sẽ không mang lại lợi ích về hiệu suất.

Theo mặc định, Oracle tạo 254 bucket trong một histogram. Chúng ta có thể chỉ định các giá trị SIZE từ 1 đến 254.