如题所述
Oracle æ°æ®å¯¼å
¥æ¹æ³æ¯è¾ æ¯ä¸ªæ°æ®åºç®¡çåé½ä¼é¢ä¸´æ°æ®å¯¼å
¥çé®é¢ï¼è¿æå¯è½åçå¨æ°æ®åºçæ°è移æ¤è¿ç¨ä¸ï¼æè
æ¯å¨æ°æ®åºå´©æºåçæ¢å¤é建è¿ç¨ä¸ï¼è¿æå¯è½æ¯å¨å建æµè¯æ°æ®åºç模æç¯å¢è¿ç¨ä¸ï¼æ»ä¹ä½ä¸ºä¸ååæ ¼çæ°æ®åºç®¡çåï¼ä½ åºè¯¥å好æ¥ååç§æ°æ®å¯¼å
¥è¯·æ±çææ¯å¨å¤ï¼åæ¶è¿è¦å°½é满足人æ¬è½ç对导å
¥é度çèæ±ãæ¬æä»
é对 Oracle æ°æ®åºææä¾çå éæ°æ®å¯¼å
¥çåç§ç¹æ§åææ¯è¿è¡æ¢è®¨ï¼å
¶ä¸çä¸äºæ¹æ³ä¹å¯ä»¥è½¬ååºç¨äºå
¶ä»æ°æ®åºã以ä¸ä¸ç§æ°æ®å¯¼å
¥æ¹æ³åªä¸ªæéç¨éè¦é对å
·ä½æ
åµå
·ä½åæï¼æä¹é带å举äºå½±å导å
¥é度çåç§å ç´ ä¾æé
ã为äºæ¯è¾åç§æ°æ®å¯¼å
¥æ¹æ³çææï¼æå建äºç¤ºä¾è¡¨åæ°æ®éï¼å¹¶ç¨åç§æ¹æ³å¯¼å
¥ç¤ºä¾æ°æ®éæ¥è®¡ç®æ»ä½å¯¼å
¥æ¶é´å导å
¥è¿ç¨å ç¨ CPU æ¶é´ï¼è¿éå¾åºçæ¶é´ä»
ä¾åèãéè¦è¯´æçæ¯ï¼å»ºè®®ä½ ä½¿ç¨ Oracle 9i ä¼ä¸çæ°æ®åºï¼å½ç¶ä½ ä¹å¯ä»¥å°è¯ä½¿ç¨ Oracle 7.3 以ä¸çæ åçæ°æ®åºãæ¬æ使ç¨çæºå¨é
置为ï¼CPU Intel P4ï¼å
å 256Mï¼æ°æ®åº Oracle 9i ä¼ä¸ç
示ä¾è¡¨ç»æåæ°æ®é
为äºæ¼ç¤ºåæ¯è¾åç§æ°æ®å¯¼å ¥æ¹æ³ï¼æåå®æ°æ®å¯¼å ¥ä»»å¡æ¯å°å¤é¨æ件æ°æ®å¯¼å ¥å° Oracle æ°æ®åºçCALLS表ä¸ï¼å¤é¨æ°æ®æ件å å«åä¸æ¡å¼å«ä¸å¿è®°å½ï¼å°è¿ 6MB çæ件大å°ï¼å ·ä½çæ°æ®ç¤ºä¾å¦ä¸:
82302284384,2003-04-18:13:18:58,5001,æè¯,ææºä¸å 维修质é82302284385,2003-04-18:13:18:59,3352,å¨è¯¢,ä¾æ°´ç线çå·ç 82302284386,2003-04-18:13:19:01,3142,建议,å¢è®¾å ¬äº¤çº¿è·¯
æ¥åå¯¼å ¥æ°æ®ç表åæ¯ CALLSï¼è¡¨ç»æå¦ä¸ï¼
Name Null? Type Comment ------------ --------- ------------- ----------------- CALL_ID NOT NULL NUMBER Primary key CALL_DATE NOT NULL DATE Non-unique index EMP_ID NOT NULL NUMBER CALL_TYPE NOT NULL VARCHAR2(12) DETAILS VARCHAR2(25)
éæ¡æ°æ®æå ¥INSERT
æ°æ®å¯¼å ¥çæç®åæ¹æ³å°±æ¯ç¼å INSERT è¯å¥ï¼å°æ°æ®éæ¡æå ¥æ°æ®åºãè¿ç§æ¹æ³åªéåå¯¼å ¥å°éæ°æ®ï¼å¦ SQL*Plus èæ¬å建æ个表çç§åæ°æ®ã该æ¹æ³çæ大缺ç¹å°±æ¯å¯¼å ¥é度ç¼æ ¢ï¼å ç¨äºå¤§éç CPU å¤çæ¶é´ï¼ä¸éå大æ¹éæ°æ®çå¯¼å ¥ï¼èå ¶ä¸»è¦ä¼ç¹å°±æ¯å¯¼å ¥ææç®ååæä¿®æ¹å®åçå¼¹æ§ï¼ä¸éè¦å¤åå ¶å®çåå¤å°±å¯ä»¥ä½¿ç¨ãå¦æä½ æå¾å¤æ¶é´æ²¡æ³æåï¼åæ³æ磨ä¸ä¸æ°æ®åºå CPUï¼é£è¿ç§æ¹æ³æ£éåä½ ã:)
为äºä¸å ¶å®æ¹æ³åæ¯è¾ï¼ç°å°åä¸æ¡è®°å½éè¿æ¤æ¹æ³å¯¼å ¥å° CALLS 表ä¸ï¼æ»å ±æ¶è 172 ç§ï¼å ¶ä¸å¯¼å ¥è¿ç¨å ç¨ CPU æ¶é´ä¸º 52 ç§ã
éæ¡æ°æ®æå ¥ INSERTï¼è¡¨ææ ç´¢å¼
为ä»ä¹ä¸ä¸ç§æ¹æ³å ç¨äºè¾å¤ç CPU å¤çæ¶é´ï¼å ³é®æ¯ CALLS 表ä¸å·²å建äºç´¢å¼ï¼å½ä¸æ¡æ°æ®æå ¥å°è¡¨ä¸æ¶ï¼Oracle éè¦å¤å«æ°æ°æ®ä¸èæ°æ®å¨ç´¢å¼æ¹é¢æ¯å¦æå²çªï¼åæ¶è¦æ´æ°è¡¨ä¸çææç´¢å¼ï¼éå¤æ´æ°ç´¢å¼ä¼æ¶èä¸å®çæ¶é´ãå æ¤æé«å¯¼å ¥é度ç好åæ³å°±æ¯å¨å建表æ¶å ä¸å建索å¼æè å¨å¯¼å ¥æ°æ®ä¹åå é¤ææç´¢å¼ï¼å¨å¤é¨æ件æ°æ®éæ¡æå ¥å°è¡¨ä¸ååç»ä¸å建表çç´¢å¼ãè¿æ ·å¯¼å ¥é度ä¼æé«ï¼åæ¶å建çç´¢å¼ä¹å¾ç´§åèææï¼è¿ä¸åååæ ·éç¨äºä½å¾ç´¢å¼ï¼Bitmap Indexï¼ã对äºä¸»è¦çåå¯ä¸çå ³é®çº¦æ(key constraints)ï¼å¯ä»¥ä½¿ä¹å ææ¶å¤±æ(disabling)æè å é¤çº¦ææ¥è·å¾åæ ·çææï¼å½ç¶è¿äºåæ³ä¼å¯¹å·²ç»åå¨ç表çå¤é®çº¦æ产çç¸å ³çå½±åï¼å¨å é¤åéè¦éçæé ã
éè¦è¯´æçæ¯ï¼è¿ç§æ¹æ³å¨è¡¨ä¸å·²åå¨å¾å¤æ°æ®çæ åµä¸ä¸å¤ªåéãä¾å¦è¡¨ä¸å·²æä¹åä¸æ¡æ°æ®ï¼èæ¤æ¶éè¦è¿½å æå ¥ä¸åä¸æ¡æ°æ®ï¼å®é å¯¼å ¥æ°æ®èççæ¶é´å°ä¼è¢«éæ°å建ä¸äº¿æ¡æ°æ®çç´¢å¼ææ¶èæ®å°½ï¼è¿æ¯æ们ä¸å¸æå¾å°çç»æãä½æ¯ï¼å¦æè¦å¯¼å ¥æ°æ®ç表æ¯ç©ºçæå¯¼å ¥çæ°æ®éæ¯å·²æçæ°æ®éè¦å¤§å¾å¤ï¼é£ä¹å¯¼å ¥æ°æ®èççæ¶é´å°ä¼å°éç¨äºéæ°å建索å¼ï¼è¿æ¶è¯¥æ¹æ³æå¯ä»¥èè使ç¨ã
å å¿«ç´¢å¼å建æ¯å¦ä¸ä¸ªéè¦èèçé®é¢ã为äºåå°ç´¢å¼å建ä¸æåºçå·¥ä½æ¶é´ï¼å¯ä»¥å¨å½åä¼è¯ä¸å¢å SORT_AREA_SIZE åæ°ç大å°ï¼è¯¥åæ°å 许å½åä¼è¯å¨å åçç´¢å¼å建è¿ç¨ä¸æ§è¡æ´å¤çæåºæä½ãåæ ·è¿å¯ä»¥ä½¿ç¨ NOLOGGING å ³é®åæ¥åå°å å建索å¼èçæç REDO æ¥å¿éï¼NOLOGGING å ³é®åä¼å¯¹æ°æ®åºçæ¢å¤å Standby å¤ç¨æ°æ®åºäº§çææ¾çå½±åï¼æ以å¨ä½¿ç¨ä¹åè¦ä»ç»æé ï¼å°åºæ¯é度ä¼å è¿æ¯ç¨³å®ä¼å ã
è¿ç¨è¿ç§æ¹æ³ï¼å å é¤ CALLS 表ç主é®åä¸å¯ä¸çç´¢å¼ï¼ç¶åéæ¡å¯¼å ¥æ°æ®ï¼å®æåéæ°å建索å¼( 表å¨å¯¼å ¥æ°æ®åæ¯ç©ºç)ã该æ¹æ³æ»å ±æ¶è 130 ç§ï¼å æ¬é建索å¼çæ¶é´ï¼å ¶ä¸å¯¼å ¥è¿ç¨å ç¨ CPU æ¶é´ä¸º 35ç§ã
è¿ç§æ¹æ³çä¼ç¹æ¯å¯ä»¥å å¿«å¯¼å ¥çé度并使索å¼æ´å ç´§åææï¼ç¼ºç¹æ¯ç¼ºä¹éç¨æ§ï¼å½ä½ 对表å¢å æ°çå¤æç模å¼å ç´ ï¼ç´¢å¼ãå¤é®çï¼æ¶ä½ éè¦æ·»å 代ç ãä¿®æ¹å¯¼å ¥æ§è¡ç¨åºãå¦å¤é对 7*24 å¨çº¿è¦æ±çæ°æ®åºå¨çº¿å¯¼å ¥æä½æ¶ï¼å é¤è¡¨çç´¢å¼ä¼å¯¹å¨çº¿ç¨æ·çæ¥è¯¢æå¾å¤§çæ§è½å½±åï¼åæ¶ä¹è¦èèï¼ä¸»è¦æå¯ä¸çå ³é®çº¦ææ¡ä»¶çå é¤æ失æå¯è½ä¼å½±åå°å¼ç¨å®ä»¬çå¤é®ç使ç¨ã
æ¹éæå ¥ï¼è¡¨ææ ç´¢å¼
å¨Oracle V6 ä¸ OCI ç¼ç¨æ¥å£å å ¥äºæ°ç»æ¥å£ç¹æ§ãæ°ç»æä½å è®¸å¯¼å ¥ç¨åºè¯»åå¤é¨æ件æ°æ®å¹¶è§£æåï¼åæ°æ®åºæ交SQLè¯å¥ï¼æ¹éæå ¥ SQL è¯å¥æ£ç´¢åºçæ°æ®ãOracle ä» éè¦æ§è¡ä¸æ¬¡ SQL è¯å¥ï¼ç¶åå¨å åä¸æ¹é解ææä¾çæ°æ®ãæ¹éå¯¼å ¥æä½æ¯éè¡æå ¥éå¤æä½æ´ææçï¼è¿æ¯å 为åªéä¸æ¬¡è§£æ SQL è¯å¥ï¼ä¸äºæ°æ®ç»è®¢æä½ä»¥åç¨åºä¸æ°æ®åºä¹é´æ¥åçæä½é½æ¾èåå°ï¼èä¸æ°æ®åºå¯¹æ¯ä¸æ¡æ°æ®çæä½é½æ¯éå¤å¯ç¥çï¼è¿ç»æ°æ®åºæä¾äºä¼åæ§è¡çå¯è½ãå ¶ä¼ç¹æ¯æ°æ®å¯¼å ¥çæ»ä½æ¶é´ææ¾åå°ï¼ç¹å«æ¯è¿ç¨å ç¨ CPU çæ¶é´ã
éè¦æéçæ¯ï¼éè¿ OCI æ¥å£ç¡®å®å¯ä»¥æ§è¡æ°æ®æ¹éå¯¼å ¥æä½ï¼ä½æ¯è®¸å¤å·¥å ·åèæ¬è¯è¨å´ä¸æ¯æ使ç¨æ¤åè½ãå¦æè¦ä½¿ç¨è¯¥æ¹æ³ï¼éè¦ç ç©¶ä½ æ使ç¨çå¼åå·¥å ·æ¯å¦æ¯æ OCI æ¹éæä½åè½ãå¯¼å ¥ç¨åºéè¦è¿è¡å¤æçç¼ç 并å¯è½åå¨é误çé£é©ï¼ç¼ºä¹ä¸å®çå¼¹æ§ã
è¿ç¨ä¸è¿°æ¹æ³ï¼ç¨åºå°å¤é¨æ°æ®æåå°å åä¸çæ°ç»éï¼å¹¶æ§è¡æ¹éæå ¥æä½ï¼100è¡/次ï¼ï¼ä¿çäºè¡¨çå é¤/é建索å¼æä½ï¼æ»çå¯¼å ¥æ¶é´ä¸éå° 14 ç§ï¼èè¿ç¨å ç¨ CPU çæ¶é´ä¸éå°7ç§ï¼å¯è§å®é å¯¼å ¥æ°æ®æè±è´¹çæ¶é´æ¾èä¸éäº 95%ã
CREATE TABLE AS SELECTï¼ä½¿ç¨Oracle9içExternal Table
Oracle 9i çä¸é¡¹æ°ç¹æ§å°±æ¯ External Tableï¼å®å°±è±¡é常çæ°æ®åºè¡¨ä¸æ ·ï¼æ¥æå段åæ°æ®ç±»å约æï¼å¹¶ä¸å¯ä»¥æ¥è¯¢ï¼ä½æ¯è¡¨ä¸çæ°æ®å´ä¸åå¨å¨æ°æ®åºä¸ï¼èæ¯å¨ä¸æ°æ®åºç¸å ³èçæ®éå¤é¨æ件éãå½ä½ æ¥è¯¢ External Table æ¶ï¼Oracle å°è§£æ该æ件并è¿å符åæ¡ä»¶çæ°æ®ï¼å°±è±¡è¯¥æ°æ®åå¨å¨æ°æ®åºè¡¨ä¸ä¸æ ·ã
éè¦æ³¨æçæ¯ï¼ä½ å¯ä»¥å¨æ¥è¯¢è¯å¥ä¸å° External Table ä¸æ°æ®åºä¸å ¶ä»è¡¨è¿è¡è¿æ¥ï¼Joinï¼ï¼ä½æ¯ä¸è½ç» External Table å ä¸ç´¢å¼ï¼å¹¶ä¸ä¸è½æå ¥/æ´æ°/å é¤æ°æ®ï¼æ¯ç«å®ä¸æ¯çæ£çæ°æ®åºè¡¨ãå¦å¤ï¼å¦æä¸æ°æ®åºç¸å ³èçå¤é¨æ件被æ¹åæè 被å é¤ï¼è¿ä¼å½±åå° External Table è¿åæ¥è¯¢ç»æï¼æ以å¨åå¨åè¦å è·æ°æ®åºææå¼ã
è¿ç§æ¹æ³ä¸ºå¯¼å ¥æ°æ®æå¼äºæ°çä¸æé¨ãä½ å¯ä»¥å¾å®¹æçå°å¤é¨æ件ä¸æ°æ®åºç¸å ³èï¼å¹¶ä¸å¨æ°æ®åºä¸å建对åºç External Tableï¼ç¶åå°±å¯ä»¥ç«å³æ¥è¯¢æ°æ®ï¼å°±è±¡å¤é¨æ°æ®å·²ç»å¯¼å ¥å°æ°æ®åºè¡¨ä¸ä¸æ ·ãå¯ä¸çä¸è¶³éè¦æç¡®ï¼æ°æ®å¹¶æªçæ£å¯¼å ¥å°æ°æ®åºä¸ï¼å½å¤é¨æ件被å é¤æè¦çæ¶ï¼æ°æ®åºå°ä¸è½è®¿é® External Table éçæ°æ®ï¼èä¸ç´¢å¼æ²¡æ被å建ï¼è®¿é®æ°æ®é度å°ææç¼æ ¢ãå建 CALLS_EXTERNALï¼External Table表ï¼å¦ä¸ï¼ä½¿ä¹ä¸å¤é¨æ°æ®æä»¶å ³èï¼
CREATE TABLE calls_external (call_id NUMBER, call_date DATE, emp_id NUMBER, call_type VARCHAR2(12), details VARCHAR2(25)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY extract_files_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (call_id, call_date CHAR DATE_FORMAT DATE MASK "yyy-mm-dd:hh24:mi:ss", emp_id, call_type, details ) ) LOCATION ('calls.dat') );
ç¶åå° External Table ä¸çæ£è¢«ä½¿ç¨ç表 CALLS å ³èåæ¥ï¼å é¤ CALLS 表并é建å®ï¼
CREATE TABLE calls ( call_id NUMBER NOT NULL, call_date DATE NOT NULL, emp_id NUMBER NOT NULL, call_type VARCHAR2(12) NOT NULL, details VARCHAR2(25) ) TABLESPACE tbs1 NOLOGGING AS SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
å 为 CALLS 表æ¯çæ£çæ°æ®åºè¡¨ï¼å¯ä»¥å建索å¼æ¥å 快访é®ï¼è¡¨ä¸çæ°æ®å°è¢«ä¿çï¼å³ä½¿å¤é¨æ°æ®æ件被æ´æ°æ被å é¤ãå¨å»ºè¡¨è¯å¥ä¸NOLOGGINGå ³é®åç¨äºå å¿«ç´¢å¼é建ã
è¿ç¨è¿ç§æ¹æ³å¯¼å ¥æ°æ®ï¼æ»çå¯¼å ¥æ¶é´ä¸º 15 ç§ï¼è¿ç¨å ç¨ CPU çæ¶é´ä¸º8ç§ï¼è¿æ¯åä¸ç§æ¹æ³ç¨å¾®æ ¢äºï¼ä½ä¸è½å°±æ¤è®¤ä¸ºä½¿ç¨ External Table å¯¼å ¥æ°æ®ä¸å®æ¯ OCI æ¹éæå ¥æ ¢ã
è¿ç§æ¹æ³çä¼ç¹æ¯ï¼æªç»è¿è¡å¤§éçç¼å代ç å°±åå¾äºä¸éçç»æï¼ä¸è±¡ OCI æ¹éæå ¥åå¨ç¼ç é误é£é©ï¼å®è¿å¯ä»¥ä½¿ç¨ dbms_job å è°åº¦æ°æ®å¯¼å ¥è¿ç¨ï¼å®ç°æ°æ®å¯¼å ¥çèªå¨åãå ¶ç¼ºç¹æ¯ç®æ è¡¨å¿ é¡»å å é¤åé建ï¼å¦æåªéè¦å¯¼å ¥å¢éæ°æ®æ¶æ¤æ¹æ³å°±ä¸åéäºï¼å¦å¤ç¨æ·å¨è¡¨çé建è¿ç¨ä¸è®¿é®æ°æ®æ¶ä¼éå° "table or view does not exist" çé误ï¼å®ä» éç¨äº Oracle 9i 以ä¸çæ¬çæ°æ®åºã
INSERT Append as SELECTï¼ä½¿ç¨ Oracle9i ç External Table
ä¸ä¸ç§æ¹æ³æ¼ç¤ºäºå¦ä½å建ä¸å¤é¨æ°æ®æä»¶å ³èçæ°æ®åºè¡¨ï¼å ¶è¡¨çæ°æ®æ¯ç±å¤é¨æ°æ®æ件æ å°è¿æ¥ã缺ç¹æ¯æ°æ®åºè¡¨éè¦è¢«å å é¤åé建æ¥ä¿æä¸å¤é¨æ°æ®æ件çä¸è´ååæ¥ï¼å¯¹å¯¼å ¥å¢éçæ°æ®èä¸éè¦å é¤å·²ææ°æ®çæ åµä¸åéãé对è¿ç§éæ±ï¼Oracle æä¾äº INSERT è¯å¥å¤å¸¦ APPEND æ示æ¥æ»¡è¶³ã
INSERT /*+ APPEND */ INTO calls (call_id, call_date, emp_id, call_type, details) SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
该è¯å¥è¯»åå¼ç¨å¤é¨æ°æ®æ件ç CALLS_EXTERNAL 表ä¸å 容ï¼å¹¶å°ä¹å¢å å°è¡¨ CALLS ä¸ãAppend æ示åè¯ Oracle 使ç¨å¿«éæºå¶æ¥æå ¥æ°æ®ï¼åæ¶å¯ä»¥é å使ç¨è¡¨ç NOLOGGING å ³é®åã
å¯ä»¥é¢è§è¿ç§æ¹æ³ä¸åä¸æ¹æ³æ¶èäºç¸åçæ¶é´ï¼æ¯ç«å®ä»¬æ¯ä½¿ç¨ External Table ç¹æ§å¯¼å ¥æ°æ®çä¸åé¶æ®µè§£å³æ¹æ³ãå¦æç®æ 表ä¸æ¯ç©ºçï¼é£å°ä¼æ¶èç¨å¾®é¿çæ¶é´ï¼å 为è¦é建æ´é¿çç´¢å¼ï¼ï¼èåä¸ CREATE TABLE as SELECT æ¹æ³æ¯æ´ä½å建索å¼ã
SQL*Loaderç强大åè½
SQL*Loader æ¯ Oracle æä¾çå¯¼å ¥å®ç¨ç¨åºï¼ç¹å«é对ä»å¤é¨æä»¶å¯¼å ¥å¤§æ¹éæ°æ®è¿å ¥æ°æ®åºè¡¨ãè¯¥å·¥å ·å·²ç»æå¤å¹´çåå²ï¼æ¯ä¸æ¬¡çæ¬å级é½ä½¿å ¶æ´å 强大ãçµæ´»åå¿«æ·ï¼ä½éæ¾çæ¯å®çè¯æ³å´æ¯ç¥ç§èä¸ç´è§ï¼å¹¶ä¸åªè½ä»å½ä»¤è¡çªå£å¤è¿è¡è°ç¨ã
尽管å®æä¸ç´è§ç缺ç¹ï¼ä½å´æ¯æå¿«æææçå¯¼å ¥æ°æ®æ¹æ³ã缺çæ åµä¸å®ä½¿ç¨ "conventional path" 常è§é项æ¥æ¹éå¯¼å ¥æ°æ®ï¼å ¶æ§è½æé«åº¦å¹¶ä¸ææ¾ãæ建议使ç¨æ´å¿«éçå¯¼å ¥åæ°é项ï¼å¨å½ä»¤è¡æ·»å "direct=true" é项è°ç¨ "direct path" å¯¼å ¥é项ãå¨ "direct path" å¯¼å ¥å®ç°ä¸ï¼ç¨åºå¨æ°æ®åºè¡¨çæ°æ°æ®åç high water mark å¤ç´æ¥åå ¥å¯¼å ¥æ°æ®ï¼ç¼©çäºæ°æ®æå ¥çå¤çæ¶é´ï¼åæ¶ä¼å使ç¨äºé常ææçB+äºåæ æ¹æ³æ¥æ´æ°è¡¨çç´¢å¼ã
è¿ç¨è¿ç§æ¹æ³ï¼å¦æ使ç¨ç¼ºçç conventional path å¯¼å ¥é项ï¼æ»çå¯¼å ¥æ¶é´æ¯ 81 ç§ï¼è¿ç¨å ç¨ CPU æ¶é´å¤§çº¦æ¯ 12 ç§ï¼è¿å æ¬äºæ´æ°è¡¨çç´¢å¼æ¶é´ãå¦æä½¿ç¨ direct path å¯¼å ¥é项ï¼æ»çå¯¼å ¥æ¶é´ç«æ¯ 9 ç§ï¼è¿ç¨å ç¨ CPU æ¶é´ä¹ä» ä» æ¯ 3 ç§ï¼ä¹å æ¬äºæ´æ°è¡¨çç´¢å¼æ¶é´ã
ç±æ¤å¯è§ï¼å°½ç®¡è¡¨ä¸çç´¢å¼å¨æ°æ®å¯¼å ¥ä¹å并没æ被å é¤ï¼ä½¿ç¨SQL*Loaderçdirect path å¯¼å ¥é项ä»ç¶æ¯å¿«éåææçãå½ç¶å®ä¹æ缺ç¹ï¼å°±åNOLOGGINGå ³é®åä¸æ ·è¯¥æ¹æ³ä¸çæREDOæ¥å¿æ°æ®ï¼å¯¼å ¥è¿ç¨åºéåå°æ æ³æ¢å¤å°å åç¶æï¼å¨æ°æ®å¯¼å ¥è¿ç¨ä¸è¡¨çç´¢å¼æ¯ä¸èµ·ä½ç¨çï¼ç¨æ·æ¤æ¶è®¿é®è¯¥è¡¨æ¶å°åºç°è¿ç¼ï¼å½ç¶å¨æ°æ®å¯¼å ¥çè¿ç¨ä¸æ好ä¸è¦è®©ç¨æ·è®¿é®è¡¨ã
ååºäº¤æ¢ (Partition Exchange)
以ä¸è®¨è®ºçæ°æ®å¯¼å ¥æ¹æ³é½æä¸ä¸ªéå¶ï¼å°±æ¯è¦æ±ç¨æ·å¨å¯¼å ¥æ°æ®å®æä¹åæå¯ä»¥è®¿é®æ°æ®åºè¡¨ãé¢å¯¹7Ã24ä¸é´æ访é®æ°æ®åºæ¥è¯´ï¼å¦ææ们åªæ¯å¯¼å ¥éè¦å¢å çæ°æ®æ¶ï¼è¿ç§éå¶å°å¯¹ç¨æ·çå®æ¶è®¿é®äº§çå½±åãOracleå¨è¿æ¹é¢æä¾äºè¡¨ååºåè½ï¼å®å¯ä»¥åå°å¯¼å ¥æ°æ®æä½å¯¹ç¨æ·å®æ¶è®¿é®æ°æ®çå½±åï¼æä½æ¨¡å¼å°±è±¡ä½¿ç¨å¯çææç硬çä¸æ ·ï¼åªä¸è¿è¿éç硬çæ¢æäºååºï¼Partitionï¼èå·²ãéè¦å£°æçæ¯ Partitioning ååºåè½åªæå¨ä¼ä¸çæ°æ®åºä¸ææä¾ã
å¨ä¸ä¸ªè¢«ååºè¿ç表ä¸ï¼åç°ç»ç¨æ·ç表æ¯å¤ä¸ªååºæ®µï¼segmentsï¼çéåãååºå¯ä»¥å¨éè¦æ¶è¢«æ·»å ï¼å¨ç»´æ¤æ¶è¢«å¸è½½æå é¤ï¼ååºè¡¨å¯ä»¥åæ°æ®åºä¸ç表交æ¢æ°æ®ï¼åªè¦å®ä»¬ç表ç»æåå段类åæ¯ä¸è´çï¼äº¤æ¢åçååºè¡¨å°æ¥æä¸ä¹äºå¨ç表çæ°æ®ãéè¦æ³¨æçæ¯ï¼è¿ç§äº¤æ¢åªæ¯å¨Oracleæ°æ®åºçæ°æ®åå ¸å±é¢ä¸è¿è¡ï¼å¹¶æ²¡ææ°æ®è¢«å®é 移å¨ï¼æ以ååºè¡¨äº¤æ¢æ¯æå ¶å¿«éçã
为äºå建å®éªç¯å¢ï¼å å设CALLS表æ¯ä¸ªååºè¡¨ï¼è¦å建ä¸ä¸ªç©ºçååºPART_01012004ï¼ç¨æ¥ä¿å2004å¹´1æ1æ¥çå¼å«æ°æ®ãç¶åéè¦åå建ä¸ä¸´æ¶è¡¨ä¸ºCALLS_TEMPï¼è¯¥è¡¨ä¸CALLS表æ¥æç¸åçå段åæ°æ®ç±»åã
æ们使ç¨å åä»ç»çå¯¼å ¥æ¹æ³å°åä¸æ¡æ°æ®å¯¼å ¥å°CALLS_TEMP表ä¸ï¼å¯ä»¥èå¿çå¾ æ°æ®å®å ¨å¯¼å ¥å°CALLS_TEMP表ä¸ï¼å¹¶ä¸å建好索å¼åç¸å ³çº¦ææ¡ä»¶ï¼ææè¿ä¸åæä½å¹¶ä¸å½±åç¨æ·å®æ¶è®¿é®CALLS表ï¼å 为æ们åªå¯¹CALLS_TEMP临æ¶è¡¨è¿è¡äºæä½ãä¸æ¦æ°æ®å¯¼å ¥å®æï¼CALLS_TEMP表就åæ2004å¹´1æ1æ¥çå¼å«æ°æ®ãåæ¶å©ç¨CALLS表ä¸å为PART_01012004ç空ååºï¼ä½¿ç¨å¦ä¸è¯å¥æ§è¡ååºäº¤æ¢ï¼
ALTER TABLE calls EXCHANGE PARTITION part_01012004 WITH TABLE calls_temp INCLUDING INDEXES WITHOUT VALIDATION;
ååºäº¤æ¢æä½å°é常快éå°åªæ´æ°CALLS表çæ°æ®åå ¸ï¼PART_01012004ååºè¡¨å³å»æ¥æCALLS_TEMP表çæææ°æ®ï¼èCALLS_TEMP表å为空表ãåå®CALLS表使ç¨å±é¨ç´¢å¼èéå ¨å±ç´¢å¼ï¼ä¸è¿°è¯å¥ä¸çINCLUDING INDEXESå°ä¿è¯ååºäº¤æ¢å æ¬ç´¢å¼çå¯ç¨æ§ï¼WITHOUT VALIDATION ææä¸æ£æ¥äº¤æ¿è¡¨ä¸æ°æ®çå¹é ï¼å å¿«äºäº¤æ¢çé度ã
ç»è®º
以ä¸æ¢è®¨äºOracleæ°æ®åºçå¤ç§æ°æ®å¯¼å ¥æ¹æ³ï¼æ¯ç§æ¹æ³é½æå ¶ä¼ç¼ºç¹åéç¨ç¯å¢ï¼è½å¤æ»¡è¶³ä½ ä¸åçå¯¼å ¥éæ±ï¼å½ç¶ä½ éè¦å¨äºè§£äºè¿äºæ¹æ³åï¼å¨é度ãç®ææ§ãçµæ´»æ§ãå¯æ¢å¤æ§åæ°æ®å¯ç¨æ§ä¹é´å¯»æ±æä½³å¯¼å ¥æ¹æ¡ã
为äºå¯¹æ¯åç§æ¹æ³çææï¼æ们å建äºä¸ä¸ªå®ä¾æ¥å±ç¤ºåç§æ¹æ³çå¯¼å ¥æçåææï¼ä»ä¸ä½ å¯ä»¥éæ©æéåçæ¹æ³ç¨äºä»åçæ°æ®å¯¼å ¥å·¥ä½ãåæ¶è¯·è®°ä½ï¼æ¬æ并æªåæ¬ææçORACLEæ°æ®å¯¼å ¥ææ¯ï¼æ¯å¦å¹¶è¡æ°æ®å¯¼å ¥ææ¯ï¼ï¼è¿éè¦æ们继ç»ä¸æçæ¢ç´¢åå°è¯ã
示ä¾è¡¨ç»æåæ°æ®é
为äºæ¼ç¤ºåæ¯è¾åç§æ°æ®å¯¼å ¥æ¹æ³ï¼æåå®æ°æ®å¯¼å ¥ä»»å¡æ¯å°å¤é¨æ件æ°æ®å¯¼å ¥å° Oracle æ°æ®åºçCALLS表ä¸ï¼å¤é¨æ°æ®æ件å å«åä¸æ¡å¼å«ä¸å¿è®°å½ï¼å°è¿ 6MB çæ件大å°ï¼å ·ä½çæ°æ®ç¤ºä¾å¦ä¸:
82302284384,2003-04-18:13:18:58,5001,æè¯,ææºä¸å 维修质é82302284385,2003-04-18:13:18:59,3352,å¨è¯¢,ä¾æ°´ç线çå·ç 82302284386,2003-04-18:13:19:01,3142,建议,å¢è®¾å ¬äº¤çº¿è·¯
æ¥åå¯¼å ¥æ°æ®ç表åæ¯ CALLSï¼è¡¨ç»æå¦ä¸ï¼
Name Null? Type Comment ------------ --------- ------------- ----------------- CALL_ID NOT NULL NUMBER Primary key CALL_DATE NOT NULL DATE Non-unique index EMP_ID NOT NULL NUMBER CALL_TYPE NOT NULL VARCHAR2(12) DETAILS VARCHAR2(25)
éæ¡æ°æ®æå ¥INSERT
æ°æ®å¯¼å ¥çæç®åæ¹æ³å°±æ¯ç¼å INSERT è¯å¥ï¼å°æ°æ®éæ¡æå ¥æ°æ®åºãè¿ç§æ¹æ³åªéåå¯¼å ¥å°éæ°æ®ï¼å¦ SQL*Plus èæ¬å建æ个表çç§åæ°æ®ã该æ¹æ³çæ大缺ç¹å°±æ¯å¯¼å ¥é度ç¼æ ¢ï¼å ç¨äºå¤§éç CPU å¤çæ¶é´ï¼ä¸éå大æ¹éæ°æ®çå¯¼å ¥ï¼èå ¶ä¸»è¦ä¼ç¹å°±æ¯å¯¼å ¥ææç®ååæä¿®æ¹å®åçå¼¹æ§ï¼ä¸éè¦å¤åå ¶å®çåå¤å°±å¯ä»¥ä½¿ç¨ãå¦æä½ æå¾å¤æ¶é´æ²¡æ³æåï¼åæ³æ磨ä¸ä¸æ°æ®åºå CPUï¼é£è¿ç§æ¹æ³æ£éåä½ ã:)
为äºä¸å ¶å®æ¹æ³åæ¯è¾ï¼ç°å°åä¸æ¡è®°å½éè¿æ¤æ¹æ³å¯¼å ¥å° CALLS 表ä¸ï¼æ»å ±æ¶è 172 ç§ï¼å ¶ä¸å¯¼å ¥è¿ç¨å ç¨ CPU æ¶é´ä¸º 52 ç§ã
éæ¡æ°æ®æå ¥ INSERTï¼è¡¨ææ ç´¢å¼
为ä»ä¹ä¸ä¸ç§æ¹æ³å ç¨äºè¾å¤ç CPU å¤çæ¶é´ï¼å ³é®æ¯ CALLS 表ä¸å·²å建äºç´¢å¼ï¼å½ä¸æ¡æ°æ®æå ¥å°è¡¨ä¸æ¶ï¼Oracle éè¦å¤å«æ°æ°æ®ä¸èæ°æ®å¨ç´¢å¼æ¹é¢æ¯å¦æå²çªï¼åæ¶è¦æ´æ°è¡¨ä¸çææç´¢å¼ï¼éå¤æ´æ°ç´¢å¼ä¼æ¶èä¸å®çæ¶é´ãå æ¤æé«å¯¼å ¥é度ç好åæ³å°±æ¯å¨å建表æ¶å ä¸å建索å¼æè å¨å¯¼å ¥æ°æ®ä¹åå é¤ææç´¢å¼ï¼å¨å¤é¨æ件æ°æ®éæ¡æå ¥å°è¡¨ä¸ååç»ä¸å建表çç´¢å¼ãè¿æ ·å¯¼å ¥é度ä¼æé«ï¼åæ¶å建çç´¢å¼ä¹å¾ç´§åèææï¼è¿ä¸åååæ ·éç¨äºä½å¾ç´¢å¼ï¼Bitmap Indexï¼ã对äºä¸»è¦çåå¯ä¸çå ³é®çº¦æ(key constraints)ï¼å¯ä»¥ä½¿ä¹å ææ¶å¤±æ(disabling)æè å é¤çº¦ææ¥è·å¾åæ ·çææï¼å½ç¶è¿äºåæ³ä¼å¯¹å·²ç»åå¨ç表çå¤é®çº¦æ产çç¸å ³çå½±åï¼å¨å é¤åéè¦éçæé ã
éè¦è¯´æçæ¯ï¼è¿ç§æ¹æ³å¨è¡¨ä¸å·²åå¨å¾å¤æ°æ®çæ åµä¸ä¸å¤ªåéãä¾å¦è¡¨ä¸å·²æä¹åä¸æ¡æ°æ®ï¼èæ¤æ¶éè¦è¿½å æå ¥ä¸åä¸æ¡æ°æ®ï¼å®é å¯¼å ¥æ°æ®èççæ¶é´å°ä¼è¢«éæ°å建ä¸äº¿æ¡æ°æ®çç´¢å¼ææ¶èæ®å°½ï¼è¿æ¯æ们ä¸å¸æå¾å°çç»æãä½æ¯ï¼å¦æè¦å¯¼å ¥æ°æ®ç表æ¯ç©ºçæå¯¼å ¥çæ°æ®éæ¯å·²æçæ°æ®éè¦å¤§å¾å¤ï¼é£ä¹å¯¼å ¥æ°æ®èççæ¶é´å°ä¼å°éç¨äºéæ°å建索å¼ï¼è¿æ¶è¯¥æ¹æ³æå¯ä»¥èè使ç¨ã
å å¿«ç´¢å¼å建æ¯å¦ä¸ä¸ªéè¦èèçé®é¢ã为äºåå°ç´¢å¼å建ä¸æåºçå·¥ä½æ¶é´ï¼å¯ä»¥å¨å½åä¼è¯ä¸å¢å SORT_AREA_SIZE åæ°ç大å°ï¼è¯¥åæ°å 许å½åä¼è¯å¨å åçç´¢å¼å建è¿ç¨ä¸æ§è¡æ´å¤çæåºæä½ãåæ ·è¿å¯ä»¥ä½¿ç¨ NOLOGGING å ³é®åæ¥åå°å å建索å¼èçæç REDO æ¥å¿éï¼NOLOGGING å ³é®åä¼å¯¹æ°æ®åºçæ¢å¤å Standby å¤ç¨æ°æ®åºäº§çææ¾çå½±åï¼æ以å¨ä½¿ç¨ä¹åè¦ä»ç»æé ï¼å°åºæ¯é度ä¼å è¿æ¯ç¨³å®ä¼å ã
è¿ç¨è¿ç§æ¹æ³ï¼å å é¤ CALLS 表ç主é®åä¸å¯ä¸çç´¢å¼ï¼ç¶åéæ¡å¯¼å ¥æ°æ®ï¼å®æåéæ°å建索å¼( 表å¨å¯¼å ¥æ°æ®åæ¯ç©ºç)ã该æ¹æ³æ»å ±æ¶è 130 ç§ï¼å æ¬é建索å¼çæ¶é´ï¼å ¶ä¸å¯¼å ¥è¿ç¨å ç¨ CPU æ¶é´ä¸º 35ç§ã
è¿ç§æ¹æ³çä¼ç¹æ¯å¯ä»¥å å¿«å¯¼å ¥çé度并使索å¼æ´å ç´§åææï¼ç¼ºç¹æ¯ç¼ºä¹éç¨æ§ï¼å½ä½ 对表å¢å æ°çå¤æç模å¼å ç´ ï¼ç´¢å¼ãå¤é®çï¼æ¶ä½ éè¦æ·»å 代ç ãä¿®æ¹å¯¼å ¥æ§è¡ç¨åºãå¦å¤é对 7*24 å¨çº¿è¦æ±çæ°æ®åºå¨çº¿å¯¼å ¥æä½æ¶ï¼å é¤è¡¨çç´¢å¼ä¼å¯¹å¨çº¿ç¨æ·çæ¥è¯¢æå¾å¤§çæ§è½å½±åï¼åæ¶ä¹è¦èèï¼ä¸»è¦æå¯ä¸çå ³é®çº¦ææ¡ä»¶çå é¤æ失æå¯è½ä¼å½±åå°å¼ç¨å®ä»¬çå¤é®ç使ç¨ã
æ¹éæå ¥ï¼è¡¨ææ ç´¢å¼
å¨Oracle V6 ä¸ OCI ç¼ç¨æ¥å£å å ¥äºæ°ç»æ¥å£ç¹æ§ãæ°ç»æä½å è®¸å¯¼å ¥ç¨åºè¯»åå¤é¨æ件æ°æ®å¹¶è§£æåï¼åæ°æ®åºæ交SQLè¯å¥ï¼æ¹éæå ¥ SQL è¯å¥æ£ç´¢åºçæ°æ®ãOracle ä» éè¦æ§è¡ä¸æ¬¡ SQL è¯å¥ï¼ç¶åå¨å åä¸æ¹é解ææä¾çæ°æ®ãæ¹éå¯¼å ¥æä½æ¯éè¡æå ¥éå¤æä½æ´ææçï¼è¿æ¯å 为åªéä¸æ¬¡è§£æ SQL è¯å¥ï¼ä¸äºæ°æ®ç»è®¢æä½ä»¥åç¨åºä¸æ°æ®åºä¹é´æ¥åçæä½é½æ¾èåå°ï¼èä¸æ°æ®åºå¯¹æ¯ä¸æ¡æ°æ®çæä½é½æ¯éå¤å¯ç¥çï¼è¿ç»æ°æ®åºæä¾äºä¼åæ§è¡çå¯è½ãå ¶ä¼ç¹æ¯æ°æ®å¯¼å ¥çæ»ä½æ¶é´ææ¾åå°ï¼ç¹å«æ¯è¿ç¨å ç¨ CPU çæ¶é´ã
éè¦æéçæ¯ï¼éè¿ OCI æ¥å£ç¡®å®å¯ä»¥æ§è¡æ°æ®æ¹éå¯¼å ¥æä½ï¼ä½æ¯è®¸å¤å·¥å ·åèæ¬è¯è¨å´ä¸æ¯æ使ç¨æ¤åè½ãå¦æè¦ä½¿ç¨è¯¥æ¹æ³ï¼éè¦ç ç©¶ä½ æ使ç¨çå¼åå·¥å ·æ¯å¦æ¯æ OCI æ¹éæä½åè½ãå¯¼å ¥ç¨åºéè¦è¿è¡å¤æçç¼ç 并å¯è½åå¨é误çé£é©ï¼ç¼ºä¹ä¸å®çå¼¹æ§ã
è¿ç¨ä¸è¿°æ¹æ³ï¼ç¨åºå°å¤é¨æ°æ®æåå°å åä¸çæ°ç»éï¼å¹¶æ§è¡æ¹éæå ¥æä½ï¼100è¡/次ï¼ï¼ä¿çäºè¡¨çå é¤/é建索å¼æä½ï¼æ»çå¯¼å ¥æ¶é´ä¸éå° 14 ç§ï¼èè¿ç¨å ç¨ CPU çæ¶é´ä¸éå°7ç§ï¼å¯è§å®é å¯¼å ¥æ°æ®æè±è´¹çæ¶é´æ¾èä¸éäº 95%ã
CREATE TABLE AS SELECTï¼ä½¿ç¨Oracle9içExternal Table
Oracle 9i çä¸é¡¹æ°ç¹æ§å°±æ¯ External Tableï¼å®å°±è±¡é常çæ°æ®åºè¡¨ä¸æ ·ï¼æ¥æå段åæ°æ®ç±»å约æï¼å¹¶ä¸å¯ä»¥æ¥è¯¢ï¼ä½æ¯è¡¨ä¸çæ°æ®å´ä¸åå¨å¨æ°æ®åºä¸ï¼èæ¯å¨ä¸æ°æ®åºç¸å ³èçæ®éå¤é¨æ件éãå½ä½ æ¥è¯¢ External Table æ¶ï¼Oracle å°è§£æ该æ件并è¿å符åæ¡ä»¶çæ°æ®ï¼å°±è±¡è¯¥æ°æ®åå¨å¨æ°æ®åºè¡¨ä¸ä¸æ ·ã
éè¦æ³¨æçæ¯ï¼ä½ å¯ä»¥å¨æ¥è¯¢è¯å¥ä¸å° External Table ä¸æ°æ®åºä¸å ¶ä»è¡¨è¿è¡è¿æ¥ï¼Joinï¼ï¼ä½æ¯ä¸è½ç» External Table å ä¸ç´¢å¼ï¼å¹¶ä¸ä¸è½æå ¥/æ´æ°/å é¤æ°æ®ï¼æ¯ç«å®ä¸æ¯çæ£çæ°æ®åºè¡¨ãå¦å¤ï¼å¦æä¸æ°æ®åºç¸å ³èçå¤é¨æ件被æ¹åæè 被å é¤ï¼è¿ä¼å½±åå° External Table è¿åæ¥è¯¢ç»æï¼æ以å¨åå¨åè¦å è·æ°æ®åºææå¼ã
è¿ç§æ¹æ³ä¸ºå¯¼å ¥æ°æ®æå¼äºæ°çä¸æé¨ãä½ å¯ä»¥å¾å®¹æçå°å¤é¨æ件ä¸æ°æ®åºç¸å ³èï¼å¹¶ä¸å¨æ°æ®åºä¸å建对åºç External Tableï¼ç¶åå°±å¯ä»¥ç«å³æ¥è¯¢æ°æ®ï¼å°±è±¡å¤é¨æ°æ®å·²ç»å¯¼å ¥å°æ°æ®åºè¡¨ä¸ä¸æ ·ãå¯ä¸çä¸è¶³éè¦æç¡®ï¼æ°æ®å¹¶æªçæ£å¯¼å ¥å°æ°æ®åºä¸ï¼å½å¤é¨æ件被å é¤æè¦çæ¶ï¼æ°æ®åºå°ä¸è½è®¿é® External Table éçæ°æ®ï¼èä¸ç´¢å¼æ²¡æ被å建ï¼è®¿é®æ°æ®é度å°ææç¼æ ¢ãå建 CALLS_EXTERNALï¼External Table表ï¼å¦ä¸ï¼ä½¿ä¹ä¸å¤é¨æ°æ®æä»¶å ³èï¼
CREATE TABLE calls_external (call_id NUMBER, call_date DATE, emp_id NUMBER, call_type VARCHAR2(12), details VARCHAR2(25)) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY extract_files_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (call_id, call_date CHAR DATE_FORMAT DATE MASK "yyy-mm-dd:hh24:mi:ss", emp_id, call_type, details ) ) LOCATION ('calls.dat') );
ç¶åå° External Table ä¸çæ£è¢«ä½¿ç¨ç表 CALLS å ³èåæ¥ï¼å é¤ CALLS 表并é建å®ï¼
CREATE TABLE calls ( call_id NUMBER NOT NULL, call_date DATE NOT NULL, emp_id NUMBER NOT NULL, call_type VARCHAR2(12) NOT NULL, details VARCHAR2(25) ) TABLESPACE tbs1 NOLOGGING AS SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
å 为 CALLS 表æ¯çæ£çæ°æ®åºè¡¨ï¼å¯ä»¥å建索å¼æ¥å 快访é®ï¼è¡¨ä¸çæ°æ®å°è¢«ä¿çï¼å³ä½¿å¤é¨æ°æ®æ件被æ´æ°æ被å é¤ãå¨å»ºè¡¨è¯å¥ä¸NOLOGGINGå ³é®åç¨äºå å¿«ç´¢å¼é建ã
è¿ç¨è¿ç§æ¹æ³å¯¼å ¥æ°æ®ï¼æ»çå¯¼å ¥æ¶é´ä¸º 15 ç§ï¼è¿ç¨å ç¨ CPU çæ¶é´ä¸º8ç§ï¼è¿æ¯åä¸ç§æ¹æ³ç¨å¾®æ ¢äºï¼ä½ä¸è½å°±æ¤è®¤ä¸ºä½¿ç¨ External Table å¯¼å ¥æ°æ®ä¸å®æ¯ OCI æ¹éæå ¥æ ¢ã
è¿ç§æ¹æ³çä¼ç¹æ¯ï¼æªç»è¿è¡å¤§éçç¼å代ç å°±åå¾äºä¸éçç»æï¼ä¸è±¡ OCI æ¹éæå ¥åå¨ç¼ç é误é£é©ï¼å®è¿å¯ä»¥ä½¿ç¨ dbms_job å è°åº¦æ°æ®å¯¼å ¥è¿ç¨ï¼å®ç°æ°æ®å¯¼å ¥çèªå¨åãå ¶ç¼ºç¹æ¯ç®æ è¡¨å¿ é¡»å å é¤åé建ï¼å¦æåªéè¦å¯¼å ¥å¢éæ°æ®æ¶æ¤æ¹æ³å°±ä¸åéäºï¼å¦å¤ç¨æ·å¨è¡¨çé建è¿ç¨ä¸è®¿é®æ°æ®æ¶ä¼éå° "table or view does not exist" çé误ï¼å®ä» éç¨äº Oracle 9i 以ä¸çæ¬çæ°æ®åºã
INSERT Append as SELECTï¼ä½¿ç¨ Oracle9i ç External Table
ä¸ä¸ç§æ¹æ³æ¼ç¤ºäºå¦ä½å建ä¸å¤é¨æ°æ®æä»¶å ³èçæ°æ®åºè¡¨ï¼å ¶è¡¨çæ°æ®æ¯ç±å¤é¨æ°æ®æ件æ å°è¿æ¥ã缺ç¹æ¯æ°æ®åºè¡¨éè¦è¢«å å é¤åé建æ¥ä¿æä¸å¤é¨æ°æ®æ件çä¸è´ååæ¥ï¼å¯¹å¯¼å ¥å¢éçæ°æ®èä¸éè¦å é¤å·²ææ°æ®çæ åµä¸åéãé对è¿ç§éæ±ï¼Oracle æä¾äº INSERT è¯å¥å¤å¸¦ APPEND æ示æ¥æ»¡è¶³ã
INSERT /*+ APPEND */ INTO calls (call_id, call_date, emp_id, call_type, details) SELECT call_id, call_date, emp_id, call_type, details FROM calls_external;
该è¯å¥è¯»åå¼ç¨å¤é¨æ°æ®æ件ç CALLS_EXTERNAL 表ä¸å 容ï¼å¹¶å°ä¹å¢å å°è¡¨ CALLS ä¸ãAppend æ示åè¯ Oracle 使ç¨å¿«éæºå¶æ¥æå ¥æ°æ®ï¼åæ¶å¯ä»¥é å使ç¨è¡¨ç NOLOGGING å ³é®åã
å¯ä»¥é¢è§è¿ç§æ¹æ³ä¸åä¸æ¹æ³æ¶èäºç¸åçæ¶é´ï¼æ¯ç«å®ä»¬æ¯ä½¿ç¨ External Table ç¹æ§å¯¼å ¥æ°æ®çä¸åé¶æ®µè§£å³æ¹æ³ãå¦æç®æ 表ä¸æ¯ç©ºçï¼é£å°ä¼æ¶èç¨å¾®é¿çæ¶é´ï¼å 为è¦é建æ´é¿çç´¢å¼ï¼ï¼èåä¸ CREATE TABLE as SELECT æ¹æ³æ¯æ´ä½å建索å¼ã
SQL*Loaderç强大åè½
SQL*Loader æ¯ Oracle æä¾çå¯¼å ¥å®ç¨ç¨åºï¼ç¹å«é对ä»å¤é¨æä»¶å¯¼å ¥å¤§æ¹éæ°æ®è¿å ¥æ°æ®åºè¡¨ãè¯¥å·¥å ·å·²ç»æå¤å¹´çåå²ï¼æ¯ä¸æ¬¡çæ¬å级é½ä½¿å ¶æ´å 强大ãçµæ´»åå¿«æ·ï¼ä½éæ¾çæ¯å®çè¯æ³å´æ¯ç¥ç§èä¸ç´è§ï¼å¹¶ä¸åªè½ä»å½ä»¤è¡çªå£å¤è¿è¡è°ç¨ã
尽管å®æä¸ç´è§ç缺ç¹ï¼ä½å´æ¯æå¿«æææçå¯¼å ¥æ°æ®æ¹æ³ã缺çæ åµä¸å®ä½¿ç¨ "conventional path" 常è§é项æ¥æ¹éå¯¼å ¥æ°æ®ï¼å ¶æ§è½æé«åº¦å¹¶ä¸ææ¾ãæ建议使ç¨æ´å¿«éçå¯¼å ¥åæ°é项ï¼å¨å½ä»¤è¡æ·»å "direct=true" é项è°ç¨ "direct path" å¯¼å ¥é项ãå¨ "direct path" å¯¼å ¥å®ç°ä¸ï¼ç¨åºå¨æ°æ®åºè¡¨çæ°æ°æ®åç high water mark å¤ç´æ¥åå ¥å¯¼å ¥æ°æ®ï¼ç¼©çäºæ°æ®æå ¥çå¤çæ¶é´ï¼åæ¶ä¼å使ç¨äºé常ææçB+äºåæ æ¹æ³æ¥æ´æ°è¡¨çç´¢å¼ã
è¿ç¨è¿ç§æ¹æ³ï¼å¦æ使ç¨ç¼ºçç conventional path å¯¼å ¥é项ï¼æ»çå¯¼å ¥æ¶é´æ¯ 81 ç§ï¼è¿ç¨å ç¨ CPU æ¶é´å¤§çº¦æ¯ 12 ç§ï¼è¿å æ¬äºæ´æ°è¡¨çç´¢å¼æ¶é´ãå¦æä½¿ç¨ direct path å¯¼å ¥é项ï¼æ»çå¯¼å ¥æ¶é´ç«æ¯ 9 ç§ï¼è¿ç¨å ç¨ CPU æ¶é´ä¹ä» ä» æ¯ 3 ç§ï¼ä¹å æ¬äºæ´æ°è¡¨çç´¢å¼æ¶é´ã
ç±æ¤å¯è§ï¼å°½ç®¡è¡¨ä¸çç´¢å¼å¨æ°æ®å¯¼å ¥ä¹å并没æ被å é¤ï¼ä½¿ç¨SQL*Loaderçdirect path å¯¼å ¥é项ä»ç¶æ¯å¿«éåææçãå½ç¶å®ä¹æ缺ç¹ï¼å°±åNOLOGGINGå ³é®åä¸æ ·è¯¥æ¹æ³ä¸çæREDOæ¥å¿æ°æ®ï¼å¯¼å ¥è¿ç¨åºéåå°æ æ³æ¢å¤å°å åç¶æï¼å¨æ°æ®å¯¼å ¥è¿ç¨ä¸è¡¨çç´¢å¼æ¯ä¸èµ·ä½ç¨çï¼ç¨æ·æ¤æ¶è®¿é®è¯¥è¡¨æ¶å°åºç°è¿ç¼ï¼å½ç¶å¨æ°æ®å¯¼å ¥çè¿ç¨ä¸æ好ä¸è¦è®©ç¨æ·è®¿é®è¡¨ã
ååºäº¤æ¢ (Partition Exchange)
以ä¸è®¨è®ºçæ°æ®å¯¼å ¥æ¹æ³é½æä¸ä¸ªéå¶ï¼å°±æ¯è¦æ±ç¨æ·å¨å¯¼å ¥æ°æ®å®æä¹åæå¯ä»¥è®¿é®æ°æ®åºè¡¨ãé¢å¯¹7Ã24ä¸é´æ访é®æ°æ®åºæ¥è¯´ï¼å¦ææ们åªæ¯å¯¼å ¥éè¦å¢å çæ°æ®æ¶ï¼è¿ç§éå¶å°å¯¹ç¨æ·çå®æ¶è®¿é®äº§çå½±åãOracleå¨è¿æ¹é¢æä¾äºè¡¨ååºåè½ï¼å®å¯ä»¥åå°å¯¼å ¥æ°æ®æä½å¯¹ç¨æ·å®æ¶è®¿é®æ°æ®çå½±åï¼æä½æ¨¡å¼å°±è±¡ä½¿ç¨å¯çææç硬çä¸æ ·ï¼åªä¸è¿è¿éç硬çæ¢æäºååºï¼Partitionï¼èå·²ãéè¦å£°æçæ¯ Partitioning ååºåè½åªæå¨ä¼ä¸çæ°æ®åºä¸ææä¾ã
å¨ä¸ä¸ªè¢«ååºè¿ç表ä¸ï¼åç°ç»ç¨æ·ç表æ¯å¤ä¸ªååºæ®µï¼segmentsï¼çéåãååºå¯ä»¥å¨éè¦æ¶è¢«æ·»å ï¼å¨ç»´æ¤æ¶è¢«å¸è½½æå é¤ï¼ååºè¡¨å¯ä»¥åæ°æ®åºä¸ç表交æ¢æ°æ®ï¼åªè¦å®ä»¬ç表ç»æåå段类åæ¯ä¸è´çï¼äº¤æ¢åçååºè¡¨å°æ¥æä¸ä¹äºå¨ç表çæ°æ®ãéè¦æ³¨æçæ¯ï¼è¿ç§äº¤æ¢åªæ¯å¨Oracleæ°æ®åºçæ°æ®åå ¸å±é¢ä¸è¿è¡ï¼å¹¶æ²¡ææ°æ®è¢«å®é 移å¨ï¼æ以ååºè¡¨äº¤æ¢æ¯æå ¶å¿«éçã
为äºå建å®éªç¯å¢ï¼å å设CALLS表æ¯ä¸ªååºè¡¨ï¼è¦å建ä¸ä¸ªç©ºçååºPART_01012004ï¼ç¨æ¥ä¿å2004å¹´1æ1æ¥çå¼å«æ°æ®ãç¶åéè¦åå建ä¸ä¸´æ¶è¡¨ä¸ºCALLS_TEMPï¼è¯¥è¡¨ä¸CALLS表æ¥æç¸åçå段åæ°æ®ç±»åã
æ们使ç¨å åä»ç»çå¯¼å ¥æ¹æ³å°åä¸æ¡æ°æ®å¯¼å ¥å°CALLS_TEMP表ä¸ï¼å¯ä»¥èå¿çå¾ æ°æ®å®å ¨å¯¼å ¥å°CALLS_TEMP表ä¸ï¼å¹¶ä¸å建好索å¼åç¸å ³çº¦ææ¡ä»¶ï¼ææè¿ä¸åæä½å¹¶ä¸å½±åç¨æ·å®æ¶è®¿é®CALLS表ï¼å 为æ们åªå¯¹CALLS_TEMP临æ¶è¡¨è¿è¡äºæä½ãä¸æ¦æ°æ®å¯¼å ¥å®æï¼CALLS_TEMP表就åæ2004å¹´1æ1æ¥çå¼å«æ°æ®ãåæ¶å©ç¨CALLS表ä¸å为PART_01012004ç空ååºï¼ä½¿ç¨å¦ä¸è¯å¥æ§è¡ååºäº¤æ¢ï¼
ALTER TABLE calls EXCHANGE PARTITION part_01012004 WITH TABLE calls_temp INCLUDING INDEXES WITHOUT VALIDATION;
ååºäº¤æ¢æä½å°é常快éå°åªæ´æ°CALLS表çæ°æ®åå ¸ï¼PART_01012004ååºè¡¨å³å»æ¥æCALLS_TEMP表çæææ°æ®ï¼èCALLS_TEMP表å为空表ãåå®CALLS表使ç¨å±é¨ç´¢å¼èéå ¨å±ç´¢å¼ï¼ä¸è¿°è¯å¥ä¸çINCLUDING INDEXESå°ä¿è¯ååºäº¤æ¢å æ¬ç´¢å¼çå¯ç¨æ§ï¼WITHOUT VALIDATION ææä¸æ£æ¥äº¤æ¿è¡¨ä¸æ°æ®çå¹é ï¼å å¿«äºäº¤æ¢çé度ã
ç»è®º
以ä¸æ¢è®¨äºOracleæ°æ®åºçå¤ç§æ°æ®å¯¼å ¥æ¹æ³ï¼æ¯ç§æ¹æ³é½æå ¶ä¼ç¼ºç¹åéç¨ç¯å¢ï¼è½å¤æ»¡è¶³ä½ ä¸åçå¯¼å ¥éæ±ï¼å½ç¶ä½ éè¦å¨äºè§£äºè¿äºæ¹æ³åï¼å¨é度ãç®ææ§ãçµæ´»æ§ãå¯æ¢å¤æ§åæ°æ®å¯ç¨æ§ä¹é´å¯»æ±æä½³å¯¼å ¥æ¹æ¡ã
为äºå¯¹æ¯åç§æ¹æ³çææï¼æ们å建äºä¸ä¸ªå®ä¾æ¥å±ç¤ºåç§æ¹æ³çå¯¼å ¥æçåææï¼ä»ä¸ä½ å¯ä»¥éæ©æéåçæ¹æ³ç¨äºä»åçæ°æ®å¯¼å ¥å·¥ä½ãåæ¶è¯·è®°ä½ï¼æ¬æ并æªåæ¬ææçORACLEæ°æ®å¯¼å ¥ææ¯ï¼æ¯å¦å¹¶è¡æ°æ®å¯¼å ¥ææ¯ï¼ï¼è¿éè¦æ们继ç»ä¸æçæ¢ç´¢åå°è¯ã
温馨提示:答案为网友推荐,仅供参考