Sunday 18 September 2011

VIEWS SEQUENCES INDEXES AND SYNONMS


VIEWS SEQUENCES INDEXES AND SYNONMS

View
To embed a sub-query within the CREATE VIEW Statement.
CREATE [OR REPLACE][FORCE|INFORCE] VIEW view [(alias[,alias]..)] as subquery
[WITH CHECK OPTION[CONSTRAINT constraint]] [WITH READY ONLY]

OR REPLACE - recreates the view if it already exists.

FORCE - creates the view regardless of whether or not the base tables exist.

NOFORCE- creates the view only if the base tables exist. This is the default.

View - the name of the view
Alias - specifies names for the expressions selected by the view's query. The number of aliases must match the number of expressions selected by the view.
Sub-query - A complete SELECT statement. You can use aliases for the columns in the SELECT list.

WITH CHECK OPTION - specified that only rows accessible to the view can be inserted or updated.

Constraint - the name assigned to the CHECK OPTION constraint.

WITH READ ONLY - ensures that no DML operations can be performed on this view.

Creating a view.

SQL> create view empvu10 as select empno,ename,job from emp where deptno=10;

View created.


Describing the structure of a view.

SQL> desc empvu10;
Name Null? Type
----------------- ------- ---------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)


Retrieving Data from a view.

SQL> select * from empvu10;


EMPNO ENAME JOB
------ -------- ---------
7782 CLARK MANAGER
7196 ME0810 salesman
2296 AROMANO SALESMAN


Modifying a view.

SQL> create or replace view empvu10(employee_number,employee_name,job_title) as select empno,ename,job from emp4 where empno=10;

View created.

SQL> desc empvu10;

Name Null? Type
------------------------- -------- ---------------
EMPLOYEE_NUMBER NUMBER(4)
EMPLOYEE_NAME NOT NULL VARCHAR2(10)
JOB_TITLE VARCHAR2(9)

SQL> select * from empvu10;

no rows selected

Creating a complex view.

SQL> create view dept_sum_vu(name,minsal,maxsal,avgsal) as select d.dname,min(e.sal),max(e.sal),avg(e.sal) from emp e,dept3 d where e.deptno=d.deptno group by d.dname;

View created.


Using the 'WITH CHECK OPTION'

SQL> create or replace view empvu20 as select * from emp where deptno=20 with check option constraint empvu20_ck;

View created.

SQL> desc empvu20;

Name Null? Type
------------------ -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Denying DML operations.

SQL> create or replace view empvu10(employee_number,employee_name,job_title) as select empno,ename,job from emp where deptno=10 with read only;

View created.
Removing a view
SQL> drop view empvu10;

View dropped.

Sequences
A Sequence generates unique numbers, which can be used as primary key in many applications. You can also build code into the application to handle this requirement but as a database object, Sequence would do the same job easily.

What is a SEQUENCE?
[1] Automatically generates unique numbers.
[2] Is a shareable Object
[3] Is typically used to create a primary key value.
[4] Replaces application code.
[5] Speeds up the efficiency of accessing sequence values when cached in memory

CREATE SEQUENCE [INCREMENT BY n] [START WITH n] [MAXVALUE n|NOMAXVALUE] [MINVALUE n|NOMINVALUE][CYCLE|NOCYCLE][CACHE n|NOCACHE];

INCREMENTED BY n:Specifies the interval between sequence numbers where n is an integer. If the clause is omitted, then sequence will increment by 1.

START WITH n : Specifies the first sequence number to be generated. If this clause is omitted, the sequence will start with 1.

MAXVALUE n : Specifies the maximum value the sequence can generate.

NOMAXVALUE : specifies the maximum value of 10^27 for an ascending sequence.

MINVALUE: specifies the minimum sequence value.

NOMINVALUE: specifies a minimum value of 1 for an ascending sequence and -10^26 for a descending sequence.

CYCLE|NOCYLE : specifies that the sequences continues to generate values after reaching either its maximum or minimum value or does not generate additional values.

CACHE n|NOCACHE : specifies how many values the Oracle server will pre allocate and keep in memory.
Create a sequence named DEPT_DEPTNO to be used for the primary key of the DEPARTMENT table.

Do not use the CYCLE option.
SQL> create sequence dept_deptno
2 increment by 1
3 start with 91
4 maxvalue 100
5 nocache
6 nocycle;

Sequence created.
The above example creates a Sequence with start number as 91 with NOCYCLE and NOCACHE. Note: Do not use the CYCLE Option if the sequence is used for generating Primary key values.

Confirming sequences.
Verify your sequence values in the USER_SEQUENCES data dictionary table.

SQL> select sequence_name,min_value,max_value,increment_by,last_number from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
DEPT_DEPTNO 1 100 1 91

The LAST_NUMBER column displays the next available sequence number.

PSEUDO COLUMNS
NEXTVAL returns the next available sequence value.
CURRVAL obtains the current sequence value.


Using A Sequence

Insert a new department named "MARKETING" in San Diego.

SQL> insert into dept(deptno,dname,loc) values(dept_deptno.NEXTVAL,'MARKETING','SAN DIEGO');

1 row created.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
91 MARKETING SAN DIEGO
60 MIS
5 reasearch bellaire
1 headquarters houstan

8 rows selected.


Modifying a Sequence
Change the increment value, maximum value, minimum value, cycle option, or cache option
SQL> alter sequence dept_deptno
2 increment by 1
3 maxvalue 999999
4 nocache
5 nocycle;

Sequence altered.




Removing a Sequence

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

SQL> drop sequence dept_deptno;

Sequence dropped.


INDEXES
(1) A Schema object
(2) Used by the Oracle Server to speed up the retrieval of rows by using a pointer.
(3) Reduces disk I/O by using rapid path access method to locate the data quickly.
(4) Independent of the table it indexes
(5) Automatically used and maintained by the Oracle Server.

How are Indexes Created?

Automatically
A Unique index is created automatically when you define a PRIMARY KEY or UNIQUE key constraint in a table deifinition.

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

Creating an Index

Create an index on one or more columns
CREATE INDEX index ON table (column[, column]…);
Improve the speed of query access on the ENAME column in the EMP table

SQL> create index emp_ename_idx on emp(ename);

Index created.


Confirming Indexes
The USER_INDEXES data dictionary view contains the name of the index and its uniqueness.

The USER_IND_COLUMNS view contains the index name, the table name, and the column name.

Removing an Index
Remove an index from the data dictionary.

Remove the EMP_ENAME_IDX index from the data dictionary.
SQL> drop index emp_ename_idx;

Index dropped.

SYNONYMS
[1] Simplify access to objects by creating a synonym (another name for an object)
[2] Refer to table owned by another user
[3] shorten lengthy object names.

To refer to a table owned by another user, you need to prefix the table name with the name of the user who created it followed by a period. Creating a synonyms eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure or other objects.

CREATE [PUBLIC] SYNONYM synonym FOR object;

The public Synonym can be dropped only by DBA

Create & Remove Synonyms

Create a shortened name for the DEPT_SUM_VU view.
SQL> create synonym d_sum for dept_sum_vu;

Synonym created.

SQL> desc d_sum;

Name Null? Type
-------------------- -------- ------------------
NAME VARCHAR2(14)
MINSAL NUMBER
MAXSAL NUMBER
AVGSAL NUMBER


Drop a synonym.

SQL> drop synonym d_sum;

Synonym dropped.

0 comments:

Post a Comment