1. Tối ưu thiết kế SQL
- Dánh partition những table có dữ liệu lớn(Từ 2G trở lên)
+ Với dữ liệu lịch sử thì đánh theo By Range;
+ Với dữ liệu xác định trước được giá trị thì đánh theo By list;
+ Với dữ liệu không có quy luật thì đánh theo By Hash.
- Đánh index những trường hay tìm kiếm(Ưu tiên number)
- Đối với những table có dữ liệu lớn:
+ Hạn chế dùng khóa ngoại
+ Hạn chế dùng Trigger
+ Nếu có tần suất update & insert lớn không nên dùng khóa chính.
2. Tối ưu câu lệnh truy vấn và tác động vào các bảng đã đánh partition, indexing
- Tất cả các câu lệnh đều phải có index, không câu lệnh nào được quét full bảng.
- Nếu bảng có partition thì trong điều kiện câu lệnh phải có thêm trường partition, ngoại trừ 1 số trường hợp đặc biệt
- Khi join 2 bảng với nhau thì bảng có dữ liệu lớn hơn phải có index
- Trong câu lệnh không dùng điều kiện is null, phải chuyển sang phương án dùng các toán tử : >, < = ….
- Với các bảng tmp có dữ liệu trong quá trình chạy và xóa dữ liệu sau khi chạy (không cần backup dữ liệu) :
+ Tạo bảng ở chế độ Nologging
+ Khi insert phải dùng /*+Append*/
+ Nếu muốn xóa cả bảng hoặc 1 partition của bảng phải dùng câu lệnh truncate.
+ Hạn chế sử dụng câu lệnh update, cần tối ưu câu lệnh insert và select
3. Yêu cầu trong các câu lệnh tạo View:
- Trong view không thêm trường ’ảo’ của Database vì khi câu lệnh select vào view có thể sẽ bị quét full bảng.
- Hạn chế sử dụng view lồng nhau, tối đa chỉ dùng 2 view lồng nhau
- Nếu cần hint index trong câu lệnh truy vấn vào view thì cần hint trong câu lệnh tạo view, không hint vào trong câu lệnh select vào view
4. Yêu cầu tối ưu câu lệnh truy vấn vào Database
- Yêu cầu tối ưu lệnh select:
+ Không thực hiện select * dữ liệu trong bảng, phải chỉ rõ các trường cần select
+ Chỉ thực hiện select số num row cần sử dụng (càng ít num row hiệu suất truy vấn dữ liệu càng nhanh)
- Giảm thiểu số lượng Subqueries trong truy vấn
- Sử dụng IN và EXISTS một cách thích hợp trong câu lệnh truy vấn
- Sử dụng EXISTS và DISTINCT khi join các bảng có quan hệ 1 nhiều
- Thay thế UNION ALL cho UNION
- Yêu cầu với câu lệnh WHERE
+ Điều kiện where khi dùng is not phải chuyển sang dùng các toán tử : >, < =
+ Điều kiện where khi dùng điều kiện kép lớn hơn hoặc nhỏ hơn trong khoảng, phải chuyển sang sử dụng BETWEEN
- Sử dụng JOIN thay cho Subqueries để tăng hiệu suất truy vấn
- Không sử dụng toán tử OR trong câu lệnh truy vấn bảng có dữ liệu lớn
- Đánh index cho foreign key trên bảng con
5. Quản lý partition
Đánh partition tự động
Hạn chế
- Chỉ hỗ trợ kiểu Number và Date
- Tên partition được tạo tự động SYS_xxxx
-
CREATE TABLE autopartitionmonth
(
saldate DATE
, employee VARCHAR2 (40)
, salary NUMBER (10)
)
PARTITION BY RANGE
(saldate)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(
PARTITION p_first
VALUES LESS THAN (TO_DATE ('01-01-2012', 'DD-MM-YYYY')));
Tao table partition
CREATE TABLE pos_data
(
start_date DATE,
store_id NUMBER,
inventory_id NUMBER(6),
qty_sold NUMBER(3)
)
PARTITION BY RANGE
(
start_date
)
(
PARTITION A072014 VALUES LESS THAN (TO_DATE('1-7-2014', 'DD-MM-YYYY')),
PARTITION A082014 VALUES LESS THAN (TO_DATE('1-8-2014', 'DD-MM-YYYY'))
)
CREATE TABLE test ( item_name varchar2(10) , money_value number , product_id integer , snapshot_date date ) PARTITION BY RANGE (SNAPSHOT_DATE) INTERVAL (NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY LIST(product_id) SUBPARTITION TEMPLATE (SUBPARTITION MONEY_1 VALUES (1), SUBPARTITION MONEY_2 VALUES (2), SUBPARTITION MONEY_3 VALUES (3) ) (PARTITION PART20111231 VALUES LESS THAN (TO_DATE('20100101','YYYYMMDD')));=> select PARTITION_NAME,HIGH_VALUE from USER_TAB_PARTITIONS where table_name='TEST';=> SELECT table_name, partition_name, subpartition_name, subpartition_position FROM user_tab_subpartitions where table_name='TEST'; Thêm parttion
ALTER TABLE pos_data ADD partition A092014 VALUES less than
(
TO_DATE('1-9-2014','DD-MM-YYYY')
);
ALTER TABLE Test SET SUBPARTITION TEMPLATE (SUBPARTITION MONEY_1 VALUES (1), SUBPARTITION MONEY_2 VALUES (2), SUBPARTITION MONEY_3 VALUES (3), SUBPARTITION MONEY_4 VALUES (4) );Xóa partition
ALTER TABLE pos_data drop PARTITION A082014;
Select partition
- SELECT * FROM pos_data PARTITION (A072014 )
- SELECT * FROM pos_data PARTITION (A072014 ) WHERE product_id=21
- SELECT * FROM pos_data PARTITION for (to_date('10-JUL-2014','dd-mon-yyyy'));
Chúng ta có thể delete, truncate table, partition, subpartition bình thường và có thể kết hợp thêm "FOR" để cho việc thao tác trên interval partition tiện lợi hơn
Tính năng hay, nhưng có 3 câu hỏi lớn về cái này mình nghĩ nên thảo luận thêm:
1. Partitioning giúp cho action nào với CSDL khả thi và nhanh hơn?
=> Trước hết là tính năng tra cứu và sau đó là tính năng backup dữ liệu (dữ liệu cũ, lịch sử)
2. Dữ liệu thế nào gọi là "lớn" mà cần dùng đến partitioning? Với dữ liệu như thế không xài partition có sao không?
=> Dữ liệu lớn cũng tùy ứng dụng và cơ sở hạ tầng. Với database trên dòng server rẻ tiền ổ cứng SATA thì vài GB/table có thể gọi là lớn. Nhưng nếu lưu dữ liệu trên exadata có thể vài chục GB chưa gọi là lớn.
3. Điều gì cần chú ý với partitioning nhất? ràng buộc hay index có gì khác với bình thường không.
=> Theo kinh nghiệm cá nhân thì khi dùng partition phải gắn liền với lưu trữ thì mới có hiệu quả cao. Khi table được chia partition mà dùng global index thì không có nghĩa mà phải dùng local index. Khi search dữ liệu sử dụng local index mà không gắn liền với các điều kiện chia partition thì rất dễ bị phản tác dụng.
6. Phục hồi dữ liệu sau khi đã delete, update và commit
-- Select dữ liệu 70 phút trước
SELECT *
FROM table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '70' minute)
WHERE key in ( 9)
-- Insert vào table trước khi phuc hồi
INSERT INTO tabletemp
SELECT *
FROM table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '70' minute)
WHERE key in ( 9 )
-- Select dữ liệu 1 ngày trước
SELECT *
FROM SUB_MT_HIS AS OF TIMESTAMP SYSDATE-1
FLASHBACK TABLE abc TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' minute); alter table abc enable row movement;
Không có nhận xét nào:
Đăng nhận xét