oracleÌøÔ¾Ë÷ÒýµÄÓ¦Óó¡ºÏ
Ë÷ÒýÌøÔ¾Ê½É¨Ãè(index skip scan)ÊÇOracle9iµÄÒ»¸öеÄÖ´ÐÐÌØÐÔ£¬ÓÈÆäÊÊÓÃÓÚʹÓÃÁ¬½ÓË÷ÒýºÍ·ÃÎʶàÖµË÷ÒýµÄOracle²éѯ¡£
Ë÷ÒýÌøÔ¾Ê½É¨Ãè(index skip scan)ÊÇOracle9iµÄÒ»¸öеÄÖ´ÐÐÌØÐÔ£¬ÓÈÆäÊÊÓÃÓÚʹÓÃÁ¬½ÓË÷ÒýºÍ·ÃÎʶàÖµË÷ÒýµÄOracle²éѯ¡£ÈÃÎÒÃÇ¿´ÒÔϵķ¶Àý¡£Çë×¢Òâµ½ÒÔÏ´úÂë°üº¬×ÅÁ¬½ÓË÷Òý£º
create indexsex_emp_idonemp (sex, emp_id);
ÔÚOracle9i°æ±¾Ö®Ç°£¬µ±SQL²éѯÖаüº¬ÐÔ±ðºÍemp_idʱ£¬»òÕß²éѯָ¶¨ÐÔ±ðÐеÄʱºò²Å¿ÉÒÔʹÓÃÕâÒ»Ë÷Òý¡£ÏÂÃæµÄ²éѯ²»Äܹ»Ê¹ÓÃÁ¬½ÓË÷Òý£º
selectemp_idfromempwhereemp_id = 123;
Oracle9iµÄË÷ÒýÌøÔ¾Ê½É¨ÃèÖ´ÐйæÔòÔÊÐíʹÓÃÁ¬½ÓË÷Òý£¬¼´Ê¹SQL²éѯÖв»Ö¸¶¨ÐԱ𡣠ÕâÒ»ÌØÐÔʹµÃÎÞÐèÔÚemp_idÐÐÖÐÌṩµÚ¶þ¸öË÷Òý¡£Oracle³ÐÈÏË÷ÒýÌøÔ¾Ê½É¨ÃèûÓÐÖ±½ÓË÷Òý²éѯËٶȿ죬µ«¿ÉÒÔÕâÑù˵£¬Ïà±ÈÓÚÕû¸ö±íɨÃè (table scan)£¬Ë÷ÒýÌøÔ¾Ê½É¨ÃèµÄËÙ¶ÈÒª¿ìµÃ¶à¡£
µ±OracleûÓÐÖ¸Ã÷Ë÷ÒýÌøÔ¾Ê½É¨ÃèµÄÄÚ²¿ÄÚÈÝʱ£¬ÎÒÃÇ¿ÉÒÔ´ÓËüµÄÖ´ÐйæÔòÖÐÅжϳö£¬OracleÔÚÄÚ²¿ÉÏÉú³ÉÁ˶à¸ö²éѯ£¬ÕâÑù¾ÍÂú×ã´øÓжà¸ö×Ó²éѯµÄ²éѯ:
SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)0 SORT (AGGREGATE)1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE)
ÔÚÄÚ²¿ÀOracleÉú³ÉÁËÁ½¸ö²éѯ£¬²¢Á¬½Ó½á¹ûµÄROWID±í¡£
selectemp_name from emp_where sex = 'F' and emp_id = 123
UNION
select emp_name from emp_where sex = 'M' and emp_id = 123;
ʹÓÃË÷ÒýÌøÔ¾Ê½É¨ÃèµÄÄÚº¾Í±äµÃºÜÇåÎú¡£
¶ÔÓÚ¸ß˳Ðò¼ü(high order key)ÖеĶÀÌØÖµÊýÄ¿£¬OracleµÄË÷ÒýÌøÔ¾Ê½É¨ÃèÐÔÄܽ«»á½µµÍ¡£Èç¹ûÖ÷ÁÐÓÐ50¸öÖµ£¬OracleÒª·¢³ö50Ìõ²éѯ²ÅÄÜÕһؽá¹û¡£ Ë÷ÒýÌøÔ¾Ê½É¨ÃèÖ»ÊÊÓÃÓÚÓ²Å̿ռäºÍ´æ´¢¿Õ¼äÏ൱½ôȱµÄÇé¿ö¡££¨ÔðÈαà¼:ÍõÈð£©
ÆÀÊö£º
¸ù¾ÝÉÏÃæÎÄÕÂÖеĽá¹û£¬Êµ¼ÊÉÏ£¬ÌøÔ¾Ë÷ÒýµÄÓ¦Óó¡ËùÊÇÓÐÏ޵ģ¬Èç¹ûË÷ÒýµÄµÚÒ»¸ö×ֶΣ¨Ö÷ÁУ©µÄÊý¾ÝÖÖÀàºÜ¶àµÄ»°£¬ÌøÔ¾Ë÷Òý¶ÔÌá¸ßÐÔÄÜÊǺÜÓÐÏ޵ģ¬ÉõÖÁÊÇûÓÐÓô¦µÄ£¬ÒòΪʵ¼ÊÉÏoracleÄÚ²¿ÊÇÉú³ÉÁ˶à¸ö²éѯÀ´Íê³ÉÕâÏ×÷µÄ¡£
ËùÒÔÔÚʵ¼ÊÓ¦Óùý³ÌÖУ¬ÕýÈ·µÄÀí½âÌøÔ¾Ë÷ÒýµÄÔÀí£¬·ÖÎöÖ÷ÁÐÊý¾ÝµÄÇé¿ö£¬ÊÇÓúÃÌøÔ¾Ë÷ÒýµÄÒ»¸öǰÌá
±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/towerjt/archi
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
extent--×îС¿Õ¼ä·ÖÅ䵥λ --tablespace management
block --×îСi/oµ¥Î» --segment management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M ¡¡¡¡¡¡¡¡¡¡¡¡--³õʼµÄÎļþ´óС¡¡
autoextend On¡¡¡¡¡¡¡¡ --×Ô¶¯Ôö³¤
next 10M¡ ......
OracleÊý¾Ý¿âÖÐÌṩÁËͬÒå´Ê¹ÜÀíµÄ¹¦ÄÜ¡£Í¬Òå´ÊÊÇÊý¾Ý¿â·½°¸¶ÔÏóµÄÒ»¸ö±ðÃû£¬¾³£ÓÃÓÚ¼ò»¯¶ÔÏó·ÃÎʺÍÌá¸ß¶ÔÏó·ÃÎʵݲȫÐÔ¡£ÔÚʹÓÃͬÒå´Êʱ£¬OracleÊý¾Ý¿â½«Ëü·Òë³É¶ÔÓ¦·½°¸¶ÔÏóµÄÃû×Ö¡£ÓëÊÓͼÀàËÆ£¬Í¬Òå´Ê²¢²»Õ¼ÓÃʵ¼Ê´æ´¢¿Õ¼ä£¬Ö»ÓÐÔÚÊý¾Ý×ÖµäÖб£´æÁËͬÒå´ÊµÄ¶¨Òå¡£ÔÚOracleÊý¾Ý¿âÖеĴ󲿷ÖÊý¾Ý¿â¶ÔÏó£¬Èç±í¡¢ÊÓͼ¡¢Í ......
ǰÑÔ
ÕâÆªÎÄÕ½éÉÜÁËDBAÿÌìÔÚ¼à¿ØOracleÊý¾Ý¿â·½ÃæµÄÖ°Ô𣬽²ÊöÁËÈçºÎͨ¹ýshell½Å±¾À´Íê³ÉÕâÐ©ÖØ¸´µÄ¼à¿Ø¹¤×÷¡£±¾ÎÄÊ×ÏȻعËÁËһЩDBA³£ÓõÄUnixÃüÁÒÔ¼°½âÊÍÁËÈçºÎͨ¹ýUnix CronÀ´¶¨Ê±Ö´ÐÐDBA½Å±¾¡£Í¬Ê±ÎÄÕ»¹½éÉÜÁË8¸öÖØÒªµÄ½Å±¾À´¼à¿ØOracleÊý¾Ý¿â£º
¼ì²éʵÀýµÄ¿ÉÓÃÐÔ
¼ì²é¼àÌýÆ÷µÄ¿ÉÓÃÐÔ
¼ì²éalertÈÕÖ¾Îļ ......
OracleµÄ¼àÌýÆ÷TNSListenerÓÐʱÎÞ·¨Æô¶¯£¬ÓÐʱÆô¶¯ÍêÒÔºó»á×Ô¶¯¹Ø±Õ£¬ÕâÀï¸ø³öÒ»ÖÖ¿ÉÄܵÄÔÒò¾ÍÊǼÆËã»úÃû³ÆµÄÐ޸쬽â¾öµÄ·½·¨ÊÇ´ò¿ª<ORALE_HOME>\network\admin\listener.ora£¬ÐÞ¸Ä(HOST = [compute_name])(PORT = 1521)£¬ÖØÐÂÐ޸ľͿÉÒÔÕý³£Æô¶¯¡££¨×¢ÒâOracleÆóÒµ¹ÜÀíÆ÷ÖÐĬÈϵÄÊý¾Ý¿âÁ¬½ÓÒ²ÐèÒª¸Ä±ä£¬·ñÔòÈÔÈ»»á ......