I am trying to create a table and then collect statistics on it, however I got an error message  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 (
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!
Solved! Go to Solution.
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?
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.