Archives of the TeradataForum

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

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

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>>