Managing Database Objects

Tables and Data Definition Language (DDL)

Tables are the fundamental storage structures in Oracle, organized as rows and columns. The CREATE TABLE statement defines the table structure, including column definitions, data types, and constraints. Oracle supports a rich set of data types: VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, CLOB, BLOB, and more.

CREATE TABLE employees (
    employee_id    NUMBER(6) PRIMARY KEY,
    first_name     VARCHAR2(50) NOT NULL,
    last_name      VARCHAR2(50) NOT NULL,
    email          VARCHAR2(100) UNIQUE,
    hire_date      DATE DEFAULT SYSDATE,
    salary         NUMBER(8,2),
    department_id  NUMBER(4),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Data Manipulation Language (DML) operations include INSERT, UPDATE, DELETE, and MERGE. The TRUNCATE statement removes all rows from a table without generating undo for individual rows (only extent deallocation is logged), making it faster than DELETE but irreversible.

Types of Constraints

Constraints enforce data integrity rules at the database level:

• PRIMARY KEY: Uniquely identifies each row. Automatically creates a unique index. Cannot contain NULL values.
• FOREIGN KEY: Enforces referential integrity between tables. The child column values must match parent column values or be NULL.
• UNIQUE: Ensures all values in a column or set of columns are distinct. Allows NULL values (unlike PRIMARY KEY).
• NOT NULL: Prevents NULL values in a column.
• CHECK: Validates that column values satisfy a Boolean expression. Example: CONSTRAINT chk_salary CHECK (salary > 0).

Constraints can be defined at the column level (inline) or at the table level (out-of-line). They can be added, dropped, enabled, or disabled dynamically:

ALTER TABLE employees ADD CONSTRAINT emp_email_uk UNIQUE (email);
ALTER TABLE employees DISABLE CONSTRAINT emp_email_uk;
ALTER TABLE employees ENABLE CONSTRAINT emp_email_uk;
ALTER TABLE employees DROP CONSTRAINT emp_email_uk;

Sequences

A sequence is a database object that generates unique integers, typically used for primary key values. Sequences are independent of tables and can be shared across multiple tables:

CREATE SEQUENCE emp_seq
  START WITH 1000
  INCREMENT BY 1
  MAXVALUE 999999
  NOCACHE
  NOCYCLE;

-- Use in INSERT
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

-- Retrieve current value
SELECT emp_seq.CURRVAL FROM DUAL;

The CACHE option pre-allocates sequence numbers in memory for performance, but may leave gaps if the instance crashes. NOCYCLE prevents the sequence from wrapping around to the minimum value when the maximum is reached.

Temporary and External Tables

Global Temporary Tables (GTTs) hold session-private data that is automatically deleted at the end of a session or transaction. The table definition is permanent, but the data is temporary:

CREATE GLOBAL TEMPORARY TABLE temp_results (
    id NUMBER,
    result_data VARCHAR2(200)
) ON COMMIT DELETE ROWS;  -- or ON COMMIT PRESERVE ROWS

External Tables allow Oracle to query data stored in external flat files as if they were regular database tables. They are read-only and defined using the ORGANIZATION EXTERNAL clause with CREATE TABLE ... ORGANIZATION EXTERNAL:

CREATE TABLE ext_employees (
    emp_id NUMBER, emp_name VARCHAR2(100)
) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_data_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
    )
    LOCATION ('employees.csv')
);

Indexes

An index is a schema object that provides fast access paths to table rows. Oracle supports several index types:

• B-tree Index: The default index type, organized as a balanced tree structure. Suitable for high-cardinality columns and equality/range queries.
• Bitmap Index: Stores bitmaps for each key value. Best for low-cardinality columns in data warehouse environments. Not suitable for OLTP due to locking behavior.
• Function-Based Index: Indexes the result of a function or expression applied to column values. Example: CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name)).
• Reverse Key Index: Reverses the bytes of the index key to distribute insertions across index blocks, preventing "hot block" contention in RAC environments.

-- Create a B-tree index
CREATE INDEX emp_name_idx ON employees(last_name, first_name);

-- Create a unique index (enforces uniqueness)
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

-- Create a function-based index
CREATE INDEX emp_upper_idx ON employees(UPPER(last_name));

-- Drop an index
DROP INDEX emp_name_idx;

Guidelines for Creating Indexes: Index columns used in WHERE clauses and join conditions. Index foreign key columns to improve join performance. Avoid indexing small tables (full table scans are faster). Avoid excessive indexing on tables with heavy DML activity (INSERT, UPDATE, DELETE) because each index must be maintained. Monitor index usage through V$OBJECT_USAGE and drop unused indexes. Rebuild indexes periodically to reclaim space and improve clustering: ALTER INDEX emp_name_idx REBUILD.

Partitioning Strategies

Partitioning decomposes large tables and indexes into smaller, more manageable pieces called partitions, while still presenting a single logical table to applications. Each partition can be managed independently — for backup, archiving, or placement on different storage tiers.

Range Partitioning divides data based on ranges of a partitioning key, typically a date column. Suitable for historical data where data is naturally divided by time periods:

CREATE TABLE sales (
    sale_id   NUMBER,
    sale_date DATE,
    amount    NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_q1_2024 VALUES LESS THAN (TO_DATE('01-APR-2024','DD-MON-YYYY')),
    PARTITION p_q2_2024 VALUES LESS THAN (TO_DATE('01-JUL-2024','DD-MON-YYYY')),
    PARTITION p_q3_2024 VALUES LESS THAN (TO_DATE('01-OCT-2024','DD-MON-YYYY')),
    PARTITION p_q4_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY')),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

List Partitioning maps rows to partitions based on discrete values of a column:

CREATE TABLE employees (
    emp_id NUMBER, emp_name VARCHAR2(100), region VARCHAR2(20)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES ('NORTH', 'NORTHEAST'),
    PARTITION p_south VALUES ('SOUTH', 'SOUTHEAST'),
    PARTITION p_west  VALUES ('WEST', 'NORTHWEST'),
    PARTITION p_east  VALUES ('EAST')
);

Hash Partitioning distributes rows evenly across partitions using a hash function on the partition key. Useful when there is no natural partitioning boundary and even distribution is desired:

CREATE TABLE orders (
    order_id NUMBER, customer_id NUMBER, order_date DATE
)
PARTITION BY HASH (customer_id) PARTITIONS 4;

Composite Partitioning combines two partitioning methods. The most common is Range-Hash or Range-List, where data is first partitioned by range and then sub-partitioned by hash or list:

CREATE TABLE sales (
    sale_id NUMBER, sale_date DATE, region VARCHAR2(20), amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
    SUBPARTITION sp_north VALUES ('NORTH'),
    SUBPARTITION sp_south VALUES ('SOUTH'),
    SUBPARTITION sp_west  VALUES ('WEST'),
    SUBPARTITION sp_east  VALUES ('EAST')
) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024','DD-MON-YYYY')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025','DD-MON-YYYY'))
);

Partitioning provides significant performance benefits through partition pruning — the optimizer eliminates partitions that cannot contain relevant data, reducing the amount of data scanned. It also enhances manageability: individual partitions can be truncated, dropped, or moved to different tablespaces without affecting the entire table.

Views

A view is a stored query that presents a logical subset or transformation of data from one or more tables. Views do not store data physically; they are virtual tables whose content is dynamically derived from the underlying base tables:

CREATE VIEW emp_dept_view 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;

-- Simple views are updatable (INSERT, UPDATE, DELETE modify base tables)
-- Complex views (with joins, aggregates, GROUP BY) may require INSTEAD OF triggers

Views provide security by restricting access to sensitive columns, simplify complex queries into reusable components, and provide logical data independence (insulating applications from schema changes).

Materialized Views

A materialized view is a database object that stores the results of a query physically, unlike a regular view. It is a snapshot of the query result at a point in time and must be refreshed to reflect changes in the base tables:

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT region, SUM(amount) AS total_sales, COUNT(*) AS order_count
FROM sales
GROUP BY region;

Key differences from views:

• Materialized views store data physically, consuming disk space but providing pre-computed results.
• Views are always up-to-date (data is computed on-the-fly); materialized views must be refreshed.
• Materialized views are ideal for data warehousing, summary tables, and replication scenarios where query performance is critical and some staleness is acceptable.
• Refresh methods include COMPLETE (recompute entire result), FAST (apply incremental changes using materialized view logs), and FORCE (try FAST, fall back to COMPLETE).

Synonyms

A synonym is an alias for a schema object (table, view, sequence, procedure, etc.). Synonyms provide location transparency and simplify access to objects in other schemas:

-- Private synonym (visible only to the owner's schema)
CREATE SYNONYM emp FOR hr.employees;

-- Public synonym (visible to all users)
CREATE PUBLIC SYNONYM emp_pub FOR hr.employees;

-- Drop a synonym
DROP SYNONYM emp;
DROP PUBLIC SYNONYM emp_pub;

Private synonyms are schema-specific; public synonyms are accessible database-wide. Synonyms are commonly used to avoid hard-coding schema names in application code, enabling easier schema migrations and multi-tenant configurations.