I may be slow on the uptake, but I just can't seem to completely shake this question:
I have a table something like the following :
If the Primary Index is composed of
Is there anything to be gained with respect to query efficiency by reordering the columns such that all columns of the Primary Index reside (in order) at the beginning of the table row?
In other words reorganize the table as follows:
PRIMARY INDEX (
No, at least not regarding the efficiency of the PI
There's one specific case where it matters, multi-column statistics.
Only the first 16 bytes of stats are stored and the order is based on the order within create table:
- "varchar(20), int" -> only the first 16 bytes of the varchar are stored and nothing else
- "int, varchar(20)" -> int plus the first 12 bytes of the varchar are stored
In TD14 this problem is solved, the order is based on the collect stats (no longer on ther create table) and the 16 byte limit is lifted.
Regarding TD14 16 byte limit lift to 25 Byte, is this evaluation done at column by column basis and not in the combined values? e.g this is the order of columns in the Create table DDL
X SMALLINT NOT NULL,
Y VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
if we are collecting STATS in this format as follows
COLUMN ( X,Y ) ,
ON <databasename.tablename>;X will be evaluated at 25 byte limit and Y will also be evaluated at 25 byte limit?
Since Y goes over the limit , it is then advisable to extend that length as follows:
USING MAXVALUELENGTH 30
COLUMN ( X,Y ) ,
ON <databasename.tablename> ;
is that a true statement?
when you do a SHOW STATS VALUES you will see that the 25 bytes (25 chars for strings) are used for each column.
I have verified that indeed its at column by column basis. Why do we have the seconf field evaluated at 24 and not 25??
COLUMN ( X ,Y )
Here is part of the Histogram values
/** SummaryInfo **/
/* Data Type and Length: 'I2:2', 'CV:24' */