数据加载完成以后，通常需要更新分析计划，因为单独做一个表的Analyze比较麻烦，整库做Analyze会比较耗时，我们可以通过下面这个脚本，指定Analyze部分表。具体实践时，只需修改下面对应的SQL语句、数据库psql命令地址、IP端口及数据库名即可：
#!/bin/bash # filename: analyze_table.sh # start time start_time=$(date) echo "-------- Start time is $start_time --------" start_seconds=$(date +%s) # get no partition tables cmd_get_nopartitions="select schemaname||'.'||tablename as tablename from pg_tables where schemaname like 'rwnas_%' and tablename not like '%_1_prt_%' and schemaname||'.'||tablename not in (select schemaname||'.'||tablename from pg_partitions group by schemaname,tablename)" exec_get_nopartitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_nopartitions}") echo "${exec_get_nopartitions}" > nopartitions_tables.txt echo "-------- No Partitions Tables List Below --------" nopartitions_file=$(cat nopartitions_tables.txt) echo "$nopartitions_file" # analyze nopartition tables rm -rf analyze_nopartitions_tables.txt function for_np_file(){ for i in $nopartitions_file do echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $i")" echo "$i" >> analyze_nopartitions_tables.txt done } for_np_file # get partition tables cmd_get_partitions="select schemaname||'.'||tablename as tablename from pg_partitions where schemaname like 'rwnas_%' group by schemaname, tablename" exec_get_partitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_partitions}") echo "${exec_get_partitions}" > partitions_tables.txt echo "-------- Partitions Tables List Below --------" partitions_file=$(cat partitions_tables.txt) echo "$partitions_file" # analyze partition tables rm -rf analyze_partitions_tables.txt function for_p_file(){ for q in $partitions_file do echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $q")" echo "$q" >> analyze_partitions_tables.txt done } for_p_file # end time end_time=$(date) echo "-------- End time is $end_time --------" end_seconds=$(date +%s) diff=$((end_seconds - start_seconds)) echo "Total $diff seconds." 相关资源：敏捷开发V1.0.pptx