Teradata Tidbits - Revision history

Admin: /* Updated preformat tags */

2007-09-12T07:14:13Z

Updated preformat tags

← Older revision		Revision as of 07:14, 12 September 2007
Line 1:		Line 1:
	== Record Count for all Tables in a Database ==		== Record Count for all Tables in a Database ==

	<~~nowiki~~>select 'select count (1), ''' \|\| trim(tablename) \|\| ''' (varchar(30)) from ' \|\| trim(databasename) \|\| '.' \|\| trim (tablename) \|\| ' union '		<pre>select 'select count (1), ''' \|\| trim(tablename) \|\| ''' (varchar(30)) from ' \|\| trim(databasename) \|\| '.' \|\| trim (tablename) \|\| ' union '
	from dbc.tables		from dbc.tables
	where databasename = 'my_database';</~~nowiki~~>		where databasename = 'my_database';</pre>


	== Recreate Collect Statistics Script (requires privs) ==		== Recreate Collect Statistics Script (requires privs) ==

Admin: /* Recreate Collect Statistics Script (requires privs) */

2007-09-12T07:13:32Z

Recreate Collect Statistics Script (requires privs)

← Older revision		Revision as of 07:13, 12 September 2007
Line 8:		Line 8:
	== Recreate Collect Statistics Script (requires privs) ==		== Recreate Collect Statistics Script (requires privs) ==

	<~~nowiki~~>SELECT 'collect statistics on ' \|\| TRIM ( databasename ) \|\| '.' \|\| TRIM ( tablename )		<pre>SELECT 'collect statistics on ' \|\| TRIM ( databasename ) \|\| '.' \|\| TRIM ( tablename )
	\|\|		\|\|
	Case		Case
Line 83:		Line 83:
	AND a.fieldstatistics IS NOT NULL		AND a.fieldstatistics IS NOT NULL
	AND databasename = 'MY_DBNAME' ) a		AND databasename = 'MY_DBNAME' ) a
	GROUP BY 1 , 2 , 3 ) b ; </~~nowiki~~>		GROUP BY 1 , 2 , 3 ) b ; </pre>

Admin: Added Collect Statistics Script

2007-09-12T07:12:32Z

Added Collect Statistics Script

← Older revision		Revision as of 07:12, 12 September 2007
Line 4:		Line 4:
	from dbc.tables		from dbc.tables
	where databasename = 'my_database';</nowiki>		where databasename = 'my_database';</nowiki>


			== Recreate Collect Statistics Script (requires privs) ==

			<nowiki>SELECT 'collect statistics on ' \|\| TRIM ( databasename ) \|\| '.' \|\| TRIM ( tablename )
			\|\|
			Case
			When indextype = 'M' Then ' column ('
			Else ' index ('
			End
			\|\| indexcols \|\| ');'
			FROM (
			SELECT databasename , tablename , indexnumber , indextype, MAXIMUM (
			CASE
			WHEN columnposition = 1 THEN TRIM ( columnname )
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 2 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 3 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 4 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 5 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 6 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 7 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) \|\| MAXIMUM (
			CASE
			WHEN columnposition = 8 THEN ',' \|\| TRIM ( columnname )
			ELSE ''
			END ) AS indexcols
			FROM (
			SELECT c.databasenamei AS databasename
			, b.tvmnamei AS tablename
			, d.indexnumber AS indexnumber
			, d.indextype AS indextype
			, d.fieldposition AS columnposition
			, a.fieldname AS columnname
			FROM dbc.tvfields a
			, dbc.tvm b
			, dbc.dbase c
			, dbc.indexes d
			WHERE a.tableid = b.tvmid
			AND b.databaseid = c.databaseid
			AND d.fieldid = a.fieldid
			AND d.tableid = b.tvmid
			AND d.databaseid = c.databaseid
			AND ( d.databaseid , d.tableid , d.indexnumber ) IN (
			SELECT databaseid , tableid , indexnumber
			FROM dbc.indexes
			WHERE fieldposition = 1
			AND indexstatistics IS NOT NULL )
			AND databasename = 'MY_DBNAME' ) a
			GROUP BY 1 , 2 , 3, 4 ) b
			UNION
			SELECT 'collect statistics on ' \|\| TRIM ( databasename ) \|\| '.' \|\| TRIM ( tablename ) \|\| ' column (' \|\| indexcols \|\| ');'

			FROM (
			SELECT databasename , tablename , TRIM ( columnname ) AS indexcols
			FROM (
			SELECT c.databasenamei AS databasename , b.tvmnamei AS tablename , a.fieldname AS columnname
			FROM dbc.tvfields a , dbc.tvm b , dbc.dbase c
			WHERE a.tableid = b.tvmid
			AND b.databaseid = c.databaseid
			AND a.fieldstatistics IS NOT NULL
			AND databasename = 'MY_DBNAME' ) a
			GROUP BY 1 , 2 , 3 ) b ; </nowiki>

Admin at 03:54, 7 August 2007

2007-08-07T03:54:46Z

New page

== Record Count for all Tables in a Database ==

<nowiki>select 'select count (1), ''' || trim(tablename) || ''' (varchar(30)) from ' || trim(databasename) || '.' || trim (tablename) || ' union '
from dbc.tables
where databasename = 'my_database';</nowiki>