spool tables Primary Indexes and confidence

Database
Kks
Enthusiast

spool tables Primary Indexes and confidence

In expalin plan , I alwys see the confidence of spool tables low
any idea how to collect stats on them
or know their primary indexes to take some benifit in query tuning

6 REPLIES
Enthusiast

Re: spool tables Primary Indexes and confidence

Confidence levels of spool files would be higher if statistics are collected
on the columns of the base tables from which the spool file is created.

Statistics can be collected on the columns shown while running with diagnostics helpstats.
This might result in better estimation of spool file size.

Spool files are designed on the fly while coming up with the Execution Plan,
So am not sure if there are ways to collect stats on them directly.

I am also curious to know if there are any ways :)

Regards,
Annal T
Enthusiast

Re: spool tables Primary Indexes and confidence

as far as I know spool does not carry any statistics.However, there is some concept called derived statistics, which Iam not sure whether applicable to spool table.Refer some documentation on this...

Confidence levels are determined based on availability of statistics on the columns used by a query.Low confidence indicates that one of the tables are fields involved in the join does not have statistics.Teradata documentation gives more information on confidence levels
Enthusiast

Re: spool tables Primary Indexes and confidence

I dont think we can collect statistics on spool files directly.

Kks
Enthusiast

Re: spool tables Primary Indexes and confidence

Thanks for all replies , yes I am interested in spool files sorry got confused with spool tables .
so is it possible to have always high confidence everywhere in explain plan

Enthusiast

Re: spool tables Primary Indexes and confidence

Spools are created internally by teradata to hold intermediate step result sets for eg:- redistributions,aggregations,sorts etc.,. As fas as,Confidence levels are concerned we do not always have high confidence steps across the full explain plan. Once the tables rows are redistributed or aggregated into a spool, we do not have high confidence steps anymore.
Kks
Enthusiast

Re: spool tables Primary Indexes and confidence

This means no sooner the spool files are created to redistribution etc. it looses the confidence , then how joins snd further processing happens ,
is there anything still can be done to improve performance