Database Schema and Basic Database Objects

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas. Schema objects include structures such as tables, views, and indexes.

Some of the most common schema objects are defined in the sections that follow.


Tables

Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. A table that has employee information, for example, can have a column called employee_number, and each row in that column is an employee number.


Indexes

Indexes are optional structures associated with tables. You can create indexes to increase the performance of data retrieval. Just as the index in this manual helps you quickly locate specific information, an Oracle database index provides an access path to table data.

When processing a request, Oracle Database can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications frequently query a table for a range of rows (for example, all employees with a salary greater than 1000) or a specific row (for example, the employee with the highest salary).

You create an index on one or more columns of a table. Thereafter, Oracle Database automatically uses and maintains the index. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes.


Views

Views are customized presentations of data in one or more tables or other views. A view can also be considered a stored query. Views do not contain actual data. Rather, they derive their data from the tables on which they are based, referred to as the base tables of the views.

You can query, update, insert into, and delete views as you can with tables, with some restrictions. If the view is updatable, then all operations performed on the view actually affect the base tables of the view.

Views can provide table security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.


Clusters

Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

Like indexes, clusters do not affect application design. Whether a table is part of a cluster is transparent to users and to applications. SQL statements access data stored in a clustered table in the same way that they access data stored in a nonclustered table.


Synonyms

A synonym is an alias for any table, view, materialized view, sequence, operator, procedure, function, package, Java class schema object, user-defined object type, or another synonym. A synonym is simply an alias, so it requires no storage other than its definition in the data dictionary.


When you create the database, several schemas are created for you.
Two important schemas are
- SYS
The SYS schema contains the data dictionary

- SYSTEM
The SYSTEM schema contains additional tables and views that store administrative information.