ãã1ã使ç¨ä¸¤è¾¹å â%âå·çæ¥è¯¢ï¼Oracleæ¯ä¸éè¿ç´¢å¼çï¼æ以æ¥è¯¢æçå¾ä½ã
ããä¾å¦ï¼select count(*) from lui_user_base t where t.user_name like '%cs%';
ãã2ãlike '...%'å like'%...'è½ç¶èµ°äºç´¢å¼ï¼ä½æ¯æçä¾ç¶å¾ä½ã
ãã3ãæ人说使ç¨å¦ä¸sqlï¼ä»çæçæé«äº10åï¼ä½æ¯æ°æ®éå°çæ¶å
ããselect count(*) from lui_user_base where rowid in (
ããselect rowid from lui_user_base t where t.user_name like '%cs%'
ãã)
ããææ¿100wè·³æ°æ®åäºæµè¯ï¼ææä¸è¬ï¼ä¾ç¶å¾æ
¢ï¼åå ï¼
ããselect rowid from lui_user_base t where t.user_name like '%cs%' è¿æ¡sqlæ§è¡å¾å¿«ï¼é£æ¯ç¸å½çå¿«ï¼
ããä½æ¯æ¾å°select count(*) from lui_user_base where rowid inï¼ï¼éåï¼æçå°±ä¼åçå¾æ
¢äºã
ãã4ãselect count(*) from lui_user_base t where instr(t.user_name,'cs')> 0
ããè¿ç§æ¥è¯¢ææå¾å¥½ï¼é度å¾å¿«ï¼æ¨è使ç¨è¿ç§ãå 为æ对oracleå
é¨æºå¶ä¸æ¯å¾æï¼åªæ¯å¯¹ç»æåäºä¸ä¸ªè¯´æãããææ§åãããã
ãã5ãæ人说äºç¨å
¨æç´¢å¼ï¼æçäºï¼æ¥éª¤æºéº»ç¦ï¼ä½æ¯æ¯ä¸ªä¸éçæ¹æ³ï¼ççå¤ç¨ï¼
ãã
http://sandish.itpub.net/post/4899/464369ãã对cmng_custominfo 表ä¸çaddresså段åå
¨ææ£ç´¢ï¼
ãã1ï¼å¨oracle9201ä¸éè¦å建ä¸ä¸ªåè¯çä¸è¥¿ï¼
ããBEGIN
ããctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');
ãã--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); ä¸ç¨
ããend;
ãã2ï¼å建å
¨ææ£ç´¢ï¼
ããCREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');
ãã3ï¼æ¥è¯¢æ¶åï¼ä½¿ç¨ï¼
ããselect * from cmng_custominfo where contains (address, 'éè²æ°å')>1;
ãã4ï¼éè¦å®æè¿è¡åæ¥åä¼åï¼
ããåæ¥ï¼æ ¹æ®æ°å¢è®°å½çææ¬å
容æ´æ°å
¨ææç´¢çç´¢å¼ã
ããbegin
ããctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');
ããend;
ããä¼åï¼æ ¹æ®è¢«å é¤è®°å½æ¸
é¤å
¨ææ索索å¼ä¸çåå¾
ããbegin
ããctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');
ããend;
ãã5ï¼éç¨jobåæ¥éª¤4ä¸çå·¥ä½ï¼
ãã1)该åè½éè¦å©ç¨oracleçJOBåè½æ¥å®æ
ããå 为oracle9Ié»è®¤ä¸å¯ç¨JOBåè½ï¼æ以é¦å
éè¦å¢å ORACLEæ°æ®åºå®ä¾çJOBé
ç½®åæ°ï¼
ããjob_queue_processes=5
ããéæ°å¯å¨oracleæ°æ®åºæå¡ålisteneræå¡ã
ãã2)åæ¥ å ä¼å
ãã--åæ¥ sync:
ããvariable jobno number;
ããBEGIN
ããDBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');', SYSDATE, 'SYSDATE + (1/24/4)');
ããcommit;
ããEND;
ãã--ä¼å
ããvariable jobno number;
ããbegin
ããDBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');
ããcommit;
ããEND;
ããå
¶ä¸ï¼ 第ä¸ä¸ªjobçSYSDATE + (1/24/4)æ¯ææ¯é15åéåæ¥ä¸æ¬¡ï¼ç¬¬äºä¸ªjobçSYSDATE + 1æ¯æ¯é1天åä¸æ¬¡å
¨ä¼åãå
·ä½çæ¶é´é´éï¼å¯ä»¥æ ¹æ®åºç¨çéè¦èå®
ãã6ï¼ç´¢å¼é建
ããé建索å¼ä¼å é¤åæ¥çç´¢å¼ï¼éæ°çæç´¢å¼ï¼éè¦è¾é¿çæ¶é´ã
ããé建索å¼è¯æ³å¦ä¸ï¼
ããALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;
ããæ®ç½ä¸ä¸äºç¨å®¶çä½ä¼ï¼oracleé建索å¼çé度ä¹æ¯æ¯è¾å¿«çï¼æä¸ç¨å®¶è¿æ ·æè¿°ï¼
ããOracle çå
¨ææ£ç´¢å»ºç«åç»´æ¤ç´¢å¼è¦æ¯ms sql serveré½è¦å¿«å¾å¤ï¼ç¬è
ç65ä¸è®°å½çä¸ä¸ªè¡¨å»ºç«ç´¢å¼åªéè¦20åéï¼åæ¥ä¸æ¬¡åªéè¦1åéã
ããå æ¤ï¼ä¹å¯ä»¥èèç¨jobçåæ³å®æé建索å¼ã