Convert Existing Database Tables | Teradata Vantage - Converting Existing Database Tables to Vantage Tables - Advanced SQL Engine - Teradata Database

This procedure uses Oracle as an example to show how you might convert tables created in another database to Teradata.

1. Identify each Oracle schema/user and all database objects (for example, tables, indexes, views, and materialized views) contained in each schema.

   Oracle uses schemas to group database objects into functional groupings that are created and owned by users.

   In Vantage, database objects are created and organized within the owning database or user:

   • A database is a collection of related database objects. A database also contains an allotment of space from which users can create and maintain their own objects, or other users or databases.
   • A user is similar to a database, having a space allocation and the capacity to contain objects. However, a user also has a password and can log on to the system, whereas a database cannot.
2. Identify the schemas that have objects you want to migrate to Teradata.
3. Create Vantage databases that correspond to the Oracle schemas and map the schemas to the corresponding Teradata databases.
4. Identify the DDL for each schema object.
5. Map Oracle data types to Teradata data types.
6. Map Oracle table elements to Teradata table elements.

   See Creating Tables in Teradata for a description of common Teradata table options.

   Oracle and Teradata tables have some common elements:

   • All tables have names.
   • All tables contain columns with data types.
   • Tables can be partitioned.

     In Oracle, you must create tablespaces to store table data. You can partition a table and distribute specific partitions for storage in specific tablespaces.

     In Teradata, the system uses the Primary Index (PI) to distribute table rows across units of parallel execution (AMPs). See Guidelines for Choosing Primary Index Columns.

     You can achieve further partitioning of the data rows within each AMP by defining a Partitioned Primary Index (PPI) for the table, which is similar to the type of partitioning that Oracle tables perform. However, it is easier to define PPI schemes in Teradata because the partitioning is a logical organization of data within the database file system, and it requires no knowledge of table space definitions or physical disks. See Row Partitioning.

   • Constraints can be defined on table columns.
   • Referential integrity can be defined.
   • Global temporary tables are supported.
   • The data block size can be defined to optimize the storage of row data.
   • Freespace can be defined to retain room after loading for future data growth.
   • Triggers can be defined.
   Teradata has a number of additional table options, including:

   • Primary index – used for data distribution, data access, table joins, and aggregations.
   • FALLBACK option – allows mirroring of table data for recovery scenarios.
   • You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
   • Journaling options – stores an image of each data row that has been changed to provide data protection in case of a failure.

   Also, in Oracle you must enable a table to be accessed with parallel query processes, whereas parallel query process are automatically accepted in Teradata.

7. Translate the DDL for each schema object to Teradata DDL.

   The DDL for creating tables differs between Oracle and Teradata primarily in how space utilization is defined for the tables.

   In Oracle, database objects are created and stored in tablespaces. Tables can have space utilization parameters describing how they will use space in tablespaces.

   In Teradata, database objects are created and stored in databases or users. The database or user definition also defines a space allocation. The system automatically transfers space from a database or user to owned tables as needed, so Teradata table definitions do not include database storage management clauses.

   Recommendation: To facilitate loading data from your Oracle table into the new Teradata table, define the column names of the Teradata table to be the same as the column names of your Oracle table. You may use the ALTER TABLE statement to rename any of the columns in the Teradata table after loading the data into the table.

   For details on using the CREATE TABLE statement, see Creating Tables in Teradata.