query slowing down

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

query slowing down

Hi,

 

suppose I Have a table DB.Table and structure is like

 

DB.TABLE

 

COL 1 (PRIMARY INDEX),

COL 2,

COL 3

 

and on top of this table I have created two views

 

DB.VIEW_1

COL 1,

COL 2,

COL 3

 

DB.VIEW_2

COL 2,

COL 1,

COL 3

 

 

now I am trying to create a table with the help VIEW_1 and another SAME table with VIEW_2

 suppose

 

CREATE STATEMENT -1

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

create multiset table db.table_2

( sel a.*, b.e_name

 from

 DB.VIEW_1 a,

 DB.employee b

 where a.col_3 = b.emp_id) with data

 

CREATE STATEMENT -2

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

create multiset table db.table_3

( sel a.*, b.e_name

 from

 DB.VIEW_2 a,

 DB.employee b

 where a.col_3 = b.emp_id) with data

 

 

NOW WHEN I AM RUNNING BOTH THE CREATE STATEMENT ONE BY ONE I CAN SEE THE 2ND CREATE STATEMENT IS SIGNIFICANTLY SLOW

AND AT LAST ITS GIVING "NO MORE ROOM IN THE DATABASE ERROR".

YOU CAN SEE THAT BOTH THE CREATE TABLE STATEMENT IS ALMOST SAME EXCEPT THE VIEW DEFINATION. 

 

I WANT TO KNOW WHY ITS HAPPENING? 

 

I THINK ITS HAPPENING BECAUSE OF THE DB.VIEW_2 DEFINATION AS THE PRIMARY INDEX DEFINATION IS GETTING CHANGED ,SO I CHANGED THE CREATE STATEMENT 2 AND AFTER THAT ITS RUNNING PERFECTLY

 

MODIFIED CREATE TABLE STATEMENT -2 

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

CREATE STATEMENT -2

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

create multiset table db.table_3

( sel COL_1, COL_2, COL_3, b.e_name

 from

 DB.VIEW_2 a,

 DB.employee b

 where a.col_3 = b.emp_id) with data

 

PLEASE HELP ME TO UNDERSTAND THIS SCENARIO IN DETAIL.

 

THANKS IN ADVANCE,

DEBORSHI CHAKRABORTY

1 REPLY 1
Ambassador

Re: query slowing down

Please DON'T SHOUT.

 

When you run a Create Table as Select you loose all existing index definitions (besides other things like all columns will be NULLable).

And if you don't specify a Primary Index explicitly it will default to the 1st column in your Select.