When I create a table with partition and compress reduce it

Database
Teradata Employee

When I create a table with partition and compress reduce it

Hi.

We have created a row partitioned table with any nulls, this table have done a compress null and it have decreased 4GB of 80GB but it hasn´t any null.

But if i don´t partitioned the table, it not compress any. i know if i don´t have null, compress null does´t decreased the size in a table.

other thing i have see is the next, when i do more partition that my number of AMPs the table size is equal, but when the number of partitions are less than AMPs, the table partitioned have less size.

in 80GB decreased 4GB, in 2MB 512b.

Tags (1)
6 REPLIES
Senior Apprentice

Re: When I create a table with partition and compress reduce it

Can you show the actual DDL?

Teradata Employee

Re: When I create a table with partition and compress reduce it

CREATE MULTISET TABLE SYS_REPORTING.aux ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL,
TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC );

SEL ID_TIEMPO_CICLO_PIC, COUNT(*) FROM SYS_REPORTING.aux GROUP BY 1;

/* Results:
ID_TIEMPO_CICLO_PIC Count(*)
1 20.150.801 1.500
2 20.150.802 1.500
3 20.150.803 1.500
4 20.150.804 1.500
5 20.150.805 1.500
6 20.150.806 1.500
7 20.150.807 1.500
8 20.150.808 1.500
9 20.150.809 1.500
10 20.150.810 1.500
11 20.150.811 1.500
12 20.150.812 1.500
13 20.150.813 1.500
14 20.150.814 1.500
15 20.150.815 1.500
16 20.150.816 1.500
17 20.150.817 1.500
18 20.150.818 1.500
19 20.150.819 1.500
20 20.150.820 1.500
21 20.150.821 1.500
22 20.150.822 1.500
23 20.150.823 1.500
24 20.150.824 1.500
25 20.150.825 1.500
26 20.150.826 1.500
27 20.150.827 1.500
28 20.150.828 1.500
29 20.150.829 1.500
30 20.150.830 1.500
31 20.150.831 1.500
*/

DROP TABLE SYS_REPORTING .borrarAS1;
CREATE MULTISET TABLE SYS_REPORTING .borrarAS1,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL
-- , SW_TRANSITO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','1'),
-- SWITCH VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC ,
, TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC )
PARTITION BY RANGE_N(ID_TIEMPO_CICLO_PIC BETWEEN
20150701 AND 20150731 EACH 1 ,
20150801 AND 20150831 EACH 1 ,
20150901 AND 20150930 EACH 1
)
;

INSERT INTO SYS_REPORTING .borrarAS1
SEL * FROM SYS_REPORTING.aux;

COLLECT STATS COLUMN PARTITION ON SYS_REPORTING .borrarAS1;
COLLECT STATS COLUMN ID_TIEMPO_CICLO_PIC ON SYS_REPORTING .borrarAS2;

DROP TABLE SYS_REPORTING .borrarAS2;
CREATE MULTISET TABLE SYS_REPORTING .borrarAS2,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL
-- , SW_TRANSITO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','1'),
-- SWITCH VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
, TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS
)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC )
PARTITION BY RANGE_N(ID_TIEMPO_CICLO_PIC BETWEEN
20150701 AND 20150731 EACH 1 ,
20150801 AND 20150831 EACH 1 ,
20150901 AND 20150930 EACH 1 )
;
INSERT INTO SYS_REPORTING .borrarAS2
SEL * FROM SYS_REPORTING .borrarAS1;

COLLECT STATS COLUMN PARTITION ON SYS_REPORTING .borrarAS2;
COLLECT STATS COLUMN ID_TIEMPO_CICLO_PIC ON SYS_REPORTING .borrarAS2;

SEL databasename, TABLENAME tabla,skewing ,espacio_ocupado,espacio_real FROM
(SEL databasename, TABLENAME
,SUM(currentperm) espacio_ocupado
,MAX(currentperm)*(SEL HASHAMP() +1) espacio_real
,espacio_real/espacio_ocupado skewing
FROM dbc.tablesizev
WHERE databasename = 'SYS_REPORTING'
AND TABLENAME IN ('borrarAS1', 'borrarAS2')
GROUP BY 1, 2
) c

/* Results
DataBaseName tabla skewing espacio_ocupado espacio_real
1 SYS_REPORTING borrarAS2 1,01 2.466.816,00 2.488.320,00
2 SYS_REPORTING borrarAS1 1,01 2.468.352,00 2.488.320,00

*/
CREATE MULTISET TABLE SYS_REPORTING.aux ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL,
TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC );

SEL ID_TIEMPO_CICLO_PIC, COUNT(*) FROM SYS_REPORTING.aux GROUP BY 1;

/* Resultado:
ID_TIEMPO_CICLO_PIC Count(*)
1 20.150.801 1.500
2 20.150.802 1.500
3 20.150.803 1.500
4 20.150.804 1.500
5 20.150.805 1.500
6 20.150.806 1.500
7 20.150.807 1.500
8 20.150.808 1.500
9 20.150.809 1.500
10 20.150.810 1.500
11 20.150.811 1.500
12 20.150.812 1.500
13 20.150.813 1.500
14 20.150.814 1.500
15 20.150.815 1.500
16 20.150.816 1.500
17 20.150.817 1.500
18 20.150.818 1.500
19 20.150.819 1.500
20 20.150.820 1.500
21 20.150.821 1.500
22 20.150.822 1.500
23 20.150.823 1.500
24 20.150.824 1.500
25 20.150.825 1.500
26 20.150.826 1.500
27 20.150.827 1.500
28 20.150.828 1.500
29 20.150.829 1.500
30 20.150.830 1.500
31 20.150.831 1.500
32 20.150.901 1.500
33 20.150.902 1.500
34 20.150.903 1.500
35 20.150.904 1.500
36 20.150.905 1.500
37 20.150.906 1.500*/

DROP TABLE SYS_REPORTING .borrarAS1;
CREATE MULTISET TABLE SYS_REPORTING .borrarAS1,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL
-- , SW_TRANSITO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','1'),
-- SWITCH VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC ,
, TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC )
PARTITION BY RANGE_N(ID_TIEMPO_CICLO_PIC BETWEEN
20150701 AND 20150731 EACH 1 ,
20150801 AND 20150831 EACH 1 ,
20150901 AND 20150930 EACH 1
)
;

INSERT INTO SYS_REPORTING .borrarAS1
SEL * FROM SYS_REPORTING.aux;

COLLECT STATS COLUMN PARTITION ON SYS_REPORTING .borrarAS1;
COLLECT STATS COLUMN ID_TIEMPO_CICLO_PIC ON SYS_REPORTING .borrarAS2;

DROP TABLE SYS_REPORTING .borrarAS2;
CREATE MULTISET TABLE SYS_REPORTING .borrarAS2,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ID_PYL_ANA_ICX_PIC INTEGER NOT NULL,
MSISDN VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
ID_TIEMPO_NATURAL_PIC INTEGER NOT NULL,
ID_TIEMPO_CICLO_PIC INTEGER NOT NULL
-- , SW_TRANSITO CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','1'),
-- SWITCH VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,
, TRUNK VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS
)
PRIMARY INDEX ( ID_PYL_ANA_ICX_PIC ,MSISDN ,ID_TIEMPO_NATURAL_PIC )
PARTITION BY RANGE_N(ID_TIEMPO_CICLO_PIC BETWEEN
20150701 AND 20150731 EACH 1 ,
20150801 AND 20150831 EACH 1 ,
20150901 AND 20150930 EACH 1 )
;
INSERT INTO SYS_REPORTING .borrarAS2
SEL * FROM SYS_REPORTING .borrarAS1;

COLLECT STATS COLUMN PARTITION ON SYS_REPORTING .borrarAS2;
COLLECT STATS COLUMN ID_TIEMPO_CICLO_PIC ON SYS_REPORTING .borrarAS2;

SEL databasename, TABLENAME tabla,skewing ,espacio_ocupado,espacio_real FROM
(SEL databasename, TABLENAME
,SUM(currentperm) espacio_ocupado
,MAX(currentperm)*(SEL HASHAMP() +1) espacio_real
,espacio_real/espacio_ocupado skewing
FROM dbc.tablesizev
WHERE databasename = 'SYS_REPORTING'
AND TABLENAME IN ('borrarAS1', 'borrarAS2')
GROUP BY 1, 2
) c

/* Resultado
DataBaseName tabla skewing espacio_ocupado espacio_real
1 SYS_REPORTING borrarAS2 1,01 2.911.744,00 2.930.688,00
2 SYS_REPORTING borrarAS1 1,01 2.911.744,00 2.930.688,00
*/

this is the tables, could be for the block size? it group better when we lanch this with compress.

if it will be for the block size how i could see the free blocks in a table?

thanks for all!!!

Teradata Employee

Re: When I create a table with partition and compress reduce it

the first with 31 partition and the second with 37 partition, the table size decreased, with more data, more decreased.

thanks

Senior Apprentice

Re: When I create a table with partition and compress reduce it

Your example #1 shows a size reduced by 1536 bytes and for #2 both sizes match exactly, this is probably due to one differently sized block on 3 AMPs (and there's hardly more than one block per AMP)

And 2468352 : 2911744 is roughly 31 : 37, so where's the decrease?

The 80 GB - 4 GB sounds more interesting, can you show this, too?

Teradata Employee

Re: When I create a table with partition and compress reduce it

We going to add all the information that i can add.

In this test the size only change the amp up to the total number of amp, in the case of this amp be lower than the total amp, but if we try with more than this, the size doesn´t change.

I see the DBS CONTROL and the block size is 512 bits.

i add an image with the comparate sizes in the amp.

Senior Apprentice

Re: When I create a table with partition and compress reduce it

Sector size is 512 byte and block in Teradata might be be between 1 and 255 (or bigger depending on the release) sectors.

One or three AMPs, any change in the range of a few KB is insignificant, you shouldn't care about it.

Run those tests on a much bigger table...