SQL> DESCRIBE trouble;
Name Null? Type
CITY NOT NULL VARCHAR2(13)
SAMPLEDATE NOT NULL DATE
NOON NUMBER(3,1)
MIDNIGHT NUMBER(3,1)
PRECIPITATION NUMBER
ð The columns in the table represent the three major data types in Oracle - VARCHAR2, DATE & NUMBER.
SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL, SampleDate DATE NOT NULL, Noon NUMBER(3,1), Midnight NUMBER(3,1), Precipitation NUMBER);
ð The individual column definitions are separated by commas. There is no comma after the last column definition. The table and column names must start with a letter of the alphabet, but may include letters, numbers and underscores. Names may be 1 to 30 characters in length, must be unique within the table and cannot be an Oracle reserved word. Case does not matter in creating a table. There are no options for DATE data types. Character data types must have their maximum length specified. Numbers may be either high-precision (up to 38 digits) or specified-precise, based on the maximum number of digits and the number of places allowed to the right of the decimal. The maximum width for CHAR (fixed-length) columns is 2,000 characters. VARCHAR2 (varying-length character) columns can have up to 4,000 characters.
Constraints in Creating Table
The CREATE TABLE statement lets you enforce several different kinds of constraints on a table: candidate keys, primary keys, foreign keys and check conditions. A constraint clause can constrain a single column or group of characters in a table. The point of these constraints is to get Oracle to do most of the work in maintaining the integrity of your database.
There are two ways to specify constraints: as part of the column definition (a column constraints) or at the end of the CREATE TABLE statement (a table constraint).
The Candidate Key
A candidate key is a combination of one or more columns, the values of which uniquely identify each row of a table.
SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_UQ UNIQUE (City, SampleDate));
ð Notice that both columns are also declared to be NOT NULL. If NOT NULL isn't specified, the column can have NULL valus.
The Primary Key
The primary key of a table is one of the candidate keys that you give some special characteristics. You can have only one primary key and a primary key column cannot contains NULLs.
Example 1:
SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_PK PRIMARY KEY (SampleDate));
Example 2:
SQL> CREATE TABLE worker(
Name VARCHAR2(25) PRIMARY KEY,
Age NUMBER,
Lodging VARCHAR2(15));
ð In the case, the Name column is the primary key and Oracle will generate a name for the PRIMARY KEY constraint.
The Foreign Key
A foreign key is a combination of columns with values based on the primary key values from another table. In the worker table the Lodging column refers to values for the lodging column in the lodging table:
SQL> CREATE TABLE worker(
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15)
CONSTRAINT worker_PK PRIMARY KEY( Name),
FOREIGN KEY (Lodging) REFERENCES lodging( Lodging));
ð You can refer to a primary or unique key, even in the same table. However, you can't refer to a table in a remote database in the references clause. Sometimes you may want to delete these dependent row when you delete the row they depend on. In the case of worker and lodging, if you delete lodging, you'll want to make the Lodging column NULL. In another case, you might want to delete the whole row. The clause on delete cascade added to the references clause tells Oracle to delete the dependent row when you delete the corresponding row in the parent table. The action automatically maintains referential integrity.
The Check Constraint
Many columns must have values that are within a certain range or that satisfy certain conditions.
SQL> CREATE TABLE worker(
Name VARCHAR2(25),
Age NUMBER CHECK (Age BETWEEN 18 AND 65),
Lodging VARCHAR2(15)
CONSTRAINT worker_PK PRIMARY KEY( Name),
FOREIGN KEY (Lodging) REFERENCES lodging( Lodging));
Naming Constraints
You can name your constraints. For example, the primary key on the trouble table could be named trouble_PK.
SQL> CREATE TABLE trouble(City VARCHAR2(13) NOT NULL,
SampleDate DATE NOT NULL,
Noon NUMBER(3,1),
Midnight NUMBER(3,1),
Precipitation NUMBER,
CONSTRAINT Trouble_PK PRIMARY KEY (City, SampleDate));
ð If you do not specify a name for the constraint, then Oracle will generate a name. Most of Oracle's generated constraint names are of the form SYS_C######; for example, SYS_C000145.
SQL> DROP TABLE trouble;
Table dropped.
In Oracle, the truncate command lets you remove all the rows in the table and reclaim the space for other uses without removing the table definition from the database.
SQL> TRUNCATE TABLE trouble;
Table truncated.
ð Truncate can't be rolled back. If there are triggers that delete rows that depend on rows in the table, truncating does not execute those triggers. You should be sure you really want to truncate before doing it.
Altering Table
Tables can be altered in one of three ways: by adding a column to an existing table; by changing a column's definition; or by dropping a column. Suppose you decide to add two new columns to the trouble table: Condition, which you believe should be NOT NULL and Wind, for the wind speed. Example,
SQL> ALTER TABLE trouble ADD (Condition VARCHAR2(9) NOT NULL,
Wind NUMBER(3));
alter table trouble add (
*
ERROR at line 1: ORA-01758: table must be empty to add mandatory (NOT NULL) column.
ð You get an error message because you cannot add a column defied as NOT NULL in a existing table where data are stored in other columns. Each row in the table would have a new empty column defied as NOT NULL. The alternative is to first alter the table by adding the column without the NOT NULL restriction:
SQL> ALTER TABLE trouble ADD (Condition VARCHAR2(9),
Wind NUMBER(3));
ð Then fill the column with data for every row by using following command.
SQL> UPDATE trouble SET Condition='SUNNY';
ð Finally, alter the table again and modify the column definition to NOT NULL:
SQL> ALTER TABLE trouble MODIFY (Condition VARCHAR2(9) NOT NULL);
Dropping a Column
Dropping a column is more complicated than adding or modifying a column, because of the additional work that Oracle has to do. It's recovering the space that was actually taken up by the column values that is more complex and potentially very time-consuming for the database. For this reason, you can drop a column immediately or you can mark it as "unused" to be dropped at a later time. If the column is dropped immediately, the action may impact performance. If the column is marked as unused; there will be no impact on performance.
To drop a column, use either the set unused clause or the drop clause of the alter table command. You cannot drop a pseudo-column, a column of a nested table, or a partition key column.
SQL> ALTER TABLE trouble DROP COLUMN Wind;
Or
SQL> ALTER TABLE trouble SET UNUSED COLUMN Wind;
ð Marking a column as "unused" does not release the space previously used by the column, until you drop the unused columns:
SQL> ALTER TABLE trouble DROP UNUSED COLUMNS;
Note: You can query USER_UNUSED_COL_TABS. DBA_UNUSED_COL_TABS and ALL_UNUSED_COL_TABS to see all tables with columns marked as unused.
You can drop multiple columns in a single command, as shown in the following listing:
SQL> ALTER TABLE trouble DROP (Condition, Wind);
If the dropped columns are part of primary key or unique constraints, then you will need to also use the cascade constraints clause as part of your alter table command. If you drop a column that belongs to a primary key, Oracle will drop both the column and the primary key index.
Creating View
If a view is based on a single underlying table, you can inset, update or delete rows in the view.
╟ You cannot insert if the underlying table has any NOT NULL columns that don't appear in the view.
╟ You cannot insert or update if any one of the view's columns referenced in the insert or update contains functions or calculations.
╟ You cannot insert, update or delete if the view contains group by, distinct, or a reference to the pseudo-column RowNum.
Stability of a View
Remember that the results of querying a view are built instantly from a table (or tables) when you execute the query. Until that moment, the view has no data of its own, as a table does. It is merely a description (a SQL statement) of what information to pull out of other tables and how to organize it. As a consequence, if a table is dropped, the validity of a view is destroyed. Attempting to query a view where the underlying table has been dropped will produce will produce an error message about the view.
In the following sequence, a view is created on an existing table, the table is dropped and the view then is queried:
SQL> CREATE VIEW rain AS SELECT City, Precipitation FROM trouble;
View created.
SQL> DROP TABLE trouble;
Table dropped.
SQL> SELECT * FROM rain;
ERROR at line 1: ORA-00942: table or view does not exist.
Again, if you add a new column in trouble trouble table, then what will the view rain act.
SQL> ALTER TABLE trouble ADD (Warning VARCHAR2(20));
Table altered.
ð Despite the change to the view's base table, the view is still valid and will access the new columns in the trouble table.
ORDER BY in View
You cannot use an order by in a create view statement. Occasionally, a group by, which can be used, may accomplish the purpose for which an order by might be used.
SQL> CREATE VIEW discomfort AS SELECT City, Precipitation FROM comfort GROUP BY Precipitation, City;
View created.
Creating a Read-Only View
You can use the with read only clause of the create view command to prevent users from manipulating records via the view.
SQL> CREATE or REPLACE VIEW rain AS SELECT City, Precipitation FROM trouble WITH READ ONLY;
SQL> CREATE TABLE rain AS SELECT City, Precipitation FROM trouble;
Table created.
ð When the new table is described, it reveals that is has "inherited" its column definitions from the trouble table.
You also can use this technique to create a table with column definitions like that of the source table, but with no rows in it, by building a where clause that will select no rows from the old table:
SQL> CREATE TABLE rain AS SELECT City, Precipitation FROM trouble WHERE 1=2;
Using Partitioned Tables
You can divide the rows of a single table into multiple parts. Dividing a table's date in this manner is called partitioning table; the table that is partitioned is called a partitioned table and the parts are called partitions. Partitioning is useful for very large tables. By splitting a large table's rows across multiple smaller partitions; you accomplish several important goals:
╟ The performance of queries against the tables may improve.
╟ Since the partitioned table's data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.
╟ Backup and recovery operations may perform better.
Creating a Partitioned Table By Range
Typically, table are partitioned by ranges of values. To partition the table's records, use the partition by range clause of the create table command, as shown next. The ranges will determine the values stored in each partition.
SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY RANGE (Lodging)(
PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_TS
)
;
ð For any lodging values less than F, the record will be stored in the partition name Part1. The part1 partition will be stored in the Part1_TS tablespace. Any Lodging in the range between F and N will be stored in the Part2 partition; values between N and T will be stored in the PART3 partition. Here, you don't need to specify a maximum value for the last partition; the maxvalue keyword tells Oracle to use the partition to store any data that could not be stored in the earlier partitions. Notice that for each partition, you only specify the maximum value for the range. The minimum value for the range is implicitly determined by Oracle.
Creating a Partitioned Table By Hash
A has partition determines the physical placement of data by performing a hash function on the values of the partition key. In range partitioning, consecutive values of the partition key are usually stored in the same partition. In hash partitioning, consecutive values of the partition key are not necessarily stored in the same partition. To create a hash partition, use the partition by hash clause in place of the partition by range clause:
SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY HASH (Lodging)
PARTITIONS 10;
You can name each partition and specify its tablespace, just as you would for range partition,
SQL> CREATE TABLE worker (
Name VARCHAR2(25),
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY HASH (Lodging)
PARTITIONS 2
STORE IN (Part1_TS, Part2_TS);
This method will create partitions with system-generated names of the format SYS_Pnnn. The number of tablespaces specified in the store in clause does not have to equal the number of partitions. You can specify named patitions:
.....................
.....................
PARTITION BY HASH (Lodging) (
PARTITION P1 TABLESPACE P1_TS,
PARTITION P2 TABLESPACE P2_TS
);
ð In this method, each partition is given a name and a tablespace, with the option of using an additional lob or varray storage clause.
Creating Subpartitions
You can create subpartitions - partitions of partitions. You can use subpartitions to combine two types of partitions: range partitions and has partitions.
SQL> CREATE TABLE worker (
Name VARCHAR2(25) PRIMARY KEY,
Age NUMBER,
Lodging VARCHAR2(15),
CONSTRAINT worker_PK PRIMARY KEY (Name)
)
PARTITION BY RANGE (Lodging)
SUBPARTITION BY HASH (Name)
SUBPARTITIONS 10
(
PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_TS
)
;
ð The worker table will be range-partitioned table, you should create into four partitions, using the range specified for the four named partitions. Each of those partitions will be hash-partitioned on the Name column.
Indexing Partitions
When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table. In the following listing, the create index command for the worker table is shown:
SQL> CREATE INDEX worker_lodging ON worker (Lodging)
LOCAL
(PARTITION Part1
TABLESPACE Part1_NDX_TS,
PARTITION Part2
TABLESPACE Part2_NDX_TS,
PARTITION Part3
TABLESPACE Part3_NDX_TS,
PARTITION Part4
TABLESPACE Part4_NDX_TS);
ð Notice the local keyword. In this create index command, no ranges are specified. Instead, the local keyword tells Oracle to create a separate index for each partition of the worker table.
You can also create "global" indexes. A global index may contain values from multiple partitions. The index itself may be partitioned,
SQL> CREATE INDEX worker_lodging ON worker (Lodging)
GLOBAL PARTITION BY RANGE (Lodging)
(PARTITION Part1 VALUES LESS THAN ('F')
TABLESPACE Part1_NDX_TS,
PARTITION Part2 VALUES LESS THAN ('N')
TABLESPACE Part2_NDX_TS,
PARTITION Part3 VALUES LESS THAN ('T')
TABLESPACE Part3_NDX_TS,
PARTITION Part4 VALUES LESS THAN (MAXVALUE)
TABLESPACE Part4_NDX_TS)
;
ð The global clause in this create index command allows you to specify ranges for the index values that are different from the range the ranges for the table partitions. Local indexes may be easier to manage than global indexes; however, global indexes may perform uniqueness checks faster than local (partitioned) indexes perform them.
Managing Partitioned Tables
you can use the alter table command to add, drop, exchange, move, modify, rename, split and truncate partitions. These alter table command options allow you to alter the existing partition structure, as may be required after a partitioned table has been used heavily.
Querying Directly from Partitions
If you know the partition from which you will be retrieving your data, you can specify the name of the partition as part of the from clause of you query. For example, suppose you want to query the records for the workers whose Lodgings begin with the letter R.
SQL> SELECT * FROM worker PARTITION (Part3) WHERE NAME LIKE 'R%';