Database Objects

 

Tables

·         Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns.

·         You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width.

·         The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width.

·          A row is a collection of column information corresponding to a single record.

·         You can specify rules for each column of a table. These rules are called integrity constraints. One example is a NOT NULL integrity constraint. This constraint forces the column to contain a value in every row.

Type of Table	Description
Ordinary (heap-organized) table	This is the basic, general-purpose type of table. Its data is stored as an unordered collection (heap)
Clustered table	A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
Index-organized table	Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
Partitioned table	Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

·         Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions.

·         Partitioning can also bring better performance, because many queries can ignore partitions that, according to the WHERE clause, won't have the requested rows, thereby reducing the amount of data to be scanned to produce a result set.

·         Partitions can be further broken down into sub partitions for finer levels of manageability and improved performance. Indexes can be partitioned in similar fashion.

Indexes

·         Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table.

·          Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data.

·         You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

·         Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space.

·         You can create or drop an index without affecting the base tables, database applications, or other indexes. The database automatically maintains indexes when you insert, update, and delete rows of the associated table.

·         If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.

 

Clusters

·         A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks.

·          The tables are grouped together because they share common columns and are often used together.

Views

·         A view is a logical representation of another table or combination of tables.

·         A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves.

·          All operations performed on a view actually affect the base table of the view. You can use views in almost the same way as tables. You can query, update, insert into, and delete from views, just as you can standard tables.

Sequences

·         Sequences are database objects from which multiple users can generate unique integers. The sequence generator generates sequential numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.

Synonyms

·         A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database.

·          Also, they are convenient to use and reduce the complexity of 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 is accessible to every user in a database.

·         A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.

constraint

·         integrity constraint--a rule that restricts the values in a database . Constraint are created on columns in the table .

The six types of integrity constraint .

·         A NOT NULL constraint prohibits a database value from being null.

·         A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.

·         A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.

·         A foreign key constraint requires values in one table to match values in another table.

·         A check constraint requires a value in the database to comply with a specified condition.

·         A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

Triggers

·         Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events

·         A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

·         A database definition (DDL) statement (CREATE, ALTER, or DROP).

·         A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

 

FUNCTION

·         A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name.

·         Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.