Search code examples
teradataquery-performance

In Teradata there get columns/fields used by join and where condition and respective table without parsing query

I am trying to automate some performance check on query in Teradata. So as part of that I want to check if columns used in joining condition are primary index of respective table or not and similarly for columns used in where condition are partition column in respective table or not. Is there any direct Teradata query which can directly give this without parsing whole query.

Solution

  • Yes there are two dbc objects where you can query :

    1. dbc.columnsv
    2. dbc.indicesv.

    Primary index information will be stored in the 2nd view just search with your tablename and database name.

    Partitioned information is stored in columnsv , there is a column with a flag value 'Y' for partitioned columns.

    Example : 

    SELECT DATABASENAME,TABLENAME,COLUMNNAME FROM DBC.COLUMNSV WHERE PARTITIONINGCOLUMN='Y' where tablename=<> and databasename=<>;


Select * from dbc.indicesv where tablename=<> and databasename=<>;