Convert Existing Database Tables | Teradata Vantage - Converting Existing Database Tables to Vantage Tables - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

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

  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 Vantage.
  3. Create databases in Vantage that correspond to the Oracle schemas and map the schemas to the corresponding databases.
  4. Identify the DDL for each schema object.
  5. Map Oracle data types to Teradata data types.
  6. Map Oracle table elements to Vantage table elements.

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

    Oracle and Vantage 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 Vantage, 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 Vantage 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.
    Vantage 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 Vantage.

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

    The DDL for creating tables differs between Oracle and Vantage 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 Vantage, 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 database table definitions do not include database storage management clauses.

    Recommendation: To facilitate loading data from your Oracle table into the new database table, define the column names of the 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 database table after loading the data into the table.

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