COPY当使用命令进入具有 4 个排序键的交错表时，我们发现性能下降。当我删除交错选项时，副本很快就完成了。
这些是将数据从 S3 复制到 Redshift 的统计数据：
我们加载了 7000 万条记录
交错排序键 - 52 分钟复合排序键 - 8 分钟无排序键 - 7 分钟
我们的排序键有 4 列。我们还有一个DIST KEY。
谁能解释为什么将性能复制到交错排序键时性能不佳？
当我尝试使用 db2se 导入 shapefile 时，我在语法中指定要加载的输入列作为 COLUMN NAME
db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns N(Cat_Zones) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1
GSE2112N The column "CAT_ZONES" cannot be found in the dBASE file.
但是，如果我用 POSITIONAL 参数替换它，它就可以工作。
db2se import_shape <dbname> -userId <username>
-pw <password> -fileName <shapefile to be loaded> -inputAttrColumns P(1) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\ABC1.txt -client 1
GSE0000I The operation was completed successfully.
发生这种情况的任何原因。shapefile 信息如下。
db2se shape_info -filename <shapefile to be loaded>
Shape file information
----------------------
File code = 9994
File length (16-bit words) = 2533850
Shape file version = 1000
Shape type = 5 (ST_MULTIPOLYGON)
Number of records = 2636
Minimum X coordinate = -173.109762
Maximum X coordinate = -66.954002
Minimum Y coordinate = 18.915497
Maximum Y coordinate = 71.398048
Shapes do not have Z coordinates.
Shapes do not have M coordinates.
Shape index file (extension .shx) is present.
Attribute file information
--------------------------
dBase file code = 3
Date of last update = 2013-09-03
Number of records = 2636
Number of bytes in header = 97
Number of bytes in each record = 305
Number of columns = 2
Column Number Column Name Data Type Length Decimal
------------- --------------- -------------- ------ -------
1 Cat_Zones C ( Character) 254 0
2 Country C ( Character) 50 0
Coordinate system definition: "GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID
["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.01
74532925199433],METADATA["World",-180.0,-90.0,180.0,90.0,0.0,0.0174532925199433,
0.0,1262]]"
当我尝试使用 db2se import_shape 文件实用程序导入 shapefile 时，
db2se import_shape <dbname> -userId <userid> -pw <password> -fileName U:\ShapeFiles\ShapeFilesMarch2014\SubLimits\Africa_Criteria_Data\AF-CIV.shp -inputAttrColumns N(FIPS_CNTRY) -srsName WGS84_SRS_1003 -tableSchema GEXPDBA -tableName TPOLYGON_STAGING -tableAttrColumns VC_FLD_1 -createTableFlag 0 -spatialColumn POLYGON_OB -typeSchema db2gse -typeName st_multipolygon -messagesFile C:\AF-CIV.txt -client 1
GSE0206N A SELECT statement failed. SQLERROR = "SQL0805N Package "DB2GSE.GSEIEIMP 0X4141414141644459" was not found. SQLSTATE=51002".
知道为什么会发生此错误吗？我在所有论坛上都试过了，但无法调试它。
如果在 MQT 中，如果我对同一个表有 2 个引用约束（角色扮演），使用其中一个约束不允许优化查询
例子
事实_销售额
DIM_BILL_TO_ID --> DIM_ADDRESS
DIM_SHIP_TO_ID --> DIM_ADDRESS
销售额
DIM_ADDRESS
DIM_ADDRESS_ID
国家的名字
我使用 sql 查询定义创建了一个 MQT
MQT 1
选择 COUNTRY_NAME，SUM(SALES_AMOUNT) FROM FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_BILL_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID GROUP BY COUNTRY_NAME
触发查询时，会自动进行查询重写并使用 MQT
可以说我的定义是
MQT 2
选择 a1.COUNTRY_NAME 作为 BILL_TO_NAME，a2.COUNTRY_NAM 作为 SHIP_TO_NAME，SUM(SALES_AMOUNT) FROM FACT_SALES INNER JOIN DIM_ADDRESS a1 ON FACT_SALES.DIM_BILL_TO_ID = a1.DIM_ADDRESS_ID INNER JOIN DIM_ADDRESS a2 ON FACT_SALES2.ADM_ID =SHIP
按 a1.COUNTRY_NAME,a2.COUNTRY_NAME 分组
并且被解雇的查询是
选择 COUNTRY_NAME，SUM(SALES_AMOUNT) FROM FACT_SALES INNER JOIN DIM_ADDRESS ON FACT_SALES.DIM_SHIP_TO_ID = DIM_ADDRESS.DIM_ADDRESS_ID GROUP BY COUNTRY_NAME
查询重写不会发生......但是如果我在查询中使用 BILL_TO_ID，那么查询重写就会发生......
是否存在 DB2 优化器无法理解角色扮演列的任何逻辑？