CREATE INDEX

中文man手册

目录

NAME CREATE INDEX
SYNOPSIS .sp .nf CREATE [ UNIQUE ] INDEX
DESCRIPTION 描述
PARAMETERS 参数
NOTES 注意
EXAMPLES 例子
COMPATIBILITY 兼容性
译者
跋

CREATE INDEX

NAME
SYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
NOTES æ³¨æ
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
è¯è
è·

NAME

CREATE INDEX - å®ä¹ä¸ä¸ªæ°ç´¢å¼

SYNOPSIS

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ WHERE predicate ]

DESCRIPTION æè¿°

CREATE INDEX å¨æå®çè¡¨ä¸æé ä¸ä¸ªåä¸º index_name çç´¢å¼ãç´¢å¼ä¸»è¦ç¨æ¥æé«æ°æ®åºæ§è½ãä½æ¯å¦æä¸æ°å½çä½¿ç¨å°å¯¼è´æ§è½çä¸éã

ç´¢å¼çé®ååæ®µæ¯ä»¥åæ®µåçæ¹å¼å£°æçï¼æèæ¯å¯éçåå¨ä¸ä¸ªåæ¬å¼§éé¢çè¡¨è¾¾å¼ã å¦æç´¢å¼æ¹å¼æ¯æå¤ä¸ªåæ®µç´¢å¼ï¼é£ä¹æä»¬ä¹å¯ä»¥å£°æå¤ä¸ªåæ®µã

ä¸ä¸ªç´¢å¼åæ®µå¯ä»¥æ¯ä¸ä¸ªä½¿ç¨è¡¨çè¡çä¸ä¸ªæå¤ä¸ªå- æ®µçæ°å¼è¿è¡è®¡ç®çè¡¨è¾¾å¼ã æ´ä¸ªç¹æ§å¯ç¨äºè·åå¯¹åºæ¬æ°æ®æç§åå½¢çå¿«éè®¿é®ã æ¯å¦ï¼ä¸ä¸ªå¨ upper(col) ä¸çå½æ°ç´¢å¼å°åè®¸åå¥ WHERE upper(col) = ’JIM’ ä½¿ç¨ç´¢å¼ã

PostgreSQL ä¸ºä»ç´¢å¼æä¾ B-treeï¼R-treeï¼hashï¼æ£åï¼ å GiST ç´¢å¼æ¹æ³ã B-tree ç´¢å¼æ¹æ³æ¯ä¸ä¸ª Lehman-Yao é«å¹¶å B-trees çå® ç°ãR-tree ç´¢å¼æ¹æ³ç¨ Guttman çäºæ¬¡åè£ç®æ³å®ç°äºæ åç R-treesã hashï¼æ£åï¼ç´¢å¼æ¹æ³æ¯ Litwin ççº¿æ§æ£åçä¸ä¸ªå®ç°ã ç¨æ·ä¹å¯ä»¥å®ä¹å®ä»¬èªå·±çç´¢å¼æ¹æ³ï¼ä½è¿ä¸ªå·¥ä½ç¸å½å¤æã

å¦æåºç°äº WHERE åå¥ï¼ååå»ºä¸ä¸ªé¨åç´¢å¼ã é¨åç´¢å¼æ¯ä¸ä¸ªåªåå«è¡¨çä¸é¨åè®°å½çç´¢å¼ï¼éå¸¸æ¯è¯¥è¡¨ä¸- æè®©äººæå´è¶£çé¨åã æ¯å¦ï¼å¦æä½ æä¸ä¸ªè¡¨ï¼éé¢åå«å·²ä¸è´¦åæªä¸è´¦çå®åï¼ æªä¸è´¦çå®ååªå è¡¨çä¸å°é¨åèä¸è¿é¨åæ¯æå¸¸ç¨çé¨åï¼ é£ä¹ä½ å°±å¯ä»¥éè¿åªå¨è¿ä¸ªé¨ååå»ºä¸ä¸ªç´¢å¼æ¥æ¹åæ§è½ã å¦å¤ä¸ä¸ªå¯è½çç¨éæ¯ç¨ WHERE å UNIQUE å¼ºå¶ä¸ä¸ªè¡¨çæä¸ªåéçå¯ä¸æ§ã

å¨ WHERE åå¥éç¨çè¡¨è¾¾å¼åªè½å¼ç¨ä¸å±è¡¨çåæ®µï¼ä½æ¯å®å¯ä»¥ä½¿ç¨ææå- æ®µï¼èä¸ä»ä»æ¯è¢«ç´¢å¼çåæ®µï¼ã ç®åï¼å- æ¥è¯¢åèéè¡¨è¾¾å¼ä¹ä¸è½åºç°å¨WHEREéã

ç´¢å¼å®ä¹éçææå½æ°åæä½ç¬¦é½å¿é¡»æ¯immutableï¼ï¼ä¸åçï¼ä¹å°±æ¯è¯´ï¼ å®ä»¬çç»æå¿é¡»åªè½ä¾èµäºå®ä»¬çè¾å¥åæ°ï¼èå³ä¸è½ä¾èµä»»ä½å¤é¨çå½±åï¼æ¯å¦å¦å¤ä¸ä¸ªè¡¨çåå®¹æèå½åæ¶é´ï¼ã è¿ä¸ªçº¦æç¡®ä¿è¯¥ç´¢å¼çè¡ä¸ºæ¯å®ä¹å®æ´çãè¦å¨ä¸ä¸ªç´¢å¼ä¸ä½¿ç¨ç¨æ·å®ä¹å½æ°ï¼è¯·è®°ä½å¨ä½åå»ºå®çæ¶åæå®æè®°ä¸ºimmutableçå½æ°ã

PARAMETERS åæ°

UNIQUE

ä»¤ç³»ç»æ£æµå½ç´¢å¼åå»ºæ¶ï¼å¦ææ°æ®å·²ç»å- å¨ï¼åæ¯æ¬¡æ·»å æ°æ®æ¶è¡¨ä¸æ¯å¦æéå¤å¼ã å¦ææå¥ææ´æ°çå¼ä¼å¯¼è´éå¤çè®°å½æ¶å°çæä¸ä¸ªéè¯¯ã

name

è¦åå»ºçç´¢å¼åãè¿éä¸è½åå«æ¨¡å¼åï¼ ç´¢å¼æ»æ¯å¨åä¸ä¸ªæ¨¡å¼ä¸- ä½ä¸ºå¶ç¶è¡¨åå»ºçã

table

è¦ç´¢å¼çè¡¨åï¼å¯è½ææ¨¡å¼ä¿®é¥°ï¼ã

method

ç¨äºç´¢å¼çæ¹æ³çååãå¯éçååæ¯ btreeï¼ hashï¼rtreeï¼å gistãç¼ºçæ¹æ³æ¯ btreeã

column

è¡¨çå/åæ®µåã

expression

ä¸ä¸ªåºäºè¯¥è¡¨çä¸ä¸ªæå¤ä¸ªåæ®µçè¡¨è¾¾å¼ã è¿ä¸ªè¡¨è¾¾å¼éå¸¸å¿é¡»å¸¦çåæ¬å¼§åå´ååºï¼å¦è¯æ³ä¸æ¾ç¤ºé£æ ·ã ä¸è¿ï¼å¦æè¡¨è¾¾å¼æå½æ°è°ç¨çå½¢å¼ï¼é£ä¹åæ¬å¼§å¯ä»¥çç¥ã

opclass

ä¸ä¸ªå³èçæä½ç¬¦è¡¨ãåéä¸æè·åç»èã

predicate

ä¸ºä¸ä¸ªé¨åç´¢å¼å®ä¹çº¦æè¡¨è¾¾å¼ã

NOTES æ³¨æ

åé ‘‘Indexes’’ è·åæå³ä½æ¶ä½¿ç¨ç´¢å¼ï¼ä½æ¶ä¸ä½¿ç¨ç´¢å¼ï¼ ä»¥ååªç§æåµä¸æ¯æç¨çä¿¡æ¯ã

ç®åï¼åªæ B-tree å gist ç´¢å¼æ¹æ³æ¯æå¤åæ®µç´¢å¼ã ç¼ºçæ¶æå¤å¯ä»¥å£°æ 32 ä¸ªé®åï¼è¿ä¸ªéå¶å¯ä»¥å¨å¶ä½ PostgreSQL æ¶ä¿®æ¹ï¼ã ç®ååªæ B-tree æ¯æå¯ä¸ç´¢å¼ã

å¯ä»¥ä¸ºç´¢å¼çæ¯ä¸ªå/åæ®µå£°æä¸ä¸ª æä½ç¬¦è¡¨ã æä½ç¬¦è¡¨æ è¯å°è¦è¢«è¯¥ç´¢å¼ç¨äºè¯¥å/åæ®µçæä½ç¬¦ã ä¾å¦ï¼ ä¸ä¸ªååèæ´æ°ç B-tree ç´¢å¼å°ä½¿ç¨ int4_ops è¡¨ï¼ è¿ä¸ªæä½ç¬¦è¡¨åæ¬ååèæ´æ°çæ¯è¾å½æ°ã å®éä¸ï¼è¯¥åçæ°æ®ç±»åçç¼ºçæä½ç¬¦è¡¨ä¸è¬å°±è¶³å¤äºã æäºæ°æ®ç±»åææä½ç¬¦è¡¨çåå æ¯ï¼å®ä»¬å¯è½æå¤äºä¸ä¸ªçææä¹çé¡ºåºã ä¾å¦ï¼æä»¬å¯¹å¤æ°ç±»åæåºæ¶æå¯è½ä»¥ç»å¯¹å¼æèä»¥å®é¨ã æä»¬å¯ä»¥éè¿ä¸ºè¯¥æ°æ®ç±»åå®ä¹ä¸¤ä¸ªæä½ç¬¦è¡¨ï¼ç¶åå¨å»ºç«ç´¢å¼çæ¶åéæ©åéçè¡¨æ¥å®ç°ã æå³æä½ç¬¦è¡¨æ´å¤çä¿¡æ¯å¨ ‘‘Operator Classes’’ å ‘‘Interfacing Extensions to Indexes’’ éã

ä½¿ç¨ DROP INDEX [drop_index(7)] å é¤ä¸ä¸ªç´¢å¼ã

EXAMPLES ä¾å

å¨è¡¨filmsä¸ç titleåæ®µåå»ºä¸ä¸ª B-tree ç´¢å¼ï¼

CREATE UNIQUE INDEX title_idx ON films (title);

COMPATIBILITY å¼å®¹æ§

CREATE INDEX æ¯ PostgreSQL è¯è¨æ©å±ã å¨ SQL æ åä¸æ²¡æ CREATE INDEX å½ä»¤ã

è¯è

Postgresql ä¸æç½ç« ä½ä¼å¹³ <laser@pgsqldb.org>

è·

æ¬é¡µé¢ä¸æçç±ä¸æ man æåé¡µè®¡åæä¾ã
ä¸æ man æåé¡µè®¡åï¼https://github.com/man-pages-zh/manpages-zh

↑