Index or statisitcs - which comes first? 1

[rmkg]

Hello.
In a general attempt to improve peformance, which is the best approach?
i) Analysing load scripts to see which columns are hit most, indexing the top X many and collecting stats.
ii) Analysing report scripts to see which columns are hit most, indexing the top X many and collecting stats.
iii) Putting indexes on likely columns & collecting stats
iv) Collecting stats on likely columns and using the info to decide what to index. (Subsidiary question - what does the info tell you?)
v) None of the above, but something you whizzes out there know is REALLY the right approach.
Thanks in advance,
Richard.

 

[dnoeth]

If you're running V2R5 try the Index/Statistics Wizards on a workload (e.g. your reports), they're doin a nice job ;-)

If not, some rules of thumb:
Statistics are vital, so collect statistics on
- every non-unique index = NUSI/NUPI (unless it's a covered NUSI, then maybe it's not necessary)
- UPIs on small tables (if you can afford it and use non-equality/BETWEEN access, then on large tables, too)
- columns that are heavily used in joins and/or where conditions

In Teradata you don't need as much indexes as in other RDBMSes. (Almost) only Primary Indexes are used for joins.
So you just create secondary indexes on columns heavily used in Where conditions returning only a small subset of the rows, typically < 1%.
But remember, SIs slow down the load...

Dieter

 

[rmkg]

Thanks, Dieter, very helpful. I'll have a think and probably get back to you!

Richard.

 

[dwtech]

dnoeth:
In your reply you mention ...
&quot;If you're running V2R5 try the Index/Statistics Wizards ...&quot;

How do we find which version is runnig?
Thanks in advance

 

[tdatgod]

sel * from dbc.dbcinfo;


Also I should point out that the

Teradata Index Wizard
Teradata Statistics Wizard

are separately orderable product in the NCR catalog under the heading

Teradata Analyst Pak

they are bundled Along with the

Teradata System Emulation tool
Visual Explain and Compare.

and therefore even if you running v2r5 you may not have access to those tools unless your site has purchased them.

However, You can get a DEMO copy of The Teradata Client utilities including those 4 stated above on the Teradata v2r5.0 Demo CD that you can order from

http://www.teradata.com/demo

It also comes with a full set of documentation. These are the real client utilities with only 1 restriction

they should not be used in prodction environment.