teradata failed collect statistics on index

Database
Enthusiast

teradata failed collect statistics on index

Hi All,

 

I am trying to create a table and then collect statistics on it, however I got an error message [3526] saying that the specified index does not exist. Could anyone point me the the solution? The second questions whether I can collect statistics on a volatile table.

 

Here is the simple code:

create multiset table dbname.table2 as (

select id,
session_dt
from dbname.table1 
where session_start_dt = cast(current_date as date) -13
group by 1,2
) with data primary index (id, session_dt);

 

COLLECT STATISTICS ON dbname.table2 INDEX (ID);


Thanks a lot!


Accepted Solutions
Junior Contributor

Re: teradata failed collect statistics on index

I don't know if <ou should use only id as PI, it depends on your needs/data demographics.

But there's only a single PI, in your case it's a two-column PI.

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: teradata failed collect statistics on index

Well, you defined the PI using  primary index (id, session_dt), of course there's no index (id)

Enthusiast

Re: teradata failed collect statistics on index

Thanks @dnoeth, so should I only use 'id' as primary index? 

If there are more than one PI, should I collect statistics on all of them?

Tags (1)
Junior Contributor

Re: teradata failed collect statistics on index

I don't know if <ou should use only id as PI, it depends on your needs/data demographics.

But there's only a single PI, in your case it's a two-column PI.

Teradata Employee

Re: teradata failed collect statistics on index

You can collect stats on COLUMN(id) in addition to, or instead of, INDEX(id, session_dt). You just can't use the INDEX keyword if the set of columns is not defined as an INDEX.

Enthusiast

Re: teradata failed collect statistics on index

Thanks a lot you guys!