Oracle 10g StatisticÊý¾Ýͳ¼Æ
http://www.ej38.com/showinfo/Oracle-108709.html
Oracle 10g statisticÊý¾Ýͳ¼Æ,Oracle»á¸ù¾ÝÕâЩͳ¼ÆÐÅÏ¢À´¾ö¶¨ÊÇ×ßRBO(Rule-BasedOptimization)£¬»¹ÊÇ×ßCBO(Cost-BasedOptimization)£¬»áȥѡÔñÄÄÖÖÖ´Ðмƻ®¸ü»®Ëã,Ó°ÏìÊÇ·ñ×ßÏà¹ØµÄË÷ÒýµÈ.Èç¹ûÊÇCBOµÄ»°£¬ËüÒÀ¿¿×¼È·µÄ£¨»òÕß˵±È½Ï׼ȷµÄ£©Í³¼ÆÐÅÏ¢À´²úÉúÓÅ»¯µÄÖ´Ðз¾¶,Èç¹ûûÓÐ×ö¹ýͳ¼Æ£¬CBOÒ²¾ÍûÓÐ×öcostÆÀ¹ÀµÄÒÀ¾Ý£¬ËùÒÔËäÈ»ÊÇCBO£¬µ«ÊÇʵ¼ÊÉÏ»¹ÊÇÓÃRBOÁË£¬¶øÇÒÈç¹û²»³£×öͳ¼ÆµÄ»°£¬ÓÉÓÚCBOÊÇÒÔͳ¼ÆΪÒÀ¾ÝµÄ£¬ËùÒÔÕâʱCBOµÄÒÀ¾ÝÐÅÏ¢ÓÐÎÊÌ⣬CBOÒ²»á²»×¼¡£ ËùÒÔ DBA ÐèҪȷ±£¶¨ÆÚÊÕ¼¯Í³¼ÆÐÅÏ¢£¬´´½¨ÁíÒ»¸öÖ´Ðк˶ÔÇåµ¥¡£
¾Ù¸öÀý×Ó£º
Ò»´Î²âÊÔ£¬Ò»¸ösqlÓï¾äÖ´ÐÐÒª20·ÖÖÓ£¬ÓÐʱºò»¹³ö²»Á˽á¹û£¬·¢Ïֲ鿴ִÐмƻ®£¬·¢ÏÖ¾ÓÈ»×ßÁËÈ«±íɨÃ裨±íÖдóÔ¼300wÌõ¼Ç¼£©£¬ÎªÉ¶²»ÓÃË÷ÒýÄØ£¬²é¿´Ë÷Òý״̬£¬Ò»ÇÐÕý³£¡£·ÖÎöÁËÏà¹ØµÄ±í£¬È»ºóÖØÐÂÖ´ÐÐ3·ÖÖӸ㶨£¡
ÊÀÊÂÎÞ¾ø¶Ô£¬analyze±í»áÔö¼ÓCBOÖ´ÐеÄÐÔÄÜ£¿²»Ò»¶¨µÄ¡£
ÎÒ¾ÍÅöµ½Ò»¸öÓï¾ä·ÖÎöºóÒªÖ´ÐÐ30¶à·ÖÖÓ£¬É¾³ý·ÖÎöºó£¬Ö»Òª30Ãë¡£
ºÜ¶àÇé¿öϲ»Ò»¶¨µÄ£¬×îºÃÊÇ×Ô¼º´ÓÖ´Ðмƻ®Åжϡ£
analyze table tablename compute statistics for all indexes;
analyze table tablename delete statistics
˳±ã²¹³äÒ»µã£¬±íÖ»ÓзÖÎöÁËÖ®ºó£¬num_rows²Å»áÓÐÖµ¡£
select * from user_all_tables a where a.num_rows <10;
analyze table tablename compute statistics;
SELECT 'ANALYZE TABLE ' || TABLE_NAME || ' COMPUTE STATISTICS;'
from (SELECT DISTINCT TABLE_NAME from ALL_COL_COMMENTS);
SQLÓï¾äµÄÖ´Ðмƻ®×ß²»×ßË÷Òý³ýÁËÓëStatisticÓйØϵ£¬»¹ÓÐpfileµÄÒ»¸ö²ÎÊýÓйأºoptimizer_index_cost_adj. ¸Ã²ÎÊýÓ°ÏìÓÅ»¯Æ÷Ñ¡ÔñË÷Òý»¹ÊÇÈ«±íɨÃèµÄÇãÏò,½¨Ò齫ÆäÉèΪ40.
ÔÚ 10g ÖУ¬Í¨¹ýÉèÖóõʼ»¯²ÎÊý STATISTIC_LEVEL Ϊ TYPICAL »ò ALL£¬¾Í¿ÉÒÔ×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢(ĬÈÏֵΪ TYPICAL£¬Òò´Ë¿ÉÒÔËæ¼´ÆôÓÃ×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢µÄ¹¦ÄÜ)¡£Oracle Êý¾Ý¿â 10g ¾ßÓÐÒ»¸öÔ¤¶¨ÒåµÄµ÷¶È³ÌÐò×÷Òµ£¬Ãû³ÆΪ GATHER_STATS_JOB£¬ËüÓÉ STATISTIC_LEVEL ²ÎÊýµÄÊʵ±ÊýÖµËù¼¤»î¡£
SQL> show parameter statistics_
NAME &nb
Ïà¹ØÎĵµ£º
--single line method
select ascii('A') Big_A from dual;
select chr(65) A, chr(122) z, chr(223) ch3 from dual;
select concat('sigo','way') from dual;
select initcap('sigoway') from dual;
select instr('ababab','a',-1,2), instr('ababab','a',1,3) from dual;
select instrb('ababab','a',1,3) from dua ......
¸Ãº¯ÊýÓÃÀ´·µ»ØÒ»¸öËÄÉáÎåÈëºóµÄÖµ
SELECT ROUND( number, [ decimalplaces ] ) from DUAL
參數:
±ØÌîÏnumber : Òª´¦ÀíµÄÊýÖµ(ÊýÖµ±í´ïʽ)
¿ÉÑ¡Ïdecimalplaces : ËÄÉáÎåÈëʱȡµÄСÊýµÄλÊý£¬²»ÌîÔò·µ»ØÕûÊý
Sample :
select round(123.456) from dual;& ......
left join ºÍ left outer join µÄÇø±ð
ͨË׵Ľ²£º
A left join B µÄÁ¬½ÓµÄ¼Ç¼ÊýÓëA±íµÄ¼Ç¼Êýͬ
A right join B µÄÁ¬½Ó ......
-- ±Ê¼ÇÖв¿·ÖÄÚÈÝ
SQL> create table tt2 as select * from employee;
Table created.
SQL> drop table tt2;
Table dropped.
SQL> select * from tt2;
select * from tt2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table tt2 to before drop;
Flashback comp ......
http://www.51testing.com/?uid-16403-action-viewspace-itemid-101605
Ò»¡¢ORACLE10g×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢--×Ô¶¯analyze
´ÓOracle Database 10g¿ªÊ¼£¬OracleÔÚ½¨¿âºó¾ÍĬÈÏ´´½¨ÁËÒ»¸öÃûΪGATHER_STATS_JOBµÄ¶¨Ê±ÈÎÎñ£¬ÓÃÓÚ×Ô¶¯ÊÕ¼¯CBOµÄͳ¼ÆÐÅÏ¢¡£
Õâ¸ö×Ô¶¯ÈÎÎñĬÈÏÇé¿öÏÂÔÚ¹¤×÷ÈÕÍíÉÏ10£º00-6£º00ºÍÖÜÄ©È«Ìì¿ªÆ ......