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   Xố 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 xố 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) Tạo bảng ảo bằng tiện ích Enterprise Manager Màn hình thiết kế dữ liệu bảng ảo. Chỉ định các cột hiển thị dữ liệu bên trong bảng ảo Gõ vào tên bảng ảo muốn lưu lại Màn hình hiển thị câu lệnh SELECT trong bảng ả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' Xố 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 tồ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 khố 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ể tồ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 khố 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 xố 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’ Xố các chỉ mục Các chỉ mục khơng cịn cần thiết cĩ thể bị xố 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 để xố chỉ mục trên một bảng hệ thống.