1 / 56

Chapter 6 Additional Database Objects (up to p.195 and all in the pptx file)

Chapter 6 Additional Database Objects (up to p.195 and all in the pptx file). Jason C. H. Chen , Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu. Objectives.

tuari
Download Presentation

Chapter 6 Additional Database Objects (up to p.195 and all in the pptx file)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6Additional Database Objects(up to p.195 and all in the pptx file) Jason C. H. Chen, Ph.D. Professor of MIS School of Business Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  2. Objectives • Define the purpose of a sequence and state how it can be used in a database • Explain why gaps may appear in the integers generated by a sequence • Use the CREATE SEQUENCE command to create a sequence • Call and use sequence values • Identify which options cannot be changed by the ALTER SEQUENCE command • Delete a sequence • Create indexes with the CREATE INDEX command • Explain the main index structures: B-tree and bitmap

  3. Objectives (continued) • Verify index use with the explain plan • Introduce variations on conventional indexes, including a function-based index and an index organized table • Verify index existence via the data dictionary • Rename an index with the ALTER INDEX command • Remove an index using the DROP INDEX command • Create and remove a public synonym

  4. Database Objects • An object is anything that has a name and defined structure • Includes: • Table – stores data • Sequence – generates sequential integers • Index – allows users to quickly locate specific records • Synonym – alias for other database objects

  5. Sequences • Used for internal control purposes by providing sequential integers for auditing • Used to generate unique value for primary key column • Surrogate key = no correlation with actual row contents

  6. Creating a Sequence • Use the CREATE SEQUENCE command • Various intervals are allowed – Default: 1 • You can specify the starting number – Default: 1 CREATESEQUENCEsequencename [INCREASE BY value] [START WITH value] [{MAXVALUE value | MAXVALUE}] [{MINVALUE value | MINVALUE}] [{CYCLE | NOCYLE}] [{ORDER | NOORDER}] [{CACHE value | NOCACHE}]; Figure 6-1 Syntax of the CREATE SEQUENCE command

  7. Creating a Sequence (continued) • Can specify MINVALUE for decreasing sequence and MAXVALUE for increasing sequence • Numbers can be reused if CYCLE is specified • The options determine whether Oracle should begin reissuing values from the sequence after reaching the minimum or maximum value. • ORDER clause is used in application cluster environment • Use CACHE to pregenerate integers – Default: 20

  8. ORDERS Data Files

  9. Refresh the Database • 1. Go to Blackboard and download (two) data files from Oracle chapter6 and save under c:\oradata\chapter6\ • 2. Run the following script file • Start c:\oradata\chapter6\JLDB_Build_6.sql

  10. Creating a Sequence (continued) Figure 6-3 Syntax of the INSERT command

  11. Creating a Sequence (continued) Figure 6-4 Query USER-OBJECTS to verify existing sequence

  12. Creating a Sequence (continued) • To verify the settings for options of a sequence, query USER_SEQUENCES data dictionary view Figure 6-5 Verifying sequence option settings SELECT SEQUENCE_NAME FROM USER_SEQUENCES; SEQUENCE_NAME ------------------------------ ORDERS_ORDER#_SEQ Next Number to issue

  13. Using Sequence Values • NEXTVAL – generates integer Figure 6-6 Inserting a row, using a sequence to provide a PRIMARY KEY value

  14. Using Sequence Values (continued) Figure 6-7 Order added, using a sequence value for the Order#

  15. Using Sequence Values (continued) • CURRVAL – contains last integer generated by NEXTVAL Figure 6-8 Using CURRVAL to insert an order detail row

  16. Using Sequence Values (continued) Figure 6-9 Verify the CURRVAL value

  17. Altering Sequence Definitions • Use ALTER SEQUENCE command to change the settings for a sequence • START WITH value cannot be altered – drop the sequence and re-create it • Changes cannot make current integers invalid

  18. ALTER SEQUENCE Command Example Figure 6-11 Command to change the INCREMENT BY setting for a sequence

  19. Checking Values on Sequences and SYSDATE Figure 6-12 New setting for the ORDERS_ORDER#_SEQ sequence Figure 6-13 Using the DUAL table

  20. Checking Values on Sequences Figure 6-14 Testing sequence values with the DUAL table Why 1041?

  21. Removing a Sequence • Use the DROP SEQUENCE command to delete a sequence • Previous values generated are not affected by removing a sequence from a database Figure 6-16 Dropping the ORDERS_ORDER#_SEQ sequence

  22. Removing a Sequence (continued) Figure 6-17 Verify that the sequence is removed

  23. Indexes –Query Optimization • An index stores frequently referenced values and ROWIDs • Can be based on one column, multiple columns, functions, or expressions • If database query activity (e.g., process speed) is the priority • Indexes should be considered • If database operations involve more DML actions than query actions • Index creation should be minimized

  24. B-Tree Index Figure 6-19 B-tree index organization

  25. B-Tree Index (continued) • Implicitly create an index by PRIMARY KEY and UNIQUE constraints • Explicitly create an index by using the CREATE INDEX command For example: SELECT index_name FROM user_indexes; -- result on the next slide

  26. Examples on Query Optimization • Note that the following slides are not in the text. • Please study them thoroughly and practice all examples (SQL commands are available in the ‘Ch6Queries.sql’.

  27. For example: SELECT index_name FROM user_indexes; INDEX_NAME ------------------------------ ACCTBONUS_AMID_PK BOOKAUTHOR_PK ORDERITEMS_PK BOOKS_ISBN_PK BOOKS_COST_IDX AUTHOR_AUTHORID_PK PUBLISHER_PUBID_PK ORDERS_ORDER#_PK CUSTOMERS_ZIP_DESC_IDX CUSTOMERS_ZIP_IDX CUSTOMERS_CUSTOMER#_PK EMPLOYEES_EMPNO_PK PT_CHARG_PATIENTNO_ITEMCODE_PK ITEM_ITEM_CODE_PK ROOM_ROOM_NO_PK ACCOMODATION_ACCOM_ID_PK DOCTOR_PHYSCIAN_ID__PK PATIENT_PATIENT_NO_PK LOCATION_LOC_ID_PK INDEX_NAME ------------------------------ ENROLLMENT_PK COURSE_SECTION_CSEC_ID_PK COURSE_COURSE_ID_PK TERM_TERM_ID_PK STUDENT_S_ID_PK SYS_IL0000110103C00010$$ FACULTY_F_ID_PK PUBLISHER3_PUBID_PK PUBLISHER2_PUBID_PK REPCONTRACTS_PK BOOKSTORES_ID_PK BOOKSTORES_NAME_UK STOREREPS_ID_PK 32 rows selected. What specific information is displayed on the output? Answer: pk Since they are all implicitly indexed.

  28. Implicitly Index (cont.) -- enter in SQL DEVELOPER SELECT table_name, index_name, index_type FROM user_indexes;

  29. Optimizing Query Processing -A Quick Way to Speed Access to Your data • Indexes may be used to improve the efficiency of data searches to meet particular search criteria after the table has been in use for some time. Therefore, the ability to create indexes quickly and efficiently at any time is important. • SQL indexes can be created on the basis of any selected attributes

  30. Optimizing Query Processing – an example (not in the text) BOOKS BOOKS

  31. -- chapter 6, (about the same as Figure 6-30; p. 199 ) -- 'BOOKS' must be in upper case SELECT table_name, index_name, index_type FROM user_indexes WHERE table_name = 'BOOKS'; Optimizing Query Processing SELECT isbn, title, cost, category FROM books WHERE cost > 20; ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 2147428890 SHORTEST POEMS 21.85 LITERATURE 7 rows selected.

  32. Optimizing Query Processing (conti.) Syntax: CREATE INDEXname_of_index ON table_name (field_to_be_indexed); For example (but, do not enter the SQL command now): CREATE INDEX books_cost_idx ON books (cost); Note that this statement defines an index called books_cost_idxfor the cost column in the books table. This index ensures that in the next example SQL only needs to look at row in the database that satisfy the WHERE condition, and is, therefore, quicker to produce an answer.

  33. Optimizing Query Processing (conti.) Note that this statement defines an index called books_cost_idx for the cost column in the books table. This index ensures that in the next example SQL only needs to look at row in the database that satisfy the WHERE condition, and is, therefore, quicker to produce an answer. SELECT isbn, title, cost, category FROM books WHERE cost > 20; CREATE INDEX books_cost_idx ON books (cost);

  34. Optimizing Query Processing (conti.) Type the following SQL: SELECT isbn, title, cost, category FROM books WHERE cost > 20; ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 2147428890 SHORTEST POEMS 21.85 LITERATURE 7 rows selected. CREATE INDEX books_cost_idx ON books (cost); SQL> CREATE INDEX books_cost_idx ON books (cost); Index created.

  35. Optimizing Query Processing (conti.) SELECT isbn, title, cost, category FROM books WHERE cost > 20; ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 2147428890 SHORTEST POEMS 21.85 LITERATURE 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 7 rows selected. What is the difference on the output between these two versions?

  36. Before “INDEX”. ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 2147428890 SHORTEST POEMS 21.85 LITERATURE 7 rows selected. After “INDEX”. ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 2147428890 SHORTEST POEMS 21.85 LITERATURE 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 7 rows selected.

  37. Index on Query Optimization SELECT table_name, index_name, index_type FROM user_indexes WHERE table_name = 'BOOKS';

  38. Index on Query Optimization (conti.) -- default is ‘ASCENDING’ CREATE INDEX books_cost_desc_idx ON books (cost DESC); SELECT isbn, title, cost, category FROM books WHERE cost > 20; ISBN TITLE COST CATEGORY ---------- ------------------------------ ---------- ------------ 1915762492 HANDCRANKED COMPUTERS 21.8 COMPUTER 2147428890 SHORTEST POEMS 21.85 LITERATURE 8843172113 DATABASE IMPLEMENTATION 31.4 COMPUTER 4981341710 BUILDING A CAR WITH TOOTHPICKS 37.8 CHILDREN 9959789321 E-BUSINESS THE EASY WAY 37.9 COMPUTER 3957136468 HOLY GRAIL OF ORACLE 47.25 COMPUTER 2491748320 PAINLESS CHILD-REARING 48 FAMILY LIFE 7 rows selected. Why the output is still displayed as “ascending” order? Internally, the books table has been indexed as ‘______’ order (using pointer); however, the display is on the ‘_________’ (default) order.

  39. Index on Query Optimization (conti.) -- chapter 6, (about the same as Figure 6-30; p. 199 ) -- 'BOOKS' must be in upper case SELECT table_name, index_name, index_type FROM user_indexes WHERE table_name = 'BOOKS';

  40. Query Optimization: DROP INDEX DROP INDEX books_cost_idx; DROP INDEX books_cost_desc_idx; -- chapter 6, (about the same as Figure 6-30; p. 199 ) -- 'BOOKS' must be in upper case SELECT table_name, index_name, index_type FROM user_indexes WHERE table_name = 'BOOKS';

  41. Summary on Optimizing Query Processing (cont.) • The indexes are defined so as to optimize the processing of SELECT statements. ; • An index is never explicitly referenced in a SELECT statement; the syntax of SQL does not allow this; • During the processing of a statement, SQL itself determines whether an existing index will be used; • An index may be created or deleted at any time; • When updating, inserting or deleting rows, SQL also maintains the indexes on the tables concerned. This means that, on the one hand, the processing time for SELECT statements is reduced, while, on the other hand, the processing time for update statements (such as INSERT, UPDATE and DELETE) can increase.

  42. Your Turn … • Practice the following examples (from the text)

  43. CREATE INDEX Command Examples Figure 6-18 Creating an index on the Zip column

  44. The Explain Plan Figure 6-20 View the explain plan indicating a full table scan

  45. The Explain Plan (continued) Figure 6-21 View the explain plan indicating a full table scan

  46. UNIQUE INDEX Figure 6-22 Explicitly creating a unique index -- chapter 6, Figure 6-23; p. 195 CREATE INDEX customers_zip_desc_idx ON customers (zip DESC);

  47. CREATE INDEX Command Examples • The composite index could improve the performance of queries that include a search condition on both the Lastname and Firstname column. • Generally, it is more efficient than creating two separate single-column indexes because less I/O is required to read a single index. Figure 6-24 Creating a composite index

  48. Verifying Composite Index SELECT customer#, lastname, firstname, city, state, zip FROM customers; -- chapter 6, Figure 6-24; p. 195 CREATE INDEX customer_name_idx ON customers (lastname, firstname); SELECT customer#, lastname, firstname, city, state, zip FROM customers; Any performance improvement? After tested, no significant performance can be found since it is a very small database.

  49. Practice all the examples in the text. • A Script file is available on the Bb (file name: Ch6Queries.sql) • After completing all examples, do the HW.

  50. Homework - Hands-On Assignments Email me with one attachment (Oracle_ch6_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch6 (or Bmis441-02_Oracle_ch6) Read and Practice all examples on Chapters 6 • 1. Run the script files (in the folder \oradata\chapter5\): JLDB_Build_6.sql • 2. Read Oracle assignment and create a script file Oracle_ch6_Lname_Fname.sql for questions (#1 to #5 and #9; pp.211-212) on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs if needed. • 3. Execute and test one problem at a time and make sure they are all running successfully. • 4. When you done, spool the script files (see next slide for spooling instructions) and email the file (Oracle_ch6_Spool_Lname_Fname.txt) to me by the midnight before the next class.

More Related