Archives of the TeradataForum

Message Posted: Mon, 19 Aug 2002 @ 15:11:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I am currently working on a script to automatically drop and - after a insert or delete - automatically create the secondary indices.

Problem: Once it comes to "ordered" or "covered" indices, the information found in the dbc.indices is not sufficient.

Any ideas?

Christian

Here is how far I got:

select
    case when t3.lfdno = 1 then 'drop ' /* 'create ' */
         else ' '
    end ( Title '' )
    , case when t3.columnposition = 1 then ' index ( '
  else ' '
    end ( Title '' )
    , trim( t3.columnname ) ( Title '' )
    , case when t3.columnposition = t4.NoOfCols then ' ) '
           else ' '
      end ( Title '' )
    , case when t3.lfdno < t3.maxno then ',   '
           when t3.lfdno = t3.maxno then ' on ' || trim( t3.databasename
)
|| '.' || trim(t3.tablename ) ||';'
           else '   '
      end ( Title '' )

from
( select t1.databasename
         ,t1.tablename
         ,t1.columnname
         ,t1.indextype
         ,t1.columnposition
         ,t1.indexnumber
         ,t1.lfdno
         ,t2.maxno
from
(
select databasename
       ,tablename
       ,columnname
       ,indextype
       , columnposition
       ,indexnumber
       ,csum(1, indexnumber, columnposition) lfdno
from dbc.indices
where databasename = 'D_DB_TR_WRK'
and tablename = 'cs_test'
and IndexType = 'S'
) t1

join

( select databasename
       ,tablename
       ,count(*) maxno
from dbc.indices
where databasename = 'D_DB_TR_WRK'
and tablename in = 'cs_test'
and IndexType = 'S'
group by 1,2
) t2

on t1.databasename = t2.databasename
and t1.tablename = t2.tablename

) t3

join

(
select databasename
, tablename
, indexnumber
, max( columnposition ) NoOfCols
from dbc.indices
group by 1,2,3
) t4
on t3.databasename = t4.databasename
and t3.tablename = t4.tablename
and t3.indexnumber = t4.indexnumber

where t3.databasename = 'D_DB_TR_WRK'
and t3.tablename = 'cs_test'
and t3.IndexType = 'S'

order by t3.databasename
,t3.tablename
,t3.indexnumber
,t3.columnposition

	<Prev	Next>		<<First	<Prev	Next>	Last>>