Bài giảng Làm việc với View và chỉ mục

Chương 4 Làm việc vớI View và Chỉ mục Mục đích  Định nghĩa view Hiểu được các ưu nhược điểm của View, cách dùng Tạo View   Sửa đổi View   Xoá view   Định nghĩa chỉ mục Tìm hiểu cách tạo chỉ mục Miêu tả các khung nhìn bảng chỉ mục Tìm hiểu cách sử dụng các chỉ mục Giải thích việc xoá các chỉ mục Khái niệm View View là một cách thức để xem các dữ liệu từ một hay nhiều bảng trong cơ sở dữ liệu. View là một bảng ảo, được tạo ra như một tập con của các cột hay các hàng từ một hay nhiều bảng khác nhau. NộI dung của nó xác định bởI một truy vấn Một view không tồn tại như một tập hợp của các giá trị dữ liệu lưu trữ trong cơ sở dữ liệu. Một view có tác dụng như một bộ lọc dữ liệu trên các bảng có địa chỉ trong các truy vấn Bảo vệ dữ liệu nhạy cảm khỏi những người dùng không được phép Làm giảm sự phức tạp của cơ sở dữ liệu Dùng lệnh truy vấn dữ liệu trên view như trên bảng csdl Ưu nhược điểm của view Thuận lợi đối với người sử dụng cuối Dễ dàng để hiểu được kết quả Dễ dàng thu được dữ liệu mong muốn Thuận lợi của View đối với những nhà phát triển Dễ dàng hạn chế việc nhận dữ liệu trả về Dễ dàng bảo trì ứng dụng Nhược điểm: Vì cần chuyển đổI các truy vấn trên khung nhìn thành truy vấn trên các bảng gốc nên chi phí thờI gian lớn hơn truy vấn trực tiếp trên bảng gốc Dữ liệu trong khung nhìn thường chỉ dùng để xem (khi cập nhật chỉ thực hiện được vớI khung nhìn đơn giản) Taïo baûng aûo baèng tieän ích Enterprise Manager Maøn hình thieát keá döõ lieäu baûng aûo. Chỉ định các cột hiển thị dữ liệu bên trong bảng ảo Goõ vaøo teân baûng aûo muoán löu laïi Maøn hình hieån thò caâu leänh SELECT trong baûng aûo Tạo View bằng T-SQL Lệnh CREATE VIEW được sử dụng để tạo view. Cú pháp là: CREATE VIEW AS Ví dụ: VD: Tạo khung nhìn DSSV CREATE VIEW DSSV AS SELECT masv,hodem,ten, DATEDIFF(YY,ngaysinh,GETDATE()) AS tuoi,tenlop FROM sinhvien,lop WHERE sinhvien.malop=lop.malop Chú ý khi tạo View View chỉ có thể được tạo trong cơ sở dữ liệu hiện tại. Tên một View tương tự như tên một bảng Một View có thể được xây dựng dựa vào các View khác. SQL SERVER cho phép các View được lồng vào nhau tới 32 mức (cấp). Mỗi một View có thể bao gồm tới 1024 cột từ một hay nhiều bảng hoặc từ nhiều View khác. Những giá trị mặc định, những quy tắc và bẫy lỗi không thể được liên kết với View. View không thể đánh chỉ mục. Chú ý khi tạo View (tiếp) Khai báo View được lưu giữ lại ngay cả khi các bảng liên quan bị loại bỏ. Câu truy vấn để khai báo View không thể bao gồm các mệnh đề ORDER BY, COMPUTE BY hoặc từ khóa INTO Sửa đổi dữ liệu thông qua View Một View có thể được sử dụng để thay đổi dữ liệu trong bảng với điều kiện: Không có tất cả các hàm sau (AVG, COUNT, SUM, MIN, MAX, GROUPING) hoặc GROUP BY, UNION, DISTINCT hoặc mệnh đề TOP trong danh sách được chọn. View không có các cột ket xuat trong danh sách chọn (danh sách chọn của câu lệnh SELECT phải là các cột trong các bảng cơ sở). Các thao tác thay đổi đến dữ liệu thông qua khung nhìn phải đảm bảo thoả mãn các ràng buộc trên các bảng cơ sở VD: Sửa dữ liệu qua VIEW Với khung nhìn được định nghĩa như sau: CREATE VIEW nv3 AS SELECT manv,hoten,ngaysinh, diachi,nhanvien.madv AS noilamviec, donvi.madv,tendv,dienthoai   FROM nhanvien FULL OUTER JOIN donvi ON nhanvien.madv=donvi.madv Thực hiện lệnh: INSERT INTO nv3(manv,hoten,noilamviec) VALUES('NV05','Le Van E',1) sẽ bổ sung thêm vào bảng NHANVIEN một bản ghi mới. Hoặc câu lệnh: INSERT INTO nv3(madv,tendv) VALUES(3,'P. Ke toan') bổ sung thêm vào bảng DONVI một bản ghi do cả hai câu lệnh này chỉ có tác động đến đúng một bảng cơ sở. Không thực hiện được lệnh: INSERT INTO nv3(manv,hoten,noilamviec,madv,tendv) VALUES('NV05','Le Van E',1,3,'P. Ke toan') Sửa đổi khung nhìn Xem/Sửa VIEW bằng EM: Để xem: Chức năng Open View/ Return all rows trên thực đơn tắt chọn trên tên bảng ảo Có thể sửa đổi nội dung câu lệnh SELECT trong bảng ảo Bằng cách chọn chức năng Design View để quay lại màn hình thiết kế dữ liệu bảng ảo trước đó Hoặc chọn chức năng Properties để có thể sửa trực tiếp câu lệnh SELECT bên trong bảng ảo Sửa view bằng T-SQL ALTER VIEW [()] AS ALTER VIEW được sử dụng để định nghĩa lại khung nhìn hiện có nhưng không làm thay đổi các quyền đã được cấp phát cho người sử dụng trước đó VD: Lúc đầu có View CREATE VIEW viewlop AS SELECT malop,tenlop,tenkhoa FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa WHERE tenkhoa='Khoa Vật lý’ Định nghĩa lại khung nhìn trên bằng câu lệnh: ALTER VIEW view_lop AS SELECT malop,tenlop,hedaotao FROM lop INNER JOIN khoa ON lop.makhoa=khoa.makhoa WHERE tenkhoa='Khoa Công nghệ thông tin' Xoá View Sau khi một View được sử dụng xong, nó có thể bị xóa để giải phóng vùng nhớ Việc xóa một View không tác động đến các bảng cơ sở mà View tham chiếu đến. Câu lệnh xóa một View là: DROP VIEW Ví dụ: DROP VIEW NewMoon_employee  So luoc ve Chỉ mục Các chỉ mục Một chỉ mục cho phép chương trình tìm dữ liệu trong bảng mà không cần duyệt qua toàn bộ bảng. Chỉ mục chỉ ra để tìm kiếm một giá trị cụ thể. Chỉ mục sử dụng để chỉ ra hàng chứa giá trị trong bảng Một chỉ mục hợp lý có thể cải thiện sự thực thi cơ sở dữ liệu bởi giảm bớt thời gian truy cập. Một chỉ mục có thể được tạo lập trên một trường hoặc trên nhiều trường. SQL Server tự động tạo lập các chỉ mục cho các kiểu ràng buộc PRIMARY KEY và UNIQUE. Các chỉ mục (tiếp theo…) Các bảng có các chỉ mục đòi hỏi nhiều dung lượng bộ nhớ hơn trong CSDL. Các lệnh thao tác dữ liệu đòi hỏi nhiều thời gian xử lý hơn, vì chúng cần cập nhật sự thay đổi tới các chỉ mục. Hướng dẫn cách tạo lập các chỉ mục Tạo chỉ mục khi: Khi cột được sử dụng để tìm kiếm thường xuyên. Khi cột được sử dụng để sắp xếp dữ liệu. Chỉ mục không nên tạo khi: Khi cột dữ liệu chứa các giá trị duy nhất. Khi bảng chỉ chứa một vài hàng. Tạo lập các chỉ mục Lệnh CREATE INDEX được sử dụng để tạo lập chỉ mục trên bảng. Câu lệnh này sap xep các bản ghi trong bảng hoặc đưa ra việc tối ưu truy vấn với một thứ tự logic của bảng nhằm tăng hiệu quả hoạt động cho các truy vấn. Chỉ có thể tạo lập chỉ mục trên các bảng người dùng. Tạo lập chỉ mục Cú pháp của câu lệnh CREATE INDEX dạng đơn giản: CREATE [CLUSTERED|NONCLUSTERED] INDEX ON ([, ]…) Hệ số điền đầy Khi một chỉ mục được tạo lập, bảng dữ liệu được chứa trong các trang dữ liệu theo một thứ tự đã được sắp xếp của giá trị trong các cột chỉ mục. Khi các bản ghi được thêm vào bảng hoặc các giá trị trong các cột chỉ mục bị thay đổi, SQL Server có thể tổ chức dữ liệu chứa định dạng để làm mẫu cho các bản ghi mới và duy trì sự lưu trữ dữ liệu được sẵp xếp. Trong khi thêm một hàng mới tới một trang dữ liệu có chỉ số, SQL Server sẽ dịch chuyển khoảng một nửa số hàng sang trang mới để có chỗ trống cho hàng mới thêm vào nằm trong trang vừa chia ra. Hệ số điền đầy Trang phân chia giới hạn sự thực thi và phân chia dữ liệu chứa trong bảng.  Hệ số điền đầy được chỉ định trong quá trình tạo lập chỉ số, để dành riêng tỉ lệ phần trăm khoảng trống của chỉ số trên mỗi trang để lưu trữ dữ liệu mở rộng trong tương lai. Hệ số điền đầy xác định dữ liệu được nhập vào trong các trang sau khi chỉ số được tạo lập. Hệ số điền đầy Khi dữ liệu không thay đổi, chỉ định một giá trị % sao cho các trang sẽ được điền đầy và sẽ chiếm tối thiểu dung lượng khoảng trống. Khi dữ liệu thay đổi thường xuyên, chỉ định một giá trị thấp hơn để dịch chuyển nhiều khoảng trống hơn trên các trang. Các kiểu của chỉ số Một chỉ mục clustered chỉ ra thứ tự lưu trữ dữ liệu trong bảng. Một chỉ mục non-clustered xác định thứ tự lưu trữ dữ liệu logic của bảng. Chỉ mục Clustered Một chỉ mục clustered tạo dung lượng lưu trữ vật lý theo thứ tự của dữ liệu. Một bảng có thể chỉ có một chỉ mục clustered. Chỉ mục có thể bao gồm nhiều cột. Ví dụ: CREATE CLUSTERED INDEX CLINDX_titleid ON roysched (title_id) Các hướng dẫn để tạo lập chỉ mục Clustered Chọn một cột có tỉ lệ phần trăm các giá trị duy nhất cao. Chọn cột khoá chính là tốt nhất Tạo lập một chỉ mục clustered trước khi chỉ mục non-clustered được tạo lập để chỉ mục non-clustered không bị tạo lại sau khi chỉ mục clustered được tạo. Sử dụng tuỳ chọn FILLFACTOR khi tạo lập các chỉ mục clustered để đảm bảo có khoảng trống thích hợp khi có các hàng mới được chèn vào. Các chỉ mục Non-clustered Một chỉ mục non-clustered xác định thứ tự logic của bảng. Dữ liệu được chứa ở một nơi, chỉ mục được chứa ở một mơi khác, với các con trỏ định vị vị trí lưu trữ dữ liệu. Một bảng có thể có nhiều chỉ mục non-clustered, tối đa là 249. Ví dụ: CREATE NONCLUSTERED INDEX NCLINDX_ordnum ON sales (ord_num) Các hướng dẫn để tạo lập các chỉ mục Non-clustered Chỉ nên thêm chỉ mục khi thực sự cần thiết, vì một chỉ mục đòi hỏi nhiều sự duy trì thêm vào sau đó. Một chỉ mục thường xuyên truy xuất các cột. Ở chế độ mặc định, lệnh CREATE INDEX tạo lập một chỉ mục non-clustered. Sử dụng chỉ mục non-clustered cho các cột để lưu trữ một số cao của các giá trị riêng biệt, giống như sự kết hợp của họ và tên (Nếu một chỉ mục clustered được sử dụng cho các cột khác) Thuộc tính của các chỉ số Các chỉ mục Clustered và non-clustered có thể được tạo lập duy nhất hoặc tổ hợp. Các chỉ mục duy nhất không cho phép các giá trị lặp lại trong cột chỉ số. Các chỉ mục tổ hợp cho phép hai hoặc nhiều cột được sử dụng trong khi tạo lập chỉ số. Hướng dẫn việc tạo lập các chỉ số duy nhất Thực thể toàn vẹn có hiệu lực bởi các chỉ số duy nhất vì một giá trị duy nhất tồn tại trong từng hàng. Một giá trị rỗng trong một cột khoá phản ánh như một giá trị duy nhất. Chỉ mục duy nhất không thể tạo lập trên một cột mà chứa dữ liệu lặp lại. Dữ liệu lặp lại phải được cập nhật hoặc xoá trước khi chỉ mục duy nhất được tạo lập. Hướng dẫn cho việc tạo lập các chỉ số phức hợp Tạo lập một chỉ số phức hợp khi hai hoặc nhiều cột thường xuyên được tìm kiếm như một nhóm. Xác định danh sách các cột hợp thành giá trị duy nhất để tạo lập chỉ mục phức hợp. Thứ tự và số lượng các cột trong một chỉ mục tổ hợp ảnh hưởng đến sự thực thi của truy vấn. Xem xét các chỉ mục Sau khi tạo lập các chỉ mục, chúng ta có thể cần định vị thông tin về các chỉ mục. Chúng ta có thể muốn nhìn thấy danh sách của các chỉ mục tạo lập trên một bảng cụ thể hoặc các cột chỉ mục trong bảng. sp_helpindex là một thủ tục lưu trữ hệ thống đưa ra thông báo thông tin về các chỉ mục trên bảng. Cú pháp: sp_helpindex Sử dụng các chỉ mục SQL Server sử dụng kỹ thuật tối ưu truy vấn để chọn cách tốt nhất để thực thi các truy vấn, bao gồm các chỉ mục để sử dụng. Kỹ thuật tối ưu cố gắng lựa chọn phương thức truy cập sao cho giá ước lượng thấp nhất trong các mục của trang I/O. Kỹ thuật tối ưu bắt buộc truy vấn tối ưu chọn một truy vấn cụ thể. Cú pháp: (INDEX=index_name) Ví dụ: SELECT * FROM sales (INDEX =nclindx_ordnum) WHERE ord_num = ‘P3087a’ Xoá các chỉ mục Các chỉ mục không còn cần thiết có thể bị xoá khỏi CSDL để giải phóng bộ nhớ bằng cách sử dụng lệnh DROP INDEX. Cú pháp: DROP INDEX table_name.index_name Ví dụ: DROP INDEX sales.NCLINDX_ordnum Lệnh DROP INDEX không được áp dụng trên các chỉ số được tạo lập bởi các ràng buộc PRIMARY KEY hoặc UNIQUE. DROP INDEX cũng không thể được sử dụng để xoá chỉ mục trên một bảng hệ thống.