Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2012
07:43 AM

07-10-2012
07:43 AM

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 :

TABLE_A

(

YR1_UNIT DECIMAL(7,0),

YR2_UNIT DECIMAL(7,0),

YR3_UNIT DECIMAL(7,0),

YR4_UNIT DECIMAL(7,0),

YR5_UNIT DECIMAL(7,0),

DATA_TYP CHAR(1),

FAC_CD CHAR(2),

INSRT_TS TIMESTAMP(0),

ACTV_RCD_IND VARCHAR(1)

)

If the Primary Index is composed of

FAC_CD,

DATA_TYP,

ACTV_REC_IND

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:

TABLE_A

(

**FAC_CD **CHAR(2),

**DATA_TYP **CHAR(1),

**ACTV_RCD_IND** VARCHAR(1),

YR1_UNIT DECIMAL(7,0),

YR2_UNIT DECIMAL(7,0),

YR3_UNIT DECIMAL(7,0),

YR4_UNIT DECIMAL(7,0),

YR5_UNIT DECIMAL(7,0),

INSRT_TS TIMESTAMP(0)

)

PRIMARY INDEX (

FAC_CD,

DATA_TYP,

ACTV_REC_IND

)

;

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-10-2012
11:00 AM

07-10-2012
11:00 AM

Hi Dave,

short answer:

**No**, at least not regarding the efficiency of the PI

Long answer:

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.

Dieter

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2014
06:00 PM

02-16-2014
06:00 PM

Hi Dieter,

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

**COLLECT** **STATISTICS**

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

**COLLECT** **STATISTICS**

**USING** **MAXVALUELENGTH** 30

**COLUMN** ( X,Y ) ,

**ON** <databasename.tablename> **;**

is that a true statement?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-16-2014
11:52 PM

02-16-2014
11:52 PM

Hi Achieng,

when you do a SHOW STATS VALUES you will see that the 25 bytes (25 chars for strings) are used for each column.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-17-2014
09:39 AM

02-17-2014
09:39 AM

Thanks Dieter!

I have verified that indeed its at column by column basis. Why do we have the seconf field evaluated at 24 and not 25??

**COLLECT** STATISTICS

**COLUMN** ( X ,Y )

**ON** <Databasename.Tablename>;

Here is part of the Histogram values

*/** SummaryInfo **/*

*/* Data Type and Length: 'I2:2', 'CV:24' */*