-- ALTER TABLE EXCHANGE WITH
-- 0. function
-- 1. table, partition not exist
-- 2. table type check
-- 3. column(name, number, type)
-- 4. constraint check
-- 5. distribute check
-- 6. index check
-- 7. validation check
-- 8. table relfilenode
-- 9. toast table relfilenode
-- 10. index relfilenode
-- 11. index is available after exchange
-- 12. tablespace(table,index)
-- 13. verbose
create schema fvt_data_partition_exchange;
create table fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_1
(
c_smallint smallint,
c_integer integer,
c_bigint bigint,
c_decimal decimal,
c_numeric numeric,
c_real real,
c_double double precision,
c_character_1 character varying(100),
c_varchar varchar(100),
c_character_2 character(100),
c_char_1 char(100),
c_character_3 character,
c_char_2 char,
c_text text,
c_nvarchar2 nvarchar2,
c_name name,
c_timestamp_1 timestamp without time zone ,
c_timestamp_2 timestamp with time zone,
c_date date
)compress
partition by range (c_varchar)
(
partition PARTITION_EXCHANGE_TABLE_005_1_1 values less than ('AAAA') ,
partition PARTITION_EXCHANGE_TABLE_005_1_2 values less than ('ZZZZ')
);
create index index_PARTITION_PARTITION_EXCHANGE_TABLE_005_1 on fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_1(c_smallint) local
(
partition PARTITION_EXCHANGE_TABLE_005_1_1 ,
partition PARTITION_EXCHANGE_TABLE_005_1_2
);
create table fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_2
(
c_smallint smallint,
c_integer integer,
c_bigint bigint,
c_decimal decimal,
c_numeric numeric,
c_real real,
c_double double precision,
c_character_1 character varying(100),
c_varchar varchar(100),
c_character_2 character(100),
c_char_1 char(100),
c_character_3 character,
c_char_2 char,
c_text text,
c_nvarchar2 nvarchar2,
c_name name,
c_timestamp_1 timestamp without time zone ,
c_timestamp_2 timestamp with time zone,
c_date date
);
create index index_PARTITION_EXCHANGE_TABLE_005_2 on fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_2(c_smallint);
alter table fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_1 exchange partition (PARTITION_EXCHANGE_TABLE_005_1_2) with table fvt_data_partition_exchange.PARTITION_EXCHANGE_TABLE_005_2 with validation;
--I1.创建分区表
create table fvt_data_partition_exchange.EXCHANGE_TABLE_023_1
(
c_smallint smallint,
c_integer integer default 0,
c_bigint bigint,
c_decimal decimal,
c_numeric numeric,
c_real real,
c_double double precision,
c_character_1 character varying(100),
c_varchar varchar(100),
c_character_2 character(100),
c_char_1 char(100),
c_character_3 character,
c_char_2 char,
c_text text,
c_nvarchar2 nvarchar2,
c_name name,
c_timestamp_1 timestamp without time zone,
c_timestamp_2 timestamp with time zone,
c_date date
)
partition by range (c_smallint,c_integer,c_bigint,c_decimal)
(
partition PARTITION_EXCHANGE_TABLE_023_1_1 values less than (0,0,0,0) ,
partition PARTITION_EXCHANGE_TABLE_023_1_2 values less than (3,30,300,400.3) ,
partition PARTITION_EXCHANGE_TABLE_023_1_3 values less than (6,60,600,800.6) ,
partition PARTITION_EXCHANGE_TABLE_023_1_4 values less than (10,100,1000,1100.2)
);
create index index_EXCHANGE_TABLE_023_1 on fvt_data_partition_exchange.EXCHANGE_TABLE_023_1(c_smallint) local;
--I2.创建普通表
create table fvt_data_partition_exchange.EXCHANGE_TABLE_023_2
(
c_smallint smallint,
c_integer integer,
c_bigint bigint,
c_decimal decimal,
c_numeric numeric,
c_real real,
c_double double precision,
c_character_1 character varying(100),
c_varchar varchar(100),
c_character_2 character(100),
c_char_1 char(100),
c_character_3 character,
c_char_2 char,
c_text text,
c_nvarchar2 nvarchar2,
c_name name,
c_timestamp_1 timestamp without time zone ,
c_timestamp_2 timestamp with time zone,
c_date date
);
create index index_EXCHANGE_TABLE_023_2 on fvt_data_partition_exchange.EXCHANGE_TABLE_023_2(c_smallint);
--I4.exchange表
alter table fvt_data_partition_exchange.EXCHANGE_TABLE_023_1 exchange partition (PARTITION_EXCHANGE_TABLE_023_1_1) with table fvt_data_partition_exchange.EXCHANGE_TABLE_023_2 without validation;
drop schema fvt_data_partition_exchange cascade;
drop schema columnar_storage cascade;
create schema columnar_storage;
create table columnar_storage.create_columnar_table_012 ( c_smallint smallint null,c_double_precision double precision,c_time_without_time_zone time without time zone null,c_time_with_time_zone time with time zone,c_integer integer default 23423,c_bigint bigint default 923423432,c_decimal decimal(19) default 923423423,c_real real,c_numeric numeric(18,12) null,c_varchar varchar(19),c_char char(57) null,c_timestamp_with_timezone timestamp with time zone,c_char2 char default '0',c_text text null,c_varchar2 varchar2(20),c_timestamp_without_timezone timestamp without time zone,c_date date,c_varchar22 varchar2(11621),c_numeric2 numeric null ) distribute by hash(c_date)
partition by range(c_smallint)(
partition create_columnar_table_partition_p1 values less than(1),
partition create_columnar_table_partition_p2 values less than(3),
partition create_columnar_table_partition_p3 values less than(7),
partition create_columnar_table_partition_p4 values less than(2341),
partition create_columnar_table_partition_p5 values less than(11121),
partition create_columnar_table_partition_p6 values less than(22222)) ;
alter table columnar_storage.create_columnar_table_012 add partition partition_create_columnar_table_012_07 values less than(maxvalue);
copy columnar_storage.create_columnar_table_012 from '@abs_srcdir@/data/exchange.data';
select count(*) from columnar_storage.create_columnar_table_012 partition(create_columnar_table_partition_p5);
select avg((length(upper(trim(both 'y' from c_varchar22)))))
from columnar_storage.create_columnar_table_012 partition(create_columnar_table_partition_p5) where
mod(abs(hashint4(c_integer)),1024) in (0,1,6,7,12,13,18,19,24,25,30,31,36,37,42,43,48,49,54,55,60,61,66,67,72,73,78,79,84,85,90,91,96,97,102,103,108,109,114,115,120,121,126,127,132,133,138,139,144,145,150,151,156,157,162,163,168,169,174,175,180,181,186,187,192,193,198,199,204,205,210,211,216,217,222,223,228,229,234,235,240,241,246,247,252,253,258,259,264,265,270,271,276,277,282,283,288,289,294,295,300,301,306,307,312,313,318,319,324,325,330,331,336,337,342,343,348,349,354,355,360,361,366,367,372,373,378,379,384,385,390,391,396,397,402,403,408,409,414,415,420,421,426,427,432,433,438,439,444,445,450,451,456,457,462,463,468,469,474,475,480,481,486,487,492,493,498,499,504,505,510,511,8,649,654,655,660,661,666,667,672,672673,678,679,684,685,690,691,696,697,702,703,708,709,714,715,720,721,726,727,732,733,738,739,744,745,750,751,756,757,762,763,768,769,774,775,780,781,786,787,792,793,798,799,804,805,810,811,816,817,822,823,828,829,834,835,840,841,846,847,852,853,858,859,864,865,870,871,876,877,882,883,888,889,894,895,900,901,906,907,912,913,918,919,924,925,930,931,936,937,942,943,948,949,954,955,960,961,966,967,972,973,978,979,984,985,990,991,996,997,1002,1003,1008,1009,1014,1015,1020,0,1,34,6,5,787,434,345);
create table columnar_storage.create_columnar_normal_table_012(like columnar_storage.create_columnar_table_012 including defaults) distribute by hash(c_date);
alter table columnar_storage.create_columnar_table_012 exchange partition(create_columnar_table_partition_p5) with table columnar_storage.create_columnar_normal_table_012;
select count(*) from columnar_storage.create_columnar_normal_table_012;
select avg((length(upper(trim(both 'y' from c_varchar22))))) from columnar_storage.create_columnar_normal_table_012 where mod(abs(hashint4(c_integer)),1024) in (0,1,6,7,12,13,18,19,24,25,30,31,36,37,42,43,48,49,54,55,60,61,66,67,72,73,78,79,84,85,90,91,96,97,102,103,108,109,114,115,120,121,126,127,132,133,138,139,144,145,150,151,156,157,162,163,168,169,174,175,180,181,186,187,192,193,198,199,204,205,210,211,216,217,222,223,228,229,234,235,240,241,246,247,252,253,258,259,264,265,270,271,276,277,282,283,288,289,294,295,300,301,306,307,312,313,318,319,324,325,330,331,336,337,342,343,348,349,354,355,360,361,366,367,372,373,378,379,384,385,390,391,396,397,402,403,408,409,414,415,420,421,426,427,432,433,438,439,444,445,450,451,456,457,462,463,468,469,474,475,480,481,486,487,492,493,498,499,504,505,510,511,8,649,654,655,660,661,666,667,672,672673,678,679,684,685,690,691,696,697,702,703,708,709,714,715,720,721,726,727,732,733,738,739,744,745,750,751,756,757,762,763,768,769,774,775,780,781,786,787,792,793,798,799,804,805,810,811,816,817,822,823,828,829,834,835,840,841,846,847,852,853,858,859,864,865,870,871,876,877,882,883,888,889,894,895,900,901,906,907,912,913,918,919,924,925,930,931,936,937,942,943,948,949,954,955,960,961,966,967,972,973,978,979,984,985,990,991,996,997,1002,1003,1008,1009,1014,1015,1020,0,1,34,6,5,787,434,345);
drop schema columnar_storage cascade;