Synonym,lock table,indexes

 

CHAPTER 4-Index

Ø  Index is scheme object, which is a pointer locates the physical address of data.

Ø  Index is used by oracle server to speedup the retrieval and manipulation of rows.

Ø  Index can created explicitly by user or automatically by server.

Ø  Index is activated where index column is used in the where clause.

Ø  Creation of index is two types these are

1.      Automatic: - Index is created automatically where we can define primary key or unique constraint in a table definition.

2.      Manual: - users can create non unique indexes on columns to speedup access of the rows. DML operations are committed on table with indexes means that indexes must be updated.

Ø  Indexing involves forming a two-dimensional matrix, which is completely independent of a table on which the index is been created. The two-dimensional matrix will have single column and address field.

Single column will hold the sorted data extracted from the table column on which index is created.

Address field identified the location of record in oracle database i.e ROWID.

Ø  Types of indexes

1.      Normal index: - They are default indexes.

2.      BITMAP index: - They stores rowid associated with a key value as bitmap.

3.      COMPOSITE index: - If we define index on more than one column is called composite index.

4.      Partitioned index: - They contain partitions, containing an entry for each value that appears.

5.      Function based index: - Create index on column with function.

6.      Domain index: - Which are instance of an application specific index.

Ø   An index can’t be created on long, long raw, lob, ref.

Ø  Syntax of index

Create UNIQUE/BITMAP index <index name> on <Table name> (col1, col2…) TABLESPACE <table space name>

Ø  Example of indexes

Create index sal_idx on emp (sal);

Create unique index eno_un_idx on emp (empno);

Create unique index eno_enm_un_idx on emp (empno, ename);

Ø  Function based index defined with upper of column, lower of column names allow case sensitive searches.

Create index upper_dnm_idx on dept (upper (dname));

Ø  BITMAP index are associated with rowid.

Create bitmap index emp_bitmap_idx on emp (job);

Ø  When to create index

·        A column contains wide range of values.

·        A column contains a large not null value.

·        One or more columns are frequently used together in where clause or join condition.

·        The table is large and most queries are expected to retrieve less then 2% to 4% of rows.

Ø  When not to create index

·        The table is small.

·        The column not used in condition.

·        The table is updated frequently.

·        The index column are reference as part of the expression.

Ø  Dropping index

·        drop index emp_bitmap_idx;

 

CHAPTER 5-Synonym

 

à It is database object which acts as name of the existing object.

à DML, DESC, select allowed on synonym provides security.

à Types of synonyms

1.      Private synonym: - it is created by user, used by specific user who have permission.

Create synonym empdept for emp;

Grant all on empdept to system;

Here system is one user.

2.      Public synonym: - It will be created by database administrator, it can access by all users.

Create public synonym empdept for emp;

à  Dropping synonym

Drop synonym empdept;

 

 

 

 

CHAPTER 6-LOCK Table

v  Locks are the mechanism used to prevent destructive interaction between users accessing the same resource simultaneously. Locks provides high degree of data concurrency.

v  Concurrent users: - Multiple users using same resource at the same time is called concurrence users.

v  Locks can be acquired at different levels

1.Row level lock

2.Table level lock

v  1.Row level lock: - It is used to lock a row of table. If a row is locked exclusively so that other users can’t modify that row until the transaction holding the lock is commit or rollback.

Example

Step 1: - Select * from emp where empno=7788 for update of hiredate;

Step 2: - Update emp set hiredate=’08-JUN-2021’ where empno=7788;

Step 3: - commit or rollback;

Until step 3 done no other user can update on that row because that row is locked. 

v  2.Table level lock: - It is used to lock entire table.

These can be done in four ways

1)     SHARE lock: - It locks the table and allowing other users to only select statement but not update, insert, delete.

Lock table emp in share mode;

2)     SHARE UPDATE lock: - It locks all the rows that are to be updated and it permits other users to concurrently select, insert, delete even lock the other rows in same table.

lock table emp in share update mode;

3)     EXCLUSSIVE lock: - It locks the table and allowing other users to only select statement but not update, insert, delete. It is almost similar to SHARE lock but only one user can place an EXCLUSSIVE lock in a table at a time, but in share lock locks the table many users put share lock at a time.

lock table emp in exclusive mode;

4)     DEAD lock: - If two users have same lock on separate object 2^nd user will have to wait until 1^st user release the lock. In this situation oracle automatically do DEAD lock and solve the situation.