Collecting statistics

Database
Enthusiast

Collecting statistics

Hi,

In my query, I am using set operator(minus) between two tables say A & B.

Select ...... 33 columns..... from A

minus

select ...... 33 columns..... from B

To enhance the performance of the query, is it recommended to collect statistics on all 33 columns of A & B tables since collecting statistics is resource consuming activity, i want to make sure that collecting statistics should not become dis-advantage here..

Regards,

Mahesh

4 REPLIES
Senior Apprentice

Re: Collecting statistics

Hi Mahesh,

no statistics will change the plan for this query, so you don't need any statistic at all.

When performance is bad it might be due large spool usage.

You might try MINUS ALL instead (if there are no duplicate rows in A).

Dieter

Enthusiast

Re: Collecting statistics

Thank you so much Dieter. Will implement according to your inputs.. :)

Teradata Employee

Re: Collecting statistics

I am taking a join on a index column & its taking time to execute the following query 

SELECT

A.ACCOUNT_NUM AS ACCOUNT_ID,

A.Limit_Type_Cd,

A.Acct_Limit_Amt,

A.Acct_Limit_Expiry_Dt,

A.Acct_Limit_Ref_Id,

B.Limit_Ref_ID,

C.Collateral_Item_Id,

C.Collateral_Item_Type_Cd,

C.Collateral_Item_Status_Cd,

C.Collateral_Value_Amt,

C.Collateral_Nominal_Value_Amt,

C.Collateral_Max_Value_Amt,

C.Collateral_Item_Expiry_Dt

FROM UT_TAB.ACCOUNT_CREDIT_LIMIT AS A

 

INNER JOIN UT_TAB.LIMIT_DETAILS AS B

ON

A.ACCT_LIMIT_REF_ID = B.LIMIT_REF_ID

 

INNER JOIN UT_TAB.COLLATERAL_ITEM AS C

ON

B.Limit_Linked_CollateraL = C.Collateral_Item_Categ_Cd

 

 

C.Collateral_Item_Categ_Cd is the Secondary index column is there any solution to get the result faster, explain is giving me 

 The size of Spool 3 is

          estimated with low confidence to be 173,112 rows (8,482,488

          bytes).  The estimated time for this step is 0.30 seconds. 

  5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 3 (Last Use) by way

     of a RowHash match scan.  Spool 2 and Spool 3 are joined using a

     merge join, with a join condition of ("Limit_Linked_Collateral =

     Collateral_Item_Categ_Cd").  The result goes into Spool 1

     (group_amps), which is built locally on the AMPs.  The result

     spool file will not be cached in memory.  The size of Spool 1 is

     estimated with low confidence to be 341,250,965 rows (

     47,433,884,135 bytes).  The estimated time for this step is 5

     minutes and 28 seconds. 

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 1 are sent back to the user as the result of

     statement 1.  The total estimated time is 5 minutes and 29 seconds. 

Senior Apprentice

Re: Collecting statistics

Are the estimated numbers close to the actual ones?

How many nodes does your system have?

What statistics exist? -> HELP STATS tablename

What are the PIs of your tables?

Dieter