Data Definition Language (DDL) Overview

Data Definition Language (DDL) statements are used to define, modify, and manage database schema objects in OmniFabric. This page provides an overview of all available DDL statements organized by category.

CREATE Statements

Create new database objects.

Statement Description
CREATE DATABASE Create a new database
CREATE TABLE Create a new table
CREATE TABLE AS SELECT Create table from SELECT query results
CREATE TABLE ... LIKE Create table with same structure as existing table
CREATE EXTERNAL TABLE Create external table for accessing external data
CREATE CLUSTER TABLE Create clustered table for improved performance
CREATE DYNAMIC TABLE Create dynamic table with automatic refresh
CREATE INDEX Create index on table columns
CREATE INDEX...USING IVFFLAT Create vector index using IVFFLAT
CREATE FULLTEXT INDEX Create full-text search index
CREATE VIEW Create virtual table based on query
CREATE SEQUENCE Create sequence for generating numbers
CREATE FUNCTION (Python) Create Python user-defined function
CREATE FUNCTION (SQL) Create SQL user-defined function
CREATE PITR Create Point-in-Time Recovery configuration
CREATE SNAPSHOT Create database snapshot
CREATE PUBLICATION Create publication for data replication
CREATE SUBSCRIPTION Create subscription for data replication
CREATE STAGE Create stage for data loading
CREATE SOURCE Create external data source

ALTER Statements

Modify existing database objects.

Statement Description
ALTER TABLE Modify table structure and properties
ALTER VIEW Modify view definition
ALTER SEQUENCE Modify sequence properties
ALTER PITR Modify Point-in-Time Recovery settings
ALTER PUBLICATION Modify publication settings
ALTER STAGE Modify stage configuration
ALTER REINDEX Rebuild indexes for better performance

DROP Statements

Remove existing database objects.

Statement Description
DROP DATABASE Remove database and all its objects
DROP TABLE Remove table and its data
DROP VIEW Remove view
DROP INDEX Remove index
DROP SEQUENCE Remove sequence
DROP FUNCTION Remove user-defined function
DROP PITR Remove Point-in-Time Recovery configuration
DROP SNAPSHOT Remove database snapshot
DROP PUBLICATION Remove publication
DROP STAGE Remove stage

Other DDL Statements

Statement Description
TRUNCATE TABLE Remove all data from table while keeping structure
RENAME TABLE Change table name
RESTORE PITR Restore database to specific point in time
RESTORE SNAPSHOT Restore database from snapshot

DDL Statement Categories

Schema Management

  • Database and table creation/modification/removal
  • Index management for performance optimization
  • View management for data abstraction

Advanced Features

  • Clustering: Improve query performance with clustered tables
  • External Tables: Access data from external sources
  • Dynamic Tables: Automatically refresh materialized views
  • Full-text Search: Enable text search capabilities
  • Vector Search: Support for AI/ML vector operations

Data Replication

  • Publications: Define what data to replicate
  • Subscriptions: Subscribe to data changes
  • Stages: Manage data loading processes

Backup & Recovery

  • PITR: Point-in-Time Recovery for precise restoration
  • Snapshots: Create consistent database backups
  • Restore: Recover data from backups

User-Defined Objects

  • Functions: Create custom Python or SQL functions
  • Sequences: Generate unique numbers automatically

Usage Tips

  1. Always backup before running DDL statements on production data
  2. Use transactions where supported to ensure atomicity
  3. Check dependencies before dropping objects
  4. Monitor performance after creating/dropping indexes
  5. Test DDL scripts in development environments first