12. Connect to the WEBSTORE schema with either SQL Developer or SQL*Plus
and create three sequences which will be used in later exercises. (You may
have to connect first as a privileged user like SYSTEM and grant the “CREATE
SEQUENCE” privilege to the WEBSTORE user.)
create sequence prod_seq;create sequence cust_seq;create sequence order_seq;Two-Minute Drill
Categorize the Main Database Objects
• Some objects contain data, principally tables and indexes.
• Programmatic objects such as stored procedures and functions are executable
code.
• Views and synonyms are objects that give access to other objects.
• Tables are two-dimensional structures, storing rows defined with columns.
• Tables exist within a schema. The schema name together with the table name
makes a unique identifier.
List the Data Types That Are Available for Columns
• The most common character data types are VARCHAR2, NUMBER, and DATE.
• There are many other data types.
Create a Simple Table
• Tables can be created from nothing or with a subquery.
• After creation, column definitions can be added, dropped, or modified.
• The table definition can include default values for columns.
Create and Use Temporary Tables
• Rows in a temporary table are visible only to the session that inserted them.
• DML on temporary tables does not generate redo.
• Temporary tables exist only in sessions’ PGAs or in temporary segments.
• A temporary table can keep rows for the duration of a session or of a
transaction, depending on how it was created.
Constraints
• Constraints can be defined at table creation time or added later.
• A constraint can be defined inline with its column or at the table level after
the columns.
• Table-level constraints can be more complex than those defined inline.
• A table may only have one primary key but can have many unique keys.
• A primary key is functionally equivalent to unique plus not null.
• A unique constraint does not stop insertion of many null values.
• Foreign key constraints define the relationships between tables.
Indexes
• Indexes are required for enforcing unique and primary key constraints.
• NULLs are not included in B*Tree indexes but are included in bitmap indexes.
• B*Tree indexes can be unique or nonunique, which determines whether they
can accept duplicate key values.
• B*Tree indexes are suitable for high cardinality columns, bitmap indexes for
low cardinality columns.
• Bitmap indexes can be compound, function based, or descending; B*Tree
indexes can also be unique, compressed, and reverse key.
Views
• A simple view has one detail (or base) table and uses neither functions nor
aggregation.
• A complex view can be based on any SELECT statement, no matter how
complicated.
• Views are schema objects. To use a view in another schema, the view name
must be qualified with the schema name.
• A view can be queried exactly as though it were a table.
• Views can be joined to other views or to tables, they can be aggregated, and in
some cases they can accept DML statements.
• Views exist only as data dictionary constructs. Whenever you query a view, the
P AR T II
underlying SELECT statement must be run.
Synonyms
• A synonym is an alternative name for a view or a table.
• Private synonyms are schema objects; public synonyms exist outside user
schemas and can be used without specifying a schema name as a qualifier.
• Synonyms share the same namespace as views and tables and can therefore be
used interchangeably with them.
Sequences
• A sequence generates unique values—unless either MAXVALUE or MINVALUE
and CYCLE have been specified.
• Incrementing a sequence need not be committed and cannot be rolled back.
• Any session can increment the sequence by reading its next value. It is possible
to obtain the last value issued to your session but not the last value issued.
Self Test
Bạn đang xem 12. - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P35 PDF