create a composite Collect stats command

Database
Enthusiast

create a composite Collect stats command

SELECT  databasename,tablename,indexnumber,columnName, columnPosition,

'COLLECT STATISTICS ON ' || TRIM(databasename)   ||'.'||   TRIM(tablename) ||' INDEX (' || TRIM(columnName) || ');' 

FROM dbc.indices

WHERE indextype in ('P','S')

    AND databasename='MENSA_TABLE'

   AND tablename='TLOCATION_HIST'

ORDER BY indexnumber,columnposition;

Output:

 

TLOCATION_HIST               

4

EXPY_DT                      

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EXPY_DT);

3

MENSA_TABLE                  

TLOCATION_HIST               

8

EFF_DT                        

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EFF_DT);

4

MENSA_TABLE                  

TLOCATION_HIST               

8

EXPY_DT                      

2

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EXPY_DT);

5

MENSA_TABLE                  

TLOCATION_HIST               

12

LOCATION_ID                  

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (LOCATION_ID);

             
             

Lines 3,4 need to be 

merged together

       

3

MENSA_TABLE                  

TLOCATION_HIST               

8

EFF_DT                       

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EFF_DT,EXPY_DT);

4

MENSA_TABLE                   

TLOCATION_HIST               

8

EXPY_DT                      

2

 

… does anyone know how to do this or does anyone have a script that will compose the correct Collect statistics command .. using composite index where applicable.

4 REPLIES
Enthusiast

Re: create a composite Collect stats command

Hi v_gabrielli,

The following recursive query will do the task. Enter your filter. If your tables have keywords as columns use "SYSLIB.SQLRESTRICTEDWORDS" and put " around keywords.

WITH RECURSIVE CREATE_COLLECT_STATS
(
DATABASENAME
, TABLENAME
, COLUMNPOSITION
, COLLECT_STATS
)
AS
(
SELECT
DATABASENAME
, TABLENAME
, COLUMNPOSITION
, CAST(TRIM(COLUMNNAME) AS VARCHAR(1000)) AS COLLECT_STATS
FROM
DBC.INDICES
WHERE
COLUMNPOSITION = 1
UNION ALL
SELECT
A.DATABASENAME
, A.TABLENAME
, B.COLUMNPOSITION
, A.COLLECT_STATS || ',' || TRIM(B.COLUMNNAME)
FROM
CREATE_COLLECT_STATS A
JOIN
DBC.INDICES B
ON A.DATABASENAME = B.DATABASENAME
AND A.TABLENAME = B.TABLENAME
AND A.COLUMNPOSITION + 1 = B.COLUMNPOSITION
)
SELECT
A.DATABASENAME
, A.TABLENAME
, 'COLLECT STATISTICS ON ' || UPPER(TRIM(A.DATABASENAME)) ||'.'|| UPPER(TRIM(A.TABLENAME)) ||' INDEX (' || UPPER(TRIM(A.COLLECT_STATS)) || ');' AS COLLECT_STATS_STATEMENT
FROM
CREATE_COLLECT_STATS A
INNER JOIN
(
SELECT
DATABASENAME
, TABLENAME
, MAX(COLUMNPOSITION) AS MAX_COLUMNPOSITION
FROM
CREATE_COLLECT_STATS
GROUP BY 1,2
) B
ON A.DATABASENAME = B.DATABASENAME
AND A.TABLENAME = B.TABLENAME
AND A.COLUMNPOSITION = B.MAX_COLUMNPOSITION;

Thanks & Regards,

Rohan Sawant

Enthusiast

Re: create a composite Collect stats command

Hi Rohan, thank you for the reply.. much appreciated.. however




your script produces this with extra fileds and the wrong order indexOrder is not used
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BRAND_ID,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_ID,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EXPY_DT,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_LOCATION_ID,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_KEY,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (STATUS_ID,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EFF_DT,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_PARTY_ID,EXPY_DT);
 
what I need is this... in this order
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_KEY);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EFF_DT,EXPY_DT);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_ID);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (STATUS_ID);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_LOCATION_ID);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_PARTY_ID);
COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BRAND_ID);

pls let me know Vince

Enthusiast

Re: create a composite Collect stats command

Hi,

Can you please share your table "MENSA_TABLE.TACCOUNT_HIST INDEX " DDL so that I could map your requirement with DDL. I thought you just want to know how to combine rows. 

Thanks,

Rohan Sawant

Senior Apprentice

Re: create a composite Collect stats command

Hi Vince/Rohan, 

you need to add IndexNumber to the join condition, too.

Btw, you can also utilize my StatsInfo view for creating COLLECT STATS on indexes:

pre-TD14: How to decode the binary statistics stored in dbc tables

TD14: New StatsInfo query for TD14

The new version for TD14 doesn't include missing index stats anymore, but you can borrow the code from the old one :-)