PermSpace of a Secondary Index

Database
Enthusiast

PermSpace of a Secondary Index

I am wanting to compare the difference in size between a table's NUSI/USI/Join Index/Hash Index. The size of last two are easy to find because they are represented as another table in the dbc.TableSize table. However the Secondary Index's of a table seem to be grouped with the Base Table. Is there any way to break the secondary index PermSpace value out of this total value?
9 REPLIES
Enthusiast

Re: PermSpace of a Secondary Index

Okay got the answer:
COLLECT DEMOGRAPHICS
FOR retail.ITEMPPI
INTO qcd_samples ALL;

Then
SEL
databasename
,tablename
,SubTableID
,SUM(RowCount*AvgRowSize) AS SizeInBytes
FROM qcd_samples.DataDemographicsView ddv
GROUP BY 1,2,3;
Enthusiast

Re: PermSpace of a Secondary Index

Thanks for this.

In addition, as anybody discovered how to join DataDemographicsView.SubTableID back onto DBC.Indices, to get a bit more summary info (assuming you don't name the index)...

Enthusiast

Re: PermSpace of a Secondary Index

Best I could do:

COLLECT DEMOGRAPHICS
FOR database.table
INTO qcd ALL;

SELECT
i.IndexName
, i.IndexNumber
, i.SubTableId
, i.IndexType
, i.IndexTypeDes
, i.UniqueFlag
, i.ColumnName
, i.ColumnPosition
, i.IndexMode
, i.IndexModeDes
, case
WHEN i.ColumnPosition = 1
THEN d.SizeInBytes
ELSE 0
END AS SizeInBytes
, i.CreatorName
, i.CreateTimeStamp
, i.LastAlterName
, i.LastAlterTimeStamp
FROM(
SEL
Indices.DatabaseName
, Indices.TableName
, Indices.IndexName
, Indices.IndexNumber
, CASE
WHEN Indices.IndexNumber = 1
THEN 1024
WHEN Indices.IndexNumber > 1
THEN 1024 + Indices.IndexNumber
ELSE NULL
END AS SubTableId
, Indices.IndexType
, CAST(CASE
WHEN Indices.IndexType = 'P' THEN 'PRIMARY INDEX - Nonpartitioned'
WHEN Indices.IndexType = 'Q' THEN 'PRIMARY INDEX- Partitioned'
WHEN Indices.IndexType = 'S' THEN 'SECONDARY INDEX'
WHEN Indices.IndexType = 'J' THEN 'JOIN INDEX'
WHEN Indices.IndexType = 'N' THEN 'HASH INDEX'
WHEN Indices.IndexType = 'K' THEN 'Primary Key'
WHEN Indices.IndexType = 'U' THEN 'UNIQUE COLUMN constraint'
WHEN Indices.IndexType = 'V' THEN 'SECONDARY INDEX ORDER BY VALUES'
WHEN Indices.IndexType = 'H' THEN 'SECONDARY INDEX ORDER BY HASH - ALL covering'
WHEN Indices.IndexType = 'O' THEN 'SECONDARY INDEX ORDER BY VALUES ALL covering'
WHEN Indices.IndexType = 'I' THEN 'ordering column of a composite secondary index'
WHEN Indices.IndexType = 'M' THEN 'Multi-Column Statistics'
WHEN Indices.IndexType = 'D' THEN 'Derived column partition statistics'
WHEN Indices.IndexType = '1' THEN 'field1 column of a join or hash index'
WHEN Indices.IndexType = '2' THEN 'field2 column of a join or hash index'
END AS VARCHAR(255)) AS IndexTypeDes
, Indices.UniqueFlag
, Indices.ColumnName
, Indices.ColumnPosition
, Indices.CreatorName
, Indices.CreateTimeStamp
, Indices.LastAlterName
, Indices.LastAlterTimeStamp
, Indices.IndexMode
, CAST(CASE
WHEN Indices.IndexMode = 'H' THEN 'Hash Distributed Across Amps'
WHEN (Indices.IndexMode = 'L' AND (Indices.IndexType = 'J' OR Indices.IndexType = 'N')) THEN 'Not Applicable'
WHEN Indices.IndexMode = 'L' THEN 'Secondary Index Rows Same Amp as Base Table Rows'
WHEN Indices.IndexMode IS NULL THEN 'Primary Index, Hash Distibuted Across Amps'
END AS VARCHAR(255)) AS IndexModeDes
FROM DBC.Indices
WHERE 1=1
AND TRIM(Indices.databasename) = 'database'
AND TRIM(Indices.tablename) = 'table'
) AS i
INNER JOIN (
SEL
databasename
,tablename
,SubTableID
,SUM(RowCount*AvgRowSize) AS SizeInBytes
FROM qcd.DataDemographicsView
WHERE 1=1
AND TRIM(databasename) = 'database'
AND TRIM(tablename) = 'table'
GROUP BY 1,2,3
)AS d
ON TRIM(i.databasename) = TRIM(d.databasename)
AND TRIM(i.tablename) = TRIM(d.tablename)
AND i.SubTableId = d.SubTableId
ORDER BY IndexNumber , ColumnPosition;

Enthusiast

Re: PermSpace of a Secondary Index

Thanks Rupert for the Query. I was also looking for a Process to identify Space Used by the SIs without using any Utilities. 

I do have 1 Question...Is ther any other way of fetching the same information as is fetched from qcd.DataDemographicsView, say another DBC Tables. I am talking in the context of having no access to the QCD Database.

Junior Contributor

Re: PermSpace of a Secondary Index

You don't need QCD, simply create a DataDemographics table in any database and then use this instead.

Check the "SQL Request and Transaction Processing" manual for the CREATE TABLE:

Chapter 6: Query Capture Facility

DataDemographics

Dieter

Highlighted
Fan

Re: PermSpace of a Secondary Index

Hi,

 

This is very useful. I was trying to the query. But collect demographics is only supported in TD 13 version. I have TD 15 version. While doing collect demographics I am getting below error in TD 15. Could you please help me.

 

COLLECT DEMOGRAPHICS for coe4.Employee_SI into coe4 all;
 *** Failure 3811 Column 'IndexName' is NOT NULL.  Give the column a value.
                Statement# 5, Info =0 
 *** Total elapsed time was 1 second.

Junior Contributor

Re: PermSpace of a Secondary Index

It's supported in every release.

Of course you must use the correct CREATE TABLE (copied from the SQL Request and Transaction Processing manual), then the column IndexName is not defined as NOT NULL:

CREATE SET TABLE DataDemographics
     (
      MachineName VARCHAR(30) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      TableName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      DatabaseName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      DBSize INTEGER NOT NULL,
      CollectedTime TIMESTAMP(6) NOT NULL,
      AMPNumber INTEGER NOT NULL,
      ClusterNumber INTEGER NOT NULL,
      SubTableID SMALLINT NOT NULL,
      SubTableType VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC,
      RowCount DECIMAL(18,0) NOT NULL,
      AvgRowSize INTEGER NOT NULL,
      QueryID INTEGER,
      IndexName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC,
      DemographicsID INTEGER)
PRIMARY INDEX ( MachineName ,TableName ,DatabaseName );
Fan

Re: PermSpace of a Secondary Index

Hi dnoeth,

 

Thank you so much for your immediate response !!

Yes, I had copied the create statement from the SQL Request and Transaction Processing manual. Below is the create statement where they have used IndexName as NOT NULL. But as per your response, I have removed the NOT NULL from IndexName and now it is working fine. Thank you so much for your help.

 

Table Definition

The following CREATE TABLE request defines the DataDemographics table:

CREATE TABLE DataDemographics (

MachineName VARCHAR(30) CHARACTER SET UNICODE

UPPERCASE NOT CASESPECIFIC NOT NULL,

TableName VARCHAR(30) CHARACTER SET UNICODE

UPPERCASE NOT CASESPECIFIC NOT NULL,

DatabaseName VARCHAR(30) CHARACTER SET UNICODE

UPPERCASE NOT CASESPECIFIC NOT NULL,

DBSize INTEGER NOT NULL,

CollectedTime TIMESTAMP(6) NOT NULL,

AMPNumber INTEGER NOT NULL,

ClusterNumber INTEGER NOT NULL,

SubTableID SMALLINT NOT NULL,

SubTableType VARCHAR(120),

RowCount DECIMAL(18,0) NOT NULL,

AvgRowSize INTEGER NOT NULL,

QueryID INTEGER,

IndexName VARCHAR(2048) CHARACTER SET UNICODE

UPPERCASE NOT CASESPECIFIC NOT NULL,

DemographicsID INTEGER)

PRIMARY INDEX (MachineName, DatabaseName, TableName);

 

Junior Contributor

Re: PermSpace of a Secondary Index

Might be a bug in some versions of the documentation...