Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Teradata to Oracle Conversion (Indexes)

660761
Member Posts: 6
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)
)
;
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)
)
;
Answers
-
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. -
user5132435 wrote: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).
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?
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/ -
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. -
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 -
user5132435 wrote:Conventional indexes that are not used to enforce a unique or primary key constraint can only be created using a separate CREATE INDEX command.
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!
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/ -
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
This discussion has been closed.