Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Configuration URL PhoneConfigurationURLPString dossier://?url=https%3A%2F%2Fpulse.seacda.com% 2FMicroStrategyLibrary%2Fapi%2Fconfig%2Fmobile% 2Fdefault&mode=0&append=true&name=Ecommerce Data warehouse version 16.20.07.01Number of nodes 4Instance typem4.4xlargeInfrastructure 64vCPUs and 256 GiB memory in totalWarehouse hosting AWS EC2 Connection mode native ODBC driverTeradata specific80 AMPs, 8 PEs in totalBuild Number11.2.0200.2412TemplateGateway-BnchmkInfrastructure 32 vCPUs 256 GiB of MemoryIntelligence Server hosting AWS |
The transactions from the Strategy Usher security usage are stored in the target warehouse for the Gateway Universal Benchmark test. It contains a fact table recording all transactions and 26 lookup tables.

The test scenario covers 37 end-user actions focusing on dossier execution and manipulations, which are the typical and common ones that represent the end-user experience.
We use JMeter to send web requests generated through the above 37 manipulations. We captured query execution time as well as query skew from the Teradata query logging. Then we tried several rounds of tests by applying different tuning methods to find the best combination to improve the query execution performance.
Towards the Universal Benchmark case, the most efficient tuning method is to collect proper statistics. Besides, adding a partition to the fact table and choosing proper PI may also improve the performance. We listed the three tuning strategies in the following.
We involved an experienced Teradata associate and co-work together and finally, we reduced the slowest query execution time from 788.6 seconds to 3.36 seconds. The result is very competitive.
Below is the comparison of OODB (out of the box) and optimized test results (The blue line is OOTB and the yellow line is optimized results, the X-axis represent average response time for each query):

Here we use the test results of a single thread as an example to illustrate our tuning strategies.
Each action will run 3 times to reduce unstable factors.
Firstly, we ran the test against Teradata with the default configuration. We call this round of test as OOTB (out of the box). We collected the performance counter from the Teradata side and generate the below grid. The Y-axis grades query execution time, 00-01 means execution time between 0 and 1 second. The X-axis grades query skew, 00-20% means the query skew between 0% and 20%.

The 4 slowest queries were listed in Appendix A - The 4 slowest queries
From the slowest 4 queries, we find that all queries are joining the view ACCESS_TRANSACTIONS_VIEW which using ACCESS_TRANSACTIONS table underlying, and the tran_date_est column is frequently used in join condition clauses.
So we add the partition to ACCESS_TRANSACTIONS on tran_date_est by interval '1' day.
The details to add partition on ACCESS_TRANSACTIONS was listed in Appendix B - Add partition

After adding the partition to the ACCESS_TRANSACTIONS table, 68 more queries can be executed within 1 second. But the 4 slowest queries didn’t get much improvement.
The Purpose of COLLECT STATISTICS is to gather and store demographic data for one or more columns or primary indexes, join indexes, secondary indexes and etc. The Teradata Cost-based optimizer uses the statistics plus the cost profile of the components of the platform to customize a query plan that works well on a wide range of platforms.

These 4 queries used a function that returns only 7 values for day-of-week and adjusts it with a case statement. Once the optimizer understood that transformation with statistics, it made a different query plan dropping the total of the 4 queries from 1,619 seconds to 14 seconds. Step by step introduction of three approaches to collecting statistics please refer to Appendix C - Collect statistics
From the Query 1 and Query 3, we can see that the REL_BADGE_BDHISTORY and badge tables are associated by using column badge_id, so we change the PI of the two tables to badge_id.
Change the PI of REL_BADGE_BDHISTORY from UNIQUE PRIMARY INDEX ( badge_id, bd_history_id ) to PRIMARY INDEX ( badge_id)
Change the PI of the badge table from UNIQUE PRIMARY INDEX ( badge_id, org_id ) to UNIQUE PRIMARY INDEX ( badge_id )

BEGIN QUERY LOGGING WITH xmlplan,explain,objects,sql,statsusage,usecount,stepinfo ON ct_idm_intel_1x;
SELECT databasename,
tablename,
totalperm,
maxperm,
NumAmps,
1 - CAST (totalperm AS FLOAT) / (maxperm*(HASHAMP()+1)) TblSkew
FROM (
SELECT databasename,
tablename,
SUM (currentperm) totalperm,
MAX (currentperm) maxperm,
COUNT(*) AS NumAmps
FROM dbc. tablesizev
WHERE databasename='ct_idm_intel_1x'
GROUP BY 1,
2) raw
ORDER BY 1,
2;SELECT CASE WHEN execsecs < 1 THEN '00-01' WHEN execsecs < 2 THEN '01-02' WHEN execsecs < 3 THEN '02-03' WHEN execsecs < 4 THEN '03-04' WHEN execsecs < 5 THEN '04-05' WHEN execsecs < 10 THEN '05-10' WHEN execsecs < 15 THEN '10-15' WHEN execsecs < 20 THEN '15-20' WHEN execsecs < 25 THEN '20-25' ELSE --execsecs -- display the specific execution time '>=25' END RunSecGroup, CASE WHEN skw < .2 THEN '00-20%' WHEN skw < .4 THEN '20-40%' WHEN skw < .6 THEN '40-60%' WHEN skw < .8 THEN '60-80%' WHEN skw < .9 THEN '80-90%' WHEN skw < .95 THEN '90-95%' ELSE '95-99%' END SkewGroup, MaxCPUAmpNumber, COUNT(*) #qry, SUM(AMPCPUTime), SUM(ParserCPUTime), SUM(TotalIOCount), SUM(NumResultRows), CASE WHEN execsecs < 1 THEN '' WHEN execsecs < 2 THEN '' WHEN execsecs < 3 THEN '' WHEN execsecs < 4 THEN '' WHEN execsecs < 5 THEN '' WHEN execsecs < 10 THEN '' WHEN execsecs < 15 THEN '1' WHEN execsecs < 20 THEN '' WHEN execsecs < 25 THEN '' ELSE sqltextinfo -- show queries which execution time is more than 25 END LongExecQry FROM ( SELECT starttime, firststeptime, firstresptime, AMPCPUTime, ParserCPUTime, TotalIOCount, NumResultRows, CacheFlag, DelayTime, NumOfActiveAMPs, MaxAMPCPUTime, MaxCPUAmpNumber, ((firststeptime - starttime HOUR(4)) (FLOAT)) * 3600. + ((firststeptime - ((firststeptime - starttime HOUR(4) )) - firststeptime SECOND(4) ) (FLOAT)) parsesecs, ((firstresptime - firststeptime HOUR(4)) (FLOAT)) * 3600. + ((firstresptime - ((firstresptime - firststeptime HOUR(4) )) - firststeptime SECOND(4) ) (FLOAT)) execsecs, 1.0 - ampcputime / NULLIFZERO((1.0 * numofactiveamps * maxAmpCpuTime)) skw, b.sqltextinfo FROM dbc.dbqlogtbl a LEFT JOIN DBC.DBQLSqlTbl b ON a.queryid=b.queryid WHERE --starttime BETWEEN '2020-08-13 05:50:00' AND '2020-08-13 07:16:19' and username = 'CT_IDM_INTEL_1X' AND statementtype = 'select') x GROUP BY 1, 2, 3, 9 ORDER BY 1, 2, 3;
"select a11.access_status_id access_status_id1, max(a11.access_status_desc) access_status_desc, a13.badge_type_id badge_type_id, max(a17.badge_type_desc) badge_type_desc, a12.tran_date_est tran_date, max(a14.day_id) tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END day_of_week_id, max(a18.day_of_week_desc) day_of_week_desc, max(LEFT(a18.day_of_week_desc,CAST(3 AS INTEGER))) CustCol_19, a15.hour_id hour_id, max(a16.hour_desc) hour_desc from ""lu_access_status"" a11 join ""ACCESS_TRANSACTIONS_VIEW"" a12 on (a11.access_status_id = a12.access_status_id) join ""badge"" a13 on (a12.badge_id = a13.badge_id) join ""day"" a14 on (a12.tran_date_est = a14.day_id) join ""minute"" a15 on (a12.est_minute_id = a15.minute_id) join ""hour"" a16 on (a15.hour_id = a16.hour_id) join ""badge_type"" a17 on (a13.badge_type_id = a17.badge_type_id) join ""day_of_week"" a18 on (CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END = a18.day_of_week_id) group by a11.access_status_id, a13.badge_type_id, a12.tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END, a15.hour_id"
"select a11.access_status_id access_status_id1, max(a11.access_status_desc) access_status_desc, a12.tran_date_est tran_date, max(a13.day_id) tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END day_of_week_id, max(a17.day_of_week_desc) day_of_week_desc, max(LEFT(a17.day_of_week_desc,CAST(3 AS INTEGER))) CustCol_19, a14.hour_id hour_id, max(a15.hour_desc) hour_desc, a12.badge_id badge_id_bigint, max(a16.badge_name) badge_name from ""lu_access_status"" a11 join ""ACCESS_TRANSACTIONS_VIEW"" a12 on (a11.access_status_id = a12.access_status_id) join ""day"" a13 on (a12.tran_date_est = a13.day_id) join ""minute"" a14 on (a12.est_minute_id = a14.minute_id) join ""hour"" a15 on (a14.hour_id = a15.hour_id) join ""badge"" a16 on (a12.badge_id = a16.badge_id) join ""day_of_week"" a17 on (CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END = a17.day_of_week_id) group by a11.access_status_id, a12.tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END, a14.hour_id, a12.badge_id"
"select a11.access_status_id access_status_id1, max(a11.access_status_desc) access_status_desc, a12.tran_date_est tran_date, max(a15.day_id) tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END day_of_week_id, max(a19.day_of_week_desc) day_of_week_desc, max(LEFT(a19.day_of_week_desc,CAST(3 AS INTEGER))) CustCol_19, a14.directorate_id jde_emp_directory_id, max(a18.jde_emp_directory_desc) jde_emp_directory_desc, a16.hour_id hour_id, max(a17.hour_desc) hour_desc from ""lu_access_status"" a11 join ""ACCESS_TRANSACTIONS_VIEW"" a12 on (a11.access_status_id = a12.access_status_id) join ""REL_BADGE_BDHISTORY"" a13 on (a12.badge_id = a13.badge_id) join ""LU_BD_HISTORY"" a14 on (a13.bd_history_id = a14.bd_history_id) join ""day"" a15 on (a12.tran_date_est = a15.day_id) join ""minute"" a16 on (a12.est_minute_id = a16.minute_id) join ""hour"" a17 on (a16.hour_id = a17.hour_id) join ""LU_JDE_EMP_DIRECTORY"" a18 on (a14.directorate_id = a18.jde_emp_directory_id) join ""day_of_week"" a19 on (CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END = a19.day_of_week_id) group by a11.access_status_id, a12.tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END, a14.directorate_id, a16.hour_id"
"select a11.access_status_id access_status_id1, max(a11.access_status_desc) access_status_desc, a13.gateway_category_id gateway_category_id, max(a17.gateway_category_desc) gateway_category_desc, a12.tran_date_est tran_date, max(a14.day_id) tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END day_of_week_id, max(a18.day_of_week_desc) day_of_week_desc, max(LEFT(a18.day_of_week_desc,CAST(3 AS INTEGER))) CustCol_19, a15.hour_id hour_id, max(a16.hour_desc) hour_desc from ""lu_access_status"" a11 join ""ACCESS_TRANSACTIONS_VIEW"" a12 on (a11.access_status_id = a12.access_status_id) join ""lu_gateway"" a13 on (a12.gateway_id = a13.gateway_id) join ""day"" a14 on (a12.tran_date_est = a14.day_id) join ""minute"" a15 on (a12.est_minute_id = a15.minute_id) join ""hour"" a16 on (a15.hour_id = a16.hour_id) join ""lu_gateway_category"" a17 on (a13.gateway_category_id = a17.gateway_category_id) join ""day_of_week"" a18 on (CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END = a18.day_of_week_id) group by a11.access_status_id, a13.gateway_category_id, a12.tran_date_est, CASE WHEN td_day_of_week(a12.tran_date_est)=1 THEN 7 ELSE td_day_of_week(a12.tran_date_est)-1 END, a15.hour_id"
CREATE TABLE CT_IDM_INTEL_1X.access_transactions_new , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( tran_id INTEGER NOT NULL, badge_id SMALLINT DEFAULT NULL, tran_lat DECIMAL(9,6) DEFAULT NULL, tran_long DECIMAL(9,6) DEFAULT NULL, tran_timestamp TIMESTAMP(6), tran_date DATE FORMAT 'YYYY-MM-DD', tran_type_id BYTEINT, access_type_id BYTEINT, success_ind VARCHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT NULL, valid_type_id SMALLINT, app_id SMALLINT, resource_id BIGINT DEFAULT NULL, gateway_id INTEGER NOT NULL, minute_id SMALLINT, session_id BIGINT DEFAULT NULL, local_time TIMESTAMP(6), local_minute_id SMALLINT, src_tran_id INTEGER DEFAULT NULL, tran_date_est DATE FORMAT 'YYYY-MM-DD', tran_timestamp_est TIMESTAMP(6) NOT NULL, est_minute_id SMALLINT, os_id SMALLINT, device_id SMALLINT, user_device_id SMALLINT, elapsed_time DECIMAL(38,20) DEFAULT NULL, insert_ts TIMESTAMP(6) NOT NULL, update_ts TIMESTAMP(6) NOT NULL, validating_badge_id SMALLINT DEFAULT NULL, action_tran_id SMALLINT DEFAULT NULL, location_id INTEGER, address_id INTEGER, facility_id INTEGER DEFAULT NULL, coalesce_score BYTEINT, access_status_id BYTEINT NOT NULL, org_id SMALLINT, ibeacon_id INTEGER, is_deleted INTEGER, space_id INTEGER, sysapplication_id INTEGER, mac_unlock_setting_id BYTEINT, mac_unlock_proximity BYTEINT, partition_id BYTEINT, ref_id BYTEINT DEFAULT NULL ) PRIMARY INDEX ( tran_id ) PARTITION BY RANGE_N(tran_date_est BETWEEN DATE '2016-10-01' AND DATE '2016-11-30' EACH INTERVAL '1' DAY ); INSERT INTO ct_idm_intel_1x.access_transactions_new SELECT * FROM ct_idm_intel_1x.access_transactions; RENAME TABLE ct_idm_intel_1x.access_transactions AS ct_idm_intel_1x.access_transactions_no_ppi; RENAME TABLE ct_idm_intel_1x.access_transactions_new AS ct_idm_intel_1x.access_transactions;
This section will describe improving performance through three approaches to collecting statistics:
Ad hoc – suitable during development of new applications or resolving top offending queries.
Production – Tool for automating statistics on thousands/millions of queries.
Benchmark – Quick collection of statistics one-time for a product evaluation.
When the Teradata optimizer processes a query, it depends on collected statistics and the “Cost” profile of the platform. The same DBMS software runs on laptops, Cloud platforms, customer provided hardware under VMWare and on Hardware provided by Teradata with, all sorts of disk speeds, CPU speeds, available memory and network connections between nodes and their storage. There may be one node (or PC) with GB of storage or hundreds of nodes with petabytes of storage.
When the Teradata optimizer has access to statistics about the data to be processed, it can adapt the query execution plan to the current platform and any platform the customer may want to move the application to in the future.
As a byproduct of its processing, the optimizer builds a list of statistics it would like to have in its planning of a query execution. That list is driven by the joins and constraints with the query. If the optimizer can determine that a constraint is highly selective against a large table, it may elect to extract those rows before joining to another table. Statistics also help the optimizer determine how many of the steps can be accomplished in parallel against different tables.
The statistics are stored in a system table which can be accessed through the DBC.STATSV view.
The list of needed statistics for a query can be accessed by using a diagnostic command before issuing an explain on the query. The command:
diagnostic helpstats on for session;
While Ad hoc collection and analysis of statistics might be ok while developing a new application or attempting to address a problem query, Teradata customers have thousands, even millions of queries accessing tables that are constantly being updated, so the Statistics Manager portlet was added into Viewpoint to automate the collection and recollection of statistics.
It relies on setting the query logging to dump information about the optimization, execution, and importantly, the list of missing statistics into XML so it can be processed periodically to identify
There is complete documentation available by searching on the web for “Teradata Stats Manager”.
What follows is a step-by-step procedure we found would work. It is long because of the screen shots.
1.During the creation of this document we found a bug that prevented proper creation of statistics if the queries were run under a session using UTF-8. The bug has been reported, but for now, use either ASCII or UTF-16 in the connection string to Teradata if you intend to determine statistics using this tool. Example:
"connection_string": "DRIVER={Teradata}; DBCName=%s; Port=%d; UseIntegratedSecurity=0; AUTHENTICATION=TD2; MaxRespSize=1048576; EnableReadAhead=Yes;SessionMode=Yes;CharacterSet=UTF16;"replace query logging with explain, objects, sql, stepinfo, xmlplan, statsusage, paraminfo, featureinfo, usecount on all;
flush query logging with all;
update tdstats.defaultstbl set defaultvalue=100 where SettingName = ‘Max_Percent_Missing’;

6. You need to grant rights to allow stored procedures in the TDSTATS database to perform work on your databases. The command is:
GRANT STATISTICS ON ___ TO TDStats;
grant statistics on ct_idm_intel_1x to tdstats;

8. You then need to fill in the job name and select what should be analyzed. In this case, we will choose a custom object list which will allow us to specify which databases we want to collect statistics on. Once we select custom object list, we can click “EDIT LIST”

9. On that list, we want to choose our database and then at the bottom of the screen (no shown) click Save.

10. We now have our database selected. We now want to use the functions tab to set additional options;

11. On the Functions tab, it is important to turn off “Require review before applying recommendations”, then edit the settings for additional options.

12. In the additional options, if PDCR is in use, you will need to change the database containing the log tables. If you will be using PDCR, make sure you grant select access for TdStats to PDCRINFO. You may also want to limit the time period to the most recent week or a specific date range by clicking Between, then filling in the dates. Then click “Advanced Options”

13. In the advanced options, you can limit the user, application, account string or query bands to be analyzed. (Note that the user and database in this example were the same… that is, the user account contained all of the tables that were used for this demonstration)

Once you have made all changes click the “OK” then “Save” at the bottom of the window/screen.
14. Once you have saved your new job, click the drop-down next to the job, and then “Run Now”

While it is running, it will indicate it in the job list and when complete, the state will change.

You can click the drop down next to the job name and this time choose to look at history. It will show a list of dates/times when the analyze has been run select the most recent one, and you will see a list of what will be collected.

15. Return to the home screen and select the “Action Button” this time to create a Collect Job. Fill in the name, select “Custom Object List” and again click the “EDIT LIST” button.

16. The database(s) you analyzed stats for should appear. Make a selection and click “Save” at the bottom of the screen.

Your selected database should now be shown.

17. You can now click the drop-down next to the job you just created, then click to “Run Now

When the job is complete, the state of the job will be noted on the Jobs screen.

To validate, issue the command “help statistics on ______
In a situation where you want to evaluate a platform where you will be testing a workload that is representative of a production workload, there is a macro below that you may install to generate the collect statistics statements from the same DBQL XML logs containing the list of statistics that the optimizer would like to have used.
1.You must enable the collection of statistics in the DBQL XML table. There may already be query logging in effect. The ones needed are highlighted:
replace query logging with explain, objects, sql, stepinfo, xmlplan, statsusage, paraminfo, featureinfo, usecount on all;
flush query logging with all;
replace macro ct_idm_intel_1x.Collect_Stats(
UserName (varchar(128), default '*'),
DBName (varchar(128), default '*'),
BeginTS (timestamp, default date),
EndTS (timestamp, default current_timestamp)) as (
-- parameter examples:
-- exec collect_stats(user, , , );
-- ... queries you executed against any DB, between midnight and now
-- exec collect_stats( , 'Acme_TPCH_DB', Current_Timestamp - interval '4' hour , );
-- ... queries anyone executed against specified database in last 4 hours
-- exec collect_stats(user, 'Acme_TPCH_DB', '2020-09-23 14:00:00', '2020-09-23 21:00:00' );
-- ... explicit user, database and time range
with ql_xml as (select * from dbc.qrylogxmldocv where queryid in
(select queryid from dbc.qrylog
where (username = :UserName or '*' = :UserName)
and starttime between :BeginTS and :EndTS )
)
select distinct 'COLLECT STATISTICS COLUMN(' ||
CASE WHEN ExpressionText is NOT NULL THEN (ExpressionText || ') AS "StatNameBegin_' ||
substring(ExpressionText from 1 for 64)) || '_StatNameEnd" ' ELSE ( cols || ') ' ) END
|| 'ON "'|| DatabaseName||'"."'||TableName ||'" ;' as CollectText
FROM (
SELECT queryid, Seqno,StepNum ,Imp, DatabaseName,TableName ,Fieldlist,ExpressionText,
TRIM(TRAILING ',' FROM (XMLAGG('"' || ColumnName || '",'
ORDER BY ColumnName
) (VARCHAR(10000)))) as cols
FROM
( Select distinct
a.queryid,
a.Seqno,
a.StepNum,
a.Imp,
a.Fieldlist,
b.DatabaseName,
b.TableName,
b.ColumnName,
a.ExpressionText
FROM (
/*multi column missing stats info */
select xmltbl.queryid, cast(1 as integer ) Seqno,a.StepNum ,a.Imp,
substring(a.FieldRef from 1 for (select position ('_' in a.FieldRef)-1)) as RelationRef,
a.Fieldlist ,a.FieldRef, a.ExpressionText
from (select queryid, xmltextdoc from ql_xml) as xmltbl,
xmltable (
xmlnamespaces (default 'http://schemas.teradata.com/queryplan'),
'//PlanStep/OptStepEst/StatsMissing/FieldList/FieldRef' passing (xmltbl.xmltextdoc)
columns
"QueryId" decimal(18,0) path '../../../../../../@QueryID',
"StepNum" varchar(50) path '../../../../@StepLev1Num',
"Imp" varchar(50) path '../../@Importance' default '',
-- "RelationRef" varchar(50) path '../@Ref' ,
"Fieldlist" varchar(50) path '../@FieldCount',
"FieldRef" varchar(50) path '@Ref',
"ExpressionText" varchar(10000)
) as a ("QueryId","StepNum" ,"Imp", "Fieldlist" ,"FieldRef","ExpressionText")
where
Fieldlist is not null
and xmltbl.queryid = a.queryid
UNION
/*single column missing stats info*/
select xmltbl.queryid,a.Seqno ,a.StepNum , a.Imp, a.RelationRef, '1' Fieldlist ,a.FieldRef, a.ExpressionText
from (select queryid, xmltextdoc from ql_xml) as xmltbl,
xmltable (
xmlnamespaces (default 'http://schemas.teradata.com/queryplan'),
'//PlanStep/OptStepEst/StatsMissing' passing (xmltbl.xmltextdoc)
columns
"QueryId" decimal(18,0) path '../../../../@QueryID',
"Seqno" FOR ORDINALITY,
"StepNum" varchar(50) path '../../@StepLev1Num',
"Imp" varchar(50) path '@Importance' ,
"RelationRef" varchar(50) path 'RelationRef/@Ref' ,
"FieldRef" varchar(50) path 'FieldRef/@Ref',
"ExpressionText" varchar(10000)
) as a ("QueryId","Seqno","StepNum" ,"Imp", "RelationRef", "FieldRef","ExpressionText")
where
FieldRef is not null
and xmltbl.queryid = a.queryid
UNION
/* missing stats on expressions */
select xmltbl.queryid,a.Seqno ,a.StepNum , a.Imp, a.RelationRef, '1' Fieldlist ,a.FieldRef, a.ExpressionText
from (select queryid, xmltextdoc from ql_xml) as xmltbl,
xmltable (
xmlnamespaces (default 'http://schemas.teradata.com/queryplan'),
'//PlanStep/OptStepEst/StatsMissing' passing (xmltbl.xmltextdoc)
columns
"QueryId" decimal(18,0) path '../../../../@QueryID',
"Seqno" FOR ORDINALITY,
"StepNum" varchar(50) path '../../@StepLev1Num',
"Imp" varchar(50) path '@Importance' ,
"RelationRef" varchar(50) path 'RelationRef/@Ref' ,
"FieldRef" varchar(50) path 'FieldRef/@Ref',
"ExpressionText" varchar(10000) path '@ExpressionText'
) as a ("QueryId","Seqno","StepNum" ,"Imp", "RelationRef", "FieldRef","ExpressionText")
where
FieldRef is null AND ExpressionText is NOT null
and xmltbl.queryid = a.queryid
) a,
(
select xmltbl.queryid, a.DatabaseName, a.RelationId, a.FieldRef, a.TableName, a.ColumnName
from (select queryid, xmltextdoc from ql_xml) as xmltbl,
xmltable (
xmlnamespaces (default 'http://schemas.teradata.com/queryplan'),
'//ObjectDefs/Database/Relation/Field' passing (xmltbl.xmltextdoc)
columns
"QueryId" decimal(18,0) path '../../../../@QueryID',
"DatabaseName" varchar(50) path '../../@DatabaseName' default '',
"RelationId" varchar(50) path '../@Id' default '',
"FieldRef" varchar(50) path '@Id' default '',
"TableName" varchar(50) path '../@TableName' default '',
"ColumnName" varchar(50) path '@FieldName' default ''
) as a ("QueryId","DatabaseName", "RelationId", "FieldRef", "TableName", "ColumnName")
where xmltbl.queryid = a.queryid
) b
where a.queryid = b. queryid
and (a.FieldRef = b.FieldRef OR (a.ExpressionText is NOT null))
and a.RelationRef = b.RelationId
and (b.DatabaseName = :DBName or '*' = :DBName)
)X
group by 1,2,3,4,5,6,7,8
)Y
;
);Change PI of REL_BADGE_BDHISTORY table:
RENAME TABLE ct_idm_intel_1x.REL_BADGE_BDHISTORY AS ct_idm_intel_1x.REL_BADGE_BDHISTORY_bak;
CREATE MULTISET TABLE ct_idm_intel_1x.REL_BADGE_BDHISTORY ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
badge_id BIGINT NOT NULL,
bd_history_id BIGINT NOT NULL)
PRIMARY INDEX ( badge_id );
INSERT INTO ct_idm_intel_1x.REL_BADGE_BDHISTORY
SELECT *
FROM ct_idm_intel_1x.REL_BADGE_BDHISTORY_bak;
COLLECT STATISTICS COLUMN("badge_id") ON "ct_idm_intel_1x"."REL_BADGE_BDHISTORY";
COLLECT STATISTICS COLUMN("badge_id","bd_history_id") ON "ct_idm_intel_1x"."REL_BADGE_BDHISTORY";
COLLECT STATISTICS COLUMN("bd_history_id") ON "ct_idm_intel_1x"."REL_BADGE_BDHISTORY";
RENAME TABLE ct_idm_intel_1x.badge AS ct_idm_intel_1x.badge_bak;
CREATE MULTISET TABLE ct_idm_intel_1x.badge ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
badge_id BIGINT NOT NULL,
status INTEGER NOT NULL,
badge_name VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
org_id BIGINT NOT NULL,
badge_type_id BIGINT DEFAULT NULL ,
user_id BIGINT DEFAULT NULL ,
connector_id INTEGER,
base_lat DECIMAL(38,20) DEFAULT NULL ,
base_long DECIMAL(38,20) DEFAULT NULL ,
badge_tenure FLOAT DEFAULT NULL ,
badge_email VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
badge_phone VARCHAR(75) CHARACTER SET LATIN CASESPECIFIC DEFAULT NULL ,
base_address VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
badge_email2 VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
badge_title VARCHAR(255) CHARACTER SET LATIN CASESPECIFIC,
employee_id INTEGER DEFAULT NULL ,
src_badge_id BIGINT DEFAULT NULL ,
source_id SMALLINT,
badge_creation TIMESTAMP(6),
badge_update TIMESTAMP(6),
badge_picture VARCHAR(1024) CHARACTER SET LATIN CASESPECIFIC DEFAULT NULL ,
badge_role_category_id INTEGER DEFAULT -1 ,
badge_source_id INTEGER DEFAULT -1 ,
badge_inactive_ts TIMESTAMP(6),
latitude DECIMAL(38,20) DEFAULT NULL ,
longitude DECIMAL(38,20) DEFAULT NULL ,
last_location_timestamp TIMESTAMP(6),
last_action_timestamp TIMESTAMP(6))
UNIQUE PRIMARY INDEX ( badge_id );
INSERT INTO ct_idm_intel_1x.badge
SELECT *
FROM ct_idm_intel_1x.badge_bak;
COLLECT STATISTICS COLUMN("badge_id","badge_type_id") ON "ct_idm_intel_1x"."badge";
COLLECT STATISTICS COLUMN("status") ON "ct_idm_intel_1x"."badge";
COLLECT STATISTICS COLUMN("status","badge_type_id") ON "ct_idm_intel_1x"."badge";
COLLECT STATISTICS COLUMN("badge_id") ON "ct_idm_intel_1x"."badge";
COLLECT STATISTICS COLUMN("badge_type_id") ON "ct_idm_intel_1x"."badge"