ããB-Treeç´¢å¼æ¯æ常è§çç´¢å¼ç»æï¼é»è®¤å建çç´¢å¼å°±æ¯B-Treeç´¢å¼ã
ããä¸ãBæ ç´¢å¼çç»æ
ããB-æ ç´¢å¼æ¯åºäºäºåæ ç»æçãB-æ ç´¢å¼ç»ææ3个åºæ¬ç»æé¨åï¼æ ¹èç¹ãåæ¯èç¹åå¶åèç¹ãå
¶ä¸æ ¹èç¹ä½äºç´¢å¼ç»æçæ顶端ï¼èå¶åèç¹ä½äºç´¢å¼ç»æçæåºç«¯ï¼ä¸é´ä¸ºååèç¹ã
ããå¶åèç¹ï¼Leaf nodeï¼ï¼å
å«æ¡ç®ç´æ¥æå表éçæ°æ®è¡ã
ããåæ¯èç¹ï¼Branch nodeï¼ï¼å
å«çæ¡ç®æåç´¢å¼éå
¶ä»çåæ¯èç¹æè
æ¯å¶åèç¹ã
ããæ ¹èç¹ï¼Branch nodeï¼ï¼ä¸ä¸ªBæ ç´¢å¼åªæä¸ä¸ªæ ¹èç¹ï¼å®å®é
å°±æ¯ä½äºæ çæ顶端çåæ¯èç¹ã
ããå¯ä»¥ç¨ä¸å¾ä¸æ¥æè¿°Bæ ç´¢å¼çç»æãå
¶ä¸ï¼B表示åæ¯èç¹ï¼èL表示å¶åèç¹ã
ããclip_image001
ãã1.1å
³äºåæ¯èç¹åï¼å
æ¬æ ¹èç¹åï¼
ãã1ã å
¶æå
å«çç´¢å¼æ¡ç®é½æ¯æç
§é¡ºåºæåçï¼ç¼ºçæ¯ååºæåï¼ä¹å¯ä»¥å¨å建索å¼æ¶æå®ä¸ºéåºæåï¼ã
ãã2ã æ¯ä¸ªç´¢å¼æ¡ç®ï¼ä¹å¯ä»¥å«åæ¯æ¡è®°å½ï¼é½å
·æ两个å段ã第ä¸ä¸ªå段表示å½å该åæ¯èç¹åä¸é¢æé¾æ¥çç´¢å¼åä¸æå
å«çæå°é®å¼ï¼ç¬¬äºä¸ªå段为å个åèï¼è¡¨ç¤ºæé¾æ¥çç´¢å¼åçå°åï¼è¯¥å°åæåä¸é¢ä¸ä¸ªç´¢å¼åã
ãã3ã å¨ä¸ä¸ªåæ¯èç¹åä¸æè½å®¹çº³çè®°å½è¡æ°ç±æ°æ®å大å°ä»¥åç´¢å¼é®å¼çé¿åº¦å³å®ãæ¯å¦ä»ä¸å¾ä¸å¯ä»¥çå°ï¼å¯¹äºæ ¹èç¹åæ¥è¯´ï¼å
å«ä¸æ¡è®°å½ï¼åå«ä¸ºï¼0 B1ï¼ãï¼500 B2ï¼ãï¼1000 B3ï¼ï¼å®ä»¬æåä¸ä¸ªåæ¯èç¹åãå
¶ä¸ç0ã500å1000åå«è¡¨ç¤ºè¿ä¸ä¸ªåæ¯èç¹åæé¾æ¥çé®å¼çæå°å¼ãèB1ãB2åB3å表示ææåçä¸ä¸ªåæ¯èç¹åçå°åã
ãã1.2å
³äºå¶åèç¹
ãã1ã Bæ ç´¢å¼çææå¶ååä¸å®ä½äºåä¸å±ä¸ï¼è¿æ¯ç±Bæ çæ°æ®ç»æå®ä¹çãOracle 设计çBæ ç´¢å¼ç»æä¿è¯äºB æ ç´¢å¼ä»æ ¹å°å¶åé½æç¸ççåæ¯èç¹ï¼ä¿è¯äºBæ ç´¢å¼ç平衡ï¼è¿æ ·å°±ä¸ä¼å 为åºè¡¨çæ°æ®æå
¥åå é¤æä½é æBæ ç´¢å¼åå¾ä¸å¹³è¡¡ï¼ä»èå½±åç´¢å¼çæ§è½ãå æ¤ï¼ä»æ ¹åå°è¾¾ä»»ä½ä¸ä¸ªå¶ååçéå代价é½æ¯ç¸åçï¼ ç´¢å¼é«åº¦æ¯æä»æ ¹åå°è¾¾å¶ååæ¶æéåçæ°æ®åç个æ°ï¼é常ï¼å¤§å¤æ°çBæ ç´¢å¼çé«åº¦é½æ¯2å°3ï¼ä¹å°±æå³çï¼å³ä½¿è¡¨ä¸æä¸ç¾ä¸æ¡è®°å½ï¼ä»ç´¢å¼ä¸å®ä½ä¸ä¸ªé®ååªéè¦2æ3次I/O,ç´¢å¼è¶é«ï¼æ§è½è¶å·®ï¼
ãã2ã å¶åèç¹æå
å«çç´¢å¼æ¡ç®ä¸åæ¯èç¹ä¸æ ·ï¼é½æ¯æç
§é¡ºåºæåçï¼ç¼ºçæ¯ååºæåï¼ä¹å¯ä»¥å¨å建索å¼æ¶æå®ä¸ºéåºæåï¼
ãã3ã æ¯ä¸ªç´¢å¼æ¡ç®ï¼ä¹å¯ä»¥ å«åæ¯æ¡è®°å½ï¼ä¹å
·æ两个å段ã第ä¸ä¸ªå段表示索å¼çé®å¼ï¼å¯¹äºååç´¢å¼æ¥è¯´æ¯ä¸ä¸ªå¼ï¼è对äºå¤åç´¢å¼æ¥è¯´åæ¯å¤ä¸ªå¼ç»åå¨ä¸èµ·çã第äºä¸ªå段表示é®å¼æ对åºçè®°å½è¡çROWIDï¼è¯¥ROWIDæ¯è®°å½è¡å¨è¡¨éçç©çå°åãROWID æ¯å¯ä¸çOracle æéï¼æå该è¡çç©çä½ç½®ï¼ä½¿ç¨ROWID æ¯Oracle æ°æ®åºä¸è®¿é®è¡æå¿«çæ¹æ³ãåç
§Oracleä¸çrowid
ãã4ã å¶åèç¹å
¶å®æ¯ä¸ä¸ªååé¾è¡¨ï¼æ¯ä¸ªå¶åèç¹å
å«ä¸ä¸ªæåä¸ä¸ä¸ªåä¸ä¸ä¸ªå¶åç¹çæéï¼è¿æ ·å¨ä¸å®èå´å
便å©ç´¢å¼ä»¥æç´¢éè¦çè®°å½ã
ããclip_image002
ããclip_image004
ããäºãBæ ç´¢å¼ç访é®
ããå½oracleè¿ç¨éè¦è®¿é®æ°æ®æ件éçæ°æ®åæ¶ï¼oracleä¼æ两ç§ç±»åçI/Oæä½æ¹å¼ï¼
ãã1ï¼ éæºè®¿é®ï¼æ¯æ¬¡è¯»åä¸ä¸ªæ°æ®åï¼éè¿çå¾
äºä»¶âdb file sequential readâä½ç°åºæ¥ï¼ã
ãã2ï¼ é¡ºåºè®¿é®ï¼æ¯æ¬¡è¯»åå¤ä¸ªæ°æ®åï¼éè¿çå¾
äºä»¶âdb file scattered readâä½ç°åºæ¥ï¼ã
ãã第ä¸ç§æ¹å¼åæ¯è®¿é®ç´¢å¼éçæ°æ®åï¼è第äºç§æ¹å¼çI/Oæä½å±äºå
¨è¡¨æ«æãè¿é顺带æä¸ä¸ªé®é¢ï¼ä¸ºä½éæºè®¿é®ä¼å¯¹åºå°db file sequential readçå¾
äºä»¶ï¼è顺åºè®¿é®åä¼å¯¹åºå°db file scattered readçå¾
äºä»¶å¢ï¼è¿ä¼¼ä¹åè¿æ¥äºï¼éæºè®¿é®æåºè¯¥æ¯åæ£ï¼scatteredï¼çï¼è顺åºè®¿é®æåºè¯¥æ¯é¡ºåºï¼sequentialï¼çãå
¶å®ï¼çå¾
äºä»¶ä¸»è¦æ ¹æ®å®é
è·åç©çI/Oåçæ¹å¼æ¥å½åçï¼èä¸æ¯æ ¹æ®å
¶å¨I/Oåç³»ç»çé»è¾æ¹å¼æ¥å½åçãä¸é¢å¯¹äºå¦ä½è·åç´¢å¼æ°æ®åçæ¹å¼ä¸ä¼å¯¹æ¤è¿è¡è¯´æã
ããäºå®ä¸å¨Bæ ç´¢å¼è½ç¶ä¸ºä¸ä¸ªæ ç¶çç«ä½ç»æï¼ä½å
¶å¯¹åºå°æ°æ®æ件éçæåå½ç¶è¿æ¯ä¸ä¸ªå¹³é¢çå½¢å¼ï¼ä¹å°±æ¯åä¸é¢è¿æ ·ã
ãã/æ ¹/åæ¯/åæ¯/å¶å/â¦/å¶å/åæ¯/å¶å/å¶å/â¦/å¶å/åæ¯/å¶å/å¶å/â¦/å¶å/åæ¯/.....
ããå æ¤ï¼å½oracleéè¦è®¿é®æ个索å¼åçæ¶åï¼å¿å¿
ä¼å¨è¿ä¸ªç»æä¸è·³è·ç移å¨ã
ããå½oracleéè¦è·å¾ä¸ä¸ªç´¢å¼åæ¶ï¼é¦å
ä»æ ¹èç¹å¼å§ï¼æ ¹æ®æè¦æ¥æ¾çé®å¼ï¼ä»èç¥éå
¶æå¨çä¸ä¸å±çåæ¯èç¹ï¼ç¶å访é®ä¸ä¸å±çåæ¯èç¹ï¼å次åæ ·æ ¹æ®é®å¼è®¿é®åä¸ä¸å±çåæ¯èç¹ï¼å¦æ¤è¿è¬ï¼æç»è®¿é®å°æåºå±çå¶åèç¹ãå¯ä»¥çåºï¼å
¶è·å¾ç©çI/Oåæ¶ï¼æ¯ä¸ä¸ªæ¥çä¸ä¸ªï¼æç
§é¡ºåºï¼ä¸²è¡è¿è¡çãå¨è·å¾æç»ç©çåçè¿ç¨ä¸ï¼æ们ä¸è½åæ¶è¯»åå¤ä¸ªåï¼å 为æ们å¨æ²¡æè·å¾å½ååçæ¶åæ¯ä¸ç¥éæ¥ä¸æ¥åºè¯¥è®¿é®åªä¸ªåçãå æ¤ï¼å¨ç´¢å¼ä¸è®¿é®æ°æ®åæ¶ï¼ä¼å¯¹åºå° db file sequential readçå¾
äºä»¶ï¼å
¶æ ¹æºå¨äºæ们æ¯æç
§é¡ºåºä»ä¸ä¸ªç´¢å¼åè·³å°å¦ä¸ä¸ªç´¢å¼åï¼ä»èæ¾å°æç»çç´¢å¼åçã
ããé£ä¹å¯¹äºå
¨è¡¨æ«ææ¥è¯´ï¼åä¸åå¨è®¿é®ä¸ä¸ä¸ªåä¹åéè¦å
访é®ä¸ä¸ä¸ªåçæ
åµãå
¨è¡¨æ«ææ¶ï¼oracleç¥éè¦è®¿é®ææçæ°æ®åï¼å æ¤å¯ä¸çé®é¢å°±æ¯å°½å¯è½é«æç访é®è¿äºæ°æ®åãå æ¤ï¼è¿æ¶oracleå¯ä»¥éç¨åæ¥çæ¹å¼ï¼åå æ¹ï¼åæ¶è·åå¤ä¸ªæ°æ®åãè¿å æ¹çæ°æ®åå¨ç©çä¸å¯è½æ¯åæ£å¨è¡¨éçï¼å æ¤å
¶å¯¹åºå°db file scattered readçå¾
äºä»¶ã
ããä¸ãDML对Bæ ç´¢å¼çå½±å
ãã3.1 INSERT
ããå¨æ¯ä¸ªINSERTæä½è¿ç¨ä¸ï¼å
³é®åå¿
须被æå
¥å¨æ£ç¡®å¶èç¹çä½ç½®ãå¦æå¶èç¹å·²æ»¡ï¼ä¸è½å®¹çº³æ´å¤çå
³é®åï¼å°±å¿
é¡»å°å¶èç¹æåãæåçæ¹æ³æ两ç§ï¼
ãã1)å¦ææ°å
³é®åå¼å¨æææ§å¶èç¹åçææå
³é®åä¸æ¯æ大çï¼é£ä¹ææçå
³é®åå°æç
§99:1çæ¯ä¾è¿è¡æåï¼ä½¿å¾å¨æ°çå¶èç¹åä¸åªåæ¾ææ°å
³é®åï¼èå
¶ä»çææå
³é®åï¼å
æ¬ææå é¤çå
³é®åï¼ä»ç¶ä¿åå¨æ§å¶èç¹åä¸ã
ãã2)å¦ææ°å
³é®åå¼ä¸æ¯æ大çï¼é£ä¹ææçå
³é®åå°æç
§50:50çæ¯ä¾è¿è¡æåï¼è¿æ¶æ¯ä¸ªå¶èç¹åï¼æ§ä¸æ°ï¼ä¸å°åå
å«åå§å¶èç¹ä¸çä¸åå
³é®åã
ããè¿ä¸ªæåå¿
é¡»éè¿ä¸ä¸ªæåæ°å¶èç¹çæ°å
¥å£åä¸ä¼ éå°ç¶èç¹ãå¦æç¶èç¹å·²æ»¡ï¼é£ä¹è¿ä¸ªç¶èç¹ä¹å¿
é¡»è¿è¡æåï¼å¹¶ä¸éè¦å°è¿ç§æååä¸ä¼ éå°ç¶èç¹çç¶èç¹ãè¿æ¶ï¼å¦æè¿ä¸ªç¶èç¹ä¹å·²æ»¡ï¼å°ç»§ç»è¿è¡è¿ä¸ªè¿ç¨ãè¿æ ·ï¼æ个æåå¯è½æç»è¢«ä¸ç´ä¼ éå°æ ¹èç¹ãå¦ææ ¹èç¹æ»¡äºï¼æ ¹ç»ç¹ä¹å°è¿è¡åè£ãæ ¹ç»ç¹å¨è¿è¡åè£çæ¶åï¼å°±æ¯æ çé«åº¦å¢å çæ¶åãæ ¹èç¹è¿è¡åè£çæ¹å¼è·å
¶ä»ççèç¹åè£çæ¹å¼ç¸æ¯è¾ï¼å¨ç©çä½ç½®ä¸çå¤çä¹æ¯ä¸åçãæ ¹èç¹åè£æ¶ï¼å°åæ¥çæ ¹ç»ç¹åè£ä¸ºåæ¯èç¹æå¶èç¹ï¼ä¿åå°æ°çåä¸ï¼èå°æ°çæ ¹èç¹ä¿¡æ¯ä¿åå°åæ¥çæ ¹ç»ç¹åä¸ï¼è¿æ ·åçæ¯ä¸ºå 为é¿å
ä¿®æ¹æ°æ®åå
¸æ带æ¥çç¸å¯¹è¾å¤§çå¼éã
ãã注æï¼ç°å¨Oracleé½æ¯éç¨äºå¹³è¡¡ç®æ³ï¼æ£å¸¸æ
åµä¸å³ä½¿ç´¢å¼å
³é®åä¸æå¢å¤§ï¼ä¹ä¸ä¼äº§çä¸å¹³è¡¡æ ãå½ç´¢å¼å
³é®åä¸æå¢å¤§ï¼å¯¼è´æ 级å«åæ¹åå¢é¿æ¶ï¼Oracleä¼èªå¨è¿è¡ç´¢å¼ç¿»è½¬ä»¥ç»´æç´¢å¼ç平衡ï¼å½ç¶è¿ç§æä½é常æ¶èèµæº
ããå¨ç´¢å¼çæ¯ä¸ä¸ªå±æ¬¡ä¹é´ï¼æ¯ä¸ä¸ªå±æ左边çèç¹çblock头é¨é½æä¸ä¸ªæåä¸å±æ左边çåçæéï¼è¿æ ·æå©äºfast full scan çå¿«éå®ä½æ左边çå¶åèç¹ã
ããæ¯ä¸ªæåè¿ç¨é½æ¯è¦è±è´¹ä¸å®çå¼éçï¼ç¹å«æ¯è¦è¿è¡ç©ç硬çI/Oå¨ä½ãæ¤å¤ï¼å¨è¿è¡æåä¹åï¼Oracleå¿
é¡»æ¥æ¾å°ä¸ä¸ªç©ºåï¼ç¨æ¥ä¿åè¿ä¸ªæåãå¯ä»¥ç¨ä»¥ä¸æ¥éª¤æ¥è¿è¡æ¥æ¾ç©ºåçå¨ä½ï¼
ãã1) å¨ç´¢å¼çèªç±å表(free-list, å称为空é²å表) ä¸æ¥å°ä¸ä¸ªç©ºé²åï¼å¯ä»¥éè¿CREATE/ALTER INDEXå½ä»¤ä¸ºä¸ä¸ªç´¢å¼å®ä¹å¤ä¸ªç©ºé²å表ãç´¢å¼ç©ºé²å表并ä¸è½å¸®å©Oracleæ¥æ¾ä¸ä¸ªå¯ç¨æ¥åæ¾å°è¦è¢«æå
¥çæ°å
³é®åçåãè¿æ¯å 为å
³é®åå¼ä¸è½éæºå°åæ¾å¨ç´¢å¼ä¸å¯ç¨ç第ä¸ä¸ªâ空é²âå¶èç¹åä¸ï¼è¿ä¸ªå¼å¿
é¡»ç»è¿éå½çæåºä¹åï¼æ¾ç½®å¨æ个ç¹å®çå¶èç¹åä¸ãåªæå¨åæåè¿ç¨ä¸æéè¦ä½¿ç¨ç´¢å¼ç空é²å表ï¼æ¯ä¸ªç©ºé²å表é½å
å«æä¸ä¸ªå
³äºâ空âåçé¾æ¥å表ãå½ä¸ºæ个索å¼å®ä¹äºå¤ä¸ªç©ºé²å表æ¶ï¼é¦å
å°ä»åé
ç»è¿ç¨ç空é´å表ä¸æ«æä¸ä¸ªç©ºé²åãå¦æ没ææ¾å°æéè¦ç空é²åï¼å°ä»ä¸»ç©ºé²å表ä¸è¿è¡æ«æ空é²åçå¨ä½ã
ãã2) å¦æ没ææ¾å°ä»»ä½ç©ºé²åï¼Oracleå°è¯å¾åé
å¦ä¸ä¸ªæ©å±æ®µãå¦æå¨è¡¨ç©ºé´ä¸æ²¡ææ´å¤çèªç±ç©ºé´ï¼Oracleå°äº§çé误ORA-01654ã
ãã3) å¦æéè¿ä¸è¿°æ¥éª¤ï¼æ¾å°äºæéç空é²åï¼é£ä¹è¿ä¸ªç´¢å¼çé«æ°´ä½æ (HWM)å°å 大ã
ãã4) ææ¾å°ç空é²åå°ç¨æ¥æ§è¡æåå¨ä½ã
ããå¨å建B*æ ç´¢å¼æ¶ï¼ä¸ä¸ªéè¦æ³¨æçé®é¢å°±æ¯è¦é¿å
å¨è¿è¡æ¶è¿è¡æåï¼æè
ï¼è¦å¨ç´¢å¼å建è¿ç¨ä¸è¿è¡æåï¼âé¢æåâï¼ï¼ä»è使å¾å¨è¿è¡æåæ¶è½å¤å¿«éå½ä¸ï¼ä»¥ä¾¿é¿å
è¿è¡æ¶æå
¥å¨ä½ãå½ç¶ï¼è¿äºæåä¹ä¸ä»
ä»
å±éäºæå
¥å¨ä½ï¼å¨è¿è¡æ´æ°çè¿ç¨ä¸ä¹æå¯è½ä¼åçæåå¨ä½ã
ãã3.2 UPDATE
ããç´¢å¼æ´æ°å®å
¨ä¸åäºè¡¨æ´æ°ï¼å¨è¡¨æ´æ°ä¸ï¼æ°æ®æ¯å¨æ°æ®åå
é¨æ¹åçï¼å设æ°æ®åä¸æ足å¤ç空é´æ¥å
许è¿è¡è¿ç§æ¹åï¼ï¼ä½å¨ç´¢å¼æ´æ°ä¸ï¼å¦ææå
³é®ååçæ¹åï¼é£ä¹å®å¨æ ä¸çä½ç½®ä¹éè¦åçæ¹åã请记ä½ï¼ä¸ä¸ªå
³é®åå¨B*æ ä¸æä¸åªæä¸ä¸ªä½ç½®ãå æ¤ï¼å½æ个å
³é®ååçæ¹åæ¶ï¼å
³é®åçæ§è¡¨é¡¹å¿
须被å é¤ï¼å¹¶ä¸éè¦å¨ä¸ä¸ªæ°çå¶èç¹ä¸å建ä¸ä¸ªæ°çå
³é®åãæ§ç表项æå¯è½æ°¸è¿ä¸ä¼è¢«éæ°ä½¿ç¨ï¼è¿æ¯å 为åªæå¨é常ç¹æ®çæ
åµä¸ï¼ Oracleæä¼éç¨å
³é®å表项槽ï¼ä¾å¦ï¼æ°æå
¥çå
³é®åæ£å¥½æ¯è¢«å é¤çé£ä¸ªå
³é®åï¼å
æ¬æ°æ®ç±»åãé¿åº¦ççï¼ãï¼è¿ééç¨çæ¯åï¼ä½å®å
¨æå
¥ç¸åçå¼çæ¶åï¼ä¹ä¸ä¸å®æå
¥å¨åæ¥ç被å é¤çä½ç½®ï¼åªæ¯æå
¥å¨åæ¥çåä¸ï¼å¯è½æ¯è¯¥åä¸çä¸ä¸ªæ°ä½ç½®ãä¹æ£å 为å¦æ¤ï¼å¨ç´¢å¼åä¸ä¿åççè®°å½å¯è½å¹¶ä¸æ¯æ ¹æ®å
³é®å顺åºæåçï¼éçupdateççæä½ï¼ä¼åçååãï¼é£ä¹ï¼è¿ç§æ
åµåççå¯è½æ§æå¤å¤§å¢ï¼è®¸å¤åºç¨ç¨åºä½¿ç¨ä¸ä¸ªæ°åæ¥äº§çNUMBERå
³é®åï¼ç¹å«æ¯ä¸»å
³é®åï¼ãé¤éå®ä»¬ä½¿ç¨äºRECYCLEé项ï¼å¦åè¿ä¸ªæ°åå°ä¸ä¼ä¸¤æ¬¡äº§çå®å
¨ç¸åçæ°ãè¿æ ·ï¼ç´¢å¼ä¸è¢«å é¤ç空é´ä¸ç´æ²¡æ被使ç¨ãè¿å°±æ¯å¨å¤§è§æ¨¡å é¤ä¸æ´æ°è¿ç¨ä¸ï¼è¡¨å¤§å°ä¸æåå°æè³å°ä¿æä¸åä½ç´¢å¼ä¸æå 大çåå ã
ãã3.3 DELETE
ããå½å é¤è¡¨éçä¸æ¡è®°å½æ¶ï¼å
¶å¯¹åºäºç´¢å¼éçç´¢å¼æ¡ç®å¹¶ä¸ä¼è¢«ç©ççå é¤ï¼åªæ¯åäºä¸ä¸ªå é¤æ è®°ãå½ä¸ä¸ªæ°çç´¢å¼æ¡ç®è¿å
¥ä¸ä¸ªç´¢å¼å¶åèç¹çæ¶åï¼oracleä¼æ£æ¥è¯¥å¶åèç¹éæ¯å¦åå¨è¢«æ 记为å é¤çç´¢å¼æ¡ç®ï¼å¦æåå¨ï¼åä¼å°ææå
·æå é¤æ è®°çç´¢å¼æ¡ç®ä»è¯¥å¶åèç¹éç©ççå é¤ã
ããå½ä¸ä¸ªæ°çç´¢å¼æ¡ç®è¿å
¥ç´¢å¼æ¶ï¼oracleä¼å°å½åææ被æ¸
空çå¶åèç¹ï¼è¯¥å¶åèç¹ä¸ææçç´¢å¼æ¡ç®é½è¢«è®¾ç½®ä¸ºå é¤æ è®°ï¼æ¶åï¼ä»èå次æ为å¯ç¨ç´¢å¼åã
ãã尽管被å é¤çç´¢å¼æ¡ç®æå ç¨ç空é´å¤§é¨åæ
åµä¸é½è½å¤è¢«éç¨ï¼ä½ä»ç¶åå¨ä¸äºæ
åµå¯è½å¯¼è´ç´¢å¼ç©ºé´è¢«æµªè´¹ï¼å¹¶é æç´¢å¼æ°æ®åå¾å¤ä½æ¯ç´¢å¼æ¡ç®å¾å°çåæï¼è¿æ¶è¯¥ç´¢å¼å¯ä»¥è®¤ä¸ºåºç°ç¢çãè导è´ç´¢å¼åºç°ç¢ççæ
åµä¸»è¦å
æ¬ï¼
ãã1ãä¸åççãè¾é«çPCTFREEãå¾ææ¾ï¼è¿å°å¯¼è´ç´¢å¼åçå¯ç¨ç©ºé´åå°ã
ãã2ãç´¢å¼é®å¼æç»å¢å ï¼æ¯å¦éç¨sequenceçæåºåå·çé®å¼ï¼ï¼åæ¶å¯¹ç´¢å¼é®å¼æç
§é¡ºåºè¿ç»å é¤ï¼è¿æ¶å¯è½å¯¼è´ç´¢å¼ç¢ççåçãå 为åé¢æ们ç¥éï¼æ个索å¼åä¸å é¤äºé¨åçç´¢å¼æ¡ç®ï¼åªæå½æé®å¼è¿å
¥è¯¥ç´¢å¼åæ¶æè½å°ç©ºé´æ¶åãèæç»å¢å çç´¢å¼é®å¼æ°¸è¿åªä¼åæå
¥æå¨åé¢çç´¢å¼åä¸ï¼å æ¤è¿ç§ç´¢å¼éç空é´å ä¹ä¸è½æ¶åï¼èåªæå
¶æå«çç´¢å¼æ¡ç®å
¨é¨å é¤æ¶ï¼è¯¥ç´¢å¼åæè½è¢«éæ°å©ç¨ã
ãã3ãç»å¸¸è¢«å é¤ææ´æ°çé®å¼ï¼ä»¥åå ä¹ä¸åä¼è¢«æå
¥æ¶ï¼è¿ç§æ
åµä¸ä¸é¢çæ
åµç±»ä¼¼ã
ããåãæ»ç»
ããéè¿ä¸é¢å¯¹Bæ çåæï¼å¯ä»¥å¾åºä»¥ä¸çåºç¨ååï¼
ãã1ãé¿å
对é£äºå¯è½ä¼äº§çå¾é«çæ´æ°å¨ä½çåè¿è¡ç´¢å¼ã
ãã2ãé¿å
对é£äºç»å¸¸ä¼è¢«å é¤ç表ä¸çå¤ä¸ªåè¿è¡ç´¢å¼ãè¥æå¯è½ï¼åªå¯¹é£äºå¨è¿æ ·ç表ä¸ä¼è¿è¡å é¤ç主å
³é®åä¸/æåè¿è¡ç´¢å¼ãå¦æ对å¤ä¸ªåè¿è¡ç´¢å¼æ¯ä¸å¯é¿å
çï¼é£ä¹å°±åºè¯¥èèæ ¹æ®è¿äºå对表è¿è¡ååï¼ç¶åå¨æ¯ä¸ªè¿æ ·çååä¸æ§è¡TRUNCATEå¨ä½ï¼èä¸æ¯DELETEå¨ä½ï¼ãTRUNCATEå¨ä¸ DROP STORAGEçè¯ä¸å使ç¨æ¶ï¼éè¿éæ°è®¾ç½®é«æ°´ä½æ æ¥æ¨¡æå é¤è¡¨ä¸ç´¢å¼ä»¥åéæ°å建表ä¸ç´¢å¼çè¿ç¨ã
ãã3ãé¿å
为é£äºå¯ä¸åº¦ä¸é«çåå建B*æ ç´¢å¼ãè¿æ ·çä½éæ©æ§å°ä¼å¯¼è´æ èç¹åçç¨ å¯æ§ï¼ä»è导è´ç±äºç´¢å¼âå¹³éº( flat)âèåºç°ç大è§æ¨¡ç´¢å¼æ«æãå¯ä¸æ§çç¨åº¦è¶é«ï¼æ§è½å°±è¶å¥½ï¼å 为è¿æ ·è½å¤åå°èå´æ«æï¼çè³å¯è½ç¨å¯ä¸æ«ææ¥å代èå´æ«æã
ãã4ï¼ç©ºå¼ä¸åå¨å¨ååç´¢å¼ä¸ã对äºå¤åç´¢å¼çæ¹å¼ï¼åªæå½æ个åä¸ç©ºæ¶ï¼æéè¦è¿è¡å¼çåå¨ãå¨ä¸ºDMLè¯å¥å建IS NULLæIS NOT NULLçè¯æ¶ï¼åºè¯¥åè®°è¿ä¸ªé®é¢ã
ãã5ï¼IS NULLä¸ä¼å¯¼è´ç´¢å¼æ«æï¼èä¸ä¸ªæ²¡æ带任ä½éå¶çIS NOT NULLåå¯è½ä¼å¯¼è´å®å
¨ç´¢å¼æ«æã
温馨提示:答案为网友推荐,仅供参考