oracleË÷ÒýµÄÈÏʶ
Ë÷Òý( Index )Êdz£¼ûµÄÊý¾Ý¿â¶ÔÏó£¬ËüµÄÉèÖúûµ¡¢Ê¹ÓÃÊÇ·ñµÃµ±£¬¼«´óµØÓ°ÏìÊý¾Ý¿âÓ¦ÓóÌÐòºÍDatabase µÄÐÔÄÜ¡£ËäÈ»ÓÐÐí¶à×ÊÁϽ²Ë÷ÒýµÄÓ÷¨£¬ DBA ºÍ Developer ÃÇÒ²¾³£ÓëËü´ò½»µÀ£¬µ«±ÊÕß·¢ÏÖ£¬»¹ÊÇÓв»ÉÙµÄÈ˶ÔËü´æÔÚÎó½â£¬Òò´ËÕë¶ÔʹÓÃÖеij£¼ûÎÊÌ⣬½²Èý¸öÎÊÌâ¡£´ËÎÄËùÓÐʾÀýËùÓõÄÊý¾Ý¿âÊÇ Oracle 8.1.7 OPS on HP N series ,ʾÀýÈ«²¿ÊÇÕæʵÊý¾Ý£¬¶ÁÕß²»ÐèҪעÒâ¾ßÌåµÄÊý¾Ý´óС£¬¶øӦעÒâÔÚʹÓò»Í¬µÄ·½·¨ºó£¬Êý¾ÝµÄ±È½Ï¡£±¾ÎÄËù½²»ù±¾¶¼Êdz´ÊÀĵ÷£¬µ«ÊDZÊÕßÊÔͼͨ¹ýʵ¼ÊµÄÀý×Ó£¬À´ÕæÕýÈÃÄúÃ÷°×ÊÂÇéµÄ¹Ø¼ü¡£
µÚÒ»½²¡¢Ë÷Òý²¢·Ç×ÜÊÇ×î¼ÑÑ¡Ôñ
Èç¹û·¢ÏÖOracle ÔÚÓÐË÷ÒýµÄÇé¿öÏ£¬Ã»ÓÐʹÓÃË÷Òý£¬Õâ²¢²»ÊÇOracle µÄÓÅ»¯Æ÷³ö´í¡£ÔÚÓÐЩÇé¿öÏ£¬Oracle ȷʵ»áÑ¡ÔñÈ«±íɨÃ裨Full Table Scan£©,¶ø·ÇË÷ÒýɨÃ裨Index Scan£©¡£ÕâЩÇé¿öͨ³£ÓУº
1. ±íδ×östatistics, »òÕß statistics ³Â¾É£¬µ¼Ö Oracle ÅжÏʧÎó¡£
2. ¸ù¾Ý¸Ã±íÓµÓеļǼÊýºÍÊý¾Ý¿éÊý£¬Êµ¼ÊÉÏÈ«±íɨÃèÒª±ÈË÷ÒýɨÃè¸ü¿ì¡£
¶ÔµÚ1ÖÖÇé¿ö£¬×î³£¼ûµÄÀý×Ó£¬ÊÇÒÔÏÂÕâ¾äsql Óï¾ä£º
select count(*) from mytable;
ÔÚδ×÷statistics ֮ǰ£¬ËüʹÓÃÈ«±íɨÃ裬ÐèÒª¶ÁÈ¡6000¶à¸öÊý¾Ý¿é£¨Ò»¸öÊý¾Ý¿éÊÇ8k£©, ×öÁËstatistics Ö®ºó£¬Ê¹ÓõÄÊÇ INDEX (FAST FULL SCAN) £¬Ö»ÐèÒª¶ÁÈ¡450¸öÊý¾Ý¿é¡£µ«ÊÇ£¬statistics ×öµÃ²»ºÃ£¬Ò²»áµ¼ÖÂOracle ²»Ê¹ÓÃË÷Òý¡£
µÚ2ÖÖÇé¿ö¾ÍÒª¸´Ôӵöࡣһ°ã¸ÅÄîÉ϶¼ÈÏΪË÷Òý±È±í¿ì£¬±È½ÏÄÑÒÔÀí½âʲôÇé¿öÏÂÈ«±íɨÃèÒª±ÈË÷ÒýɨÃè¿ì¡£ÎªÁ˽²Çå³þÕâ¸öÎÊÌ⣬ÕâÀïÏȽéÉÜÒ»ÏÂOracle ÔÚÆÀ¹ÀʹÓÃË÷ÒýµÄ´ú¼Û£¨cost£©Ê±Á½¸öÖØÒªµÄÊý¾Ý£ºCF(Clustering factor) ºÍ FF(Filtering factor).
CF: Ëùν CF, ͨË׵ؽ²£¬¾ÍÊÇÿ¶ÁÈëÒ»¸öË÷Òý¿é£¬Òª¶ÔÓ¦¶ÁÈë¶àÉÙ¸öÊý¾Ý¿é¡£
FF: Ëùν FF, ¾ÍÊǸÃsql Óï¾äËùÑ¡ÔñµÄ½á¹û¼¯£¬Õ¼×ܵÄÊý¾ÝÁ¿µÄ°Ù·Ö±È¡£
´óÔ¼µÄ¼ÆË㹫ʽÊÇ£ºFF * (CF + Ë÷Òý¿é¸öÊý) £¬Óɴ˹À¼Æ³ö£¬Ò»¸ö²éѯ£¬ Èç¹ûʹÓÃij¸öË÷Òý£¬»áÐèÒª¶ÁÈëµÄÊý¾Ý¿é
Ïà¹ØÎĵµ£º
1.ÀûÓÃÏÂÃæµÄ½Å±¾´´½¨BOOK£¬READER ºÍ BORROW ±í£¬²¢Íê³ÉºóÃæµÄÁªÏµ¡£
CREATE TABLE BOOK(
NO CHAR(8) PRIMARY KEY,
TITLE VARCHAR2(50) NOT NULL,
AUTHOR VARCHAR2(20) ,
PUBLISH VARCHAR2(20),
PUB_DA ......
oracleµÄ·ÖÎöº¯Êýover ¼°¿ª´°º¯Êý
Ò»£º·ÖÎöº¯Êýover
Oracle´Ó8.1.6¿ªÊ¼Ìṩ·ÖÎöº¯Êý£¬·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄijÖÖ¾ÛºÏÖµ£¬ËüºÍ¾ÛºÏº¯ÊýµÄ²»Í¬Ö®´¦ÊÇ
¶ÔÓÚÿ¸ö×é·µ»Ø¶àÐУ¬¶ø¾ÛºÏº¯Êý¶ÔÓÚÿ¸ö×éÖ»·µ»ØÒ»ÐС£
ÏÂÃæͨ¹ý¼¸¸öÀý×ÓÀ´ËµÃ÷ÆäÓ¦Óᣠ&nb ......
ÊÂÎñ¾ÍÊDZ»°ó¶¨ÔÚÒ»Æð×÷Ϊһ¸öÂß¼¹¤×÷µ¥ÔªµÄSQLÓï¾ä·Ö×飬Èç¹ûÈκÎÒ»¸öÓï¾ä²Ù×÷ʧ°ÜÄÇôÕû¸ö²Ù×÷¾Í±»Ê§°Ü£¬ÒÔºó²Ù×÷¾Í»á»Ø¹öµ½²Ù×÷ǰ״̬£¬»òÕßÊÇÉÏÓиö½Úµã¡£ÎªÁËÈ·±£ÒªÃ´Ö´ÐУ¬ÒªÃ´²»Ö´ÐУ¬¾Í¿ÉÒÔʹÓÃÊÂÎñ¡£ÊÂÎñÓÐËĸöÌØÐÔ£¬·Ö±ðÊÇ£ºÔ×ÓÐÔ£¬Ò»ÖÂÐÔ£¬¸ôÀëÐԺͳ־ÃÐÔ¡£
Ë÷Òý¾ÍÒ»ÖÖÌØÊâµÄ²éѯ±í£¬Êý¾Ý¿âµÄËÑË÷ÒýÇæ¿ ......
1.±¸·Ýscott
£¨1£©ÔÚCÅ̵ĸùĿ¼Ï´´½¨Ò»¸öÎļþ¼Ðtemp
£¨2£©ÔÚcmdÔÚ½øÈëÎļþ¼Ð£¬del *.* £¬É¾³ýËùÓÐÎļþ¡£
£¨3£©ÊäÈëexp
£¨4£©ÊäÈëÓû§ÃûºÍÃÜÂë:scott/tiger
£¨5£©¾Í»áÏÔʾ“ÊäÈëÊý×éÌáÈ¡»º³åÇø´óС”£¬ÔÚÕâÒ»²½Ö±½Ó»Ø³µ¾ÍÐÐÁË
£¨6£©ÏÔʾ“µ¼³öÎļþ£ºDXPDAT.DMP>”£¬ÕâÒ»²½Ò²Êǻسµ
£¨ ......
ËäÈ»±¾ÈËûÓÐÑéÖ¤¹ý,²»¹ý¾Ý˵ͨ¹ýADODB·ÃÎÊOracle¶ÔÐÔÄÜÓ°ÏìºÜС.¶øÇÒADODB±ÈOCI8Ò²Òª·½±ãºÜ¶à,±Ï¾¹Ðí¶à¶«Î÷¶¼Ôٴηâ×°¹ý.²»¹ý²»¹ÜÔõô˵ÕⶼÔÚÖмäÓÖ¼ÓÉÏÁËÒ»²ã,ÀíÂÛÉÏÀ´Ëµ³öÏÖÎÊÌâµÄ¼¸Âʺͽâ¾öÎÊÌâµÄÄѶȶ¼±ä´óÁË.¶øOracleÓÖ¾³£ÓÃÔÚ²»ÄÜËæ±ã³öÎÊÌâµÄ»·¾³,ËùÒÔÏë±ØÏëÒªÖ±½ÓʹÓÃOCIµÄÇé¿öÒ²²»ÉÙ.ÒÔϾÍÊÇÎÒµÄÀý×Ó:
<?p ......