Teradata for Oracle developers

Hi Guys,

I have some good knowledge on oracle.You can check out the oracle related post on this blog.I have decided to explore teradata so i am tracking my experience here and i hope other people find this helpful.There are very few resources on net on teradata. You can download sql reference books from teradata site but they are in too much detail so difficult to read. Other book available are by some author called Coffin. But thery are very expensive. So below are my experiences hope you find it useful.

Some useful site

http://www.bikinfo.com/HTML/TD/TD_vs_Oracle.html

teradata has concept of primary index you need to specify this while creating a table which determines how data is distributed across the AMP.( across different computes (virtual or real))

this is also called skewness factor in oracle.Need to check how oracle handles skewness for partition and indexes 

http://developer.teradata.com/database/articles/what-you-need-to-know-before-creating-a-table-in-teradata

oracle syntax

create table trial1

( col1 int )

  CREATE TABLE "GOSALES"."TRIAL1" 

   ( "COL1" NUMBER(*,0)

   ) SEGMENT CREATION DEFERRED 

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  TABLESPACE "USERS" ;

segment creation deferred means oracle does not allot space to the table until it is created it saves space when you create large number of tables upon installation

A segment is a database object that has space allocated to it - for example, a table, index, materialized view, etc. A segment consists of one or more extents allocated within a tablespace.In our example tablespace is users 

Question to ponder 

Max lenght number that oracle can store in integer and maximum length number teradata can store.Look for data types such as BIG int and all

Number in oracle ----> interger

NVARCHAR2 is a data type used to store variable-length NLS (national language support) (japanese,chinese) or multibyte character set data.

---------------------------------------------------------

 CREATE TABLE KAPIL.BRANCH

  

   ( BRANCH_SEQ_KEY INT NOT NULL,

     BRANCH_CODE int NOT NULL primary key,

        ADDRESS1 VARCHAR(40) NOT NULL  ,

ADDRESS1_MB VARCHAR(40), 

ADDRESS2 VARCHAR(40), 

ADDRESS2_MB VARCHAR(40), 

CITY VARCHAR(20) NOT NULL , 

CITY_MB VARCHAR(20) NOT NULL , 

PROV_STATE VARCHAR(20), 

PROV_STATE_MB VARCHAR(20), 

POSTAL_ZONE VARCHAR(10) NOT NULL , 

COUNTRY_CODE INT NOT NULL , 

ORGANIZATION_CODE VARCHAR(10) NOT NULL , 

WAREHOUSE_BRANCH_CODE INT NOT NULL      

   

   )

   PRIMARY INDEX (BRANCH_SEQ_KEY)------primary index is not same as indexing in oracle its a way of distributing data in teradata

Alter table syntax in teradata

ALTER TABLE KAPIL.BRANCH ADD EFF_DATE DATE 

   

ALTER TABLE KAPIL.BRANCH ADD TRM_DATE DATE 

Question to ponder --- what is the difference between ASCI and unicode