Tables are used as definitional structural repositories for data. Tables in a relational database are organized in columns where repetitions or tuples are represented by duplications of that column structure in the form of rows. In order to examine tables briefly the first thing we need to look at is datatypes.
Datatypes can be divided into simple and complex datatypes. Simple datatypes encompass the more traditional datatypes such as strings and integers. Complex datatypes include object types such as binary objects and reference pointers.
| Datatype | Description |
|---|---|
| [N]VARCHAR2(n) | Variable length string up to 4000 characters. |
| [N]CHAR(n) | Fixed length string up to 2000 characters. |
| NUMBER(p,s) | Precision = number of characters, Scale = number of decimal places. p = 1 to 38, s = -84 to 127. |
| FLOAT(p) | A floating point or real number. p = 1 to 126. |
| INTEGER | Creates NUMBER(38), size 38 characters NUMBER field. |
| SMALLINT | Creates NUMBER(38), size 38 characters NUMBER field. |
| DATE | Stored as Julian date in seconds containing year, month, day, hours, minutes, seconds to 100th precision. |
| TIMESTAMP(p) | Precision is scale in fractions of a second. Thus TIMESTAMP(2) stores a 10th of a second. |
| Datatype | Description |
|---|---|
| REF | Pointer referencing another object. |
| BFILE | Pointer including full path and file name to a file stored externally to the database. Typically used for multimedia objects as the most efficient method since only the pointer to the multimedia object is stored in the database. Storing large objects in a database can result in a large amount of block level fragmentation, leading to slow retrieval later on. |
| Nested Table | Dynamic or variable length array used for creating user defined data types. |
| VARRAY(n) | Fixed length array used for creating user defined data types. |
| BLOB, CLOB and NCLOB | Binary object datatypes include BLOBs, containing binary objects such as multimedia files, CLOBs for large text objects and NCLOBs for a national language character sets. |
| XML | Storage and direct XML document retrieval. |
| Spatial | GIS, CAD and CAM systems geometric modeling data storage. |
| Media | Multimedia storage. |
| "Any" | Generic data type storage. |
Tables can fall into several different categories.
A cluster takes some columns from one or more tables and clusters them into a single object for fast read access. An index organized table (IOT) would include all columns from a single table and organize the data into index order.
CREATE [ GLOBAL TEMPORARY ] TABLE [ schema.] table
[ (relational properties) | (object properties) ]
[ ON COMMIT [ DELETE | PRESERVE ] ROWS ]
[ physical properties ] [ table properties ];
CREATE TABLE [ schema.] table OF XMLTYPE ... ;
relational properties:
{ column datatype [ DEFAULT expression ]
[ column reference constraint | [ column constraint [, column constraint ] ] ] ]
[ table constraint ] | [ table reference constraint ] }
physical properties:
[ [ storage ] [ TABLESPACE tablespace ] [ [ NO ] LOGGING ] [ [ NO ] CACHE ] ]
| [ ORGANIZATION [ [ heap | index ] organized ] | external ]
| [ CLUSTER cluster [ column [, column ] ] ] ]
table properties:
[ [ table level column properties ] [ partitioning ] [ [ NO ] CACHE ] [ parallel ]
[ AS subquery ] ]
ALTER TABLE [ schema.] table
{ properties | columns | constraints [ enable clause | disable clause ]
| partitioning | external table | move table }
| [ { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS } };
properties: except for RENAME TO new table
columns:
[ ADD (column datatype [ DEFAULT expression ] [ constraint ]) [ column properties ]
| MODIFY (column [ datatype ] [ DEFAULT expression ] [ constraint ])
| DROP { UNUSED COLUMNS | COLUMNS CONTINUE } [ CHECKPOINT n ]
| DROP { COLUMN column | (column [,column ] ) }
[ CASCADE CONSTRAINTS | INVALIDATE ] [ CHECKPOINT n ]
| SET UNUSED { COLUMN column | (column [,column ] ) }
[ CASCADE CONSTRAINTS | INVALIDATE ] [ CHECKPOINT n ]
| RENAME COLUMN column TO column ]
When altering tables you can:
DROP TABLE table;