WORKING WITH DATABASE OBJECTS
VIEWS
VIEWS are Database Objects whose contents are derived from another table
- A VIEW Contains no data of its own
- The command for creating VIEW is CREATE VIEW command
- The changes in the tables are automatically reflected in the VIEWS
A VIEW is like a ‘windows’ through which data can viewed or changed.
Advantages of a VIEW
- To restrict data access
- To make complex queries easy
- To provide data independence
- To present different views of the same data
A Simple View is one that:
- Derives data from only one table
- Contains no functions or groups of data
- Can perform DML operations through the view
A Complex view is one that:
- Derives data from many tables
- Contains funcions or groups of data
- Does not always allow DML opearations through the view
Simple View
SQL> create view vname as select empno,ename,deptno from emp ;
SQL> create view EmpView as select empno,ename,sal from emp where empno=7900 ;
SQL> select * from EmpView ;
SQL> Drop view EMPPVIEW1 ;
Insert, Update and Delete statement in a Simple View
SQL>insert into vname (empno,ename,sal,deptno) values (4567,'XYZ',3456,50) ;
SQL>update vname set ename='FFFF' where empno=4567 ;
SQL>delete from vname where empno=4567 ;
Complex View
- Creating a View using Group by function
SQL> create or replace view emppview1 as select deptno, count(*) total from emp group by deptno ;
SQL> select rownum as rank, ename,sal from (select ename,sal from emp order by sal desc) where rownum <= 3 ;
alter view vname as select empno,ename,sal,deptno from emp ;
create view vname1 as select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno);
VIEWS with Check Option
create view vname as select empno,ename,deptno from emp where deptno=10 with check option ;
insert into vname values (2345,'fffff',10) ;
Views with Read Only
create view vname as select empno,ename,deptno from emp where deptno=10 with read only ;
Creating a Force view
- Creating a view with out the table existing
Create a view first, and then create a table
sql> create force view bcd as select * from blank ;
SQL> Create table blank ( a number) ;
SQL> insert into blank values (33) ;
SQL> insert into blank values (33) ;
SQL> insert into blank values (33) ;
SQL> select * from bcd ;
SQL> DESC bcd
Dropping a View
SQL> Drop view viewname ;
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 effeciency of accessing sequence values when cached in memory
SQL> create sequence Dept_seq
increment by 10
start with 120
MAXVALUE 9999 ;
SQL> select sequence_name,min_values,max_value, increment_by, last_number from user_sequences ;
SQL> select Dept_seq .currval from dual ;
SQL> select Dept_seq .nextval from dual ;
SQL> Drop sequence Dept_seq ;
INDEXES
An Index:
- Is used by the Oracle Server to speed up the retrieval of rows by using pointer.
- Can reduce disk I/O by using a rapid path access method to locate data quickly.
Automatically:
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
Manually:
Users can create non-unique indexes on column to speed up access to the rows.
Oracle Database supports several types of index:
• Normal indexes. (By default, Oracle Database creates B-tree indexes.)
• Bitmap indexes, which store rowids associated with a key value as a bitmap
• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table
• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
• Domain indexes, which are instances of an application-specific index of type indextype
SQL> CREATE INDEX EMPIDX on emp (empno) ;
FUNCTION - BASED INDEXES
- A function-based index is an index based on expressions.
SQL> Create Index Upper_dept_idx on dept (upper(deptno)) ;
SQL> Drop Index Upper_dept_idx ;
BIT MAP INDEXES
SQL> CREATE BIT MAP INDEX EMPIDX on emp(sex) ;
Dropping a Index
SQL> Drop Index ;
SYNONYMS
Simplify access to objects by creating a synonym.
- Ease referring to a table owned by another user
- Shorten lengthy object names
SQL> Create synonym dept for scott.dept;
SQL> select * from dept;
SQL> DROP synonym dept;
CLUSTER
Clustering is a method of storing tables that are intimately related and often join together into the same area on disk. It requires at least one cluster column from each of the tables. These must be of the same data types and size, but are not required to possess identical names. For the tables in a cluster, rows with identical column values are kept together on disk in the same area, the same logical block(s). This clauses improvement of performance when the cluster columns are the columns by which the tables are generally joined.
SQL> create cluster emp_add (empno number(4) ) ;
SQL> create table emp2
(empno number(4) primary key,
ename varchar2(30),
address varchar2(20))
cluster emp_add (empno) ;
SQL> create table emp_address
(empno number(4) primary key,
address varchar2(20))
cluster emp_add (empno) ;
SQL> create table emp_address1
(empno number(4) primary key,
address varchar2(20))
cluster emp_add (empno) ;
SQL> drop cluster emp_add including tables ;
SQL> create index emp_address on cluster emp_add ;
- Delete the cluster item
SQL> drop index emp_address ;
PARTITIONS
Storing partitions in separate tablespace enables you to:
----Reduce the possibility of data corruption in multiple partitions
----Back up and recover each partition independently
----Improve manageability, availability, and performance
----Partitioning is transparent to existing applications and standard DML statements run against partitioned tables.
However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML.
Partitioning Method
There are several partitioning methods offered by Oracle Database:
- Range partitioning
- Hash partitioning
- List partitioning
Range Partitioning
Use range partitioning to map rows to partitions based on ranges of column values.
SQL> create table emp345
(empno number(3) primary key,
ename varchar2(30),
sal number(8,2),
deptno number(2))
Partition by range (deptno)
(Partition p1 values less than (10) tablespace tbs1,
Partition p2 values less than (20) tablespace tbs2,
Partition p3 values less than (30) tablespace tbs3,
Partition p4 values less than (40) tablespace tbs4)
List Partitioning
Use list partitioning when you require explicit control over how rows map to partitions. You can
specify a list of discrete values for the partitioning column in the description for each partition.
SQL> CREATE TABLE Dept_part
(deptno number,
deptname varchar2(20),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1 VALUES ('OR', 'WA') tablespace tbs1,
PARTITION q2 VALUES ('AZ', 'UT', 'NM') tablespace tbs2,
PARTITION q3 VALUES ('NY', 'VM', 'NJ') tablespace tbs3,
PARTITION q4 VALUES ('FL', 'GA') tablespace tbs4);
SQL> select tablespace_name, partition_name from dba_tab_subpartitions where table_name='emp_sub_template' order by tablespace_name;
Hash Partitioning
Hash partitioning provides a method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on a hash
value of the partitioning key.
It controls the physical placement of data across a fixed number of partitions and gives
you a highly tunable method of data placement.
To Create HASH Partition
SQL> create table purchases (inv_no number , day number,
month number, year number)
partition by hash (day, month, year)
(partition h1 tablespace ts1,
partition h2 ta blespace ts2,
partition h3 tablespace ts3);
Adding a Partition to a Partitioned Table
alter table emp345 add partition p5 values less than (50) tablespace tbs4 ;
Truncating Partitions
alter table emp345 truncate partition p5 ;
Dropping a Table Partition
Alter table emp345 drop partition p5;
Merging Partitions
ALTER TABLE emp345 MERGE PARTITIONS p1 , p2 into PARTITION p6 ;
Moving a Partitions
ALTER TABLE emp345 MOVE PARTITION p4 tablespace tbs4 ;
Renaming a Table Partition
ALTER TABLE emp345 rename PARTITION p4 to p45 ;
Coalescing a Partitioned Table
ALTER TABLE emp345 COALESCE PARTITION;
LOCKING MECHANISM
Locks :
Locks are of two types,
1. Row Level Locks
2. Table Level Locks
--> Row Level Locks:
This lock is used to lock either a single or group of records, for the purpose of updations. A Row Level Lock is implemented with in a select query using "for update of " clause.
Ex: Select * from emp where empno=7788 for update of comm,sal;
note: another user cannot manipulate comm&sal of 7788,for the rest of the numbers & entities he can do manipulation
Ex: Select * from emp for update of comm, sal;
Note: another user cannot manipulate comm&sal all the numbers, for the rest of the entities he can do manipulation
Ex: Select * from EMP for update;
note: another user cannot manipulate all the entities of the table
Note: only manipulations r not allowed but he can retrive values (select)of the table
--> Table Level Locks: These are further classified into three caregories...
-> Share Lock
-> Share Update Lock
-> Exclusive Lock
-> Share Lock : This is a sharable lock, i.e., multiple users can implement this sharable lock on a single table at the same time.
note: the 2nd user who didn`t lock the table which is locked by the first user should wait uptill the 1st user`s end transaction.
when it ends 2nd user can use it for manipulations, but the 1stuser need not wait for the 2nd user`s end transaction
E.g. Lock table emp in share mode;
-> Share Update Lock: Even though this lock fall under Table Level locks, but still is used to lock records, to use this lock first we need to lock the required records using row level lock and then implement this lock.
E.g. Lock table emp in share update mode;
-> Exclusive Lock : When a user locks a table in Exclusive mode, no other user can access that table except the user who has locked it in Exclusive mode.
E.g. Lock table emp in Exclusive Mode;
VIEWS
VIEWS are Database Objects whose contents are derived from another table
- A VIEW Contains no data of its own
- The command for creating VIEW is CREATE VIEW command
- The changes in the tables are automatically reflected in the VIEWS
A VIEW is like a ‘windows’ through which data can viewed or changed.
Advantages of a VIEW
- To restrict data access
- To make complex queries easy
- To provide data independence
- To present different views of the same data
A Simple View is one that:
- Derives data from only one table
- Contains no functions or groups of data
- Can perform DML operations through the view
A Complex view is one that:
- Derives data from many tables
- Contains funcions or groups of data
- Does not always allow DML opearations through the view
Simple View
SQL> create view vname as select empno,ename,deptno from emp ;
SQL> create view EmpView as select empno,ename,sal from emp where empno=7900 ;
SQL> select * from EmpView ;
SQL> Drop view EMPPVIEW1 ;
Insert, Update and Delete statement in a Simple View
SQL>insert into vname (empno,ename,sal,deptno) values (4567,'XYZ',3456,50) ;
SQL>update vname set ename='FFFF' where empno=4567 ;
SQL>delete from vname where empno=4567 ;
Complex View
- Creating a View using Group by function
SQL> create or replace view emppview1 as select deptno, count(*) total from emp group by deptno ;
SQL> select rownum as rank, ename,sal from (select ename,sal from emp order by sal desc) where rownum <= 3 ;
alter view vname as select empno,ename,sal,deptno from emp ;
create view vname1 as select e.ename,e.deptno,d.dname from emp e left outer join dept d on (e.deptno=d.deptno);
VIEWS with Check Option
create view vname as select empno,ename,deptno from emp where deptno=10 with check option ;
insert into vname values (2345,'fffff',10) ;
Views with Read Only
create view vname as select empno,ename,deptno from emp where deptno=10 with read only ;
Creating a Force view
- Creating a view with out the table existing
Create a view first, and then create a table
sql> create force view bcd as select * from blank ;
SQL> Create table blank ( a number) ;
SQL> insert into blank values (33) ;
SQL> insert into blank values (33) ;
SQL> insert into blank values (33) ;
SQL> select * from bcd ;
SQL> DESC bcd
Dropping a View
SQL> Drop view viewname ;
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 effeciency of accessing sequence values when cached in memory
SQL> create sequence Dept_seq
increment by 10
start with 120
MAXVALUE 9999 ;
SQL> select sequence_name,min_values,max_value, increment_by, last_number from user_sequences ;
SQL> select Dept_seq .currval from dual ;
SQL> select Dept_seq .nextval from dual ;
SQL> Drop sequence Dept_seq ;
INDEXES
An Index:
- Is used by the Oracle Server to speed up the retrieval of rows by using pointer.
- Can reduce disk I/O by using a rapid path access method to locate data quickly.
Automatically:
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
Manually:
Users can create non-unique indexes on column to speed up access to the rows.
Oracle Database supports several types of index:
• Normal indexes. (By default, Oracle Database creates B-tree indexes.)
• Bitmap indexes, which store rowids associated with a key value as a bitmap
• Partitioned indexes, which consist of partitions containing an entry for each value that appears in the indexed column(s) of the table
• Function-based indexes, which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
• Domain indexes, which are instances of an application-specific index of type indextype
SQL> CREATE INDEX EMPIDX on emp (empno) ;
FUNCTION - BASED INDEXES
- A function-based index is an index based on expressions.
SQL> Create Index Upper_dept_idx on dept (upper(deptno)) ;
SQL> Drop Index Upper_dept_idx ;
BIT MAP INDEXES
SQL> CREATE BIT MAP INDEX EMPIDX on emp(sex) ;
Dropping a Index
SQL> Drop Index
SYNONYMS
Simplify access to objects by creating a synonym.
- Ease referring to a table owned by another user
- Shorten lengthy object names
SQL> Create synonym dept for scott.dept;
SQL> select * from dept;
SQL> DROP synonym dept;
CLUSTER
Clustering is a method of storing tables that are intimately related and often join together into the same area on disk. It requires at least one cluster column from each of the tables. These must be of the same data types and size, but are not required to possess identical names. For the tables in a cluster, rows with identical column values are kept together on disk in the same area, the same logical block(s). This clauses improvement of performance when the cluster columns are the columns by which the tables are generally joined.
SQL> create cluster emp_add (empno number(4) ) ;
SQL> create table emp2
(empno number(4) primary key,
ename varchar2(30),
address varchar2(20))
cluster emp_add (empno) ;
SQL> create table emp_address
(empno number(4) primary key,
address varchar2(20))
cluster emp_add (empno) ;
SQL> create table emp_address1
(empno number(4) primary key,
address varchar2(20))
cluster emp_add (empno) ;
SQL> drop cluster emp_add including tables ;
SQL> create index emp_address on cluster emp_add ;
- Delete the cluster item
SQL> drop index emp_address ;
PARTITIONS
Storing partitions in separate tablespace enables you to:
----Reduce the possibility of data corruption in multiple partitions
----Back up and recover each partition independently
----Improve manageability, availability, and performance
----Partitioning is transparent to existing applications and standard DML statements run against partitioned tables.
However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML.
Partitioning Method
There are several partitioning methods offered by Oracle Database:
- Range partitioning
- Hash partitioning
- List partitioning
Range Partitioning
Use range partitioning to map rows to partitions based on ranges of column values.
SQL> create table emp345
(empno number(3) primary key,
ename varchar2(30),
sal number(8,2),
deptno number(2))
Partition by range (deptno)
(Partition p1 values less than (10) tablespace tbs1,
Partition p2 values less than (20) tablespace tbs2,
Partition p3 values less than (30) tablespace tbs3,
Partition p4 values less than (40) tablespace tbs4)
List Partitioning
Use list partitioning when you require explicit control over how rows map to partitions. You can
specify a list of discrete values for the partitioning column in the description for each partition.
SQL> CREATE TABLE Dept_part
(deptno number,
deptname varchar2(20),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1 VALUES ('OR', 'WA') tablespace tbs1,
PARTITION q2 VALUES ('AZ', 'UT', 'NM') tablespace tbs2,
PARTITION q3 VALUES ('NY', 'VM', 'NJ') tablespace tbs3,
PARTITION q4 VALUES ('FL', 'GA') tablespace tbs4);
SQL> select tablespace_name, partition_name from dba_tab_subpartitions where table_name='emp_sub_template' order by tablespace_name;
Hash Partitioning
Hash partitioning provides a method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on a hash
value of the partitioning key.
It controls the physical placement of data across a fixed number of partitions and gives
you a highly tunable method of data placement.
To Create HASH Partition
SQL> create table purchases (inv_no number , day number,
month number, year number)
partition by hash (day, month, year)
(partition h1 tablespace ts1,
partition h2 ta blespace ts2,
partition h3 tablespace ts3);
Adding a Partition to a Partitioned Table
alter table emp345 add partition p5 values less than (50) tablespace tbs4 ;
Truncating Partitions
alter table emp345 truncate partition p5 ;
Dropping a Table Partition
Alter table emp345 drop partition p5;
Merging Partitions
ALTER TABLE emp345 MERGE PARTITIONS p1 , p2 into PARTITION p6 ;
Moving a Partitions
ALTER TABLE emp345 MOVE PARTITION p4 tablespace tbs4 ;
Renaming a Table Partition
ALTER TABLE emp345 rename PARTITION p4 to p45 ;
Coalescing a Partitioned Table
ALTER TABLE emp345 COALESCE PARTITION;
LOCKING MECHANISM
Locks :
Locks are of two types,
1. Row Level Locks
2. Table Level Locks
--> Row Level Locks:
This lock is used to lock either a single or group of records, for the purpose of updations. A Row Level Lock is implemented with in a select query using "for update of " clause.
Ex: Select * from emp where empno=7788 for update of comm,sal;
note: another user cannot manipulate comm&sal of 7788,for the rest of the numbers & entities he can do manipulation
Ex: Select * from emp for update of comm, sal;
Note: another user cannot manipulate comm&sal all the numbers, for the rest of the entities he can do manipulation
Ex: Select * from EMP for update;
note: another user cannot manipulate all the entities of the table
Note: only manipulations r not allowed but he can retrive values (select)of the table
--> Table Level Locks: These are further classified into three caregories...
-> Share Lock
-> Share Update Lock
-> Exclusive Lock
-> Share Lock : This is a sharable lock, i.e., multiple users can implement this sharable lock on a single table at the same time.
note: the 2nd user who didn`t lock the table which is locked by the first user should wait uptill the 1st user`s end transaction.
when it ends 2nd user can use it for manipulations, but the 1stuser need not wait for the 2nd user`s end transaction
E.g. Lock table emp in share mode;
-> Share Update Lock: Even though this lock fall under Table Level locks, but still is used to lock records, to use this lock first we need to lock the required records using row level lock and then implement this lock.
E.g. Lock table emp in share update mode;
-> Exclusive Lock : When a user locks a table in Exclusive mode, no other user can access that table except the user who has locked it in Exclusive mode.
E.g. Lock table emp in Exclusive Mode;
