, , ,

What is Index in database?

An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries.
Indexes are objects in the database that provide a mapping of all the values in a table column, along with the ROWID(s) for all rows in the table that contain that value for the column. A ROWID is a unique identifier for a row in an Oracle database table. Indexes have multiple uses on the Oracle database. Indexes can be used to ensure uniqueness on a database, and they can also boost performance when you’re searching for records in a table. Indexes are used by the Oracle Server to speed up the retrieval of rows by using a pointer. The improvement in performance is gained when the search criteria for data in a table include a reference to the indexed column or columns.
In Oracle, indexes can be created on any column in a table except for columns of the LONG datatype. Especially on large tables, indexes make the difference between an application that drags its heels and an application that runs with efficiency. However, many performance considerations must be weighed before you make the decision to create an index.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

B-tree Index Structure

The traditional index in the Oracle database is based on a highly advanced algorithm for sorting data called a B-tree. A B-tree contains data placed in layered, branching order, from top to bottom, resembling an upside-down tree. The midpoint of the entire list is placed at the top of the “tree” and is called the root node. The midpoints of each half of the remaining two lists are placed at the next level, and so on

By using a divide-and-conquer method for structuring and searching for data, the values of a column are only a few hops away on the tree, rather than several thousand sequential reads through the list away. However, traditional indexes work best when many distinct values are in the column or when the column is unique.
The algorithm works as follows:
1. Compare the given value to the value in the halfway point of the list. If the value at hand is greater, discard the lower half of the list. If the value at hand is less, discard the upper half of the list.
2. Repeat step 1 for the remaining part of the list until a value is found or the list exhausted.
Along with the data values of a column, each individual node of an index also stores a piece of information about the column value’s row location on disk. This crucial piece of lookup data is called a ROWID. The ROWID for the column value points Oracle directly to the disk location of the table row corresponding to the column value. A ROWID identifies the location of a row in a data block in the datafile on disk. With this information, Oracle can then find all the data associated with the row in the table.

Tip: The ROWID for a table is an address for the row on disk. With the ROWID, Oracle can find the data on disk rapidly.

Bitmap Index Structure
This topic is pretty advanced, so consider yourself forewarned. The other type of index available in Oracle is the bitmap index. Try to conceptualize a bitmap index as being a sophisticated lookup table, having rows that correspond to all unique data values in the column being indexed. Therefore, if the indexed column contains only three distinct values, the bitmap index can be visualized as containing three rows. Each row in a bitmap index contains four columns. The first column contains the unique value for the column being indexed. The next column contains the start ROWID for all rows in the table. The third column in the bitmap index contains the end ROWID for all rows in the table. The last column contains a bitmap pattern, in which every row in the table will have one bit. Therefore, if the table being indexed contains 1,000 rows, this last column of the bitmap index will have 1,000 corresponding bits in this last column of the bitmap index. Each bit in the bitmap index will be set to 0 (off) or 1 (on), depending on whether the corresponding row in the table has that distinct value for the column. In other words, if the value in the indexed column for that row matches this unique value, the bit is set to 1; otherwise, the bit is set to 0. Figure 7-2 displays a pictorial representation of a bitmap index containing three distinct values.

Each row in the table being indexed adds only a bit to the size of the bitmap pattern column for the bitmap index, so growth of the table won’t affect the size of the bitmap index too much. However, each distinct value adds another row to the bitmap index, which adds another entire bitmap pattern with one bit for each row in the table. Be careful about adding distinct values to a column with a bitmap index, because these indexes work better when few distinct values are allowed for a column. The classic example of using a bitmap index is where you want to query a table containing employees based on a GENDER column, indicating whether the employee is male or female. This information rarely changes about a person, and only two distinct possibilities, so a traditional B-tree index is not useful in this case. However, this is exactly the condition where a bitmap index would aid performance. Therefore, the bitmap index improves performance in situations where traditional indexes are not useful, and vice versa.
Tip : Up to 32 columns from one table can be included in a single B-tree index on that table, whereas a bitmap index can include a maximum of 30 columns from the table.
CREATE INDEX
You can create a unique B-tree index on a column manually by using the create index name on table (column) statement containing the unique keyword. This process is the manual equivalent of creating a unique or primary key constraint on a table. (Remember, unique indexes are created automatically in support of those constraints.)
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Creating Function-Based Indexes
To create a function-based index in your own schema on your own table, you must have the CREATE INDEX and QUERY REWRITE system privileges. To create the index in another schema or on another schema’s table, you must have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges. The table owner must also have the EXECUTE object privilege on the functions used in the function-based index.
The function-based index is a new type of index in Oracle that is designed to improve query performance by making it possible to define an index that works when your where clause contains operations on columns. Traditional B-tree indexes won’t be used when your where clause contains columns that participate in functions or operations. For example, suppose you have table EMP with four columns: EMPID, LASTNAME, FIRSTNAME, and SALARY. The SALARY column has a B-tree index on it. However, if you issue the select * from EMP where (SALARY*1.08) > 63000 statement, the RDBMS will ignore the index, performing a full table scan instead. Function-based indexes are designed to be used in situations like this one, where your SQL statements contain such operations in their where clauses. The following code block shows a function-based index defined:
SQL> CREATE INDEX ixd_emp_01
  2  ON emp(SAL*1.08);
By using function-based indexes like this one, you can optimize the performance of queries containing function operations on columns in the where clause, like the query shown previously. As long as the function you specify is repeatable, you can create a function-based index around i
DROP Indexes
When an index is no longer needed in the database, the developer can remove it with the drop index command. Once an index is dropped, it will no longer improve performance on searches using the column or columns contained in the index. No mention of that index will appear in the data dictionary any more either. You cannot drop the index that is used for a primary key.
The syntax for the drop index statement is the same, regardless of the type of index being dropped (unique, bitmap, or B-tree). If you want to rework the index in any way, you must first drop the old index and then create the new one. The following is an example:
DROP INDEX employee_last_first_indx_01;

/by
, , ,

Sequences in SQL

A sequence is a database object that generates integers according to rules specified at the time the sequence is created. A sequence automatically generates unique numbers and is sharable between different users in Oracle. Sequences have many purposes in database systems—the most common of which is to generate primary keys automatically. However, nothing binds a sequence to a table’s primary key, so in a sense it’s also a sharable object
Sequences are created with the create sequence statement
CREATE SEQUENCE
START WITH
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE
ORDER
CACHE
1. Start with n Enables the creator of the sequence to specify the first value generated by the sequence. Once created, the sequence will generate the value specified by start with the first time the sequence’s NEXTVAL virtual column is referenced. If no start with value is specified, Oracle defaults to a start value of 1.
2. Increment by n Defines the number by which to increment the sequence every time the NEXTVAL virtual column is referenced. The default for this clause is 1 if it is not explicitly specified. You can set n to be positive for incrementing sequences or negative for decrementing or countdown sequences.
3. Minvalue n Defines the minimum value that can be produced by the sequence. If no minimum value is specified, Oracle will assume the default, nominvalue.
4. Maxvalue n Defines the maximum value that can be produced by the sequence. If no maximum value is desired or specified, Oracle will assume the default, nomaxvalue.
5. Cycle Enables the sequence to recycle values produced when maxvalue or minvalue is reached. If cycling is not desired or not explicitly specified, Oracle will assume the default, nocycle. You cannot specify cycle in conjunction with nomaxvalue or nominvalue. If you want your sequence to cycle, you must specify maxvalue for incrementing sequences or minvalue for decrementing or countdown sequences.
6. Cache n Enables the sequence to cache a specified number of values to improve performance. If caching is not desired or not explicitly specified, Oracle will assume the default, which is to cache 20 values.
7. Order Enables the sequence to assign values in the order in which requests are received by the sequence. If order is not desired or not explicitly specified, Oracle will assume the default, noorder.
Example 1:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,…}. It will cache up to 20 values for performance.
Example 2:
The below sequence is a dercrment one. It starts with 100 and decreases by 1.
CREATE SEQUENCE XX_Notification_number
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20
Referencing Sequences in Data Changes
Sequence-value generation can be incorporated directly into data changes made by insert and update statements. This direct use of sequences in insert and update statements is the most common use for sequences in a database. In the situation where the sequence generates a primary key for all new rows entering the database table, the sequence would likely be referenced directly from the insert statement. Note, however, that this approach sometimes fails when the sequence is referenced by triggers. Therefore, it is best to reference sequences within the user interface or within stored procedures. The following statements illustrate the use of sequences directly in changes made to tables:
INSERT INTO expense(expense_no, empid, amt, submit_date)
VALUES(countdown_20.nextval, 59495, 456.34, ’21-NOV-99′);
SEQUENCE_NAME.NEXTVAL & SEQUENCE_NAME.CURRVAL
Once the sequence is created, it is referenced using the CURRVAL and NEXTVAL pseudocolumns. The users of the database can view the current value of the sequence by using a select statement. Similarly, the next value in the sequence can be generated with a select statement. Because sequences are not tables—they are only objects that generate integers via the use of virtual columns—the DUAL table acts as the “virtual” table from which the virtual column data is pulled. As stated earlier, values cannot be placed into the sequence; instead, they can only be selected from the sequen
Example 3:
Select XX_Notification_number.NEXTVAL from dual
Select XX_Notification_number.CURRVAL from dual
Alter sequence
The time may come when the sequence of a database will need its rules altered in some way. For example, you may want sequence XX_Notification_number to decrement by a different number. Any parameter of a sequence can be modified by issuing the alter sequence statement. The following is an example:

Alter Sequence sequence_name//Write new values of the sequence parameters
START WITH 100
INCREMENT BY -1
MAXVALUE 100
MINVALUE 1
CYCLE
CACHE 20
Example 4:
alter sequence XX_Notification_number
increment by -2;

/by
, , ,

Using Public and Private Synonyms

The objects in Oracle you create are available only in your schema unless you grant access to the objects explicitly to other users. We’ll discuss privileges and user access in the next section. However, even when you grant permission to other users for using an object, the boundary created by schema ownership will force other users to prefix the object name with your schema name in order to access your object. For example, SCOTT owns the EMP table. If TURNER wants to access SCOTT’s EMP table, he must refer to EMP as SCOTT.EMP. If TURNER doesn’t, the following happens:

SELECT * FROM emp WHERE empno = 7844;
So, TURNER can’t even see his own employee data—in fact, Oracle tells him that the EMP table doesn’t even exist (pretty sneaky, eh?). Yet, as soon as TURNER prefixes the EMP table with its schema owner, SCOTT, a whole world of data opens up for TURNER, as you can see in the following code block:
SELECT empno, ename, sal FROM SCOTT.emp   2  WHERE empno = 7844;
If remembering which user owns which table seems unnecessarily complicated, synonyms can be used on the database for schema transparency. Synonyms are alternative names that can be created as database objects in Oracle to refer to a table or view. You can refer to a table owned by another user using synonyms. Creating a synonym 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. Synonyms are also used to shorten lengthy object names.
Two types of synonyms exist in Oracle: private synonyms and public synonyms. You can use a private synonym within your own schema to refer to a table or view by an alternative name. Private synonyms are exactly that—they are private to your schema and therefore usable only by you. A private synonym name must be distinct from all other objects owned by the same user.
Think of private synonyms as giving you the ability to develop “pet names” for database objects in Oracle. You can use public synonyms to enable all users in Oracle to access database objects you own without having to prefix the object names with your schema name. This concept of referencing database objects without worrying about the schema the objects are part of is known as schema transparency. Public synonyms are publicly available to all users of Oracle; however, you need special privileges to create public synonyms. We’ll talk more about the privilege required for creating public synonyms in the next section. For now, the following code block demonstrates how to create private and public synonyms, respectively:
 create synonym all_my_emps for emp;
Tip     
Synonyms do not give you access to data in a table that you do not already have access to. Only privileges can do that. Synonyms simply enable you to refer to a table without prefixing the schema name to the table reference. When resolving a database table name, Oracle looks first to see whether the table exists in your schema. If Oracle doesn’t find the table, Oracle searches for a private synonym. If none is found, Oracle looks for a public synonym.
Drop Synonyms
Synonyms are dropped using the drop synonym command, as shown in the following code block:
Drop synonym emp;
/by
, , ,

Database Objects

Some of the objects that are part of the relational database produced by Oracle and that are used in the functions just mentioned are as follows:

  • Tables, views, and synonyms Used to store and access data. Tables are the basic unit of storage in Oracle. Views logically represent subsets of data from one or more tables. Synonyms provide alternate names for database objects.
  • Indexes and the Oracle RDBMS Used to speed access to data.
  • Sequences Used for generating numbers for various purposes.
  • Triggers and integrity constraints Used to maintain the validity of data entered.
  • Privileges, roles, and profiles Used to manage database access and usage.
  • Packages, procedures, and functions Application PL/SQL code used in the database.
/by
, , ,

Transaction Control

One of the great benefits Oracle provides you is the ability to make changes in database using SQL statements and then decide later whether we want to save or discard them. Oracle enables you to execute a series of data-change statements together as one logical unit of work, called a transaction, that’s terminated when you decide to save or discard the work. A transaction begins with your first executable SQL statement. Some advantages for offering transaction processing in Oracle include the following:

  • Transactions enable you to ensure read-consistency to the point in time a transaction began for all users in the Oracle database.
  • Transactions enable you to preview changes before making them permanent in Oracle.
  • Transactions enable you to group logically related SQL statements into one logical unit of work.

Transaction processing consists of a set of controls that enable a user issuing an insert, update, or delete statement to declare a beginning to the series of data-change statements he or she will issue. When the user has finished making the changes to the database, the user can save the data to the database by explicitly ending the transaction. Alternatively, if a mistake is made at any point during the transaction, the user can have the database discard the changes made to the database in favor of the way the data existed before the transaction.
The commands that define transactions are as follows:

  1. Set transaction Initiates the beginning of a transaction and sets key features. This command is optional. A transaction will be started automatically when you start SQL*Plus, commit the previous transaction, or roll back the previous transaction.
  2. Commit Ends the current transaction by saving database changes and starts a new transaction.
  3. Rollback Ends the current transaction by discarding database changes and starts a new transaction.
  4. Savepoint Defines breakpoints for the transaction to enable partial rollbacks.
  5. Locks

Set transaction

This command can be used to define the beginning of a transaction. If any change is made to the database after the set transaction command is issued but before the transaction is ended, all changes made will be considered part of that transaction. The set transaction statement is not required, because a transaction begins under the following circumstances:

  • As soon as you log onto Oracle via SQL*Plus and execute the first command
  • Immediately after issuing a rollback or commit statement to end a transaction
  • When the user exits SQL*Plus
  • When the system crashes
  • When a data control language command such as alter database is issued

By default, a transaction will provide both read and write access unless you override this default by issuing set transaction read only. You can set the transaction isolation level with set transaction as well. The set transaction isolation level serializable command specifies serializable transaction isolation mode as defined in SQL92. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, the DML statement fails. The set transaction isolation level read committed command is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, the DML statement waits until the row locks are released
Commit
The commit statement in transaction processing represents the point in time where the user has made all the changes he or she wants to have logically grouped together, and because no mistakes have been made, the user is ready to save the work. The work keyword is an extraneous word in the commit syntax that is designed for readability.
Issuing a commit statement also implicitly begins a new transaction on the database because it closes the current transaction and starts a new one. By issuing a commit, data changes are made permanent in the database. The previous state of the data is lost. All users can view the data, and all savepoints are erased. It is important also to understand that an implicit commit occurs on the database when a user exits SQL*Plus or issues a data-definition language (DDL) command such as a create table statement, used to create a database object, or an alter table statement, used to alter a database object.
The following is an example:
SQL> COMMIT;
Commit complete.
SQL> COMMIT WORK;
Commit complete.
Rollback
  If you have at any point issued a data-change statement you don’t want, you can discard the changes made to the database with the use of the rollback statement. The previous state of the data is restored. Locks on the affected rows are released. After the rollback command is issued, a new transaction is started implicitly by the database session. In addition to rollbacks executed when the rollback statement is issued, implicit rollback statements are conducted when a statement fails for any reason or if the user cancels a statement with the CTRL-C cancel command. The following is an example:
SQL> ROLLBACK;
Rollback complete
Savepoint
In some cases involving long transactions or transactions that involve many data changes, you may not want to scrap all your changes simply because the last statement issued contains unwanted changes. Savepoints are special operations that enable you to divide the work of a transaction into different segments. You can execute rollbacks to the savepoint only, leaving prior changes intact. Savepoints are great for situations where part of the transaction needs to be recovered in an uncommitted transaction. At the point the rollback to savepoint so_far_so_good statement completes in the following code block, only changes made before the savepoint was defined are kept when the commit statement is issued:
UPDATE products
SET quantity = 55
WHERE product# = 59495;
SAVEPOINT so_far_so_good;
//Savepoint created.
UPDATE spanky.products
SET quantity = 504;
ROLLBACK TO SAVEPOINT so_far_so_good;
COMMIT;

Locks
The final aspect of the Oracle database that enables the user to employ transaction processing is the lock, the mechanism by which Oracle prevents data from being changed by more than one user at a time. Several different types of locks are available, each with its own level of scope. Locks available on a database are categorized into table-level locks and row-level locks.
A table-level lock enables only the user holding the lock to change any piece of row data in the table, during which time no other users can make changes anywhere on the table. A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock’s mode determines the modes in which other table locks on the same table can be obtained and held.
A row-level lock gives the user the exclusive ability to change data in one or more rows of the table. However, any row in the table that is not held by the row-level lock can be changed by another user
Tip
An update statement acquires a special row-level lock called a row-exclusive lock, which means that for the period of time the update statement is executing, no other user in the database can view or change the data in the row. The same goes for delete or insert operations. Another update statement—the select for update statement—acquires a more lenient lock called the share row lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

/by