Creating And Managing Other Oracle Database Objects

6

Creating and Managing Other Oracle Database Objects

Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Create and maintain a view • Retrieve, insert, update, and delete data through a view • Create, maintain, and use sequences • Create and maintain indexes • Create private and public synonyms

6-2

Copyright © 2004, Oracle. All rights reserved.

Understanding Database Objects

6-3

Object

Description

Table

Basic unit of storage; composed of rows

View

Logically represents subsets of data from one or more tables

Materialized View

Unlike views, they are physical tables that summarize, compute, replicate, and distribute data

Sequence

Generates numeric values

Index

Improves the performance of some queries

Synonym

Gives alternative names to objects

Copyright © 2004, Oracle. All rights reserved.

Understanding Indexes An index: • Is a schema object • Is used by the Oracle server to speed up the retrieval of rows by using a pointer • Can reduce disk I/O by using a rapid path access method to locate data quickly • Is independent of the table it indexes • Is used and maintained automatically by the Oracle server 6-4

Root Index entry Branch

Leaf

Copyright © 2004, Oracle. All rights reserved.

Index entry header Key column length Key column value Row ID

6-5

Copyright © 2004, Oracle. All rights reserved.

Creating an Index

6-6

•

Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

•

Manually: Users can create nonunique indexes on columns to speed up access to the rows.

Copyright © 2004, Oracle. All rights reserved.

Creating an Index •

Create an index on one or more columns.

CREATE INDEX index ON table (column[, column]...);

•

Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table.

CREATE INDEX emp_last_name_idx ON employees(last_name); Index created.

6-7

Copyright © 2004, Oracle. All rights reserved.

Creating an Index

You should not create an index if: You should create an index if: • The table is small • A column contains a wide • The columns are not often range of values used as a condition in the • A column contains a large query number of null values • Most queries are expected to • One or more columns are retrieve more than 2–4% of the frequently used together in rows in the table a WHERE clause or a join • The table is updated frequently condition • The indexed columns are • The table is large and most referenced as part of an queries are expected to expression retrieve less than 2–4% of the rows

6-8

Copyright © 2004, Oracle. All rights reserved.

Confirming Indexes

•

•

The USER_INDEXES data dictionary view contains the name of the index and information about whether it is unique. The USER_IND_COLUMNS view contains the index name, the table name, and the column name.

SELECT FROM WHERE AND

6-9

ic.index_name, ic.column_name, ic.column_position col_pos,ix.uniqueness user_indexes ix, user_ind_columns ic ic.index_name = ix.index_name ic.table_name = 'EMPLOYEES';

Copyright © 2004, Oracle. All rights reserved.

Creating Function-Based Indexes • •

A function-based index is an index based on expressions. The index expression is built from table columns, constants, SQL functions, and user-defined functions.

CREATE INDEX upper_dept_name_idx ON departments(UPPER(department_name)); Index created. SELECT * FROM departments WHERE UPPER(department_name) = 'SALES';

6-10

Copyright © 2004, Oracle. All rights reserved.

6-11

Copyright © 2004, Oracle. All rights reserved.

Creating an Index While Creating a Table

CREATE TABLE new_emp (employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON new_emp(employee_id)), first_name VARCHAR2(20), last_name VARCHAR2(25)); Table created. SELECT INDEX_NAME , TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = ‘new_emp’;

6-12

Copyright © 2004, Oracle. All rights reserved.

6-13

Copyright © 2004, Oracle. All rights reserved.

Removing an Index •

Remove an index from the data dictionary by using the DROP INDEX command.

DROP INDEX index;

•

Remove the UPPER_LAST_NAME_IDX index from the data dictionary.

DROP INDEX upper_last_name_idx; Index dropped.

•

6-14

To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.

Copyright © 2004, Oracle. All rights reserved.

Understanding Views EMPLOYEES Table

EMPVU80 View

…

… 6-15

Copyright © 2004, Oracle. All rights reserved.

Differentiating Simple Views from Complex Views

6-16

Feature

Simple Views

Complex Views

Number of tables

One

One or more

Contain functions

No

Yes

Contain groups of data

No

Yes

DML operations through a view

Yes

Not always

Copyright © 2004, Oracle. All rights reserved.

Creating a View

•

You embed a subquery within the CREATE VIEW statement.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY [CONSTRAINT constraint]];

•

6-17

The subquery can contain complex SELECT syntax.

Copyright © 2004, Oracle. All rights reserved.

Creating a Simple View

•

Create a view, EMPVU80, containing details of employees in department 80. CREATE VIEW empvu80 AS SELECT employee_id, last_name, salary FROM employees WHERE department_id = 80; View created.

•

Describe the structure of the view by using the iSQL*Plus DESCRIBE command.

DESCRIBE empvu80

6-18

Copyright © 2004, Oracle. All rights reserved.

6-19

Copyright © 2004, Oracle. All rights reserved.

Creating a Complex View Create a complex view that contains group functions to display values from two tables. CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name; View created.

6-20

Copyright © 2004, Oracle. All rights reserved.

6-21

Copyright © 2004, Oracle. All rights reserved.

Querying a View

Oracle server iSQL*Plus SELECT FROM

…

6-22

* empvu80;

USER_VIEWS EMPVU80

SELECT employee_id, last_name, salary FROM employees WHERE department_id=80;

EMPLOYEES

Copyright © 2004, Oracle. All rights reserved.

Modifying a View

•

Modify the EMPVU80 view by using the CREATE OR REPLACE VIEW clause. Add an alias for each column name.

CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS SELECT employee_id, first_name || ' '|| last_name, salary, department_id FROM employees WHERE department_id = 80; View created.

•

6-23

Column aliases in the CREATE VIEW clause are listed in the same order as the columns in the subquery.

Copyright © 2004, Oracle. All rights reserved.

Performing DML Operations on a View

View Contains

INSERT

UPDATE DELETE

Group functions GROUP BY clause DISTINCT keyword ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables (that are not selected by the view)

Note: 6-24

signifies that the operation is not allowed. Copyright © 2004, Oracle. All rights reserved.

Using the WITH CHECK OPTION Clause •

You can ensure that DML operations performed on the view stay within the domain of the view by using the WITH CHECK OPTION clause.

CREATE OR REPLACE VIEW empvu20 AS SELECT * FROM employees WHERE department_id = 20 WITH CHECK OPTION CONSTRAINT empvu20_ck; View created.

•

6-25

Any attempt to change the department number for any row in the view fails, because it violates the WITH CHECK OPTION constraint.

Copyright © 2004, Oracle. All rights reserved.

6-26

Copyright © 2004, Oracle. All rights reserved.

Denying DML Operations •

•

You can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition. Any attempt to perform a DML operation on any row in the view results in a server error. CREATE OR REPLACE VIEW empvu10 (employee_number, employee_name, job_title) AS SELECT employee_id, last_name, job_id FROM employees WHERE department_id = 10 WITH READ ONLY CONSTRAINT empvu10_ro ; View created.

6-27

Copyright © 2004, Oracle. All rights reserved.

Removing a View You can remove a view without losing data because a view is based on underlying tables in the database. Syntax: DROP VIEW view;

Example: DROP VIEW empvu80; View dropped.

6-28

Copyright © 2004, Oracle. All rights reserved.

Understanding Inline Views •

• •

An inline view is a subquery with an alias (or correlation name) that you can use within a SQL statement. An inline view is similar to using a named subquery in the FROM clause of the main query. An inline view is not a schema object.

SELECT FROM

WHERE AND

6-29

a.last_name, a.salary, a.department_id, b.maxsal employees a, (SELECT department_id, max(salary) maxsal FROM employees GROUP BY department_id) b a.department_id = b.department_id a.salary < b.maxsal;

Copyright © 2004, Oracle. All rights reserved.

6-30

Copyright © 2004, Oracle. All rights reserved.

Understanding Materialized Views • • • •

Previously called snapshot Unlike views, they are physical tables Can be used to summarize, compute, replicate, and distribute data Suitable for: – Data warehousing – Distributed computing – Mobile computing

6-31

Copyright © 2004, Oracle. All rights reserved.

6-32

Copyright © 2004, Oracle. All rights reserved.

Comparing Materialized Views with Indexes •

Similarities: – Both consume storage space. – Both must be refreshed when data in master table changes. – Both improve the performance of SQL execution. – Both are transparent to SQL applications and users.

•

Differences: – Materialized views can be accessed directly using a SELECT statement

6-33

Copyright © 2004, Oracle. All rights reserved.

Creating a Materialized View

•

Created using the CREATE MATERIALIZED VIEW statement. CREATE MATERIALIZED VIEW [LOG ON] mat_view ENABLE QUERY REWRITE AS subquery;

6-34

Copyright © 2004, Oracle. All rights reserved.

Understanding Sequences A sequence: • Automatically generates unique numbers • Is a sharable object • Is typically used to create a primary-key value • Replaces application code • Speeds up the efficiency of accessing sequence values when cached in memory

6-35

Copyright © 2004, Oracle. All rights reserved.

Creating a Sequence Define a sequence to generate sequential numbers automatically. CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; Sequence created. 6-36

Copyright © 2004, Oracle. All rights reserved.

6-37

Copyright © 2004, Oracle. All rights reserved.

Confirming Sequences •

Verify your sequence values in the USER_SEQUENCES data dictionary table.

SELECT FROM

•

6-38

sequence_name, min_value, max_value, increment_by, last_number user_sequences;

The LAST_NUMBER column displays the next available sequence number if NOCACHE is specified.

Copyright © 2004, Oracle. All rights reserved.

Using a Sequence •

You can refer to sequence values in SQL statements with the following pseudocolumns: – The CURRVAL pseudocolumn returns the current value of a sequence. – The NEXTVAL pseudocolumn increments the sequence and returns the next value.

•

• • 6-39

You must qualify CURRVAL and NEXTVAL with the name of the sequence: – sequence.CURRVAL – sequence.NEXTVAL

NEXTVAL returns a unique value every time it is referenced, even for different users. NEXTVAL must be issued for that sequence before CURRVAL contains a value. Copyright © 2004, Oracle. All rights reserved.

6-40

Copyright © 2004, Oracle. All rights reserved.

Using a Sequence: Example •

Insert a new department named Support.

INSERT INTO depts(id,name) VALUES (dept_deptid_seq.NEXTVAL, 'Support'); 1 row created.

•

View the current value for the DEPT_DEPTID_SEQ sequence.

SELECT FROM

6-41

dept_deptid_seq.CURRVAL dual;

Copyright © 2004, Oracle. All rights reserved.

Caching Sequence Values • •

Caching sequence values in memory allows faster access to those values. Gaps in sequence values can occur when: – A rollback occurs – The system crashes – A sequence is used in another table

•

6-42

If the sequence was created with NOCACHE, view the next available value, by querying the USER_SEQUENCES table.

Copyright © 2004, Oracle. All rights reserved.

6-43

Copyright © 2004, Oracle. All rights reserved.

Altering a Sequence Change the increment value, maximum value, minimum value, cycle option, or cache option. ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; Sequence altered.

6-44

Copyright © 2004, Oracle. All rights reserved.

Modifying Sequences: Guidelines

• • • •

6-45

You must be the owner or have the ALTER privilege for the sequence. Only future sequence numbers are affected. The sequence must be dropped and re-created to restart the sequence at a different number. Some validation is performed.

Copyright © 2004, Oracle. All rights reserved.

Removing a Sequence • •

Remove a sequence from the data dictionary by using the DROP SEQUENCE statement. Once removed, the sequence can no longer be referenced.

DROP SEQUENCE dept_deptid_seq; Sequence dropped.

6-46

Copyright © 2004, Oracle. All rights reserved.

Creating a Synonym for an Object • •

Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can: – Refer to a table owned by another user – Shorten lengthy object names

CREATE [PUBLIC] SYNONYM synonym FOR object;

6-47

Copyright © 2004, Oracle. All rights reserved.

Creating and Removing Synonyms

•

Create a shortened name for the DEPT_SUM_VU view.

CREATE SYNONYM d_sum FOR dept_sum_vu; Synonym Created.

•

Drop a synonym.

DROP SYNONYM d_sum; Synonym dropped.

6-48

Copyright © 2004, Oracle. All rights reserved.

Summary In this lesson, you should have learned how to do the following: • Create and maintain a view • Retrieve, insert, update, and delete data through a view • Create, maintain, and use sequences • Create and maintain indexes • Create private and public synonyms

6-49

Copyright © 2004, Oracle. All rights reserved.

6-50

Copyright © 2004, Oracle. All rights reserved.

Practice 6: Overview This practice covers the following topics: • Creating a simple and a complex view • Creating a view with a CHECK constraint • Attempting to modify data in the view • Removing views • Creating and using sequences • Creating nonunique indexes • Displaying data dictionary information about views, sequences, and indexes • Dropping indexes

6-51

Copyright © 2004, Oracle. All rights reserved.

6-52

Copyright © 2004, Oracle. All rights reserved.

6-53

Copyright © 2004, Oracle. All rights reserved.

6-54

Copyright © 2004, Oracle. All rights reserved.