Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 10 Dec 2013 @ 21:13:49 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   DBC AccessRights table is skewed
 
From:   Bakthavachalam, Roopalini

Hi Forum,

We are on 13.10. Our DBC.AccessRights table is highly skewed with skew factor 72. I tried collecting the below stats , however there was no improvement at all. How can I get rid of the skew from that table. We have just 100,000 records in the table.

     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (USERID ,DATABASEID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,DATABASEID,USERID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN DATABASEID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,DATABASEID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMID ,USERID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN TVMID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN USERID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (AccessRight,GrantorID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (AccessRight,CreateUID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (FieldId,AccessRight);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN FieldId;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN AccessRight;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN GrantorID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN CreateUID;
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (DatabaseId,AccessRight);
     COLLECT STATISTICS DBC.ACCESSRIGHTS COLUMN (TVMId,AccessRight);
     COLLECT STATISTICS DBC.ACCESSRIGHTS INDEX (USERID ,DATABASEID);
     COLLECT STATISTICS DBC.ACCESSRIGHTS INDEX (TVMID);

Table structure below.

     CREATE SET TABLE DBC.AccessRights ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT,
          DEFAULT MERGEBLOCKRATIO
          (
           UserId BYTE(4) NOT NULL,
           DatabaseId BYTE(4) NOT NULL,
           TVMId BYTE(6) NOT NULL,
           FieldId SMALLINT FORMAT '---,--9' NOT NULL,
           AccessRight CHAR(2) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
           WithGrant CHAR(1) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
           GrantorID BYTE(4) NOT NULL,
           AllnessFlag CHAR(1) CHARACTER SET LATIN UPPERCASE NOT CASESPECIFIC NOT NULL,
           CreateUID BYTE(4),
           CreateTimeStamp TIMESTAMP(0),
           LastAccessTimeStamp TIMESTAMP(0),
           AccessCount INTEGER FORMAT '--,---,---,--9') PRIMARY INDEX ( UserId ,DatabaseId ) INDEX ( TVMId );

Thanks

Roopalini



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016