ORACLE SQLÐÔÄÜÓÅ»¯(ÄÚ²¿Åàѵ×ÊÁÏ)
ORACLE SQLÐÔÄÜÓÅ»¯
ORACLE SQLÐÔÄÜÓÅ»¯ÏµÁÐ (Ò»)
1. Ñ¡ÓÃÊʺϵÄORACLEÓÅ»¯Æ÷
ORACLEµÄÓÅ»¯Æ÷¹²ÓÐ3ÖÖ:
a. RULE (»ùÓÚ¹æÔò) b. COST (»ùÓڳɱ¾) c. CHOOSE (Ñ¡ÔñÐÔ)
ÉèÖÃȱʡµÄÓÅ»¯Æ÷,¿ÉÒÔͨ¹ý¶Ôinit.oraÎļþÖÐOPTIMIZER_MODE²ÎÊýµÄ¸÷ÖÖÉùÃ÷,ÈçRULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . Ä㵱ȻҲÔÚSQL¾ä¼¶»òÊǻỰ(session)¼¶¶ÔÆä½øÐи²¸Ç.
ΪÁËʹÓûùÓڳɱ¾µÄÓÅ»¯Æ÷(CBO, Cost-Based Optimizer) , Äã±ØÐë¾³£ÔËÐÐanalyze ÃüÁî,ÒÔÔö¼ÓÊý¾Ý¿âÖеĶÔÏóͳ¼ÆÐÅÏ¢(object statistics)µÄ׼ȷÐÔ.
Èç¹ûÊý¾Ý¿âµÄÓÅ»¯Æ÷ģʽÉèÖÃΪѡÔñÐÔ(CHOOSE),ÄÇôʵ¼ÊµÄÓÅ»¯Æ÷ģʽ½«ºÍÊÇ·ñÔËÐйýanalyzeÃüÁîÓйØ. Èç¹ûtableÒѾ±»analyze¹ý, ÓÅ»¯Æ÷ģʽ½«×Ô¶¯³ÉΪCBO , ·´Ö®,Êý¾Ý¿â½«²ÉÓÃRULEÐÎʽµÄÓÅ»¯Æ÷.
ÔÚȱʡÇé¿öÏÂ,ORACLE²ÉÓÃCHOOSEÓÅ»¯Æ÷, ΪÁ˱ÜÃâÄÇЩ²»±ØÒªµÄÈ«±íɨÃè(full table scan) , Äã±ØÐ뾡Á¿±ÜÃâʹÓÃCHOOSEÓÅ»¯Æ÷,¶øÖ±½Ó²ÉÓûùÓÚ¹æÔò»òÕß»ùÓڳɱ¾µÄÓÅ»¯Æ÷.
2. ·ÃÎÊTableµÄ·½Ê½
ORACLE ²ÉÓÃÁ½ÖÖ·ÃÎʱíÖмǼµÄ·½Ê½:
a. È«±íɨÃè
È«±íɨÃè¾ÍÊÇ˳ÐòµØ·ÃÎʱíÖÐÿÌõ¼Ç¼. ORACLE²ÉÓÃÒ»´Î¶ÁÈë¶à¸öÊý¾Ý¿é(database block)µÄ·½Ê½ÓÅ»¯È«±íɨÃè.
b. ͨ¹ýROWID·ÃÎʱí
Äã¿ÉÒÔ²ÉÓûùÓÚROWIDµÄ·ÃÎÊ·½Ê½Çé¿ö,Ìá¸ß·ÃÎʱíµÄЧÂÊ, , ROWID°üº¬Á˱íÖмǼµÄÎïÀíλÖÃÐÅÏ¢..ORACLE²ÉÓÃË÷Òý(INDEX)ʵÏÖÁËÊý¾ÝºÍ´æ·ÅÊý¾ÝµÄÎïÀíλÖÃ(ROWID)Ö®¼äµÄÁªÏµ. ͨ³£Ë÷ÒýÌṩÁË¿ìËÙ·ÃÎÊROWIDµÄ·½·¨,Òò´ËÄÇЩ»ùÓÚË÷ÒýÁеIJéѯ¾Í¿ÉÒԵõ½ÐÔÄÜÉϵÄÌá¸ß.
3. ¹²ÏíSQLÓï¾ä
ΪÁ˲»Öظ´½âÎöÏàͬµÄSQLÓï¾ä,ÔÚµÚÒ»´Î½âÎöÖ®ºó, ORACLE½«SQLÓï¾ä´æ·ÅÔÚÄÚ´æÖÐ.Õâ¿éλÓÚϵͳȫ¾ÖÇøÓòSGA(system global area)µÄ¹²Ïí³Ø(shared buffer pool)ÖеÄÄÚ´æ¿ÉÒÔ±»ËùÓеÄÊý¾Ý¿âÓû§¹²Ïí. Òò´Ë,µ±ÄãÖ´ÐÐÒ»¸öSQLÓï¾ä(ÓÐʱ±»³ÆΪһ¸öÓαê)ʱ,Èç¹ûËüºÍ֮ǰµÄÖ´ÐйýµÄÓï¾äÍêÈ«Ïàͬ, ORACLE¾ÍÄܺܿì»ñµÃÒѾ±»½âÎöµÄÓï¾äÒÔ¼°×îºÃµÄÖ´Ðз¾¶. ORACLEµÄÕâ¸ö¹¦ÄÜ´ó´óµØÌá¸ßÁËSQLµÄÖ´ÐÐÐÔÄܲ¢½ÚÊ¡ÁËÄÚ´æµÄʹÓÃ.¿ÉϧµÄÊÇORACLEÖ»¶Ô¼òµ¥µÄ±íÌṩ¸ßËÙ»º³å(cache buffering) ,Õâ¸ö¹¦Äܲ¢²»ÊÊÓÃÓÚ¶à±íÁ¬½Ó²éѯ.
Êý¾Ý¿â¹ÜÀíÔ±±ØÐëÔÚinit.oraÖÐΪÕâ¸öÇøÓòÉèÖúÏÊʵIJÎÊý,µ±Õâ¸öÄÚ´æÇøÓòÔ½´ó,¾Í¿ÉÒÔ±£Áô¸ü¶àµÄÓï¾ä,µ±È»±»¹²ÏíµÄ¿ÉÄÜÐÔÒ²¾ÍÔ½´óÁË.
µ±ÄãÏòORACLE Ìá½»Ò»¸öSQLÓï¾ä,ORACLE»áÊ×ÏÈÔÚÕâ¿éÄÚ´æÖвéÕÒÏàͬµÄÓï¾ä.
ÕâÀïÐèҪעÃ÷µÄÊÇ,ORACLE¶ÔÁ½Õ
Ïà¹ØÎĵµ£º
oracle±í¿Õ¼ä²Ù×÷Ïê½â
1
2
3×÷Õߣº À´Ô´£º ¸üÐÂÈÕÆÚ£º2006-01-04
5
6
7½¨Á¢±í¿Õ¼ä
8
9CREATE TABLESPACE data01
10DATAFILE '/ora ......
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
--´´½¨´¥·¢Æ÷£¨Ðм¶´¥·¢Æ÷£©
create or replace trigger tri_update_emp_bak
after update
on emp_bak
for each row --ÿ¸üÐÂÒ»ÐÐ ¾Í´¥·¢Ò»´Î
begin
--oracle ÀïÃæ ¶Ô´¥·¢Æ÷ Ò²ÌṩÁËÌØÊâµÄ¶ÔÏó :NEW :OLD À´·ÃÎÊ ¸üÐÂÇ°ºóµÄÊý¾Ý
dbms_output.put_line('¸üкó' || :NEW.sal);
dbms_outpu ......
Exam : Oracle 1Z0-051
Title : Oracle Database: SQL Fundamentals I
1. View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * from SALES
WHERE cust_id = 2034
WITH CHECK OPTI ......
GROUPINGº¯Êý¿ÉÒÔ½ÓÊÜÒ»ÁУ¬·µ»Ø0»òÕß1¡£Èç¹ûÁÐֵΪ¿Õ£¬ÄÇôGROUPING()·µ»Ø1£»Èç¹ûÁÐÖµ·Ç¿Õ£¬ÄÇô·µ»Ø0¡£GROUPINGÖ»ÄÜÔÚʹÓÃROLLUP»òCUBEµÄ²éѯÖÐʹÓᣵ±ÐèÒªÔÚ·µ»Ø¿ÕÖµµÄµØ·½ÏÔʾij¸öֵʱ£¬GROUPING()¾Í·Ç³£ÓÐÓá£
¹ØÓÚROLLUPºÍCUBEº¯ÊýµÄʹÓã¬Çë²Î¼ûÎÒµÄÁíһƪÎÄÕ¡£
http://blog.csdn.net/wh62592855/archive/2009/1 ......