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
Ïà¹ØÎĵµ£º
ÔÎÄ´«ËÍÃÅ£ºhttp://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html?24122
¾³£Óöµ½·¢ÌûÇóÐÐÁÐת»»µÄ´úÂ룬ÓÃmax(decode(..))»Ø¸´ºó£¬Ê®Óа˾ŻáÔÙÎÊÒ»¾ä£ºÈç¹ûÁÐÃû²»¹Ì¶¨£¬»òÕßÁÐÊý²»¹Ì¶¨Ôõô°ì¡£¾ÍÒªÓô洢¹ý³ÌÀ´Ð´£¬ÕâЩ´æ´¢¹ý³ÌµÄ´úÂë¶¼´óͬСÒ죬ÎÒ¾ÍÏëÄܲ»ÄÜд¸öͨÓõãµÄ¹ý³Ì
ÊÔÁ ......
http://topic.csdn.net/t/20040818/16/3287013.html
1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
²ÎÊý1 IN NUMBER,
²ÎÊý2 IN NUMBER
) IS|AS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN
END ´æ´¢¹ý³ÌÃû×Ö
˵Ã÷£ºisÒ²¿É»»³Éas
2.SELECT INTO STATEMENT
& ......
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 ......