4.7 How do I…Create a table from another table?

Problem

When developing applications, I like to make a backup of a table before making untested operations on the table. To do this, I want to create a new table from the existing table and make a copy of all of the rows. In our production environment, we want to create summary tables that are complicated queries of other tables. How do I create a table as a query from another table?

Technique

The CREATE TABLE statement is used to create new tables. The CREATE TABLE statement allows a query to be specified as the source of the columns and rows for the table. If the query used in the CREATE TABLE statement returns rows, the rows are inserted into the new table when it is created. If the query does not return rows, the table is created containing no rows. Complex queries can be used to create a table. A column alias is required if Oracle cannot create a valid column name from the column in the query.

Steps

1. Connect to SQL*Plus as the WAITE user account. CHP4_21.SQL, shown in Figure 4.22, creates the sample tables used in this How-To and populates them with data.

The DEPT04 table is created by the CREATE TABLE statement and populated with one record. In the steps that follow, a new table will be created by querying the sample table. The EMP04 table is created and populated with three records. The final example in the How-To creates a new table by joining the DEPT04 and EMP04 table. Run the file to create the sample table and populate it with data.

SQL> START CHP4_21.sql

Table Dropped.

Table Created.

Table Dropped.

Table Created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

SQL> START CHP4_21.sql

Table Dropped.

Table Created.

Table Dropped.

Table Created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

2. Load CHP4_22.SQL into the SQL buffer. The file contains a CREATE TABLE statement that makes a new table as a query of the table created in Step 1.

SQL> GET CHP4_22.sql

1 CREATE TABLE NDEPT04

2* AS SELECT * FROM DEPT04

The CREATE TABLE keywords presented in line 1 are used to create a new table in the database. The table name, NDEPT04, is specified in line 1. Line 2 contains a query that specifies the columns to be created and the initial data populated in the table.

3. Execute the statement to create the new table.

SQL> /

Table Created.

The table is created, but Oracle does not tell you how many rows were inserted. In order to get the number of rows, COUNT(*) can be queried from the new table.

4. Load CHP4_23.SQL into the SQL buffer. The file contains a statement to create a new table with the same columns as the original table, but with none of the data.

SQL> GET CHP4_23.sql

1 CREATE TABLE N2DEPT04

2 AS SELECT * FROM DEPT04

3* WHERE 1 = 0

Line 1 contains the CREATE TABLE keywords used to create a new table. The query contained in lines 2 and 3 define the columns for the new table, but the WHERE clause in line 3 doesn’t create any records in the table.

5. Execute the statement to create the new table.

SQL> /

Table Created.

6. Load CHP4_24.SQL (seen in Figure 4.23) into the SQL buffer. The file contains a query to create summary data from multiple tables.

Line 1 contains the required keywords and specifies the name of the new table. The query contained in lines 2 through 3 defines the columns of the new table and creates the initial records. Lines 2 and 3 define the columns returned by the query and created in the new table. An alias is required on all calculated fields so that a valid column name can be created. The NO_EMPLOYEES alias in line 3 tells the CREATE TABLE statement what to name the column. Functions such as COUNT are reserved words and cannot be used as the column name in the new table. Lines 4 and 5 contain the FROM clause and specify the two sample tables created in step 1 as the source of the query. The WHERE clause in lines 6 and 7 joins the two tables used in the query. The GROUP BY clause in lines 8 and 9 is required by the COUNT function specified in line 3.

7. Execute the statement to create the new table.

SQL> /

Table Created.

8. Describe the new table using the SQL*Plus DESCRIBE statement. The columns created in the new table will have the datatypes of the source table. The datatype of calculated fields will have a datatype based on the operator used in the SELECT statement. The description of the new table is shown in Figure 4.24.

9. Load CHP4_25.SQL into the SQL buffer. The file contains a statement to create a table with custom storage parameters and tablespace information.

SQL> GET CHP4_25.sql

1 CREATE TABLE N4DEPT0

2 STORAGE (

3 INITIAL 1M

4 NEXT 1M)

6 AS SELECT * FROM DEPT04

Line 1 defines the new table created by the statement with the CREATE TABLE keywords. Lines 2 through 5 contain custom storage parameters that define how the table will be created in the database. The INITIAL clause specifies how much storage is allocated when the table is first created. The NEXT clause specifies how much storage will be allocated when the first allocated storage is used. Line 5 contains the query used to create the table.

10. Run the statement to create the table. The table is created with the storage parameters defined by the STORAGE clause.

SQL> /

Table created.

How It Works

Step 1 creates the sample tables used by this How-To. Steps 2 and 3 create a new table with all of the columns and data from the DEPT04 table created in Step 1. The CREATE TABLE statement includes an AS SELECT clause to create the new table from the results of the query. Steps 4 and 5 create a new table as a query without moving any data into the table. The WHERE 1 = 0 clause in the SELECT statement ensures that no rows will be returned by the query, but still creates the table. Steps 6 and 7 create a new table as a complex query from multiple tables. An alias is required for all calculated fields to give the column a name in the new table. Step 8 describes the table created in the previous two steps using the DESCRIBE command. The data type of the calculated column is the result type of the expression. Steps 9 and 10 create a new table as a query containing a STORAGE clause. The STORAGE clause is usually at the end of the CREATE TABLE statement, unless the table is created from a query.

Comments

Creating a table as a query from one or more additional tables or views is a very useful technique that can be used to create summary tables that can dramatically improve the performance of summary data queries. The Parallel Query Option in Oracle8 dramatically improves the performance of CREATE TABLE statements with SELECT clauses on multiprocessor computers.