How to create unique index and how it works

Suppose you have a column (or combination of columns) that contains values that should always be unique. You want to create an index on this column (or combination of columns) that enforces the uniqueness and also provides efficient access to the table when using the unique column in the WHERE clause of a query.

When you create a unique key constraint, Oracle will automatically create an index for you. This is recommended approach for creating unique key constraints and indexes. This example creates a unique constraint named CUST_UX1 on the combination of the LAST_NAME and FIRST_NAME columns of the CUST table:

alter table cust add constraint cust_ux1 unique (last_name, first_name) using index tablespace users;

The prior statement creates the unique constraint, and additionally Oracle automatically creates an associated index. The following query displays the constraint that was created successfully:

How It Works

Defining a unique constraint ensures that when you insert or update column values, then any combination of non-null values are unique. Besides the approach we displayed in the above section, there are several additional techniques for creating unique constraints:

Use the CREATE TABLE statement.

• Create a regular index, and then use ALTER TABLE to add a constraint.

• Create a unique index and don’t add the constraint.

• These techniques are described in the next few subsections.

Use CREATE TABLE

Listed next is an example of using the CREATE TABLE statement to include a unique constraint.

create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
,constraint cust_ux1 unique(last_name, first_name)
using index tablespace users);

The advantage of this approach is that it’s simple and encapsulates the constraint and index
creation within one statement.

Create Index First, Then Add Constraint

You have the option of first creating an index and then adding the constraint as a separate statement— for example:

SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;

SQL> alter table cust add constraint cust_uidx1 unique (last_name, first_name);

The advantage of creating the index separate from the constraint is that you can drop or disable the constraint without dropping the underlying index. When working with large indexes, you may want to consider this approach. If you need to disable the constraint for any reason and then re-enable it later, you can do so without dropping the index (which may take a long time for large indexes).

Creating Only a Unique Index

You can also create just a unique index without adding the unique constraint—for example:

SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;

When you create only a unique index explicitly (as in the prior statement), Oracle creates a unique index but doesn’t add an entry for a constraint in DBA/ALL/USER_CONSTRAINTS.

Why does this matter?

Consider this scenario:
SQL> insert into cust values (1, 'STARK', 'JIM');
SQL> insert into cust values (1, 'STARK', 'JIM');

Here’s the corresponding error message that is thrown:
ERROR at line 1:
ORA-00001: unique constraint (MV_MAINT.CUST_UIDX1) violated

If you’re asked to troubleshoot this issue, the first place you look is in DBA_CONSTRAINTS for a constraint named CUST_UIDX1. However, there is no information:

select
constraint_name
from dba_constraints
where constraint_name='CUST_UIDX1';

no rows selected

The “no rows selected” message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data-dictionary views. In this situation, you have to look at DBA_INDEXES to view the details of the unique index that has been created—for example:

How to create Primary key index and how it works

Suppose you want to enforce that the primary key columns are unique within a table. Furthermore many of the columns in the primary key are frequently used within the WHERE clause of several queries. Also, you want to ensure that indexes are created on primary key columns.

When you define a primary key constraint for a table, Oracle will automatically create an associated index for you. There are several methods available for creating a primary key constraint. Preferred approach is to use the ALTER TABLE...ADD CONSTRAINT statement. This will create the index and the constraint at the same time. This example creates a primary key constraint named CUST_PK and also instructs Oracle to create the corresponding index (also named CUST_PK) in the USERS tablespace:

alter table cust add constraint cust_pk primary key (cust_id)
using index tablespace users;

The following queries and output provide details about the constraint and index that Oracle created. The first query displays the constraint information:

select
constraint_name
,constraint_type
from user_constraints
where table_name = 'CUST';

How It Works

We prefer to create primary key constraints and the corresponding index. In most situations, this approach is acceptable. However, you should be aware that there are several other methods for creating the primary key constraint and index. These methods are listed here:

•  Create an index first, and then use ALTER TABLE...ADD CONSTRAINT.

•  Specify the constraint inline (with the column) in the CREATE TABLE statement.

•  Specify the constraint out of line (from the column) within the CREATE TABLE statement.

Create Index and Constraint Separately

You have the option of first creating an index and then altering the table to apply the primary key constraint. Here’s an example:

SQL> create index cust_pk on cust(cust_id);

SQL> alter table cust add constraint cust_pk primary key(cust_id);

The advantage to this approach is that you can drop or disable the primary key constraint
independently of the index. If you work with large data volumes, you may require this sort of flexibility. This approach allows you to disable/re-enable a constraint without having to later rebuild the index.

Create Constraint Inline

You can directly create an index inline (with the column) in the CREATE TABLE statement. This approach is simple but doesn’t allow for multiple column primary keys and doesn’t name the constraint:

SQL> create table cust(cust_id number primary key);

If you don’t explicitly name the constraint (as in the prior statement), Oracle automatically
generates a name like SYS_C123456. If you want to explicitly provide a name, you can do so as follows:

create table cust(cust_id number constraint cust_pk primary key
using index tablespace users);

The advantage of this approach is that it’s very simple. If you’re experimenting in a development or test environment, this approach is quick and effective.

How to decide which column to index

The database you manage contains hundreds of tables. Each table typically contains a dozen or more columns. You wonder which columns should be indexed.

Below are the general guidelines for deciding which columns to index:

• Define a primary key constraint for each table that results in an index automatically being created on the columns specified in the primary key.

• Create unique key constraints on non-null column values that are required to be unique (different from the primary key columns). This results in an index automatically being created on the columns specified in unique key constraints.

• Explicitly create indexes on foreign key columns.

• Create indexes on columns used often as predicates in the WHERE clause of frequently executed SQL queries.

After you have decided to create indexes, I would recommend that you adhere to index creation standards that facilitate the ease of maintenance. Specifically, follow these guidelines when creating an index:

• Use the default B-tree index unless you have a solid reason to use a different index type.

• Create a separate tablespace for the indexes. This allows you to more easily manage indexes separately from tables for tasks such as backup and recovery.

• Let the index inherit its storage properties from the tablespace. This allows you to specify the storage properties when you create the tablespace and not have to manage storage properties for individual indexes.

• If you have a variety of storage requirements for indexes, then consider creating separate tablespaces for each type of index—for example, INDEX_LARGE, INDEX_MEDIUM, and INDEX_SMALL tablespaces, each defined with storage characteristics appropriate for the size of the index.

You should add an index only when you’re certain it will improve performance. Misusing indexes can have serious negative performance effects. Indexes created of the wrong type or on the wrong columns  do nothing but consume space and processing resources. As a DBA, you must have a strategy to ensure that indexes enhance performance and don’t negatively impact applications.

                         Guideline                                                                            Reason

How to create B-tree index and how it works

Suppose you want to create an index. You understand that the default type of index in Oracle is the B-tree, but you don’t quite understand how an index is physically implemented. You want to fully comprehend the B-tree index internals so as to make intelligent performance decisions when building database applications.

An example with a good diagram will help illustrate the mechanics of a B-tree index. Even if you’ve been working with B-tree indexes for quite some time, a good example may illuminate technical aspects of using an index. To get started, suppose you have a table created as follows:

create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30));

You determine that several SQL queries will frequently use LAST_NAME in the WHERE clause. This
prompts you to create an index:

SQL> create index cust_idx1 on cust(last_name);

Several hundred rows are now inserted into the table (not all of the rows are shown here):

insert into cust values(7, 'ACER','SCOTT');
insert into cust values(5, 'STARK','JIM');
insert into cust values(3, 'GREY','BOB');
insert into cust values(11,'KHAN','BRAD');
.....
insert into cust values(274, 'ACER','SID');

After the rows are inserted, we ensure that the table statistics are up to date so as to provide the query optimizer sufficient information to make good choices on how to retrieve the data.

SQL> exec dbms_stats.gather_table_stats(ownname=>'MV_MAINT', -
tabname=>'CUST',cascade=>true);

As rows are inserted into the table, Oracle will allocate extents that consist of physical database blocks. Oracle will also allocate blocks for the index. For each record inserted into the table, Oracle will also create an entry in the index that consists of the ROWID and column value (the value in LAST_NAME in this example). The ROWID for each index entry points to the datafile and block that the table column value is stored in. Figure shows a graphical representation of how data is stored in the table and the corresponding B-tree index. For this example, datafiles 10 and 15 contain table data stored in associated blocks and datafile 22 stores the index blocks.

 There are two dotted lines in Figure. These lines depict how the ROWID (in the index structure) points to the physical location in the table for the column values of ACER. These particular values will be used in the scenarios in this solution.

When selecting data from a table and its corresponding index, there are three basic scenarios:

• All table data required by the SQL query is contained in the index structure.Therefore only the index blocks need to be accessed. The blocks from the table are never read.

• All of the information required by the query is not contained in the index blocks.Therefore the query optimizer chooses to access both the index blocks and the table blocks to retrieve the data needed to satisfy the results of the query.

• The query optimizer chooses not to access the index. Therefore only the tableblocks are accessed.

The prior situations are covered in the next three subsections.

Scenario 1: All Data Lies in the Index Blocks

There are two scenarios that will be shown in this section:

• Index range scan: This occurs when the optimizer determines it is efficient to use the index structure to retrieve multiple rows required by the query. Index range scans are used extensively in a wide variety of situations.

• Index fast full scan: This occurs when the optimizer determines that most of the rows in the table will need to be retrieved. However, all of the information required is stored in the index. Since the index structure is usually smaller than the table structure, the optimizer determines that a full scan of the index is more efficient. This scenario is common for queries that count values.

First the index range scan is demonstrated. For this example, suppose this query is issued that selects from the table:

SQL> select last_name from cust where last_name='ACER';

Before reading on, look at above figure and try to answer this question: “What are the minimal number of blocks Oracle will need to read to return the data for this query?” In other words, what is the most efficient way to access the physical blocks in order to satisfy the results of this query? The optimizer could choose to read through every block in the table structure. However, that would result in a great deal of I/O, and thus it is not the most optimal way to retrieve the data.

For this example, the most efficient way to retrieve the data is to use the index structure. To return the rows that contain the value of ACER in the LAST_NAME column, Oracle will need to read three blocks: block 20, block 30, and block 39. We can verify that this is occurring by using Oracle’s Autotrace utility:

SQL> set autotrace on;
SQL> select last_name from cust where last_name='ACER';

Here is a partial snippet of the output:

 The prior output shows that Oracle needed to use only the CUST_IDX1 index to retrieve the data to satisfy the result set of the query. The table data blocks were not accessed; only the index blocks were required. This is a particularly efficient indexing strategy for the given query. Listed next are the statistics displayed by Autotrace for this example:

Statistics
-----------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads

The consistent gets value indicates that three blocks were read from memory (db block gets plus consistent gets equals the total blocks read from memory). Since the index blocks were already in memory, no physical reads were required to return the result set of this query.

Next an example that results in an index fast full scan is demonstrated. Consider this query:

SQL> select count(last_name) from cust;

Using SET AUTOTRACE ON, an execution plan is generated. Here is the corresponding output:

 The prior output shows that only the index structure was used to determine the count within the table. In this situation, the optimizer determined that a full scan of the index was more efficient than a full scan of the table.


Scenario 2: All Information Is Not Contained in the Index

Now consider this situation: suppose we need additional information from the CUST table. This query additionally selects the FIRST_NAME column:

SQL> select last_name, first_name from cust where last_name = 'ACER';

Using SET AUTOTRACE ON and executing the prior query results in the following execution plan:

The prior output indicates that the CUST_IDX1 index was accessed via an INDEX RANGE SCAN. The INDEX RANGE SCAN identifies the index blocks required to satisfy the results of this query. Additionally the table is read by TABLE ACCESS BY INDEX ROWID. The access to the table by the index’s ROWID means that Oracle uses the ROWID (stored in the index) to locate the data contained within the table blocks. In Figure , this is indicated by the dotted lines that map the ROWID to the appropriate table blocks that contain the value of ACER in the LAST_NAME column.

Again, looking at Figure, how many table and index blocks need to be read in this scenario? The index requires that blocks 20, 30, and 39 must be read. Since FIRST_NAME is not included in the index, Oracle must read the table blocks to retrieve these values. Oracle knows the ROWID of the table blocks and directly reads blocks 11 and 2500 to retrieve that data. That makes a total of five blocks. Here is a partial snippet of the statistics generated by Autotrace that confirms the number of blocks read is five:


Statistics
---------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads


Scenario 3: Only the Table Blocks Are Accessed

In some situations, even if there is an index, Oracle will determine that it’s more efficient to use only the table blocks. When Oracle inspects every row within a table, this is known a full table scan. For example, take this query:

SQL> select * from cust;

Here are the corresponding execution plan and statistics:

The prior output shows that a total of 119 blocks were inspected. Oracle searched every row in the table to bring back the results required to satisfy the query. In this situation, all blocks of the table must be read, and there is no way for Oracle to use the index to speed up the retrieval of the data.

How It Works

The B-tree index is the default index type in Oracle. For most OLTP-type applications, this index type is sufficient. This index type is known as B-tree because the ROWID and associated column values are stored within blocks in a balanced tree-like structure. The B stands for balanced.

B-tree indexes are efficient because, when properly used, they result in a query retrieving data far faster than it would without the index. If the index structure itself contains the required column values to satisfy the result of the query, then the table data blocks need not be accessed. Understanding these mechanics will guide your indexing decision-making process. For example, this will help you decide which columns to index and whether a concatenated index might be more efficient for certain queries and less optimal for others.

Index Types and their Descriptions

1. B-tree Index: Default, balanced tree index, good for high-cardinality (high degree of distinct values) columns.

2. B-tree cluster Index:  Used with clustered tables.

3. Hash cluster Index:  Used with hash clusters.

4. Function-based Index:  Good for columns that have SQL functions applied to them.

5. Indexed virtual column Index:  Good for columns that have SQL functions applied to them; viable alternative. to using a function-based index.

6. Reverse-key Index:  Useful to balance I/O in an index that has many sequential inserts.

7. Key-compressed Index:  Useful for concatenated indexes where the leading column is often repeated, compresses leaf block entries.

8. Bitmap Index:  Useful in data warehouse environments with low-cardinality columns. these indexes aren’t appropriate for online transaction processing (OLTP) databases
where rows are heavily updated.

9. Bitmap join:  Useful in data warehouse environments for queries that join fact and
dimension tables.

10. Global partitioned:  Global index across all partitions in a partitioned table.

11. Local partitioned: Local index based on individual partitions in a partitioned table.

12. Domain:  Specific for an application or cartridge

In the next topic I will cover the details of B-tree index.

What is index

An index is a database object used primarily to improve the performance of SQL queries.

The function of a database index is similar to an index in the back of a book. A book index associates a topic with a page number. When you’re locating information in a book, it’s usually much faster to inspect the index first, find the topic of interest, and identify associated page numbers. With this information, you can navigate directly to specific page numbers in the book. In this situation, the number of pages you need to inspect
is minimal.

If there were no index, you would have to inspect every page of the book to find information. This results in a great deal of page turning, especially with large books. This is similar to an Oracle query that does not use an index and therefore has to scan every used block within a table. For large tables, this results in a great deal of I/O.

The book index’s usefulness is directly correlated with the uniqueness of a topic within the book. For example, take this book; it would do no good to create an index on the topic of “performance” because every page in this book deals with performance. However, creating an index on the topic of “bitmap indexes” would be effective because there are only a few pages within the book that are applicable to this feature.

Keep in mind that the index isn’t free. It consumes space in the back of the book, and if the material in the book is ever updated (like a second edition), every modification (insert, update, delete) potentially requires a corresponding change to the index. It’s important to keep in mind that indexes consume space and require resources when updates occur.

Also, the person who creates the index for the book must consider which topics will be frequently looked up. Topics that are selective and frequently accessed should be included in the book index. If an index in the back of the book is never looked up by a reader, then it unnecessarily wastes space.

Much like the process of creating an index in the back of the book, there are many factors that must be considered when creating an Oracle index. Oracle provides a wide assortment of indexing features and options. These objects are manually created by the DBA or a developer. Therefore, you need to be aware of the various features and how to utilize them. If you choose the wrong type of index or use a feature incorrectly, there may be detrimental performance implications. Listed next are aspects to consider before you create an index:

• Type of index
• Table column(s) to include
• Whether to use a single column or a combination of columns
• Special features such as parallelism, turning off logging, compression, invisible
• indexes, and so on
• Uniqueness
• Naming conventions
• Tablespace placement
• Initial sizing requirements and growth
• Impact on performance of SELECT statements (improvement)
• Impact on performance of INSERT, UPDATE, and DELETE statements
• Global or local index, if the underlying table is partitioned

When you create an index, you should give some thought to every aspect mentioned in the previous list. One of the first decisions you need to make is the type of index and the columns to include. Oracle provides a robust variety of index types. For most scenarios, you can use the default B-tree (balanced tree) index. Other commonly used types are concatenated, bitmap, and function-based indexes. In the next post I will describe the types of indexes available with Oracle

Session Performance issue in Oracle DB

Most of the time end user complaining that the database is slow as high performance is common expectation for end user. The database itself is never slow or fast in most of the case session connected to the database slow down when they receive unexpected hit. To resolve session performance issue you need to identify unexpected hit and remove it. As we know an oracle database is always one of the 3 states:

Idle: Waiting for the task.
Processing: Doing some useful task.
Waiting: Waiting for something, a block to come from disk or lock to be released.

Sometimes the situation is session is waiting for resource and another session trying to update that record and many other such scenarios. Our goal is to find and eliminate that type of session.

Update pay_employee_personal_info
Set amount = 4000
Where employee_number = 5205;

Do not issue a commit after this update operation. That means you are forcing the session to get and hold a lock on the first row of the ‘pay_employee_personal_info’ table.
Now if you try the below update statement on the second session. The statement will hang! The question why?

Update pay_employee_personal_info
Set amount = 5000
Where employee_number = 5205;

This is due to the first session holds a lock on the row, which cause the second session to hang and the user to complain that the session is slow.

To know exactly what the second session is doing join your query with v$session_wait.

--Displays information on particular user session waits.
SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,  s.serial#,  sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM   v$session_wait sw, v$session s
WHERE  s.sid = sw.sid and s.username = 'HRMS'
ORDER BY sw.seconds_in_wait DESC;
USERN  SID    SERIAL#‎EVENT                       WAIT_TIME SECONDS STATE
------ ---   ------ ------------------------    --------  ------- ----------
HRMS   ‎53‎     ‎6,581‎  SQL*Net message from client‎ 0‎         870‎   WAITED KNOWN TIME
HRMS   ‎22‎     ‎47,542‎ SQL*Net message from client‎ 0‎     ‎    633   WAITED KNOWN TIME
HRMS   ‎18‎     ‎21,757‎ SQL*Net message from client‎ 0‎         24‎   WAITED KNOWN TIME
HRMS   ‎36‎     ‎18,360‎ enq:TX - row lock contention‎0‎         12   WAITING
HRMS   ‎34‎     ‎18,633‎ SQL*Net message from client‎ 0‎         9‎    WAITING

If you don’t know exactly which user or Terminal causing issues you can run your query to ask whole database session waits information then gradually move for particular user or Terminal session wait information. From the output you can see the users are connected with different application such as Payroll software (HRMS), Oracle Financial software (ORAFIN), EDSS, ITGFIN.

--Displays information on all database session waits.
SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,  s.serial#,  sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM   v$session_wait sw, v$session s
WHERE  s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
Select SID, osuser, machine, terminal,
       logon_time, last_call_et
from v$session
where username = 'HRMS' AND TERMINAL = 'HR-RAFEQ';

If you study the output carefully for SID (53, 22, and 18) which shows that it waited for some known amount of time earlier but now it is working properly where as SID (36, 34) indicates that it is waiting for something therefore it is not working. Why it is waiting for, you can check the reason in EVENT column of the output. The EVENT column not only shows the current waiting situation, also shows an EVENT session waited for earlier.

From the SID 36 output shows that session is waiting right now for transaction level lock on row and session is still waiting to lock one or more rows, but another session has already placed locks on the rows. Unless that other session commits or rolls back its transaction, SID 36 will not release the lock. You can also view the time since the session is waiting. A very long wait usually indicates some sorts of performance bottleneck.

From the above output you can also see the session 34 is idle but any complain regarding this session is not related to the session performance. Check the other aspects of performance troubleshooting why it is going through an infinite loop or high CPU consumption on the application server.

From the below query you can get the information of system identification as well as user information along with logon_time. It is important for you to know which user or system is creating this issue.

--Displays system and user details with logon_time for database sessions
SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser, s.sid, s.serial#, p.spid, s.lockwait,s.status,
       s.module,s.machine, TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s, v$process p
WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

Once you find the issue ‘a session is waiting for row lock’ it is important for you to find which session holds that lock.

To identify the locked row:
Select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=36;
To identify the lock object:
Select owner, object_type, object_name, data_object_id
from dba_objects
where object_id = 145425;
To find Lock session Text:
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;

Follow the link to find query on database locks or who is blocking the session or blocker session details: Find Locks : Blockers
Once you find which session blocking the lock or which session holds the lock, you need to find the SQL statements which cause issue.

--To find Lock session Text
select address, s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;
Select sql_text from v$sql
where address = '4AC67EE4';

Locking is not only the cause to effects the performance. Another major case of contention is disk I/O. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as “db file sequential read” (for index scan) or “db file scattered read” (for full table scan). You can find more related query on event details and I/O details by clicking on the link: DB Locks

When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. There are several ways to reduce the wait. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.
–        Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.
–        Place the tables used in the SQL statement on a faster part of the disk.
–        Consider increasing the buffer cache to see if the expanded size will accommodate the additional blocks, therefore reducing the I/O and the wait.
–        Tune the I/O subsystem to return data faster.