Database Objects

A database object is any defined object in a database that is used to store or reference data.Anything which we make from create command is known as Database Object. It can be used to hold and manipulate the data. Some of the examples of database objects are : view, sequence, indexes, etc.

The various database objects are

  • Synonym

  • Sequences

  • views

  • Indexes

Synonyms

  • A synonym is a database object, which is used as an alias(alternative name) for a table, views or sequence.

  • Synonym can be private or public. The former is created by normal user, which is available only to that person whereas the latter is created by DBA, which can be availed by any database user.

  • A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package.

  • Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary.

Synonyms are often used for security and convenience. For example, they can do the following:

  • Mask the name and owner of an object.

  • Provide location transparency for remote objects of a distributed database.

  • Simplify SQL statements for database users.

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.

Synonyms are very useful in both distributed and non distributed database environments because they hide the identity of the underlying object, including its location in a distributed system. This is advantageous because if the underlying object must be renamed or moved, then only the synonym needs to be redefined. Applications based on the synonym continue to function without modification.

Syntax:

create [public] synonym <synonym_name> for <table_name>;
SQL> create synonym e for emp;

  • When a grant is given to a other user on this synonym he can perform DML operations such as insert, delete, update on the synonym in the same way that of the table, but he cannot perform any DDL operations on the synonym except dropping the synonym.

  • The synonym is just an alias of the table and all the manipulations on it actually effect the table.

  • Public synonyms are created by database administrator to hide the identity of the base table and reduce the complexity of SQL statements.

  • One such public synonym is TAB, which we use for selecting the tables owned by the user.

  • These public synonyms are owned by user group PUBLIC.

Sequences

  • A Sequence is a database object, which can generate unique, sequential integer values. It can be used to automatically generate primary key or unique key values.

  • A sequence can be either in an ascending or a descending order.

  • A sequence generates a serial list of unique numbers for numeric columns of a database’s tables.

  • Sequences simplify application programming by automatically generating unique numerical values for the rows of a single table or multiple tables.

  • For example, assume two users are simultaneously inserting new employee rows into the EMP table.

  • By using a sequence to generate unique employee numbers for the EMPNO column, neither user has to wait for the other to enter the next available employee number.

  • The sequence automatically generates the correct values for each user.

  • Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. After creation, a sequence can be accessed by various users to generate actual sequence numbers.

Syntax:

Create sequence <seq_name>
[increament by n]
[start with n]
[maxvalue n]
[minvalue n]
[cycle/nocycle]
[cache/nocache]

  • While creating a sequence we can define the following, start with, increment by n, n is an integer which specifies the interval between sequence numbers. The default is 1.

  • Start with specifies the first sequence numbers to be generated.

  • Minvalue specifies the minimum value of the sequence.

  • Maxvalue specifies the maximum value that the sequence can generate.

  • Cycle specifies that the sequence continues to generate values from the beginning after reaching either its max or min value.

  • No cycle specifies that the sequence cannot generate more values after reaching either its max value or min value. The default is ‘no cycle’.

  • Cache The CACHE option pre-allocates a set of sequence numbers and retains them in memory so that sequence numbers can be assigned faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache.

  • No cache The default value ‘nocache’, does not preallocate sequence numbers for faster access

Ex:

SQL> create sequence stdid Increament by 1 Start with 1 Maxval 10 Minval 1
Cycle Cache 4;

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. After creating the sequence we can access it with the pseudo columns like currval and Nextval.

Nextval - Returns initial value of the sequence, when referred to,for the first time.

Currval - Returns the current value of the sequence which is the value returned by the last reference to nextval.

SQL> select stdid.nextval from dual;

Note: A sequence can be altered to change the increment value or to change the number of cached sequence numbers.

SQL> alter sequence stdid maxval 30;

How to Use Sequence Values

  • When you create a sequence, you can define its initial value and the increment between its values.

  • The first reference to NEXTVAL returns the sequence’s initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value.

  • Any reference to CURRVAL always returns the sequence’s current value, which is the value returned by the last reference to NEXTVAL.

  • Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

  • Within a single SQL statement, Oracle will increment the sequence only once for each row.

  • If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

  • If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless of their order within the statement.

  • A sequence can be accessed by many users concurrently with no waiting or locking.

VIEW

  • A view is a Stored query or virtual table. We can use views in most places where a table can be used.

  • The table upon which a view is based are called base tables.

  • Views provide an additional level of table security by restricting the access to a predetermined set of rows and /or columns of a table.

They hide data complexity.i.e a single view can be defined with a join, which is a collection of related columns or rows in multiple tables, the view hides the fact that this information actually originates from several tables.

  • They simplify commands for the user because they allow them to select information from multiple tables without actually knowing how perform a join.

  • Furthermore, they isolate applications from changes in definitions of base tables.

Syntax:

Create [or repalce][no][force]] view <view_name> [column alias name...] as
<query> [with[check option][read only][constraint]];

INDEX

Indexes are totally optional structures that are intended to speed up the execution of SQL statements against table data and cluster data. Indexes are used for direct access to a particular row or set of rows in a table. Indexes are most typically organized as some type of tree structure.

Concepts and Facts

An index can be composed of a single column for a table, or it may be comprised of more than one column for a table. An index based on more than one column is termed a concatenated (or composite) index.

Examples:

  • The SSN key is used to track individual students at a university.

  • The concatenated primary key index for an ENROLL table (SSN + SectionID + Term + Year) that is used to track the enrollment of a student in a particular course section.

  • The maximum number of columns for a concatenated index is 32; but the combined size of the columns cannot exceed about one-half of a data block size.

A unique index allows no two rows to have the same index entry. An example would be an index on student SSN. A non-unique index allows more than one row to have the same index entry (this is also called a secondary key index). An example would be an index on U.S. Mail zip codes.

A function-based index is created when using functions or expressions that involve one or more columns in the table that is being indexed. A function-based index pre-computes the value of the function or expression and stores it in the index. Function-based indexes can be created as either a B-tree or a bitmap index.

A partitioned index allows an index to be spread across several tablespaces - the index would have more than one segment and typically access a table that is also partitioned to improve scalability of a system. This type of index decreases contention for index lookup and increases manageability.

To create an index in our own schema:

  • The table to be indexed must be in our schema, OR

  • We have the INDEX privilege on the table to be indexed, OR

  • We have the CREATE ANY INDEX system privilege.

To create an index in another schema:

  • We have the CREATE ANY INDEX system privilege, AND

  • The owner of the other schema has a quota for the table space that will store the index segment (or UNLIMITED TABLESPACE privilege).

⌖ sql rdbms
← Integrity Constraints
SQL * Plus →

Subscribe For More Content