Views Indexes and more Stored Procedures tutorial Professor

Views, Indexes and more Stored Procedures tutorial Professor: Dr. Shu-Ching Chen TA: Haiman Tian 1

Outline � Introduction of Views & Indexes � Stored Procedures 2

Views

Views (1) § A selective presentation of the structure of, and data in, one or more tables (or other views) § A ‘virtual table’, having predefined columns and joins to one or more tables, reflecting a specific facet of information § Structure data in a way that users or classes of users find natural or intuitive. § Restrict access to the data such that a user can only see limited data instead of complete table. § Summarize data from various tables which can be used to generate reports.

Views (2) § CREATE OR REPLACE VIEW is similar § If a view of the same name already exists, it is replaced § The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types) § It may additional columns to the end of the list. The calculations giving rise to the output columns may be completely different. § If a schema name is given (for example, CREATE VIEW myschema. myview . . . ) then the view is created in the specified schema. Otherwise it is created in the current schema.

Views (3) § TEMPORARY or TEMP § Temporary views are automatically dropped at the end of the current session. § Existing permanent relations with the same name are not visible to the current session while the temporary view exists, unless they are referenced with schemaqualified names. § If any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMPORARY is specified or not).

Views (4) § Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. § The name of the view must be distinct from the name of any other view, table, sequence, index or foreign table in the same schema.

Example CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; DROP VIEW myview;

Example (2) • A view can be very useful in some cases such as: – A view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple SELECT statement. – Like a table, you can grant permission to users through a view that contains specific data that the users are authorized to see. – A view provides a consistent layer even the columns of underlying table changes.

Example (3) � Removing Postgre. SQL Views � To remove an existing view in Postgre. SQL, you use DROP VIEW statement as follows: DROP VIEW IF EXISTS customer_info; You specify the name of the view that you want to remove after DROP VIEW clause. Removing a view that does not exist in the database will result in an error. To avoid this, you normally add IF EXISTSoption to the statement to instruct Postgre. SQL to remove the view if it exists, otherwise, do nothing. 10

Example (4) � Posgre. SQL Updatable Views Example � First, we create a new updatable view name usa_cities using CREATE VIEW statement. This view contains cities in the city table that belongs to USA. CREATE VIEW usa_cities AS SELECT city, country_id FROM city WHERE country_id = 103; 11

Example (5) � Next, we can check the data in the usa_cities view by executing a simple SELECT statement: SELECT * FROM usa_cities; � Then, we insert a new city to the city table through the usa_cities view using the following INSERT statement: INSERT INTO usa_cities (city, country_id) VALUES('San Jose', 103); 12

Indexes

Indexes § Primary mechanism to get improved performance on a database § Persistent data structure, stored in database § Many interesting implementation issues

Indexes (1) Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. � For example, if you want to reference all pages in a book that discusses a certain topic, you have to first refer to the index, which lists all topics alphabetically and then refer to one or more specific page numbers. � An index helps to speed up SELECT queries and WHERE clauses; however, it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data. � Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order. � Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index. �

Indexes (2) � Single-Column Indexes � A single-column index is one that is created based on only one table column. The basic syntax is as follows − � CREATE INDEX index_name ON table_name (column_name); Multicolumn Indexes � A multicolumn index is defined on more than one column of a table. The basic syntax is as follows − � CREATE INDEX index_name ON table_name (column 1_name, column 2_name); 16

Indexes (3) �A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. The basic syntax is as follows − � CREATE INDEX index_name on table_name (conditional_expression); � Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints. 17

Functionality T A T. A = ‘cow’ T. A = ‘cat’ Index on T. A B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … …

Functionality T A T. B = 2 T. B < 6 4< T. B <= 8 Index on T. A Index on T. B B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … …

Functionality T A T. A = ‘cat’ and T. B > 5 T. A < ‘d’ And T. B = 1 Index on T. A Index on Index T. (A, B) on T. B B C 1 cat 2 … 2 dog 5 … 3 cow 1 … 4 dog 9 … 5 cat 2 … 6 cat 8 … 7 cow 6 … …

Utility § Index = difference between full table scans and immediate location of tuples â Orders of magnitude performance difference A=V, A<V, V 1< A < V 2 – Balanced trees (B trees, B+ trees) § Underlying data structures – Hash tables. A=V

Select s. Name From Student Where s. ID = 18942 Index on s. ID Many DBMS’s build indexes automatically on PRIMARY KEY (and sometime UNIQUE)attributes

Select s. ID From Student Where s. Name = ‘Mary’ And GPA > 3. 9 Index on s. Name Index on GPA Index on (s. Name, GPA) Hash-based or Treebased Tree-based

Downsides of Indexes 1) Extra 2) space Index creation 3) Index maintenance - Marginal - Medium - Can offset benefits

Picking which indexes to create Benefit of an index depends on: § Size of table (and possibly layout) § Data distributions § Query vs. update load

SQL Syntax Create Index. Name on T(A) Create Index. Name on T(A 1, A 2, …, An) Create Unique Index. Name on T(A) Drop Index. Name

Outline � Introduction of Views & Indexes � Stored 27 Procedures

Stored Procedures (1) � By now you should be familiar with the creation and management of stored procedures. This tutorial will demonstrate more techniques related to the stored procedures, specifically user-defined functions (UDF). 28

Stored Procedures (2) � Recap: A stored procedure and user-defined function (UDF) is a set of SQL and procedural statements (declarations, assignments, loops, flow-of-control etc. ) that stored on the database server and can be invoked using the SQL interface. � Example: 29

Stored Procedures (3) � Why do we need stored procedure? One Query Internet Database Server Wait, receive, process/compute � Reduce roundtrips across the network � Can make security easier to manage � Are precompiled 30

Stored Procedures (4) � Structure of PL/pg. SQL 31

Stored Procedures (5) � No Value Returned � If a stored procedure does not return any value, you can specify void as the return type: -- Procedure to insert a new city CREATE OR REPLACE FUNCTION add_city(city VARCHAR(70), state CHAR(2)) RETURNS void AS $$ BEGIN INSERT INTO cities VALUES (city, state); END; $$ LANGUAGE plpgsql; 32

Stored Procedures (6) � How to Return a Result Set from a Stored Procedure -- Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$ DECLARE refcursor; -- Declare a cursor variable BEGIN OPEN ref FOR SELECT city, state FROM cities; -- Open a cursor RETURN ref; -- Return the cursor to the caller END; $$ LANGUAGE plpgsql; 33

Stored Procedures (7) � To return multiple result sets, specify SETOF refcursor return type and use RETURN NEXT to return each cursor: -- Procedure that returns multiple result sets (cursors) CREATE OR REPLACE FUNCTION show_cities_multiple() RETURNS SETOF refcursor AS $$ DECLARE ref 1 refcursor; -- Declare cursor variables ref 2 refcursor; BEGIN OPEN ref 1 FOR SELECT city, state FROM cities WHERE state = 'CA'; -- Open the first cursor RETURN NEXT ref 1; -- Return the cursor to the caller OPEN ref 2 FOR SELECT city, state FROM cities WHERE state = 'TX'; -- Open the second cur RETURN NEXT ref 2; -- Return the cursor to the caller END; $$ LANGUAGE plpgsql; 34

Stored Procedures (8) � Advantages of Using PL/pg. SQL � can be used to create functions and trigger procedures, � adds control structures to the SQL language, � can perform complex computations, � inherits all user-defined types, functions, and operators, � can be defined to be trusted by the server, � is easy to use. 35

Stored Procedures (9) � Why use Stored Procedures? � Reusability � Avoid rewriting subqueries and improve readability. � If you can't store a query in a library that all the applications can access, you can put that query in a stored procedure. � Separation of duties � You don't trust non-DBA's to write queries. � Data integrity � Use triggers or constraints to prevent bad data from entering � Run several interdependent queries in a transaction in a single stored procedure. 36

Stored Procedures (10) � Why use Stored Procedures? � Event handling � Log changes. � Notify other systems of new data. 37

Stored Procedures (11) � Why NOT use Stored Procedures? � Views may be all you need. � An object-relational mapper (ORM) can help write queries safely. � Difficult to version control stored procedures. � Software rollouts may require more DB changes. � Could slow software development process. 38

Stored Procedures (12) � Examples (1) Calculating the percentage of the total for each row of the price column with Post. GRES can be done as follows (1) SELECT price, (COUNT(price)* 100 / (SELECT COUNT(*) FROM store_table)) AS percentage FROM store_table GROUP BY price 39

Stored Procedures (13) � Examples (1) � (2) Using a stored procedure would be as follows CREATE OR REPLACE FUNCTION show_percentage( ) RETURNS text AS $$ DECLARE result text; SELECT price, (COUNT(price)* 100 / (SELECT COUNT(*) FROM store_table)) AS percentage INTO result FROM store_table GROUP BY price RETURN result; END; $$ LANGUAGE plpgsql; SELECT show_percentage(); 40

Stored Procedures (14) � Example (2): Use a stored procedure to get the name of a product, given the product_id. CREATE OR REPLACE FUNCTION get. Product. Name (p_id product_id%TYPE ) RETURNS text AS $$ DECLARE result text; BEGIN SELECT name INTO result FROM product WHERE product_id = p_id; RETURN result; END; $$ LANGUAGE plpgsql; SELECT get. Product. Name (4); 41

Stored Procedures (15) � Example (3) How to make a procedure that calls the procedure shown in the previous slide (get. Product. Name()) and get all the names for all the product ids in the table. CREATE OR REPLACE FUNCTION get. All. Product. Name () RETURNS SETOF text as $$ DECLARE prod_id product%rowtype; result text; BEGIN FOR p_id IN SELECT product_id FROM PRODUCT LOOP SELECT prod_id. product_id || ‘: ‘ || get. Product. Name(prod_id. product_id ) INTO result; RETURN NEXT result; END LOOP; RETURN; END; $$ language plspsql; SELECT get. All. Product. Name (); 42

Stored Procedures (16) � Concatenating two SELECTS for a stored procedure CREATE OR REPLACE FUNCTION get. Product&Vendor. Name () RETURNS text as $$ DECLARE result text; BEGIN SELECT name INTO result FROM PRODUCT; SELECT result || ‘ vendor: ’ || name INTO result FROM VENDOR; END; RETURN result; $$ language plspsql; SELECT get. All. Product. Name (); 43

Stored Procedures (17) � Creating a function using pg. Admin The following steps show you how to create the function increment in pg. Admin 3. Right click on functions and choose “New Function…” from the menu 44

Stored Procedures (18) � Give the function a name and add some comments. 45

Stored Procedures (19) � In the definition tab, choose integer as the return type and plpgsql as the procedural language. 46

Stored Procedures (20) � In the parameter tab, enter the name for each parameter and click the Add button 47

Stored Procedures (21) � In the Code section, enter the core code of the procedure 48

Stored Procedures (22) � Click the SQL tab and you will be able to preview the code generated by pg. Admin. 49

Stored Procedures (23) � Whether you use pg. Admin or write the code from scratch, is just a matter of preference � Just remember to always submit the entire SQL code for the Homework/Project. 50