Forum Stats

  • 3,836,775 Users
  • 2,262,185 Discussions
  • 7,900,105 Comments

Discussions

Categories

Teradata to Oracle Conversion (Indexes)

660761
660761 Member Posts: 6
edited Sep 29, 2008 11:12PM in SQL & PL/SQL
Hello all:

I'm writing a script to convert Teradata DDL to Oracle. I'd like to be able to take a UNIQUE PRIMARY INDEX in Teradata and a PRIMARY INDEX in Teradata and convert them to Oracle indexes.

So right now if I have a UNIQUE PRIMARY INDEX I am creating a Primary Key in Oracle. But what do I do if I have a PRIMARY INDEX which can have nulls and duplicates?

How would I create the DDL for this index or would I need a separate CREATE INDEX statement?

Thanks for the help!


Teradata:
CREATE SET TABLE TesterChar3
(
colCharUni1 CHAR(32000) CHARACTER SET UNICODE NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( colCharUni1 );

Oracle:
CREATE TABLE TESTERCHAR3
(
COLCHARUNI1 NCLOB,
PRIMARY KEY (COLCHARUNI1)
)
;
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 486393
    486393 Member Posts: 487
    A primary key in Oracle has to be on not null-columns. A unique key in Oracle can have null values.

    However you can't create a unique or primary key on a nclob-column. You can use a varchar2(4000)-column, 4000 is the max length of a varchar2-column.
    486393
    · Share on TwitterShare on Facebook
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    edited Sep 23, 2008 3:40PM
    user5132435 wrote:
    Hello all:

    So right now if I have a UNIQUE PRIMARY INDEX I am creating a Primary Key in Oracle. But what do I do if I have a PRIMARY INDEX which can have nulls and duplicates?
    If you are certain that the PRIMARY INDEX in Teradata can have duplicates then you can't use a UNIQUE index/constraint in Oracle because it's supposed to be unique as the name suggests (although it can have an arbitrary number of nulls in contrast to the PRIMARY KEY which is NOT NULL by definition).

    In that case you would end up with an conventional index in Oracle, which can have duplicates (and normally doesn't index NULL values).

    Note that you can define the UNIQUE CONSTRAINT to be deferred in Oracle which means that it temporarily can hold duplicates (until you COMMIT or issue SET CONSTRAINTS) but I doubt that this is what you're looking for.

    In addition you need to be aware of index key length limits on Oracle, which are based on the block size of the tablespace where the index is stored. Only if you use at least a blocksize of 8k you're able to create an index on a VARCHAR2(4000) column, otherwise it will fail with an error. The maximum key length in an 8k tablespace in Oracle 10g is approx. 6k I think (e.g. in case of a composite index created on multiple columns).

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
    Randolf Geist
    · Share on TwitterShare on Facebook
  • 660761
    660761 Member Posts: 6
    Hello Randolf:

    I appreciate your input.

    A PRIMARY INDEX in Teradata can have duplicates and nulls so it looks like I would be looking at a conventional index.

    Can this be created when you create the table DDL or is the correct way to do it to use a separate CREATE INDEX statement?

    CREATE INDEX <index> on <table> (column 1, ..., column n);

    I couldn't really find in the documentation how to create an index in the DDL although it could be there. The CREATE TABLE statement syntax was about 15 pages so I might have missed it! ;)

    Thansk for your help.
    · Share on TwitterShare on Facebook
  • 660761
    660761 Member Posts: 6
    Thanks for your help. I found this in the documentation also so I'll need to check the data types I'm converting to before creating any kind of primary key.

    None of the columns in the primary key can be LOB, LONG, LONG RAW, VARRAY,
    NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type.
    However, the primary key can contain a column of TIMESTAMP WITH LOCAL
    TIME ZONE
    · Share on TwitterShare on Facebook
  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    user5132435 wrote:
    Can this be created when you create the table DDL or is the correct way to do it to use a separate CREATE INDEX statement?

    CREATE INDEX <index> on <table> (column 1, ..., column n);

    I couldn't really find in the documentation how to create an index in the DDL although it could be there. The CREATE TABLE statement syntax was about 15 pages so I might have missed it! ;)
    Conventional indexes that are not used to enforce a unique or primary key constraint can only be created using a separate CREATE INDEX command.

    Indexes that are used to enforce a unique/primary key constraint can and will be part of the CREATE TABLE command, and starting with Oracle 9i you can even use an explicit CREATE INDEX statement as part of the USING INDEX clause that is optionally part of the "constraint" definition.

    And there is of course another special case: If you create a so called index-organized table which is actually an index used as a table then of course you're implicitly creating an index because it represents the table :-)

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
    · Share on TwitterShare on Facebook
  • 660761
    660761 Member Posts: 6
    Hello Randolf:

    That's exactly the information that I was looking for! I appreciate your help. It is very clear now and for these non-unique indexes in Teradata I'll use a separate CREATE INDEX statement. Thanks for helping me out! :)

    Todd
    · Share on TwitterShare on Facebook
This discussion has been closed.