建表 分区 索引分区

create table nps_user_kpi(
msisdn                          VARCHAR2(64),
age                             NUMBER,
gender                          char(6),
two_i2c                             VARCHAR2(64),
stars                           NUMBER,
duration                        NUMBER,
b_contract                      char(6),
b_fusion                        char(6),
b_int_wo                        char(6),
contract_type                   VARCHAR2(64),
contract_start                  VARCHAR2(64),
contract_end                    VARCHAR2(64),
package_type                    NUMBER,
arpu_t                          NUMBER,
bill_network_t                  VARCHAR2(64),
low_speed_n                     NUMBER,
low_speed_d                     NUMBER,
avg_call_tcp_link_latency_n     NUMBER,
avg_call_tcp_link_latency_d     NUMBER,
tcp_call_n                      NUMBER,
tcp_call_succ                   NUMBER,
avg_py_tcp_link_latency_n       NUMBER,
avg_py_tcp_link_latency_d       NUMBER,
tcp_py_n                        NUMBER,
tcp_py_succ                     NUMBER,
avg_call_page_show_latency_n    NUMBER,
avg_call_page_show_latency_d    NUMBER,
avg_call_page_resp_latency_n    NUMBER,
avg_call_page_resp_latency_d    NUMBER,
http_call_present_succ          NUMBER,
http_call_response_succ         NUMBER,
http_call_res_pre_d             NUMBER,
avg_py_page_show_latency_n      NUMBER,
avg_py_page_show_latency_d      NUMBER,
avg_py_page_resp_latency_n      NUMBER,
avg_py_page_resp_latency_d      NUMBER,
http_py_present_succ            NUMBER,
http_py_response_succ           NUMBER,
http_py_res_pre_d               NUMBER,
avg_speed_n                     NUMBER,
avg_speed_d                     NUMBER,
shopping                        NUMBER,
news                            NUMBER,
games                           NUMBER,
videos                          NUMBER,
socialities                     NUMBER,
others                          NUMBER,
dl_rate_data                    NUMBER,
p_http_re_succ                  NUMBER,
p_http_dis_succ                 NUMBER,
p_http_re_latency               NUMBER,
p_http_dis_latency              NUMBER,
c_http_re_succ                  NUMBER,
c_http_dis_succ                 NUMBER,
c_http_re_latency               NUMBER,
c_http_dis_latency              NUMBER,
p_tcp_succ                      NUMBER,
p_tcp_latency                   NUMBER,
c_tcp_succ                      NUMBER,
c_tcp_latency                   NUMBER,
low_rate                        NUMBER,
IMSI                            VARCHAR2(64),
emergency_drop                  NUMBER,
s_rsrp105_n                     NUMBER,
s_rsrp110_n                     NUMBER,
s_rsrp115_n                     NUMBER,
total                           NUMBER,
avg_rspr_n                      NUMBER,
avg_rspq_n                      NUMBER,
indoor_use_n                    NUMBER,
s_rsrp105                       NUMBER,
s_rsrp110                       NUMBER,
s_rsrp115                       NUMBER,
avg_rspr                        NUMBER,
avg_rspq                        NUMBER,
indoor_use                      NUMBER,
beauty_http_re_succ             NUMBER,
beauty_http_dis_succ            NUMBER,
beauty_http_re_latency          NUMBER,
beauty_http_dis_latency         NUMBER,
beauty_tcp_succ                 NUMBER,
beauty_tcp_latency              NUMBER,
trans_hub_http_re_succ          NUMBER,
trans_hub_http_dis_succ         NUMBER,
trans_hub_http_re_latency       NUMBER,
trans_hub_http_dis_latency      NUMBER,
trans_hub_tcp_succ              NUMBER,
trans_hub_tcp_latency           NUMBER,
office_http_re_succ             NUMBER,
office_http_dis_succ            NUMBER,
office_http_re_latency          NUMBER,
office_http_dis_latency         NUMBER,
office_tcp_succ                 NUMBER,
office_tcp_latency              NUMBER,
public_http_re_succ             NUMBER,
public_http_dis_succ            NUMBER,
public_http_re_latency          NUMBER,
public_http_dis_latency         NUMBER,
public_tcp_succ                 NUMBER,
public_tcp_latency              NUMBER,
college_http_re_succ            NUMBER,
college_http_dis_succ           NUMBER,
college_http_re_latency         NUMBER,
college_http_dis_latency        NUMBER,
college_tcp_succ                NUMBER,
college_tcp_latency             NUMBER,
other_http_re_succ              NUMBER,
other_http_dis_succ             NUMBER,
other_http_re_latency           NUMBER,
other_http_dis_latency          NUMBER,
other_tcp_succ                  NUMBER,
other_tcp_latency               NUMBER,
month                           NUMBER,
mou                             NUMBER,
dou                             NUMBER,
voice_package                   NUMBER,
data_package                    NUMBER,
cnt_unicom                      NUMBER,
freq_workday                    NUMBER,
freq_weekend                    NUMBER,
initiative_ratio                NUMBER,
avg_time                        NUMBER,
all_day_time                    NUMBER,
single_day_time                 NUMBER,
all_night_time                  NUMBER,
single_night_time               NUMBER,
b_voice_change                  NUMBER,
b_data_change                   NUMBER
)
PARTITION BY RANGE(month)
(
PARTITION kpi_201803 VALUES LESS THAN(201804) TABLESPACE nps,
PARTITION kpi_201804 VALUES LESS THAN(201805) TABLESPACE nps,
PARTITION kpi_201805 VALUES LESS THAN(201806) TABLESPACE nps,
PARTITION kpi_201806 VALUES LESS THAN(201807) TABLESPACE nps,
PARTITION kpi_201807 VALUES LESS THAN(201808) TABLESPACE nps,
PARTITION kpi_201808 VALUES LESS THAN(201809) TABLESPACE nps,
PARTITION kpi_201809 VALUES LESS THAN(201810) TABLESPACE nps,
PARTITION kpi_201810 VALUES LESS THAN(201811) TABLESPACE nps,
PARTITION kpi_201811 VALUES LESS THAN(201812) TABLESPACE nps,
PARTITION kpi_201812 VALUES LESS THAN(201901) TABLESPACE nps,
PARTITION kpi_201901 VALUES LESS THAN(201902) TABLESPACE nps,
PARTITION kpi_201902 VALUES LESS THAN(201903) TABLESPACE nps,
PARTITION kpi_201903 VALUES LESS THAN(201904) TABLESPACE nps,
PARTITION kpi_201904 VALUES LESS THAN(201905) TABLESPACE nps
);

create index kpi_index on nps_user_kpi(msisdn)
local
(
PARTITION kpi_201803 TABLESPACE nps,
PARTITION kpi_201804 TABLESPACE nps,
PARTITION kpi_201805 TABLESPACE nps,
PARTITION kpi_201806 TABLESPACE nps,
PARTITION kpi_201807 TABLESPACE nps,
PARTITION kpi_201808 TABLESPACE nps,
PARTITION kpi_201809 TABLESPACE nps,
PARTITION kpi_201810 TABLESPACE nps,
PARTITION kpi_201811 TABLESPACE nps,
PARTITION kpi_201812 TABLESPACE nps,
PARTITION kpi_201901 TABLESPACE nps,
PARTITION kpi_201902 TABLESPACE nps,
PARTITION kpi_201903 TABLESPACE nps,
PARTITION kpi_201904 TABLESPACE nps
);

查询dba_ind_partitions视图来查看索引分区信息

select partition_name,tablespace_name from dba_ind_partitions where index_name='KPI_INDEX';