Any defined object in the database which can be used to reference or store data is known as a database object. Database objects can be made using the create command. These database objects are used for holding and manipulating the data in the database.

The examples of database objects include:

1. View: Subsets of data from more than one table are logically represented.
2. Table: Composed of rows and columns, the table is the primary form of data representation.
3. Sequence: Sequence generates the primary key values.
4. Synonym: It is an alternative name for a specific object.
5. Index: It improves the performance of some particular queries.

Different Database Objects:

1. Table:

A table is a database object used as a unit of storage.

Syntax:

CREATE TABLE [schema.]table
               (column datatype [DEFAULT expr][, ...]);

Example:

CREATE TABLE dept
           (deptno NUMBER(2),
            dname VARCHAR2(14),
            loc VARCHAR2(13));

2. View:

This object is used in the database to create a type of view of the database. It is a logical view based on one or more tables or even another view. A view does not contain any data of its own but is used to view data in other tables. The view is based on a table, and that table is known as the base table. The data dictionary stores the view as a SELECT statement.

Syntax:

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
                       [(alias[, alias]...)]
                       AS subquery
                       [WITH CHECK OPTION [CONSTRAINT constraint]]
                       [WITH READ ONLY [CONSTRAINT constraint]];

Example:

CREATE VIEW salvu50
               AS SELECT employee_id ID_NUMBER, last_name NAME,
               salary*12 ANN_SALARY
               FROM employees
               WHERE department_id = 50;

3. Sequence:

This object in the database is used to create a sequence in the database. It is a user created database object which can be shared by more than two users to create a unique integer in the database. The most often use of sequence is to create a primary value in the database. This primary value is unique for each row. Once the sequence is generated, it is decremented or incremented using an Oracle routine.

Syntax:

CREATE SEQUENCE sequence
                    [INCREMENT BY n]
                    [START WITH n]
                    [{MAXVALUE n | NOMAXVALUE}]
                    [{MINVALUE n | NOMINVALUE}]
                    [{CYCLE | NOCYCLE}]
                    [{CACHE n | NOCACHE}];

Example:

CREATE SEQUENCE dept_deptid_seq
                        INCREMENT BY 10
                        START WITH 120
                        MAXVALUE 9999
                        NOCACHE
                        NOCYCLE;

Check if the sequence is created by:

SELECT sequence_name, min_value, max_value,
                       increment_by, last_number
                       FROM   user_sequences;

4. Index:

Indexes are created in a database using this database object. Oracle server indexes help fasten the retrieval process of rows using pointers. These indexes can be created automatically or explicitly. In case you do not have an index on rows, a full table scan is performed every time. It creates an indexed path for a location of the data which can be accessed fast. The core purpose of creating an index is to reduce the necessity of running the disk I/O and using an indexed path to the location of the data quickly. The Oracle server creates and maintains this index automatically so that it is easier for access later on. Indexes are quite basic, and once they are created no direct activity from a user is required to maintain it. These indexes are also physically and logically independent of the tables which they index. This independence means that they can be dropped or created at any point of time without affecting the table or its contents in any way.

Syntax:

CREATE INDEX index
            ON table (column[, column]...);

Example:

CREATE INDEX emp_last_name_idx
                ON  employees(last_name);

5. Synonym:

Synonym is a database object which is used for the creation of indexes in a database. The purpose of this database object is to simplify the access to another object by creating an alternate name for it. Using synonyms, you can shorten lengthy names to shorter ones in order make referring easy. In case you want to refer to a table created by another user; you have to prefix the name of the specific table by the name of the user which should be followed by a period. This database object is extremely useful to shorten lengthy names make accessibility easier in the database.

Syntax

CREATE [PUBLIC] SYNONYM synonym FOR object;

Example

CREATE SYNONYM d_sum FOR dept_sum_vu;

Please follow and like us: