View parition elimination vs table performance

Database
Teradata Employee

View parition elimination vs table performance

Hi 

 

I have a view which is the union all of 4 tables (year wise data). I am picking the data of 3 months and the based tables are partitioned. When i query the view partition elimination was performed but spool goes to 3 terabytes but when i query the one table in which my 3 month data is present, the spool usage is max 900 GB. Any clue why there is so much difference?

 

regards

 

11 REPLIES 11
Highlighted
Ambassador

Re: View parition elimination vs table performance

Can your share both Explains?

What's your hardware, IntelliFlex?

Do you have DQBL data?

Teradata Employee

Re: View parition elimination vs table performance

I donot have dbql data at the moment. Asked for hardware info. let you know when get info..

 

--========SINGLE TABLE EXPLAIN===========
EXPLAIN
																		SELECT 201811 AS MONTH_ID
																						,USAGE.BASIC_CALL_TYPE_ID                                                                                          
																						,USAGE.Service_Type_Id
																						,USAGE.TOTAL_TYPE_ID                                                                                          
																						,USAGE.MSISDN                                                                                                                                                                                    
                                                                                        ,USAGE.CELL_ID                                                                                          
                                                                                        ,USAGE.LOCATION_AREA_ID                                                                                          
                                                                                          ,USAGE.Callindicator    																		
																						  ,Sum(USAGE.TOTAL_DURATION) AS TOTAL_DURATION
                                                                                          ,Sum(USAGE.TOTAL_CALLS) AS TOTAL_CALLS
																						  ,Sum(USAGE.TOTAL_GROSS) AS TOTAL_GROSS																						  
																						  ,Count(MSISDN) AS NO_OF_TRANSACTIONS
                                                                       FROM 
																	    A_O_TR_MONTHLY_N usage
																		
																		
                                                                           WHERE USAGE.MONTH_ID BETWEEN To_Number(To_Char(Add_Months(To_Date('201811', 'yyyymm'), - 2), 'yyyymm'))  AND To_Number('201811')
																					    AND USAGE.Service_Type_Id NOT IN ('51','61','21')   																		
                                                                        				AND CALLINDICATOR NOT IN ('SHD','SHP','SLF')
																						AND USAGE.BASIC_CALL_TYPE_ID IN (1,3,4,5,6,7)  																						
																						
																						GROUP BY 1,2,3,4,5,6,7,8;

  1) FIRST, we LOCK usage FOR READ ON a reserved RowHash IN ALL
     PARTITIONS TO prevent GLOBAL deadlock.
  2) NEXT, we LOCK usage FOR READ.
  3) We DO an ALL-AMPs Sum step TO AGGREGATE FROM 3 PARTITIONS OF
     usage WITH a CONDITION OF ("(usage.Month_Id >= 201809)
     AND ((usage.BASIC_CALL_TYPE_ID IN (1 ,
3 TO 7 )) AND
     ((usage.Callindicator <> 'SHP') AND
     ((usage.Callindicator <> 'SHD') AND
     ((usage.Callindicator <> 'SLF') AND ((usage.Month_Id
     <= 201811) AND ((usage.Service_Type_Id <> '21 ') AND
     ((usage.Service_Type_Id <> '51 ') AND
     (usage.Service_Type_Id <> '61 '))))))))")
     , GROUPING BY field1 ( 201811 ,usage.BASIC_CALL_TYPE_ID
     ,usage.Service_Type_Id ,usage.Total_Type_Id
     ,usage.Msisdn ,usage.Cell_Id
     ,usage.Location_Area_Id ,usage.Callindicator).
     AGGREGATE Intermediate Results are computed locally, THEN placed
     IN SPOOL 3.  The INPUT TABLE will NOT be cached IN memory, BUT it
     IS eligible FOR synchronized scanning.  The AGGREGATE SPOOL FILE
     will NOT be cached IN memory.  The SIZE OF SPOOL 3 IS estimated
     WITH NO confidence TO be 1,579,847,754 ROWS (470,794,630,692
     BYTES).  The estimated TIME FOR this step IS 3 Minutes AND 19
     Seconds.
  4) We DO an ALL-AMPs RETRIEVE step FROM SPOOL 3 (LAST USE) BY way OF
     an ALL-ROWS scan 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 NO confidence TO be
     1,579,847,754 ROWS (289,112,138,982 BYTES).  The estimated TIME
     FOR this step IS 2 Minutes AND 12 Seconds.
  5) 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 31 Seconds.
     BEGIN RECOMMENDED STATS FOR FINAL PLAN->
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID ,SERVICE_TYPE_ID
     ,
TOTAL_TYPE_ID ,MSISDN ,CELL_ID ,LOCATION_AREA_ID ,CALLINDICATOR)
     ON A_O_TR_MONTHLY_N" (High Confidence)
     -- "COLLECT STATISTICS COLUMN (TOTAL_TYPE_ID) ON
     A_O_TR_MONTHLY_N" (Medium Confidence)
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID ,MONTH_ID) ON
     A_O_TR_MONTHLY_N" (Medium Confidence)
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_N" (High Confidence)
     <- END RECOMMENDED STATS FOR FINAL PLAN
---============VIEW EXPLAIN==============
Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 69829 StartFragment: 314 EndFragment: 69797 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet
EXPLAIN
                                                                        SELECT 201811 AS MONTH_ID                                                                                        ,USAGE.BASIC_CALL_TYPE_ID                                                                                          
                                                                                        ,USAGE.Service_Type_Id                                                                                        ,USAGE.TOTAL_TYPE_ID                                                                                          
                                                                                        ,USAGE.MSISDN                                                                                                                                                                                    
                                                                                        ,USAGE.CELL_ID                                                                                          
                                                                                        ,USAGE.LOCATION_AREA_ID                                                                                          
                                                                                          ,USAGE.Callindicator                                                                          
                                                                                          ,Sum(USAGE.TOTAL_DURATION) AS TOTAL_DURATION                                                                                          ,Sum(USAGE.TOTAL_CALLS) AS TOTAL_CALLS                                                                                          ,Sum(USAGE.TOTAL_GROSS) AS TOTAL_GROSS                                                                                          
                                                                                          ,Count(MSISDN) AS NO_OF_TRANSACTIONS
                                                                       FROM 
                                                                       A_O_TR_MONTHLY usage--9 MINS TO GET COUNT-----29 minutes approx                                                                                                                              
                                                                           WHERE USAGE.MONTH_ID BETWEEN To_Number(To_Char(Add_Months(To_Date('201811', 'yyyymm'), - 2), 'yyyymm'))  AND To_Number('201811')
                                                                                        AND USAGE.Service_Type_Id NOT IN ('51','61','21')                                                                           
                                                                                        AND CALLINDICATOR NOT IN ('SHD','SHP','SLF')
                                                                                        AND USAGE.BASIC_CALL_TYPE_ID IN (1,3,4,5,6,7)                                                                                       
                                                                                        
                                                                                        GROUP BY 1,2,3,4,5,6,7,8;

  1) FIRST, we LOCK A_O_TR_MONTHLY_NEW IN VIEW
     A_O_TR_MONTHLY FOR ACCESS, we LOCK
     A_O_TR_MONTHLY_2016 IN VIEW
     A_O_TR_MONTHLY FOR ACCESS, we LOCK
     A_O_TR_MONTHLY_NEW_20180930 IN VIEW
     A_O_TR_MONTHLY FOR ACCESS, AND we LOCK
     A_O_TR_MONTHLY_NEW_20170821 IN VIEW
     A_O_TR_MONTHLY FOR ACCESS.
  2) NEXT, we DO an ALL-AMPs RETRIEVE step FROM 3 PARTITIONS OF
     A_O_TR_MONTHLY_2016 IN VIEW
     A_O_TR_MONTHLY WITH a CONDITION OF (
     "(A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.month_id >= 201809) AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.month_id <= 201811) AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.basic_call_type_id IN (3 TO 7
     ,
1 )) AND ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.service_type_id <> '61') AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.service_type_id <> '51') AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.service_type_id <> '21') AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.callindicator <> 'SHP') AND
     ((A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.callindicator <> 'SHD') AND
     (A_O_TR_MONTHLY_2016 in view
     A_O_TR_MONTHLY.callindicator <>
     'SLF'))))))))") INTO SPOOL 1 (all_amps), which IS built locally ON
     the AMPs.  The INPUT TABLE will NOT be cached IN memory, BUT it IS
     eligible FOR synchronized scanning.  The SIZE OF SPOOL 1 IS
     estimated WITH Low confidence TO be 1 ROW (474 BYTES).  The
     estimated TIME FOR this step IS 0.01 Seconds.
  3) We DO an ALL-AMPs RETRIEVE step FROM 3 PARTITIONS OF
     A_O_TR_MONTHLY_NEW_20170821 IN VIEW
     A_O_TR_MONTHLY WITH a CONDITION OF (
     "(A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Month_Id >= 201809) AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Month_Id <= 201811) AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.BASIC_CALL_TYPE_ID IN (3 TO 7
     ,
1 )) AND ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Callindicator <> 'SHP') AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Callindicator <> 'SHD') AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Callindicator <> 'SLF') AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '61 ') AND
     ((A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '51 ') AND
     (A_O_TR_MONTHLY_NEW_20170821 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '21
     '))))))))") INTO SPOOL 1 (all_amps), which IS built locally ON the
     AMPs.  The INPUT TABLE will NOT be cached IN memory, BUT it IS
     eligible FOR synchronized scanning.  The SIZE OF SPOOL 1 IS
     estimated WITH Low confidence TO be 2 ROWS (948 BYTES).  The
     estimated TIME FOR this step IS 0.01 Seconds.
  4) We DO an ALL-AMPs RETRIEVE step FROM 3 PARTITIONS OF
     A_O_TR_MONTHLY_NEW_20180930 IN VIEW
     A_O_TR_MONTHLY WITH a CONDITION OF (
     "(A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Month_Id >= 201809) AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Month_Id <= 201811) AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.BASIC_CALL_TYPE_ID IN (1 ,
3
     TO 7 )) AND ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Callindicator <> 'SHP') AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Callindicator <> 'SHD') AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Callindicator <> 'SLF') AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '61 ') AND
     ((A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '51 ') AND
     (A_O_TR_MONTHLY_NEW_20180930 in view
     A_O_TR_MONTHLY.Service_Type_Id <> '21
     '))))))))") INTO SPOOL 1 (all_amps), which IS built locally ON the
     AMPs.  The INPUT TABLE will NOT be cached IN memory, BUT it IS
     eligible FOR synchronized scanning.  The SIZE OF SPOOL 1 IS
     estimated WITH Low confidence TO be 3 ROWS (1,422 BYTES).  The
     estimated TIME FOR this step IS 0.01 Seconds.
  5) We DO an ALL-AMPs RETRIEVE step FROM 3 PARTITIONS OF
     A_O_TR_MONTHLY_NEW IN VIEW
     A_O_TR_MONTHLY WITH a CONDITION OF (
     "(A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Month_Id >= 201809) AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.BASIC_CALL_TYPE_ID IN (1 ,
3
     TO 7 )) AND ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Callindicator <> 'SHP') AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Callindicator <> 'SHD') AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Callindicator <> 'SLF') AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Month_Id <= 201811) AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Service_Type_Id <> '21 ') AND
     ((A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Service_Type_Id <> '51 ') AND
     (A_O_TR_MONTHLY_NEW in view
     A_O_TR_MONTHLY.Service_Type_Id <> '61
     '))))))))") INTO SPOOL 1 (all_amps), which IS built locally ON the
     AMPs.  The INPUT TABLE will NOT be cached IN memory, BUT it IS
     eligible FOR synchronized scanning.  The RESULT SPOOL FILE will
     NOT be cached IN memory.  The SIZE OF SPOOL 1 IS estimated WITH NO
     confidence TO be 2,106,463,674 ROWS (998,463,781,476 BYTES).  The
     estimated TIME FOR this step IS 5 Minutes AND 10 Seconds.
  6) We DO an ALL-AMPs Sum step TO AGGREGATE FROM SPOOL 1 (LAST USE) BY
     way OF an ALL-ROWS scan WITH a CONDITION OF (
     "(usage.BASIC_CALL_TYPE_ID IN (1 ,3 TO 7 )) AND
     ((usage.CALLINDICATOR NOT IN ('SHD',
'SHP','SLF')) AND
     ((usage.SERVICE_TYPE_ID NOT IN ('21','51',
'61')) AND
     ((usage.MONTH_ID >= 201809) AND (usage.MONTH_ID <= 201811 ))))")
     , GROUPING BY field1 ( 201811 ,BASIC_CALL_TYPE_ID ,SERVICE_TYPE_ID     ,TOTAL_TYPE_ID ,MSISDN ,CELL_ID ,LOCATION_AREA_ID ,CALLINDICATOR).
     AGGREGATE Intermediate Results are computed globally, THEN placed
     IN SPOOL 4.  The AGGREGATE SPOOL FILE will NOT be cached IN memory.
     The SIZE OF SPOOL 4 IS estimated WITH NO confidence TO be
     1,579,847,756 ROWS (2,771,052,964,024 BYTES).  The estimated TIME
     FOR this step IS 6 Hours AND 22 Minutes.
  7) We DO an ALL-AMPs RETRIEVE step FROM SPOOL 4 (LAST USE) BY way OF
     an ALL-ROWS scan INTO SPOOL 2 (group_amps), which IS built locally
     ON the AMPs.  The RESULT SPOOL FILE will NOT be cached IN memory.
     The SIZE OF SPOOL 2 IS estimated WITH NO confidence TO be
     1,579,847,756 ROWS (1,423,442,828,156 BYTES).  The estimated TIME
     FOR this step IS 8 Minutes AND 25 Seconds.
  8) Finally, we send OUT an END TRANSACTION step TO ALL AMPs involved
     IN processing the REQUEST.
  -> The contents OF SPOOL 2 are sent back TO the USER AS the RESULT OF
     STATEMENT 1.  The total estimated TIME IS 6 Hours AND 35 Minutes.
     BEGIN RECOMMENDED STATS FOR FINAL PLAN->
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID ,SERVICE_TYPE_ID     ,TOTAL_TYPE_ID ,MSISDN ,CELL_ID ,LOCATION_AREA_ID ,CALLINDICATOR)
     ON A_O_TR_MONTHLY_2016" (High Confidence)
     -- "COLLECT STATISTICS COLUMN (MONTH_ID ,BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_NEW" (Medium Confidence)
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_NEW" (High Confidence)
     -- "COLLECT STATISTICS COLUMN (MONTH_ID ,BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_NEW_20180930" (Medium Confidence)
     -- "COLLECT STATISTICS COLUMN (BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_NEW_20180930" (High Confidence)
     -- "COLLECT STATISTICS COLUMN (MONTH_ID ,BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_NEW_20170821" (Medium Confidence)
     -- "COLLECT STATISTICS COLUMN (MONTH_ID ,BASIC_CALL_TYPE_ID) ON
     A_O_TR_MONTHLY_2016" (Medium Confidence)
     <- END RECOMMENDED STATS FOR FINAL PLAN
Ambassador

Re: View parition elimination vs table performance

Forget the hardware, it's not due to IntelliFlex compression settings.

 

In query #2 there's no partition elimination for the 1st three tables, they're still accessed (Explain just estimates that no rows are returned).

The optimizer first spools rows and the aggregates globally while the 1st Select directly aggregates locally.

 

Can you show the Where-conditions from the view definition?

Which Teradata release do you have?

Teradata Employee

Re: View parition elimination vs table performance

DB version is 15.10

View code is:

SHOW SELECT * FROM A_O_TR_MONTHLY;

CREATE MULTISET TABLE A_O_TR_MONTHLY_N ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      Month_Id INTEGER,
      BASIC_CALL_TYPE_ID INTEGER Compress (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ),
      Onnet_Crossnet_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('0','A','C','F','O','Z'),
      Tap_In_Out_Ind CHAR(2) CHARACTER SET Latin NOT CaseSpecific Compress ('MO','MT','ST','TI'),
      Pricing_Plan_Id SMALLINT Compress (639 ,640 ,641 ),
      Service_Type_Id CHAR(10) CHARACTER SET Latin NOT CaseSpecific,
      Nat_Intl_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      Total_Type_Id SMALLINT Compress (1 ,7 ,8 ,10 ,11 ,524 ,47 ,100 ,365 ,895 ,910 ,911 ,913 ,170 ,181 ,182 ,188 ,191 ,447 ,193 ,468 ,473 ,481 ,482 ,483 ,230 ,240 ,244 ,245 ),
      SERVICE_IND VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Fav_No_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      DES_COUNTRY_ID VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific Compress ('20','62','63','90','91','92','94','880','961','962','963','965','967','971','974','977','212','234','249'),
      Msisdn VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific,
      Traffic_Type_Id INTEGER Compress (1300 ,1070 ,1330 ,2104 ,1090 ,1111 ,100 ,1172 ,1700 ,1190 ,1191 ,1706 ,1200 ,1201 ,1721 ,1215 ,2243 ,1230 ,2255 ,1234 ,1238 ,1751 ,2010 ,2011 ,2269 ,1250 ,1252 ,1253 ,1254 ,1255 ,2279 ,2280 ,1005 ,500 ,2302 ),
      Package_Id INTEGER Compress (1325 ,1341 ,1346 ,1347 ,1604 ,1352 ,1353 ,1393 ,1144 ,1665 ,1186 ,1448 ,1197 ,1453 ,1218 ,1221 ,1485 ,1490 ,1262 ,1271 ),
      ACCOUNT_TYPE VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Customer_Category_Id CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('B','C'),
      Cug_Flag CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('N','Y'),
      Cell_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific,
      Location_Area_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress ('00000','01330','01325','01326','01338','01324','02171','01331','01332','02175','03329','02176','02148','03328','02173','04026','01337','02155','02016','07027','03319','02311','07030','04061','02302','02170','04025','02174','01072','03318'),
      Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      FLAG_TOTAL_TYPE BYTEINT Compress (0 ,1 ),
      Actual_Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      Total_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Calls DECIMAL(18,4) Compress (16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_discounted_Duration DECIMAL(18,4),
      Total_discounted_Gross DECIMAL(18,4),
      Total_Discounted_Free DECIMAL(18,4),
      Total_Discounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Nodiscounted_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Overlap_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Created_Date DATE FORMAT 'YYYY-MM-DD',
      DA300_COST DECIMAL(18,2) Compress 0.00 ,
      Callindicator VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress ('3GC','PPS','2GC','POS','SLF','4GC','3GF'),
      CHARGED_VOLUME BIGINT,
      ZERO_RATED_VOLUME BIGINT,
      FREE_VOLUME BIGINT,
      CHARGED_NUMBER_OF_CALLS INTEGER,
      ZERO_RATED_NUMBER_OF_CALLS INTEGER,
      FREE_NUMBER_OF_CALLS INTEGER,
      HYBRID_NUMBER_OF_CALLS INTEGER,
      ACCT_NUM VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific DEFAULT NULL  Compress ('UGW','','SDP','5444','5666','5600','EXT','5400','140','5699','5411','5655','5466','5422','5688','5677','5433','5455','5477','5633','5611','5644','AIR','5622','5499','5555','5404','5656','5440','5405','5414','5445','5488','5665','5660','5454','5448','2008','5410','5447','5446','5441','5443','5415','5406','5442','5696','5667','5434','5668','5606','5669','5662','5664','5407','5650','5661','5409','5678','5480','5408','5402','5690','5464','5663','5412','5695','5680','5430','5651','5449','5421','5403','2069','5420','5670','5450','5605','5490','5416','5413','5698','5691','5686','5616','5640','5694','5456','5630','5697','5692','5423','5646','5693','5432','5636','5620','5602','5610','5676','5424','5609','5607','5417','5470','150','5612','5608','5000','5056','5451','5626','5419','5453','5641','5435','5460','5418','5058','5425','5484','5654','5469','5685','5553','5615','5659','5431','5055','5671','5658','5657','5053','5426','5437','5452','5645','5556','5494','5481','5436','5681','5059','5457','5679','5631','5684','5632','5689','5613','5672','5675','5474','5428','5635','5639','5653','5687','5623','5604','5438','5427','5614','5652','5617','5634','5458','5618','5673','5483','5459','5629','5468','5619','5429','5683','5682','5621','5559','5642','5482','5465','5648','5674','5649','5492','5485','5462','5439','5647','5461','5637','5487','5558','5551','5628','5625','5049','5467','5552','5999','5624','5638','5478','5643','5603','5498','5051','5486','5493','5627','119','5463','5557','5033','5471','5052','5043','5479','5489','5046','5048','5544','5475','5472','5496','5495','5044','5054','5497','5554','5550','5566','5045','5057','5039','5533','5473','5038','5476','5900','5066','5036','5001','5005','5333','5042','5577','5041'),
      PPC_ACCT_NO VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific)
PRIMARY INDEX ( Msisdn )
PARTITION BY Range_N(Month_Id  BETWEEN 200001  AND 210012  EACH 1 ,
 NO RANGE OR UNKNOWN)
INDEX ( Package_Id );

CREATE MULTISET TABLE A_O_TR_MONTHLY_N_20180930 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      Month_Id INTEGER,
      BASIC_CALL_TYPE_ID INTEGER Compress (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ),
      Onnet_Crossnet_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('0','A','C','F','O','Z'),
      Tap_In_Out_Ind CHAR(2) CHARACTER SET Latin NOT CaseSpecific Compress ('MO','MT','ST','TI'),
      Pricing_Plan_Id SMALLINT Compress (639 ,640 ,641 ),
      Service_Type_Id CHAR(10) CHARACTER SET Latin NOT CaseSpecific,
      Nat_Intl_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      Total_Type_Id SMALLINT Compress (1 ,7 ,8 ,10 ,11 ,524 ,47 ,100 ,365 ,895 ,910 ,911 ,913 ,170 ,181 ,182 ,188 ,191 ,447 ,193 ,468 ,473 ,481 ,482 ,483 ,230 ,240 ,244 ,245 ),
      SERVICE_IND VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Fav_No_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      DES_COUNTRY_ID VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific Compress ('20','62','63','90','91','92','94','880','961','962','963','965','967','971','974','977','212','234','249'),
      Msisdn VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific,
      Traffic_Type_Id INTEGER Compress (1300 ,1070 ,1330 ,2104 ,1090 ,1111 ,100 ,1172 ,1700 ,1190 ,1191 ,1706 ,1200 ,1201 ,1721 ,1215 ,2243 ,1230 ,2255 ,1234 ,1238 ,1751 ,2010 ,2011 ,2269 ,1250 ,1252 ,1253 ,1254 ,1255 ,2279 ,2280 ,1005 ,500 ,2302 ),
      Package_Id INTEGER Compress (1325 ,1341 ,1346 ,1347 ,1604 ,1352 ,1353 ,1393 ,1144 ,1665 ,1186 ,1448 ,1197 ,1453 ,1218 ,1221 ,1485 ,1490 ,1262 ,1271 ),
      ACCOUNT_TYPE VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Customer_Category_Id CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('B','C'),
      Cug_Flag CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('N','Y'),
      Cell_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific,
      Location_Area_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress ('00000','01330','01325','01326','01338','01324','02171','01331','01332','02175','03329','02176','02148','03328','02173','04026','01337','02155','02016','07027','03319','02311','07030','04061','02302','02170','04025','02174','01072','03318'),
      Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      FLAG_TOTAL_TYPE BYTEINT Compress (0 ,1 ),
      Actual_Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      Total_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Calls DECIMAL(18,4) Compress (16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_discounted_Duration DECIMAL(18,4),
      Total_discounted_Gross DECIMAL(18,4),
      Total_Discounted_Free DECIMAL(18,4),
      Total_Discounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Nodiscounted_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Overlap_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Created_Date DATE FORMAT 'YYYY-MM-DD',
      DA300_COST DECIMAL(18,2) Compress 0.00 ,
      Callindicator VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress ('3GC','PPS','2GC','POS','SLF','4GC','3GF'),
      CHARGED_VOLUME BIGINT,
      ZERO_RATED_VOLUME BIGINT,
      FREE_VOLUME BIGINT,
      CHARGED_NUMBER_OF_CALLS INTEGER,
      ZERO_RATED_NUMBER_OF_CALLS INTEGER,
      FREE_NUMBER_OF_CALLS INTEGER,
      HYBRID_NUMBER_OF_CALLS INTEGER,
      ACCT_NUM VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific DEFAULT NULL  Compress ('UGW','','SDP','5444','5666','5600','EXT','5400','140','5699','5411','5655','5466','5422','5688','5677','5433','5455','5477','5633','5611','5644','AIR','5622','5499','5555','5404','5656','5440','5405','5414','5445','5488','5665','5660','5454','5448','2008','5410','5447','5446','5441','5443','5415','5406','5442','5696','5667','5434','5668','5606','5669','5662','5664','5407','5650','5661','5409','5678','5480','5408','5402','5690','5464','5663','5412','5695','5680','5430','5651','5449','5421','5403','2069','5420','5670','5450','5605','5490','5416','5413','5698','5691','5686','5616','5640','5694','5456','5630','5697','5692','5423','5646','5693','5432','5636','5620','5602','5610','5676','5424','5609','5607','5417','5470','150','5612','5608','5000','5056','5451','5626','5419','5453','5641','5435','5460','5418','5058','5425','5484','5654','5469','5685','5553','5615','5659','5431','5055','5671','5658','5657','5053','5426','5437','5452','5645','5556','5494','5481','5436','5681','5059','5457','5679','5631','5684','5632','5689','5613','5672','5675','5474','5428','5635','5639','5653','5687','5623','5604','5438','5427','5614','5652','5617','5634','5458','5618','5673','5483','5459','5629','5468','5619','5429','5683','5682','5621','5559','5642','5482','5465','5648','5674','5649','5492','5485','5462','5439','5647','5461','5637','5487','5558','5551','5628','5625','5049','5467','5552','5999','5624','5638','5478','5643','5603','5498','5051','5486','5493','5627','119','5463','5557','5033','5471','5052','5043','5479','5489','5046','5048','5544','5475','5472','5496','5495','5044','5054','5497','5554','5550','5566','5045','5057','5039','5533','5473','5038','5476','5900','5066','5036','5001','5005','5333','5042','5577','5041'),
      PPC_ACCT_NO VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific)
PRIMARY INDEX ( Month_Id ,Msisdn )
PARTITION BY Range_N(Month_Id  BETWEEN 200001  AND 210012  EACH 1 ,
 NO RANGE OR UNKNOWN)
INDEX OUTG_AGG_MR_MSISDN ( Msisdn )
INDEX ( Package_Id );

CREATE MULTISET TABLE A_O_TR_MONTHLY_N_20170821 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      Month_Id INTEGER,
      BASIC_CALL_TYPE_ID INTEGER Compress (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ),
      Onnet_Crossnet_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('0','A','C','F','O','Z'),
      Tap_In_Out_Ind CHAR(2) CHARACTER SET Latin NOT CaseSpecific Compress ('MO','MT','ST','TI'),
      Pricing_Plan_Id SMALLINT Compress (639 ,640 ,641 ),
      Service_Type_Id CHAR(10) CHARACTER SET Latin NOT CaseSpecific,
      Nat_Intl_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      Total_Type_Id SMALLINT Compress (1 ,7 ,8 ,10 ,11 ,524 ,47 ,100 ,365 ,895 ,910 ,911 ,913 ,170 ,181 ,182 ,188 ,191 ,447 ,193 ,468 ,473 ,481 ,482 ,483 ,230 ,240 ,244 ,245 ),
      SERVICE_IND VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Fav_No_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'),
      DES_COUNTRY_ID VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific Compress ('20','62','63','90','91','92','94','880','961','962','963','965','967','971','974','977','212','234','249'),
      Msisdn VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific,
      Traffic_Type_Id INTEGER Compress (1300 ,1070 ,1330 ,2104 ,1090 ,1111 ,100 ,1172 ,1700 ,1190 ,1191 ,1706 ,1200 ,1201 ,1721 ,1215 ,2243 ,1230 ,2255 ,1234 ,1238 ,1751 ,2010 ,2011 ,2269 ,1250 ,1252 ,1253 ,1254 ,1255 ,2279 ,2280 ,1005 ,500 ,2302 ),
      Package_Id INTEGER Compress (1325 ,1341 ,1346 ,1347 ,1604 ,1352 ,1353 ,1393 ,1144 ,1665 ,1186 ,1448 ,1197 ,1453 ,1218 ,1221 ,1485 ,1490 ,1262 ,1271 ),
      ACCOUNT_TYPE VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress '0',
      Customer_Category_Id CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('B','C'),
      Cug_Flag CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('N','Y'),
      Cell_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific,
      Location_Area_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress ('00000','01330','01325','01326','01338','01324','02171','01331','01332','02175','03329','02176','02148','03328','02173','04026','01337','02155','02016','07027','03319','02311','07030','04061','02302','02170','04025','02174','01072','03318'),
      Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      FLAG_TOTAL_TYPE BYTEINT Compress (0 ,1 ),
      Actual_Call_Cost DECIMAL(18,4) Compress 0.0000 ,
      Total_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Calls DECIMAL(18,4) Compress (16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_discounted_Duration DECIMAL(18,4),
      Total_discounted_Gross DECIMAL(18,4),
      Total_Discounted_Free DECIMAL(18,4),
      Total_Discounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Nodiscounted_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Nodiscounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Total_Overlap_Duration DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Net DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Gross DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Free DECIMAL(18,4) Compress 0.0000 ,
      Total_Overlap_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ),
      Created_Date DATE FORMAT 'YYYY-MM-DD',
      DA300_COST DECIMAL(18,2) Compress 0.00 ,
      Callindicator VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress ('3GC','PPS','2GC','POS','SLF','4GC','3GF'),
      CHARGED_VOLUME BIGINT,
      ZERO_RATED_VOLUME BIGINT,
      FREE_VOLUME BIGINT,
      CHARGED_NUMBER_OF_CALLS INTEGER,
      ZERO_RATED_NUMBER_OF_CALLS INTEGER,
      FREE_NUMBER_OF_CALLS INTEGER,
      HYBRID_NUMBER_OF_CALLS INTEGER,
      ACCT_NUM VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific DEFAULT NULL  Compress ('UGW','','SDP','5444','5666','5600','EXT','5400','140','5699','5411','5655','5466','5422','5688','5677','5433','5455','5477','5633','5611','5644','AIR','5622','5499','5555','5404','5656','5440','5405','5414','5445','5488','5665','5660','5454','5448','2008','5410','5447','5446','5441','5443','5415','5406','5442','5696','5667','5434','5668','5606','5669','5662','5664','5407','5650','5661','5409','5678','5480','5408','5402','5690','5464','5663','5412','5695','5680','5430','5651','5449','5421','5403','2069','5420','5670','5450','5605','5490','5416','5413','5698','5691','5686','5616','5640','5694','5456','5630','5697','5692','5423','5646','5693','5432','5636','5620','5602','5610','5676','5424','5609','5607','5417','5470','150','5612','5608','5000','5056','5451','5626','5419','5453','5641','5435','5460','5418','5058','5425','5484','5654','5469','5685','5553','5615','5659','5431','5055','5671','5658','5657','5053','5426','5437','5452','5645','5556','5494','5481','5436','5681','5059','5457','5679','5631','5684','5632','5689','5613','5672','5675','5474','5428','5635','5639','5653','5687','5623','5604','5438','5427','5614','5652','5617','5634','5458','5618','5673','5483','5459','5629','5468','5619','5429','5683','5682','5621','5559','5642','5482','5465','5648','5674','5649','5492','5485','5462','5439','5647','5461','5637','5487','5558','5551','5628','5625','5049','5467','5552','5999','5624','5638','5478','5643','5603','5498','5051','5486','5493','5627','119','5463','5557','5033','5471','5052','5043','5479','5489','5046','5048','5544','5475','5472','5496','5495','5044','5054','5497','5554','5550','5566','5045','5057','5039','5533','5473','5038','5476','5900','5066','5036','5001','5005','5333','5042','5577','5041'))
PRIMARY INDEX ( Month_Id ,Msisdn )
PARTITION BY Range_N(Month_Id  BETWEEN 200001  AND 210012  EACH 1 ,
 NO RANGE OR UNKNOWN)
INDEX OUTG_AGG_MR_MSISDN ( Msisdn );

CREATE MULTISET TABLE a_o_tr_monthly_2016 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      month_id INTEGER,
      basic_call_type_id INTEGER,
      onnet_crossnet_ind VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      tap_in_out_ind VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      pricing_plan_id INTEGER,
      service_type_id VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      nat_intl_ind VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      total_type_id INTEGER,
      service_ind VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      fav_no_ind VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      des_country_id VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      msisdn VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      traffic_type_id INTEGER,
      package_id INTEGER,
      account_type VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      customer_category_id VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      cug_flag VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      cell_id VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      location_area_id VARCHAR(250) CHARACTER SET Latin CaseSpecific,
      call_cost FLOAT,
      flag_total_type INTEGER,
      actual_call_cost FLOAT,
      total_duration FLOAT,
      total_net FLOAT,
      total_gross FLOAT,
      total_free FLOAT,
      total_calls FLOAT,
      total_discounted_duration FLOAT,
      total_discounted_gross FLOAT,
      total_discounted_free FLOAT,
      total_discounted_calls FLOAT,
      total_nodiscounted_duration FLOAT,
      total_nodiscounted_net FLOAT,
      total_nodiscounted_gross FLOAT,
      total_nodiscounted_calls FLOAT,
      total_overlap_duration FLOAT,
      total_overlap_net FLOAT,
      total_overlap_gross FLOAT,
      total_overlap_free FLOAT,
      total_overlap_calls FLOAT,
      created_date DATE FORMAT 'YY/MM/DD',
      da300_cost FLOAT,
      callindicator VARCHAR(250) CHARACTER SET Latin CaseSpecific)
PRIMARY INDEX ( month_id ,msisdn )
PARTITION BY Range_N(Month_Id  BETWEEN 200001  AND 210012  EACH 1 ,
 NO RANGE OR UNKNOWN)
INDEX OUTG_AGG_MR_MSISDN ( msisdn )
INDEX ( package_id );

REPLACE VIEW A_O_TR_MONTHLY
AS LOCKING ROW FOR ACCESS 
SEL 
MONTH_ID
,BASIC_CALL_TYPE_ID
,ONNET_CROSSNET_IND
,TAP_IN_OUT_IND
,PRICING_PLAN_ID
,SERVICE_TYPE_ID
,NAT_INTL_IND
,TOTAL_TYPE_ID
,SERVICE_IND
,FAV_NO_IND
,DES_COUNTRY_ID
,MSISDN
,TRAFFIC_TYPE_ID
,PACKAGE_ID
,ACCOUNT_TYPE
,CUSTOMER_CATEGORY_ID
,CUG_FLAG
,CELL_ID
,LOCATION_AREA_ID
--,OFFER_ID
,FLAG_TOTAL_TYPE

,CALL_COST
,ACTUAL_CALL_COST
,TOTAL_DURATION

,TOTAL_GROSS
,TOTAL_NET
,TOTAL_FREE
,TOTAL_CALLS

,TOTAL_DISCOUNTED_DURATION
,TOTAL_DISCOUNTED_GROSS
,TOTAL_DISCOUNTED_FREE
,TOTAL_DISCOUNTED_CALLS

,TOTAL_NODISCOUNTED_DURATION
,TOTAL_NODISCOUNTED_NET
,TOTAL_NODISCOUNTED_GROSS
,TOTAL_NODISCOUNTED_CALLS
,TOTAL_OVERLAP_DURATION
,TOTAL_OVERLAP_NET
,TOTAL_OVERLAP_GROSS
,TOTAL_OVERLAP_FREE
,TOTAL_OVERLAP_CALLS
,DA300_COST

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_DURATION END  AS TOTAL_ACC_DUR
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_DURATION END AS TOTAL_DA_DUR
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_MAIN_DUR --REVISED
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_OVERLAP_DUR

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_GROSS END  AS TOTAL_ACC_REV
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_GROSS END AS TOTAL_DA_REV
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_MAIN_REV
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_OVERLAP_REV

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_CALLS END  AS TOTAL_ACC_CALL
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_CALLS END AS TOTAL_DA_CALL
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_MAIN_CALL
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_OVERLAP_CALL
,Callindicator
,Cast(NULL AS BIGINT) AS CHARGED_VOLUME                
,Cast(NULL AS BIGINT) AS ZERO_RATED_VOLUME
,Cast(NULL AS BIGINT) AS FREE_VOLUME                
,Cast(NULL AS INTEGER) AS  CHARGED_NUMBER_OF_CALLS
,Cast(NULL AS INTEGER) AS ZERO_RATED_NUMBER_OF_CALLS
,Cast(NULL AS INTEGER) AS FREE_NUMBER_OF_CALLS    
,Cast(NULL AS INTEGER) AS HYBRID_NUMBER_OF_CALLS  

 -------------------- new field---------------------------
, Cast(NULL AS VARCHAR(100)) AS ACCT_NUM
,Cast (NULL AS VARCHAR(100)) AS PPC_ACCT_NO

FROM  A_O_TR_MONTHLY_2016
UNION ALL
SEL 
MONTH_ID
,BASIC_CALL_TYPE_ID
,ONNET_CROSSNET_IND
,TAP_IN_OUT_IND
,PRICING_PLAN_ID
,SERVICE_TYPE_ID
,NAT_INTL_IND
,TOTAL_TYPE_ID
,SERVICE_IND
,FAV_NO_IND
,DES_COUNTRY_ID
,MSISDN
,TRAFFIC_TYPE_ID
,PACKAGE_ID
,ACCOUNT_TYPE
,CUSTOMER_CATEGORY_ID
,CUG_FLAG
,CELL_ID
,LOCATION_AREA_ID
--,OFFER_ID
,FLAG_TOTAL_TYPE

,CALL_COST
,ACTUAL_CALL_COST
,TOTAL_DURATION

,TOTAL_GROSS
,TOTAL_NET
,TOTAL_FREE
,TOTAL_CALLS

,TOTAL_DISCOUNTED_DURATION
,TOTAL_DISCOUNTED_GROSS
,TOTAL_DISCOUNTED_FREE
,TOTAL_DISCOUNTED_CALLS

,TOTAL_NODISCOUNTED_DURATION
,TOTAL_NODISCOUNTED_NET
,TOTAL_NODISCOUNTED_GROSS
,TOTAL_NODISCOUNTED_CALLS
,TOTAL_OVERLAP_DURATION
,TOTAL_OVERLAP_NET
,TOTAL_OVERLAP_GROSS
,TOTAL_OVERLAP_FREE
,TOTAL_OVERLAP_CALLS
,DA300_COST

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_DURATION END  AS TOTAL_ACC_DUR
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_DURATION END AS TOTAL_DA_DUR
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_MAIN_DUR --REVISED
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_OVERLAP_DUR

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_GROSS END  AS TOTAL_ACC_REV
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_GROSS END AS TOTAL_DA_REV
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_MAIN_REV
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_OVERLAP_REV

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_CALLS END  AS TOTAL_ACC_CALL
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_CALLS END AS TOTAL_DA_CALL
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_MAIN_CALL
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_OVERLAP_CALL
,Callindicator
,Cast(CHARGED_VOLUME AS BIGINT) AS CHARGED_VOLUME                
,Cast(ZERO_RATED_VOLUME AS BIGINT) AS ZERO_RATED_VOLUME
,FREE_VOLUME                
,CHARGED_NUMBER_OF_CALLS
,Cast(ZERO_RATED_NUMBER_OF_CALLS AS INTEGER) AS ZERO_RATED_NUMBER_OF_CALLS
,FREE_NUMBER_OF_CALLS    
,HYBRID_NUMBER_OF_CALLS  

 -------------------- new field---------------------------
, ACCT_NUM
,Cast (NULL AS VARCHAR(100)) AS PPC_ACCT_NO

FROM  A_O_TR_MONTHLY_N_20170821

UNION ALL  -------New Table Union

SEL 
MONTH_ID
,BASIC_CALL_TYPE_ID
,ONNET_CROSSNET_IND
,TAP_IN_OUT_IND
,PRICING_PLAN_ID
,SERVICE_TYPE_ID
,NAT_INTL_IND
,TOTAL_TYPE_ID
,SERVICE_IND
,FAV_NO_IND
,DES_COUNTRY_ID
,MSISDN
,TRAFFIC_TYPE_ID
,PACKAGE_ID
,ACCOUNT_TYPE
,CUSTOMER_CATEGORY_ID
,CUG_FLAG
,CELL_ID
,LOCATION_AREA_ID
--,OFFER_ID
,FLAG_TOTAL_TYPE

,CALL_COST
,ACTUAL_CALL_COST
,TOTAL_DURATION

,TOTAL_GROSS
,TOTAL_NET
,TOTAL_FREE
,TOTAL_CALLS

,TOTAL_DISCOUNTED_DURATION
,TOTAL_DISCOUNTED_GROSS
,TOTAL_DISCOUNTED_FREE
,TOTAL_DISCOUNTED_CALLS

,TOTAL_NODISCOUNTED_DURATION
,TOTAL_NODISCOUNTED_NET
,TOTAL_NODISCOUNTED_GROSS
,TOTAL_NODISCOUNTED_CALLS
,TOTAL_OVERLAP_DURATION
,TOTAL_OVERLAP_NET
,TOTAL_OVERLAP_GROSS
,TOTAL_OVERLAP_FREE
,TOTAL_OVERLAP_CALLS
,DA300_COST

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_DURATION END  AS TOTAL_ACC_DUR
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_DURATION END AS TOTAL_DA_DUR
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_MAIN_DUR --REVISED
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_OVERLAP_DUR

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_GROSS END  AS TOTAL_ACC_REV
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_GROSS END AS TOTAL_DA_REV
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_MAIN_REV
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_OVERLAP_REV

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_CALLS END  AS TOTAL_ACC_CALL
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_CALLS END AS TOTAL_DA_CALL
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_MAIN_CALL
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_OVERLAP_CALL
,Callindicator
,Cast(CHARGED_VOLUME AS BIGINT) AS CHARGED_VOLUME                
,Cast(ZERO_RATED_VOLUME AS BIGINT) AS ZERO_RATED_VOLUME
,FREE_VOLUME                
,CHARGED_NUMBER_OF_CALLS
,Cast(ZERO_RATED_NUMBER_OF_CALLS AS INTEGER) AS ZERO_RATED_NUMBER_OF_CALLS
,FREE_NUMBER_OF_CALLS    
,HYBRID_NUMBER_OF_CALLS  

 -------------------- new field---------------------------
, ACCT_NUM
,Cast (NULL AS VARCHAR(100)) AS PPC_ACCT_NO

FROM  A_O_TR_MONTHLY_N_20180930

UNION ALL
SEL 
MONTH_ID
,BASIC_CALL_TYPE_ID
,ONNET_CROSSNET_IND
,TAP_IN_OUT_IND
,PRICING_PLAN_ID
,SERVICE_TYPE_ID
,NAT_INTL_IND
,TOTAL_TYPE_ID
,SERVICE_IND
,FAV_NO_IND
,DES_COUNTRY_ID
,MSISDN
,TRAFFIC_TYPE_ID
,PACKAGE_ID
,ACCOUNT_TYPE
,CUSTOMER_CATEGORY_ID
,CUG_FLAG
,CELL_ID
,LOCATION_AREA_ID
--,OFFER_ID
,FLAG_TOTAL_TYPE

,CALL_COST
,ACTUAL_CALL_COST
,TOTAL_DURATION

,TOTAL_GROSS
,TOTAL_NET
,TOTAL_FREE
,TOTAL_CALLS

,TOTAL_DISCOUNTED_DURATION
,TOTAL_DISCOUNTED_GROSS
,TOTAL_DISCOUNTED_FREE
,TOTAL_DISCOUNTED_CALLS

,TOTAL_NODISCOUNTED_DURATION
,TOTAL_NODISCOUNTED_NET
,TOTAL_NODISCOUNTED_GROSS
,TOTAL_NODISCOUNTED_CALLS
,TOTAL_OVERLAP_DURATION
,TOTAL_OVERLAP_NET
,TOTAL_OVERLAP_GROSS
,TOTAL_OVERLAP_FREE
,TOTAL_OVERLAP_CALLS
,DA300_COST

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_DURATION END  AS TOTAL_ACC_DUR
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_DURATION END AS TOTAL_DA_DUR
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_MAIN_DUR --REVISED
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_DURATION END AS TOTAL_OVERLAP_DUR

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_GROSS END  AS TOTAL_ACC_REV
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_GROSS END AS TOTAL_DA_REV
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_MAIN_REV
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_GROSS END AS TOTAL_OVERLAP_REV

,CASE WHEN CALL_COST=0 AND ACTUAL_CALL_COST=0 THEN TOTAL_CALLS END  AS TOTAL_ACC_CALL
,CASE WHEN CALL_COST > 0 AND ACTUAL_CALL_COST = 0 THEN TOTAL_CALLS END AS TOTAL_DA_CALL
,CASE WHEN CALL_COST=ACTUAL_CALL_COST AND (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_MAIN_CALL
,CASE WHEN CALL_COST<>ACTUAL_CALL_COST AND  (CALL_COST > 0 AND ACTUAL_CALL_COST > 0) THEN TOTAL_CALLS END AS TOTAL_OVERLAP_CALL
,Callindicator
,Cast(CHARGED_VOLUME AS BIGINT) AS CHARGED_VOLUME                
,Cast(ZERO_RATED_VOLUME AS BIGINT) AS ZERO_RATED_VOLUME
,FREE_VOLUME                
,CHARGED_NUMBER_OF_CALLS
,Cast(ZERO_RATED_NUMBER_OF_CALLS AS INTEGER) AS ZERO_RATED_NUMBER_OF_CALLS
,FREE_NUMBER_OF_CALLS    
,HYBRID_NUMBER_OF_CALLS  

 -------------------- new field---------------------------
, ACCT_NUM
 ,PPC_ACCT_NO

FROM  A_O_TR_MONTHLY_N;
Ambassador

Re: View parition elimination vs table performance

Having stats on MONTH_ID is not enough, you can't get partition elimination (or better removing the whole table) without WHERE-conditions.

As you seem to have a known range of  months In your tables you must add WHERE-conditions matching those ranges in the view, e.g.

 

FROM  A_O_TR_MONTHLY_2016
WHERE MONTH_ID BETWEEN 201601 AND 201612

 

 

The data model was also improved over time (did you port it from Oracle?), e.g. customer_category_id was changed from VARCHAR(250) to CHAR(1), of course it could improve further :-)

But the 1st Select in a view determines the datatype of a column and it accesses to oldest table :-(

This is a list of columns with significantly different definitions and the resulting datatype in the view:

 

TableName                          ColumnName               ColumnType  ColumnLength
A_O_TR_MONTHLY                     account_type                     CV           250a_o_tr_monthly_2016                account_type                     CV           250
A_O_TR_MONTHLY_N                   ACCOUNT_TYPE                     CV            20
A_O_TR_MONTHLY_N_20170821          ACCOUNT_TYPE                     CV            20
A_O_TR_MONTHLY_N_20180930          ACCOUNT_TYPE                     CV            20
A_O_TR_MONTHLY                     callindicator                    CV           250a_o_tr_monthly_2016                callindicator                    CV           250
A_O_TR_MONTHLY_N                   Callindicator                    CV             6
A_O_TR_MONTHLY_N_20170821          Callindicator                    CV             6
A_O_TR_MONTHLY_N_20180930          Callindicator                    CV             6
A_O_TR_MONTHLY                     cell_id                          CV           250a_o_tr_monthly_2016                cell_id                          CV           250
A_O_TR_MONTHLY_N                   Cell_Id                          CV            20
A_O_TR_MONTHLY_N_20170821          Cell_Id                          CV            20
A_O_TR_MONTHLY_N_20180930          Cell_Id                          CV            20
A_O_TR_MONTHLY                     cug_flag                         CV           250a_o_tr_monthly_2016                cug_flag                         CV           250
A_O_TR_MONTHLY_N                   Cug_Flag                         CF             1
A_O_TR_MONTHLY_N_20170821          Cug_Flag                         CF             1
A_O_TR_MONTHLY_N_20180930          Cug_Flag                         CF             1
A_O_TR_MONTHLY                     customer_category_id             CV           250a_o_tr_monthly_2016                customer_category_id             CV           250
A_O_TR_MONTHLY_N                   Customer_Category_Id             CF             1
A_O_TR_MONTHLY_N_20170821          Customer_Category_Id             CF             1
A_O_TR_MONTHLY_N_20180930          Customer_Category_Id             CF             1
A_O_TR_MONTHLY                     des_country_id                   CV           250a_o_tr_monthly_2016                des_country_id                   CV           250
A_O_TR_MONTHLY_N                   DES_COUNTRY_ID                   CV            30
A_O_TR_MONTHLY_N_20170821          DES_COUNTRY_ID                   CV            30
A_O_TR_MONTHLY_N_20180930          DES_COUNTRY_ID                   CV            30
A_O_TR_MONTHLY                     fav_no_ind                       CV           250a_o_tr_monthly_2016                fav_no_ind                       CV           250
A_O_TR_MONTHLY_N                   Fav_No_Ind                       CF             1
A_O_TR_MONTHLY_N_20170821          Fav_No_Ind                       CF             1
A_O_TR_MONTHLY_N_20180930          Fav_No_Ind                       CF             1
A_O_TR_MONTHLY                     flag_total_type                  I              4a_o_tr_monthly_2016                flag_total_type                  I              4
A_O_TR_MONTHLY_N                   FLAG_TOTAL_TYPE                  I1             1
A_O_TR_MONTHLY_N_20170821          FLAG_TOTAL_TYPE                  I1             1
A_O_TR_MONTHLY_N_20180930          FLAG_TOTAL_TYPE                  I1             1
A_O_TR_MONTHLY                     location_area_id                 CV           250a_o_tr_monthly_2016                location_area_id                 CV           250
A_O_TR_MONTHLY_N                   Location_Area_Id                 CV            20
A_O_TR_MONTHLY_N_20170821          Location_Area_Id                 CV            20
A_O_TR_MONTHLY_N_20180930          Location_Area_Id                 CV            20
A_O_TR_MONTHLY                     msisdn                           CV           250a_o_tr_monthly_2016                msisdn                           CV           250
A_O_TR_MONTHLY_N                   Msisdn                           CV           100
A_O_TR_MONTHLY_N_20170821          Msisdn                           CV           100
A_O_TR_MONTHLY_N_20180930          Msisdn                           CV           100
A_O_TR_MONTHLY                     nat_intl_ind                     CV           250a_o_tr_monthly_2016                nat_intl_ind                     CV           250
A_O_TR_MONTHLY_N                   Nat_Intl_Ind                     CF             1
A_O_TR_MONTHLY_N_20170821          Nat_Intl_Ind                     CF             1
A_O_TR_MONTHLY_N_20180930          Nat_Intl_Ind                     CF             1
A_O_TR_MONTHLY                     onnet_crossnet_ind               CV           250a_o_tr_monthly_2016                onnet_crossnet_ind               CV           250
A_O_TR_MONTHLY_N                   Onnet_Crossnet_Ind               CF             1
A_O_TR_MONTHLY_N_20170821          Onnet_Crossnet_Ind               CF             1
A_O_TR_MONTHLY_N_20180930          Onnet_Crossnet_Ind               CF             1
A_O_TR_MONTHLY                     service_ind                      CV           250a_o_tr_monthly_2016                service_ind                      CV           250
A_O_TR_MONTHLY_N                   SERVICE_IND                      CV             6
A_O_TR_MONTHLY_N_20170821          SERVICE_IND                      CV             6
A_O_TR_MONTHLY_N_20180930          SERVICE_IND                      CV             6
A_O_TR_MONTHLY                     service_type_id                  CV           250a_o_tr_monthly_2016                service_type_id                  CV           250
A_O_TR_MONTHLY_N                   Service_Type_Id                  CF            10
A_O_TR_MONTHLY_N_20170821          Service_Type_Id                  CF            10
A_O_TR_MONTHLY_N_20180930          Service_Type_Id                  CF            10
A_O_TR_MONTHLY                     tap_in_out_ind                   CV           250a_o_tr_monthly_2016                tap_in_out_ind                   CV           250
A_O_TR_MONTHLY_N                   Tap_In_Out_Ind                   CF             2
A_O_TR_MONTHLY_N_20170821          Tap_In_Out_Ind                   CF             2
A_O_TR_MONTHLY_N_20180930          Tap_In_Out_Ind                   CF             2

 

This results in 1262 bytes in Group By instead of 174 when accessing the base table

 

       201811 AS MONTH_ID         -- integer         4
    ,USAGE.BASIC_CALL_TYPE_ID     -- integer         4
    ,USAGE.Service_Type_Id        -- varchar(250)  250
    ,USAGE.TOTAL_TYPE_ID          -- integer         4
    ,USAGE.MSISDN                 -- varchar(250)  250
    ,USAGE.CELL_ID                -- varchar(250)  250
    ,USAGE.LOCATION_AREA_ID       -- varchar(250)  250
    ,USAGE.Callindicator          -- varchar(250)  250
                                                  1262

 

 

A simple fix would be changing the order of tables in the view definition (of course only if the data in a_o_tr_monthly_2016.customer_category_id is actually a single character only).

 

Ambassador

Re: View parition elimination vs table performance

General remark regarding the data model:

All columns are defined as NULLable, why?

According to the Relational data model you can't have tables like these, because they can't have a logical Primary Key.

Teradata Employee

Re: View parition elimination vs table performance

Tables and view has been defined earlier. So i cannot say at the moment for sure why all are nullable. regarding the order of tables it is very valid point. However month condition cannot be added in all table where clauses of view  as view is generric and month id can vary from time to time. it is just hard coded in example. So anyother way of partition elimination?

Ambassador

Re: View parition elimination vs table performance

You mean you got data with overlapping date ranges in those tables, e.g the same MONTH_ID 201809 in multiple tables?

Stats seemed to indicate this doesn't happen, only A_O_TR_MONTHLY_NEW got data for 201809 to 201811 based on Exlain estimates.

 

 

 

 

Teradata Employee

Re: View parition elimination vs table performance

No we donot have overlapping ranges in multiple tables. but we may querying old data like of 2017 in 2018.