Fundamentals of Databases Lecture 7 PDF

FUNDAMENTALS OF DATABASES Non-table Objects in RDBMS NGUYEN Hoang Ha Email: [email protected]  Agenda  Index  View  Stored Procedure  Trigger 2 INDEX  Why indexing?  Indexing are one of the most important and useful tools for achieving h...

FUNDAMENTALS OF DATABASES Non-table Objects in RDBMS NGUYEN Hoang Ha Email: [email protected]  Agenda  Index  View  Stored Procedure  Trigger 2 INDEX  Why indexing?  Indexing are one of the most important and useful tools for achieving high performance in a relational database  Many database administrators consider indexes to be the single most critical tool for improving database performance  An index is a data structure that contains a copy of some of the data from one or more existing database tables  A database index provides an organizational framework that the DBMS can use to quickly locate the information that it needs  This can vastly improve the speed with which SQL queries can be answered 4  Without index Query for a random name within the table What is the average search time if the process is repeated many times? 𝑛+1 𝑎𝑣𝑒𝑟𝑎𝑔𝑒 = 2 What is the maximum search time? 𝑀𝑎𝑥𝑖𝑚𝑢𝑚 = 𝑛 5  With index Query for a random name within the table What is the average search time if the process is repeated many times? 𝑎𝑣𝑒𝑟𝑎𝑔𝑒 = log 2 (𝑛) − 1 = 3.5 What is the maximum search time? 𝑀𝑎𝑥𝑖𝑚𝑢𝑚 = log 2(𝑛) = 4.5 6 6  Index concepts  Indexes are created on one or more columns in a table  For example:  An index is created on a PK column  The index will contain the PK value for each row in the table, along with each row’s ordinal position (row number) within the table  When a query involving the PK is run, the DBMS will find the PK value within the index. The DBMS will then know the position of the row within the table  The DBMS can then quickly locate the row in the table that is associated with the PK value  Without an index, the DBMS has to perform a table scan in order to locate the desired row 7  Index concepts  An index can be created on most, but not all, columns. Whether an index can be created on a column depends on the column's datatype  Columns with large object data types cannot be indexed without employing additional mechanisms These data types include:  Text  ntext  Image  varchar (max)  Nvarchar(max)  varbinary(max) 8  Index concepts  Creating an index increases the amount of storage space required by the database  This occurs because an index contains a copy of some of the data in a table  To estimate the storage space requirements of an index, we can use the following formula: Number of rows in table x Average number of bytes required per row for the indexed columns 9  B-Tree Index  Balance-Tree: the most common type of database indexing  B-trees use pointers and several layers of nodes in order to quickly locate desired data  Root node  Intermediate nodes  Leaf nodes  When the DBMS processes a query which includes an indexed column, it starts at the root node of the B-tree and navigates downward until it finds the desired leaf 10  B-tree example 11  Clustered Indexes  In a clustered index, the actual data rows that comprise the table are stored at the leaf level of the index  The indexed values are stored in a sorted order  This means that there can be only one clustered index per table  PK columns are good candidates for clustered indexes 12  Clustered B-Tree Example 13  Indexing Guidelines  If a table is heavily updated, index as few columns as possible  If a table is updated rarely, use as many indexed columns as necessary to achieve maximum query performance  Clustered indexes are best used on columns that do not allow null values and whose value are unique  The performance benefits on an index are related to the uniqueness of the values in the indexed column  Index performance is poor when an indexed column contains a large proportion of duplicate values  Index performance is best when an indexed column contains unique values 14 VIEW  View concept  A view is a “virtual” or logical table that is derived from other tables 16  View in a database Query Table A defining View view SQL Table B SQL Application 17  Pros vs. Cons  Pros:  Simplify complex queries  Enable computed columns  Provide a security layer: hide sensitive data  Enable backward capability  Cons  Performance  Table dependency: table changes → need to change views 18  Syntax CREATE [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW view_name [(column_list)] AS select-statement [WITH CHECK OPTION];  ALGORITHM  MERGE: MySQL combines input query with the select-statement. MERGE is not allowed if the SELECT statement contains aggregate functions or DISTINCT, GROUP BY, HAVING, LIMIT, UNION, UNION ALL, subquery, SELECT statement refers to no table.  TEMPTABLE: MySQL creates a temporary table based on the SELECT statement that defines the view, then performs query against this temporary table.  UNDEFINED: MySQL makes choice of MERGE or TEMTABLE. 19  View examples  Computed columns CREATE VIEW sale_per_order AS SELECT order_id, SUM(quantity * unit_price * (1-discount)) total FROM order_details GROUP BY order_id ORDER BY total DESC;  Based on a sub query CREATE VIEW above_avg_products AS SELECT product_code, product_name, list_price FROM products WHERE list_price > (SELECT AVG(list_price) FROM products) ORDER BY list_price DESC;  Based on another view CREATE VIEW big_sale_orders AS SELECT order_id, ROUND(total,2) AS total FROM sale_per_order WHERE total > 1000; 20  Updatable views  SELECT statement defining the view must not contain following elements:  Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.  DISTINCT  GROUP BY clause.  HAVING clause.  UNION or UNION ALL clause.  Left join or outer join.  Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.  Reference to non-updatable view in the FROM clause.  Reference only to literal values.  Multiple references to any column of the base table 21  WITH CHECK OPTION Clause  Role: to prevent updating or inserting rows that are not visible through the view  Example CREATE OR REPLACE VIEW northwind_products AS SELECT id, product_code, product_name FROM products WHERE product_name LIKE 'Northwind%' WITH CHECK OPTION; INSERT INTO northwind_products (product_code, product_name) VALUES('HNB', 'Hanoi Beer');-- This is invalid INSERT INTO northwind_products (product_code, product_name) VALUES('NWnew', 'Northwind Beer'); UPDATE northwind_products SET product_name = 'Nwd beer' WHERE product_code = 'NWnew'; --- WITH CHECK OPTION will prevent this statement from running 22  View management  Show view definition  SHOW CREATE VIEW [database_name].[view_ name];  Delete view:  DROP VIEW [IF EXISTS] view_name  Change view ALTER[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW view_name [(column_list)] AS select-statement [WITH CHECK OPTION];  OR: CREATE OR REPLACE VIEW  List all views with updateable information SELECT table_name, is_updatable FROM information_schema.views 23 STORED PROCEDURE  Concept  A stored procedure is a segment of SQL statements stored inside the database catalog Stored Tables Procedure SQL Application 25  Stored Procedure (SP)  SP is stored in cache area of memory when it is first executed so that it can be used repeatedly, not need recompiled  Parameters:  Input  Output 26  SP Syntax [ENCRYPTION] [RECOMPILE] [EXECUTE AS username] CREATE [ OR ALTER ] { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY] ] [ WITH [ ,...n ] ] [ FOR REPLICATION ] AS { [ BEGIN ] sql_statement [;] [...n ] [ END ] } DROP PROC [schema_name.] procedure_name 27  Stored Procedure vs. SQL Statement SQL Statement Stored Procedure Creating - Check syntax - Compile First Time First Time - Check syntax - Execute - Compile - Return data - Execute - Return data Second Time Second Time - Check syntax - Execute - Compile - Return data - Execute - Return data 28  Types of SP  System stored procedure:  Name begins with sp_  Created in master database  For application in any database  Often used by sysadmins  Local stored procedure:  Defined in the local database 29  Executing a SP  EXEC pr_GetTopProducts  With parameters  By Name: EXEC pr_GetTopProducts @StartID = 1, @EndID = 10  By Position: EXEC pr_GetTopProducts 1, 10  Leveraging Default values EXEC pr_GetTopProducts @EndID=10  Place parameters with default values at the end of the list for flexibility of use 30  Pros vs. Cons  Pros:  Better performance  Reduce traffic  Be reusable and transparent  Provide a secure way to access data  Cons  CPU usage can increase if logical operators are overused  Hard to debug, maintain 31  Example DROP PROCEDURE IF EXISTS count_products; delimiter // CREATE PROCEDURE count_products (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM products; END// delimiter ; CALL count_products(@a); SELECT @a; 32  Input parameter DELIMITER // CREATE PROCEDURE get_customers_by_city(IN search_city nvarchar(255)) AS BEGIN SELECT * FROM customers WHERE city = search_city; END // DELIMITER ; CALL get_customers_by_city('Seattle'); 33 TRIGGERS  Trigger overview  Definition: A trigger is a special SP executed automatically as part of a data modification (INSERT, UPDATE, or DELETE)  Associated with a table  Invoked automatically  Cannot be called explicitly 35  Syntax CREATE TRIGGER trigger_name ON {[DELETE] [,] [INSERT] [,] [UPDATE]} AS SQL_Statement [...n] 36  Simplied Syntax CREATE TRIGGER trg_one ON tablename FOR INSERT, UPDATE, DELETE Temporary table holding new records AS BEGIN SELECT * FROM Inserted Temporary table holding old, SELECT * FROM Deleted deleted, updated records END 37  Uses of Triggers  Maintenance of duplicate and derived data  Ensure integrity  Complex column constraints  Cascading referential integrity  Inter-database referential integrity  Complex defaults  Logging/Auditing  Maintaining de-normalized data 38  Trigger example Use Northwind GO CREATE TRIGGER Cust_Delete_Only1 ON Customers FOR DELETE AS IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN RAISERROR('You are not allowed to delete more than one customer at a time.', 16, 1) ROLLBACK TRANSACTION END DELETE FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders) Define a trigger preventing users from updating more than 2 records at a time? 39  INSERT-Trigger example USE Northwind GO CREATE TRIGGER Order_Insert ON [Order Details] FOR INSERT AS UPDATE P SET UnitsInStock = (P.UnitsInStock – I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID Order Details OrderID ProductID UnitPrice Quantity Discount ProductID UnitsInStock … … 10522 10 31.00 7 0.2 1 15 10523 41 9.65 9 0.15 2 10 5 10524 7 30.00 24 0.0 3 65 10523 2 19.00 5 0.2 4 20 Products INSERT [Order Details] VALUES inserted (10525, 2, 19.00, 5, 0.2) 10523 2 19.00 5 0.2 40  UPDATE-Trigger example CREATE TABLE PriceTracking (ProductID int, Time DateTime, OldPrice money, NewPrice money) GO CREATE TRIGGER Products_Update ON Products FOR UPDATE AS INSERT INTO PriceTracking (ProductID, Time, OldPrice, NewPrice) SELECT I.ProductID, GETDATE(), D.UnitPrice, I.UnitPrice FROM inserted AS I INNER JOIN Deleted AS D ON I.ProductID = D.ProductID AND I.UnitPrice D.UnitPrice UPDATE Products SET UnitPrice = UnitPrice + 2 41  Enforcing integrity with Trigger CREATE TRIGGER Products_Delete ON Products FOR DELETE AS IF (SELECT COUNT(*) FROM [Order Details] OD WHERE OD.ProductID = (SELECT ProductID FROM deleted) ) > 0 BEGIN PRINT 'Violate Foreign key reference. Rollback!!!' ROLLBACK TRAN END DELETE Products WHERE ProductID = 11 42  Performance Considerations  Triggers work quickly because the Inserted and Deleted tables are in cache  Execution time is determined by:  Number of tables that are referenced  Number of rows that are affected  Actions contained in triggers implicitly are part of a transaction 43