Indexes at Oracle and Teradata

Though the syntax of SQL will be same in Oracle and Teradata level, the mail difference is lied at their basic architecture.

Here I am describing the difference of Index structure between Oracle and Tearadata..

Oracle -- As we all know, at oracle Index is created as a separate object [which you can find at user_indexes] which is a B+ tree structure. Indexes are ordered by default in asc order, if you do not specify the order at time of creation of it. As this is a separate object, you can drop and re-create index on particular table, and that will not harm your table data.


--here I am creating a table and defining an index on ID column of it.
create table myTable
(id number,
name varchar2(200)
);


create unique index myIndex on myTable(id)

-- I am re-creating a table using previous table.
create table DummyTable
as
select *
from myTable


-- As index is a separate object altogether,

when you create a table, index will not get create by default and you need to create it explicitly.

Please check below query to verify it..

select a.index_name, b.table_name
from all_indexes a right join all_tables b
on a.table_name = b.table_name
where b.table_name in ('DUMMYTABLE', 'MYTABLE')

If you see here, index did not got created on DummyTable. That proves that, at oracle level index will not get created by default when you re-create a table as its a separate object and not attached with the table.





Same thing applies if you define and index as a primary key - see below

-- I have created a table with primary key defined. i.e. now index will get implicitly created because of primary key constraints.

-- Note : When you define unique or primary key constraint - a unique index will get created automatically on that table for that column.

create table myTable1
(id number primary key,
name varchar2(200)
);


create table DummyTable1
as
select *
from myTable

select a.index_name, b.table_name
from all_indexes a right join all_tables b
on a.table_name = b.table_name
where b.table_name in ('DUMMYTABLE1', 'MYTABLE1')

If you see here also, there is a index [system defined as we mentioned primary key] got created on original table and no index available on dummy table.


Also, as index is a separate object and not attached with the table, you can any time create and drop the index on any table.




Teradata -- Now same thing we will check at tearadata level. In teradata, index[Primary Index] is not created as a separate object. If you did not specify the Primary Key / Primary Index at the time of table creation,teardata will automatically create primary index on first column.

In tearadata, every table rows itself are ordered according to the index column which you specify, because of which there is no need of separate index structure.

If we check below sqls picture will get more clear.

-- we have created table and defined the primary key [primary index]. We can not create primary index separately as we do the same at oracle level.

create table myTable
(id integer not null,
name varchar(200),
primary key (id));

-- we have created dummy table using the pre-existing table on which primary key is defined,
create table DummyTable
as
(select *
from myTable)with data

-- we will check for the indexes on both the tables now

select a.indexname, a.indextype, indexNumber, columnName, b.tableName
from dbc.indicesV a right join dbc.tables b
on a.tableName = b.tableName
where b.tableName in ('DUMMYTABLE', 'MYTABLE')

If you look here, index got created on both the tables. Only thing is, on dummy table index is not unique - i.e. original table is having Unique Primary Index whereas re-created table is having non-unique primary index.

This stats that, when we create table using another table, primary index also gets created automatically - because primary index is not a separate structure at TD level and its a part of Table itself.

Note -- There are indexes like Secondary Index, Join Index [same as MVs in Oralcle] and Hash Index which are separate index structures and are not attached with the table structure.

IMP -- I am not a full fledged TD resource and learning the things on my on and written above block as per my understanding. In case you find any issues in above content please reply back so that even I can understand the things better.