Sử dụng gợi ý trong Oracle

Các khái niệm cơ bản

• B-tree

  • Là một cấu trúc dữ liệu dạng cây cho phép tìm kiếm, truy vấn tuần tự, chèn, xóa trong thời gian logarit.
  • B-tree là tổng quát hóa của cây nhị phân tìm kiếm, trong đó một nút có thể có nhiều hơn hai con.
  • Số lần truy cập bộ nhớ trong mỗi thao tác trên B-tree tỉ lệ với chiều cao của cây. Một B-tree có n nút thì có chiều cao không quá loga m của n

• B+-tree, B*-tree

  • Trong B+-tree các nút trong lưu bản sao của khóa. Tất cả các khóa, cùng với dữ liệu đi kèm được lưu ở các nút lá.Ngoài ra các nút lá còn có con trỏ đến các nút là kế bên để tăng tốc truy cập tuần tự.
  • B*-tree được thực hiện nhiều thao tác tái cân bằng hơn để lưu trữ dữ liệu dày đặc hơn. Mỗi nút trong khác gốc phải đầy tới hai phần ba thay vì chiir một nửa như trong B+-tree.

• Ví dụ B+- tree

• Ví dụ B*-tree

• Hàm băm (Hash)

  • Là giải thuật nhằm sinh ra các giá trị băm tương ứng với mỗi khối dữ liệu.
  • Giá trị băm đóng vai trò gần như một khóa để phân biệt các khối dữ liệu.
  • Đầu vào của một hàm băm là một khối dữ liệu riêng biệt, nhưng đầu ra của hàm băm là một chuỗi các bit có độ dài cố định.
  • Ví dụ: Hàm băm MD4, MD5: 128 bit Hàm băm SHA-1: 160 bit Hàm băm SHA-2: 256 bit Hàm băm SHA-512: 512 bit
  • Tuy nhiên người ta chấp nhận hiện tượng trùng khóa hay còn gọi là đụng độ và cố gắng cải thiện giải thuật để giảm thiểu tối đa sự đụng độ đó.
  • Hàm băm thường được dùng trong bảng băm nhằm giảm chi phí tính toán khi tìm một khối dữ liệu trong một tập hợp ( nhờ việc so sánh các giá trị băm nhanh hơn việc so sánh các khối dữ liệu có kích thước lớn).

• Bitmap index

  • Lập chỉ mục đầy đủ của một bảng lớn với chỉ số B-tree truyền thống có thể gây tốn kém về không gian đĩa vì kích thước các chỉ số có thể lớn hơn gấp nhiều lần so với các dữ liệu trong bảng.
  • Chỉ số bitmap thường chỉ có kích thước bằng một phần nhỏ trong kích thước dữ liệu được lập chỉ mục trong bảng.
  • Chỉ số bitmap được thiết kế chủ yếu cho các ứng dụng kho dữ liệu mà người dùng truy vấn dữ liệu hơn là cập nhật nó. Chứ bitmap không thích hợp cho các ứng dụng OLTP với số lượng lớn các giao dịch đồng thời sửa đổi dữ liệu.

Tổng quan về gợi ý

• Sử dụng gợi ý là làm ảnh hưởng đến các quyết định của tối ưu hóa. Cung cấp cho chúng ta một cơ chế chỉ đạo để lựa chọn một kế hoạch thực hiện câu truy vấn dựa trên các tiêu chí cụ thể.
• Các dấu cộng (+) trong câu truy vấn nhằm giúp hệ thống hiểu đó là một danh sách các gợi ý. Các dấu cộng phải được thực hiện ngay sau các câu lệnh đầu tiên.
• Ví dụ

  SELECT /\*+  FULL(e) +\*/ employee_id, last_name
  FROM hr.employees e
  WHERE last_name LIKE 'le';
  

Các loại gợi ý

• Single-table: gợi ý đơn bảng được chỉ định trên một bảng hoặc view. INDEX và USE_NL là ví dụ về những gợi ý đơn bảng.
• Multitable: gợi ý multitable giống như gợi ý đơn bảng, ngoại trừ những gợi ý có thể chỉ định một hoặc nhiều bảng hoặc khung nhìn. LEADING là một ví dụ về gợi ý multitable.
• Query block: chặn truy vấn gợi ý hoạt động trên các khối truy vấn đơn. STAR_TRANFORMATION và UNNEST là những ví dụ của các khối truy vấn gợi ý.
• Statement: gợi ý được áp dụng cho toàn bộ câu lệnh SQL. ALL_ROWS là một ví dụ cho gợi ý này.

Cấu trúc gợi ý

Sử dụng gợi ý

• Optimation Goals And Approaches
  • FIRST_ROWS Hint
    • Sử dụng phương pháp tối ưu hóa để tối ưu các khối lệnh và trả về kết quả với thời gian đáp ứng là tốt nhất.

      SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
      FROM employees
      WHERE department_id = 20; 
      

      Trong ví dụ trên thì mỗi văn phòng có chứa nhiều nhân viên, nhưng người dùng muốn lấy ra 10 nhân viên đầu tiên của văn phòng có ID bằng 20 sẽ được hiển thị càng nhanh càng tốt.
  • ALL_ROWS Hint
• Hints for Access Paths
  • FULL Hint
    • Các gợi ý FULL yêu cầu bộ tối ưu thực hiện một phương án quét toàn bộ bảng quy định.

    SELECT /*+ FULL(e) */ employee_id, last_name
    FROM hr.employees e
    WHERE last_name LIKE ‘b1’;
    

    • Cơ sở dữ liệu Oracle thực hiện một phương án quét toàn bộ trên bảng nhân viên để thực hiện câu lệnh này.
    • Bảng nhân vien có bí danh là e trong mệnh đề FROM, vì vậy những gợi ý phải tham khảo bằng bí danh của nó chứ không theo tên của bảng ban đầu. Không nên dùng tên của lược đồ trong các gợi ý ngay cả khi chúng được trong mệnh đề FROM.
  • CLUSTER Hint
  • HASH Hint
  • INDEX Hint
  • NO_INDEX Hint
  • INDEX_ASC Hint
  • INDEX_DESC Hint
• Hints for Query Tranformation
  • NO_QUERY_TRANFORMATION Hint
  • USE_CONTACT Hint
  • NO_EXPAND Hint
  • NO_REWRITE Hint
  • MERGE Hint
  • NO_MERGE Hint
  • STAR_TRANSFORMATION Hint
  • NO_STAR_TRANSFORMATION Hint
  • FACT Hint
• Hints for Join Orders
  • LEADING Hint
  • ORDERED Hint
• Hints for Join Operations
  • USE_NL Hint
  • NO_USE_NL Hint
  • USE_MERGE Hint
  • NO_USE_MERGE Hint
  • USE_HASH Hint
    • Các hệ tối ưu hash chỉ thị bộ tối ưu để tham gia mỗi bảng quy đinh với một nguồn bằng một hàm băm tham gia

    SELECT /*+ USE_HASH(l h) */ *
    FROM orders h, order_items l
    WHERE l.order_id = h.order_id
    AND l.order_id > 2400
    

Tài liệu tham khảo

• 17Optimizer Hints