205
HybridDB for PostgreSQL排序鍵使用
排序鍵是什麼
排序鍵是表的一種屬性，可以將數據按照排序鍵順序存儲在磁盤文件中。
排序鍵主要有兩大優勢：(1) 加速列存優化，收集的min、max元信息很少重疊，過濾性很好 (2) 對於含有order by和group by等需要排序的SQL可以避免再次排序，直接從磁盤中讀取出來就是滿足條件的有序數據
創建表
Command: CREATE TABLE
Description: define a new table
Syntax:
CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name (
[ { column_name data_type [ DEFAULT default_expr ] [column_constraint [ ... ]
[ ENCODING ( storage_directive [,...] ) ]
]
| table_constraint
| LIKE other_table [{INCLUDING | EXCLUDING}
{DEFAULTS | CONSTRAINTS}] ...}
[, ... ] ]
[column_reference_storage_directive [, ] ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter=value [, ... ] )
[ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ]
[ TABLESPACE tablespace ]
[ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ]
[ SORTKEY (column, [ ... ] )]
[ PARTITION BY partition_type (column)
[ SUBPARTITION BY partition_type (column) ]
[ SUBPARTITION TEMPLATE ( template_spec ) ]
[...]
( partition_spec )
| [ SUBPARTITION BY partition_type (column) ]
[...]
( partition_spec
[ ( subpartition_spec
[(...)]
) ]
)
樣例:
create table test(date text, time text, open float, high float, low float, volume int) with(APPENDONLY=true,ORIENTATION=column) sortkey (volume);
對表進行排序
VACUUM SORT ONLY [tablename]
修改排序鍵
這個命令隻改catalog不會對數據立即排序，需要通過vaccum sort only命令排序。
ALTER [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name SET SORTKEY (column, [ ... ] )
樣例:
alter table test set sortkey (high,low);
最後更新：2017-09-13 14:33:39