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 µÄ 9i/10g ÖÐÒѾ¶ÔÄÚ´æ¹ÜÀíÖð²½×öÁ˺ܴóµÄ¼ò»¯£¬11g Ôò¸ü½øÒ»²½£¬ÒýÈëÁËÒ»¸öеĸÅÄî×Ô¶¯»¯ÄÚ´æ¹ÜÀí(Automatic Memory Management,AMM) . Èç¹û DBA ÕæµÄÏë͵ÀÁµÄ»°£¬Ö»ÐèÒªÉ趨Á½¸ö²ÎÊý¾Í¿ÉÒÔ°Ñ·³ÐĵÄÊÂÇé¶¼½»¸ø Oracle ÕÛÌÚÁË(Ö»Òª DBA ×ã¹»ÐÄ¿í)¡£PGA Óë SGA Ò»Æð¸ã¶¨¡£ÕâÁ½¸ö²ÎÊý·Ö±ðÊÇ£º
MEMORY_TARGET--²Ù×÷Ï ......
1.´´½¨ÁÙʱ±í¿Õ¼ä
CREATE TEMPORARY TABLESPACE Test_temp
TEMPFILE 'E:\Test\TEST_temp.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
2.´´½¨Óû§±í¿Õ¼ä
create tablespace TEST
datafile 'E:\TEST\TEST.dbf'
size 100M
autoextend on maxsize   ......
/*connect byº¯Êý*/
CREATE TABLE TBL_TEST
(
ID NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER   ......
String sql = "select t.FILE_INFO from T_FILE_DB_DETAIL t where t.id= "
+ fileDBUpDownDetail.getId() + " for update";
rs = stmt.executeQuery(sql);
if (rs.next()) {
// ÔÚweblogicϲ¿Êðʱ£¬ÐèÒª¸ü¸ÄΪBea We ......