如何在Amazon Redshift中创建索引 [英] How to create an Index in Amazon Redshift
问题描述
我正在尝试在Amazon Redshift中创建索引但我收到错误
I'm trying to create indexes in Amazon Redshift but I received an error
create index on session_log(UserId);
UserId
是一个整数字段。
推荐答案
如果您尝试在Redshift表上创建索引(带名称):
If you try and create an index (with a name) on a Redshift table:
create index IX1 on "SomeTable"("UserId");
您将收到错误
执行SQL命令时发生错误:
在SomeTable上创建索引IX1(UserId)
错误:SQL命令创建索引IX1 onSomeTable( Redshift表不支持UserId)。
An error occurred when executing the SQL command: create index IX1 on "SomeTable"("UserId") ERROR: SQL command "create index IX1 on "SomeTable"("UserId")" not supported on Redshift tables.
这是因为,像其他,红移使用data仓库href=\"http://docs.aws.amazon.com/redshift/latest/dg/c_columnar_storage_disk_mem_mgmnt.html\">列式存储,因此,其他RDBMS中使用的许多索引技术(如添加非聚集索引)都不适用。
This is because, like other data warehouses, Redshift uses columnar storage, and as a result, many of the indexing techniques (like adding non-clustered indexes) used in other RDBMS aren't applicable.
但是你会这样做可以选择为每个表提供一个排序键,您还可以使用分发键,并选择合适的压缩编码,以最大限度地减少存储和I / O开销。
You do however have the option of providing a single sort key per table, and you can also influence performance with a distribution key for sharding your data, and selecting appropriate compression encodings for each column to minimize storage and I/O overheads.
例如,在您的情况下,您可以选择使用 UserId
作为排序键:
For example, in your case, you may elect to use UserId
as a sort key:
create table if not exists "SomeTable"
(
"UserId" int,
"Name" text
)
sortkey("UserId");
You might want to read a few primers like these
这篇关于如何在Amazon Redshift中创建索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!