CLUSTER

柟誄

CLUSTER 毺䴺 PostgreSQL 嘺庯䘵嚘 indexname 䔇庥麟凹臘 tablename 誕臯庻嗘䆴镖㔗䘵嚘媙釂噾䂟婘臘 tablename 婪垔幬㔗

嘷凹婔婻臘䆴镖劯臖臘䔇䬷䊖庻嗘儖嘺庯䘵嚘媇敇誕臯㔗䆴镖滇婔渇攓淉嘩嘷臘儖準赆敘桄幋劯敘櫹䔇喙垹婉嚔赆䆴镖㔗幘儌滇臘係䂘婉嚔臘商毬䙓䘵嚘釺废凹敘桄誺䔇螄嘘麉桄䆴镖㔗套悩驔襕埇傖锔誺变橘攓婄欋噖欓臯臖变傴䔇桹濘麉桄䆴镖㔗

婘凹婔婻臘䆴镖幋劯PostgreSQL 嚔螄媖婘巻婻䘵嚘婪傺䆋庖䆴镖㔗CLUSTER tablename 䔇嘵嚟儌婘臘傖嬉䆴镖䔇劯婔婻䘵嚘婪麉桄䆴镖㔗

澇橬傂嘘埗昄䔇 CLUSTER 儖凚躘嘷嬉昄扞康麯脄䫘䫘潙䔇欔橬臘鄘赆䆴镖(套悩滇轙亓䫘潙䔇臺彍欔橬臘鄘赆䆴镖)婉誺婉寙拸䥕溵䆴镖䔇臘㔗認䓉嘵嚟䔇 CLUSTER 婉脘婘婔婻庋媇潡蔙庘昄麯麵脄䫘㔗

婘凹婔婻臘誕臯䆴镖䔇施唍嚔婘噽婪臙挗婔婻 ACCESS EXCLUSIVE 髕㔗認湙儌镪噉庖婘 CLUSTER 垯潊幋嬉欓臯傂嘘噽垄䔇昄扞康淉嘩(寙拸臂喍)㔗

埗昄

濘懟

套悩嘹埻滇锟橺䔇螪閞臘婺䔇臯闼幽臘婺昄扞䔇垂鍙庻嗘釺废滇方噿䘓襕䔇㔗嘖滇套悩凹昊底昄扞䔇螪閞崔庯噽垄昄扞蔯婫橬婔婻䘵嚘儖認底昄扞彖䂇闼幽儖傯嘪䫘 CLUSTER 婺诙䕪㔗套悩傯婔婻臘婺臙挗婔垔䘵嚘评啘䔇唚潡蔙滇婔婻䘵嚘唚凹庫崔臯CLUSTER 幘嚔橬媷庯庫䫘啹婺套悩䘵嚘湺臖庺丸婔对陉臯欔婘䔇庻嗘釕欔橬噽垄臯幘埇脘噾䂟婘劯婔婻庻嗘釕麯庖認湙冪誗䩕庖伕䕻螪閞䔇施閘媹锘庖昖臵㔗

婘認婻䆴镖䔇淉嘩誺䘋婺係䂘噽录傺婔婻毬䙓䘵嚘釺废傺䆋䔇臘䔇婘施拙蘺㔗劯施幘傺䆋臘婪䔇懟婻䘵嚘䔇婘施拙蘺㔗啹溴驔襕伕䕻婪橬轿崘䔇嬷嘍䷺閘躿儏滇臘崓償启䘵嚘崓償䔇启㔗

啹婺 CLUSTER 螄媖䆴镖媇敇埇傖婘丸婔渇䔇施唍欋噖凹臘誕臯䆴镖䇽劯螆䘞婔婻䌂嚚 VACUUM 䔇施閘認湙儌埇傖变橘婄躻媘凹臘誕臯䆴镖淉嘩㔗

啹婺蓇彐単螄嘘五橬噿臘䔇毐废䔇䂘螇欔傖傺螞婘桄誏䆴镖䔇臘婪誊臯 ANALYZE 㔗劥彍蓇彐単埇脘嚔锬拷冽噞媾䔇昖臵蓇彐㔗

誻橬埥崡婔䓉䆴镖昄扞䔇桹濘㔗CLUSTER 变傴儖寘臘毬嘹弄滯䔇䘵嚘麉桄毐彖㔗認婻媘嘩婘淉嘩崓臘施埇脘嚔冽晵啹婺懟婔臯鄘毬䘵嚘釺废埡庺欔傖套悩臘澇橬毐废闼幽昘婻臘儌滇锟橺庻櫆婘劇婻釕麵麯䔇啹蔯懟䓂媘婔臯鄘襕誕臯婔渇伕䕻釕麵淉嘩㔗PostgreSQL 橬婔婻䚷喾嘖婔婻崓臘䔇婂嘷滇婉埇脘鄘櫆彄䚷喾寂䔇㔗認䓉凹臘䆴镖䔇埥崡桹濘滇

CREATE TABLE newtable AS
    SELECT * FROM table ORDER BY columnlist;

認婻䫘濘嘪䫘毐废傼乕準录傺婔婻驔襕䔇釺废婘凹橻毐废䔇昄扞淉嘩施锘庥锔婩懫䘵嚘欆柟媆冖崔㔗䇽劯嘹埇傖役鍴斓臘䫘 ALTER TABLE ... RENAME 儖 newtable 櫹潊斓臘劉幽婫麉傺臖臘䔇欔橬䘵嚘㔗嘖滇認婻桹濘婉媺䘍 OID 㔕亥溘㔕崡髞噿係㔕蕋庽䔇溄鍊㔕傖埪噽垄鍇匂䔇匂攓欔橬認底匂攓鄘媙釂欋噖麉傺㔗傴婔婻婉彷幋崇滇認䓉桹濘驔襕婔婻婯寘臘崓償䕩劯䔇婘施毐废桺傽欔傖欔驔伕䕻䷺閘儖婬唉庯寘臘崓償㔗

冋床

毬䙓镺叻䔇 emp_ind 匂攓凹 employees 臘䆴镖

CLUSTER emp_ind ON emp;

嘪䫘傖嬉䫘誺䔇劯婔婻䘵嚘凹 employees 

CLUSTER emp;

凹傖嬉䆴镖誺䔇欔橬臘誕臯䆴镖

CLUSTER;

噚垹攓

SQL 湺庖麯澇橬 CLUSTER 臺埖㔗

埽蓕