Creating other Schema Objects
- Database Objects
a.Table – Basic Unit of storage. Composed of Rows.
b.View – Logically represent subsets of Data from one or more tables.
c.Sequence – Generate Numerical Values
d.Index – Improves performance of some queries
e. Synonym – Gives an alternate names to objects.
Views present and hide Data from Tables.
Many applications require the use of unique numbers as Primary Key Values.
We can build the code into the application to handle this requirement or use a sequence to generate unique numbers.
If we want to improve the performance of some queries we can consider creating an Index.
The index can also be created to enforce uniqueness on a column or a collection of columns.
We can provide alternate names for objects by using Synonyms.
2. What is a View?
We can present logical subsets or combinations of data by creating views of Tables.
A view is a logical table based on a Table or another view.A view contains no data of its own but is like a window through which data from tables can be viewed or changed.
The Tables on which a view is based are called Base Tables.
The view is stored as a select statement in the Database.
3. Advantages of a View
a.To restrict Data Access – Views restrict access to data because the view can display selected columns from the table.
b.To make complex queries easy – Views can be used to make simple queries to retrieve the results of complicated queries.
For ex. Views can be used to query information from multiple tables without the user knowing how to write a join statement.
c.To provide Data independence – One view can be used to retrieve data from several tables.
d.To present different views of the same data
4. Simple views and Complex Views
There are two classifications of view :
a.Simple View –
This view :
- Derives data from only one Table.
- Contains no functions or groups of data
- Can perform DML operations through the view.
b. Complex View –
This view :
- Derives data from many tables.
- Contains functions or groups of data
- Does not always allow DML operations through the view.
5. Creating a View
Syntax
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]…)]
AS
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE – Re-creates the view if it already exists after editing the SQL query.
FORCE – Creates the view regardless of whether or not the base tables exists.
NOFORCE – Creates the view only if the Base Tables exists (This is the default)
alias – Specifies names for the expressions selected by the view’s query
(The no. of aliases must match the no.of expressions selected by the view)
WITH CHECK OPTION – Specifies that only those rows that are accessible to the view can be inserted or updated.
constraint – It is the name assigned to the CHECK OPTION constraint
WITH READ ONLY – ensures that no DML operations can be performed on this view.
6. Creating a View ..Contd
Ex.
— Create view EMPVU20 which contain detail of Employee in Department 20 —
CREATE VIEW empvu20
AS
SELECT empno,ename,sal,deptno
FROM EMP
WHERE deptno = 20
/
DESCRIBE empvu20;
SELECT empno,ename,sal,deptno
FROM EMP
WHERE deptno = 20
/
SELECT *
FROM empvu20
/
DROP VIEW empvu20
/
7. Creating a View ..Contd
— Create View using Column Aliases in the Query —
CREATE VIEW salvu20
AS SELECT empno ID_NUMBER,ename NAME,sal * 12 ANN_SALARY
FROM emp
WHERE deptno = 20
/
DESCRIBE salvu20
SELECT empno ID_NUMBER,ename NAME,sal * 12 ANN_SALARY
FROM emp
WHERE deptno = 20
/
SELECT ID_NUMBER,NAME,ANN_SALARY
FROM salvu20
/
As an alternative to this, we can use an alias after the CREATE statement and before the SELECT query.
The no. of aliases listed must match the no. of expressions selected in the query.
CREATE OR REPLACE VIEW salvu20(ID_NUMBER,NAME,ANN_SALARY)
AS SELECT empno no,ename,sal * 12
FROM emp
WHERE deptno = 30
/
SELECT ID_NUMBER,NAME,ANN_SALARY
FROM salvu20
/
INSERT INTO salvu20(id_number,ann_salary)values(900,1000)
/
DROP VIEW salvu20 PURGE
/
8. Retrieving Data from a view
Ex.
— Query to retrieve records from the view —
SELECT * FROM salvu20
/
We can retrieve data from a view as you would from any table.
We can display either the contents of the entire view or just specific rows and columns.
9.Modifying a View
Ex.
— Query to Modify the view EMPVU20 and add alias for each column name —
CREATE OR REPLACE VIEW empvu20
(id_number,name,salary)
AS
SELECT
empno,ename||’ designation is ‘||job,sal
FROM emp
WHERE deptno = 20
/
SELECT NAME FROM empvu20
/
Note the column aliases in the CREATE OR REPLACE VIEW clause are listed in the same order as the columns in the query.
10.Creating a Complex View
Ex.
— Query to create a Complex view that contains Group Functions to display values from two tables —
SELECT d.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)
FROM emp e JOIN dept d
ON (e.deptno = d.deptno)
GROUP BY d.dname
/
CREATE OR REPLACE VIEW dept_sum_vu
(name,minsal,maxsal,avgsal)
AS
SELECT d.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)
FROM emp e JOIN dept d
ON (e.deptno = d.deptno)
GROUP BY d.dname
/
SELECT * FROM dept_sum_vu
/
11. Rules for performing DML operations on a View
a.We can usually perform DML operations on simple views.
b.We cannot remove a row if the view contains the following :
- GROUP Functions
- A GROUP BY Clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyword
c.We cannot modify data in a view if it contains : - GROUP Functions
- A GROUP BY Clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyword
- Columns defined by expressions
d.We cannot add data through a view if the view includes : - Group Functions
- A GROUP BY Clause
- The DISTINCT keyword
- The pseudocolumn ROWNUM keyword
- Columns defined by expressions
- NOT NULL columns in the base tables that are not selected by the view.
12. Using the WITH CHECK OPTION Clause
a.We can ensure that DML operations performed on the view stay in the domain of the view by using the WITH CHECK OPTION clause.
b.Any attempt to change the department number for any row in the view fails because it violates the WITH CHECK OPTION clause.
Ex.
— Query to modify the view EMPVU20 using the WITH CHECK OPTION Clause —
CREATE OR REPLACE VIEW empvu20
AS
SELECT *
FROM emp
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT empvu20_chk
/
SELECT owner,constraint_name,constraint_type,table_name FROM
user_constraints WHERE constraint_name = ‘EMPVU20_CHK’
/
UPDATE empvu20 SET deptno = 30
WHERE empno = 7369
/
The WITH CHECK OPTION clause relates to inserts and updates on the view only.It has no effect on Deletes.
13.Denying DML operations
a. We can ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition.
b.Any attempt to perform a DML operation on any row in the view results in an Oracle server error.
Ex.
— Query to deny DML operations for the view using WITH READ —
CREATE OR REPLACE VIEW empvu20
AS
SELECT * FROM emp
WHERE deptno = 20
WITH READ ONLY
/
INSERT INTO empvu20(empno)values(100)
/
DELETE empvu20
/
SELECT owner,constraint_name,constraint_type,table_name FROM
user_constraints WHERE table_name = ‘EMPVU20’
/
14. Removing a View
We can remove a view without losing data because a view is based on underlying tables in the database.
DROP VIEW view_name
/
Dropping the view has no effect on the tables on which the view was based.
Views or other applications based on deleted views become invalid.
— Force View —
SELECT * FROM dept_sum_vu
/
CREATE FORCE VIEW a_v AS SELECT * FROM a
/
CREATE TABLE a(id NUMBER)
/
SELECT * FROM a_v
/
DROP VIEW a_v
/
DROP TABLE a PURGE
/
15. Sequences
A Sequence is a Database Object that creates integer values.
We can create a Sequence and then use them to generate numbers.
A sequence :
a.Can automatically generate unique numbers
b.Can be used to create a Primary Key value
c.Replaces the Application Code
d.Speeds up the efficiency of accessing sequence values when cached in memory.
Sequence numbers are stored and generated independently of Tables.
The same sequence can be used for multiple tables.
16.CREATE SEQUENCE Statement: Syntax
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
In the syntax :
INCREMENT BY n – Specifies the interval between sequence numbers where n is an integer (If this clause is omitted the sequence increments by 1)
START WITH n – Specifies the first sequence number to be generated
(If this clause is omitted the sequence starts with 1)
MAXVALUE n – Specifies the maximum value the sequence can generate
NOMAXVALUE – Specifies a maximum value of 10^27 (99999999999999999999999999)
MINVALUE n – Specifies the minimum sequence value
NOMINVALUE – Specifies a minimum value of 1
17. Creating a Sequence
a.Create a Sequence named EMP_EMPNO_SEQ to be used for the Primary Key of the EMP Table.
b.Do not use the CYCLE Option for the Sequence if this is used to generate Primary key values unless we have a reliable mechanism that purges old rows faster.
— Query to create the Sequence EMP_EMPNO_SEQ —
CREATE SEQUENCE emp_empno_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 99999
CACHE 5
/
SELECT * FROM user_sequences WHERE sequence_name = ‘EMP_EMPNO_SEQ’
/
CACHE – This option specifies how many sequence values will be stored in memory for faster access.
By default the Oracle server caches 20 values.
The downside of creating a sequence with a cache is that if a system failure occurs all cached sequence values that have not been used will be lost resulting in a gap in the assigned sequence values.
When the system reboots Oracle will cache new numbers from where it left off in the sequence ignoring the lost sequence values.
NOCACHE – This option means that none of the sequence values are stored in memory.
18. NEXTVAL and CURRVAL pseudo columns
a.NEXTVAL returns the next available sequence value.It returns a unique value every time it is referenced even for different users using the same sequence.
b.CURRVAL obtains the current sequence value
c.NEXTVAL must be issued for that sequence before CURRVAL contains a value.
Rules for using NEXTVAL and CURRVAL :
We can use NEXTVAL and CURRVAL in the following contexts :
1. The SELECT list of a SELECT statement that is not part of a subquery
2. The SELECT list of a Subquery in an INSERT statement
3. The VALUES clause of an INSERT statement
4. The SET clause of an UPDATE statement
We cannot use NEXTVAL and CURRVAL in the following contexts:
1.The SELECT list of a view
2. A SELECT statement with the DISTINCT keyword
3. A SELECT statement with GROUP BY, HAVING, or ORDER BY clause
4. A subquery in a SELECT, DELETE or UPDATE statement
5. The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement
19. Using a Sequence
Ex.
a.Insert a new Employee named ‘JOHN’ in Department 30 using the Sequence: emp_empno_seq
INSERT INTO emp VALUES
(emp_empno_seq.NEXTVAL,’JOHN’,
‘MANAGER’,7839,
’01-JAN-2010′,10000,
10,30)
/
SELECT * FROM emp
/
b.View the current value for the emp_empno_seq sequence
SELECT emp_empno_seq.currval FROM dual
/
SELECT emp_empno_seq.nextvaL FROM dual
/
20. Caching sequence values
a.Caching sequence values in memory give faster access to those values
b.Gaps in sequence values can occur when:
- A Rollback occurs
- The system crashes
- A sequence is used in another table
We can cache sequences in memory to provide faster access to those sequence values.The cache is populated the first time you refer to the sequence.
Each request for the next sequence value is retrieved from the cached sequence. After the last sequence value is used the next request for the sequence pulls another cache of sequences into memory.
21. Modifying a Sequence
Change the increment value,maximum value,minimum value,cycle option or cache option
Ex.
— Query to alter the Sequence emp_empno_seq —
ALTER SEQUENCE emp_empno_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE
/
ALTER SEQUENCE emp_empno_seq
INCREMENT BY 20
MAXVALUE 99
NOCACHE
NOCYCLE
/
The MAXVALUE cannot be less than the value set during the sequence creation.
The above example will error out.
The START WITH option cannot be used in ALTER SEQUENCE clause.
The sequence has to be dropped and then recreated again to use the new START WITH option.
SELECT * FROM user_sequences WHERE sequence_name = ‘EMP_EMPNO_SEQ’
/
To remove the Sequence object created
DROP SEQUENCE emp_empno_seq
/
22. Indexes
Indexes are database objects that you can create to improve the performance of some queries.
An Index :
a.Is a schema object
b.Can be used by the Oracle server to speed up the retrieval of rows by using a pointer
c.Can reduce disk I/O by using a rapid path access method to locate data quickly.
d.Is independent of the table that it indexes
e.Is used and maintained automatically by the Oracle server.
If you do not have an index on the column then a full table scan occurs.
When you drop a Table corresponding indexes are also dropped.
23.How are indexes created ?
a.Automatically – A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a Table definition
b.Manually – Users can create nonunique indexes on columns to speed up access to rows.
Unique Index : The Oracle server automatically creates this index when you define a column in the table to have a PRIMARY KEY or a UNIQUE key constraint.
The name of the index is the name that is given to the constraint.
Nonunique Index: This is an index that a user can create.
For example you can create a FOREIGN KEY column index for a join in a query to improve retrieval speed.
24. Creating an Index
a.Create an index on one or more columns
Ex.
— Query to create an Index on ID column for EMP Table —
CREATE INDEX emp_ename_idx
ON emp(ename)
/
SELECT index_name FROM user_indexes WHERE table_name = ‘EMP’
/
— Query to create a Unique Index for a Column in a Table —
CREATE TABLE test_tab(id NUMBER PRIMARY KEY,name VARCHAR2(150),
place VARCHAR2(150))
/
CREATE UNIQUE INDEX TEST_TAB_IDX
ON test_tab(name,place)
/
SELECT index_name,table_name,table_type,uniqueness FROM user_indexes
WHERE table_name = ‘TEST_TAB’
/
— Query to create a Function Based Index —
CREATE INDEX TEST_TAB_IDX_FUNC
ON test_tab(UPPER(name))
/
SELECT index_name,table_type,index_type,uniqueness
FROM user_indexes
WHERE table_name = ‘TEST_TAB’
/
— Test this Index for the following SQL Query —
SELECT id,UPPER(name)
FROM test_tab
WHERE UPPER(name) IS NOT NULL
ORDER BY UPPER(name);
DROP TABLE test_tab
/
— Index gets dropped the moment a Table is dropped —
SELECT index_name,table_name,table_type,uniqueness
FROM user_indexes
WHERE table_name = ‘TEST_TAB’
/
— Query to rename an Index —
ALTER INDEX RENAME
/
— Query to get the Column Names a Table on which Index is created —
SELECT * FROM user_ind_columns WHERE table_name =
/
25. Removing an Index
Ex.
DROP INDEX emp_ename_idx
/
If you drop a Table indexes and constraints are automatically dropped by views and sequences remain.
26. Synonyms
Simplify access to objects by creating a Synonym i.e (Another name for an object)
With synonyms we can :
a.Create an easier reference to a Table that is owned by another user.
b.Shorten lengthy object names
Syntax :
CREATE [PUBLIC] SYNONYM
FOR