Comparative Analysis of SELECT INTO vs CREATE TABLE AS SELECT in Oracle

Problem Background and Error Analysis

Many SQL developers transitioning from other database systems to Oracle frequently encounter a common error: when attempting to use the SELECT * INTO NEW_TABLE FROM OLD_TABLE statement to create a new table, the system returns an ORA-00905: missing keyword error. This error indicates that Oracle does not recognize SELECT INTO as valid syntax for table creation.

Correct Usage Scenarios for SELECT INTO

In Oracle Database, the SELECT INTO statement has specific usage boundaries. It is primarily used in PL/SQL programming environments for assigning query results to variables. For example:

DECLARE
  v_employee_name VARCHAR2(100);
BEGIN
  SELECT employee_name INTO v_employee_name
  FROM employees
  WHERE employee_id = 100;
END;

This usage is fundamentally different from table creation functionality in standard SQL, and developers must clearly distinguish between these two application scenarios.

Detailed Syntax of CREATE TABLE AS SELECT

In Oracle, the correct syntax for creating a new table and copying data is CREATE TABLE AS SELECT (commonly abbreviated as CTAS). The basic syntax structure is as follows:

CREATE TABLE new_table
AS
SELECT *
FROM old_table;

This statement performs the following operations: creates a new table named new_table with identical column structure to old_table, and copies all data from the original table to the new table.

Functional Limitations and Considerations

While CREATE TABLE AS SELECT provides a convenient method for table creation, developers should be aware of the following limitations:

• Does not replicate table constraints (primary keys, foreign keys, unique constraints, etc.)
• Does not replicate storage attributes (tablespace, storage parameters, etc.)
• Does not replicate any trigger definitions
• Does not replicate index structures
• Does not replicate grants and permission settings

Advanced Usage and Variants

CREATE TABLE AS SELECT supports various advanced usage patterns to meet different business requirements:

Selective Column Copying

CREATE TABLE employee_backup
AS
SELECT employee_id, first_name, last_name, salary
FROM employees;

Conditional Data Copying

CREATE TABLE high_salary_employees
AS
SELECT *
FROM employees
WHERE salary > 50000;

Multi-Table Join Copying

CREATE TABLE employee_department_info
AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

Creating Empty Table Structures

CREATE TABLE empty_employee_table
AS
SELECT *
FROM employees
WHERE 1 = 0;

Comparison with Other Database Systems

Different database systems exhibit variations in table creation syntax:

• SQL Server: Supports SELECT INTO syntax
• MySQL: Uses CREATE TABLE ... SELECT syntax
• PostgreSQL: Supports CREATE TABLE AS syntax
• Oracle: Uses CREATE TABLE AS SELECT syntax

These differences require developers to make appropriate syntax adjustments in cross-database projects.

Performance Considerations and Best Practices

When using CREATE TABLE AS SELECT, consider the following performance factors:

• For large tables, execute during business off-peak hours
• Consider using NOLOGGING option to reduce redo log generation
• Appropriately set tablespace and storage parameters
• Rebuild necessary indexes and constraints after execution
• Consider using parallel processing to improve efficiency for large table copying

Practical Application Scenarios

CREATE TABLE AS SELECT is particularly useful in the following scenarios:

• Data backup and archiving
• Test environment data preparation
• Data migration and transformation
• Report data preprocessing
• Intermediate table creation in performance optimization

Conclusion

Table creation syntax in Oracle Database differs significantly from other database systems. Developers must understand that SELECT INTO in Oracle is strictly limited to PL/SQL variable assignment, while table creation should use CREATE TABLE AS SELECT syntax. Understanding these differences and mastering correct usage methods is crucial for efficient database development in Oracle environments. Through the detailed analysis and code examples provided in this paper, developers should be able to avoid common syntax errors and fully leverage Oracle's table creation capabilities.