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
Ïà¹ØÎĵµ£º
£¨1£©ÒÔSYSÉí·ÝµÇ½
conn sys/sysem_pwd as sysdba;
£¨2£©´´½¨amlyÓû§
create user amly identified by 9imly;
£¨3£©´´½¨±í¿Õ¼ä
create tablespace ts_amly datafile 'd:\amlyfile\test.dbf' size 100M;
£¨4£©½«±í¿Õ¼ä·ÖÅä¸øÓû§
alter user amly default tablespace ts_amly;
£¨5£©¸øÓû§ÊÚȨ
grant create sess ......
¿ª·¢»·¾³ VS2008+NET3.5+ORACLE10G
Êý¾Ý¿âÖбíÉè¼Æ
CREATE TABLE CCOP (
A CHAR(20) NULL,
B CHAR(20) NULL,
C CHAR(20) NULL,
D CHAR(20) NULL
)
EXCELµÄµÚÒ»ÐÐÇ°ËÄÁÐÁÐÃûΪ A B C D
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using Sys ......
¸Ãº¯ÊýÓÃÀ´·µ»ØÒ»¸öËÄÉáÎåÈëºóµÄÖµ
SELECT ROUND( number, [ decimalplaces ] ) from DUAL
參數:
±ØÌîÏnumber : Òª´¦ÀíµÄÊýÖµ(ÊýÖµ±í´ïʽ)
¿ÉÑ¡Ïdecimalplaces : ËÄÉáÎåÈëʱȡµÄСÊýµÄλÊý£¬²»ÌîÔò·µ»ØÕûÊý
Sample :
select round(123.456) from dual;& ......
OracleÖÐstart with...connect by prior×Ó¾äÓ÷¨
connect by Êǽṹ»¯²éѯÖÐÓõ½µÄ£¬Æä»ù±¾Óï·¨ÊÇ£º
select ... from tablename start with Ìõ¼þ1
connect by Ìõ¼þ2
where Ìõ¼þ3;
Àý£º
select * from table
start with org_id = 'HBHqfWGWPy'
connect by prior org_id = parent_id;
¼òµ ......
Oracle°²×°Íêºó£¬ÆäÖÐÓÐÒ»¸öȱʡµÄÊý¾Ý¿â£¬³ýÁËÕâ¸öȱʡµÄÊý¾Ý¿âÍ⣬ÎÒÃÇ»¹¿ÉÒÔ´´½¨×Ô¼ºµÄÊý¾Ý¿â¡£
¶ÔÓÚ³õѧÕßÀ´Ëµ£¬ÎªÁ˱ÜÃâÂé·³£¬¿ÉÒÔÓÃ'Database Configuration Assistant'Ïòµ¼À´´´½¨Êý¾Ý¿â¡£
´´½¨ÍêÊý¾Ý¿âºó£¬²¢²»ÄÜÁ¢¼´ÔÚÊý¾Ý¿âÖн¨±í£¬±ØÐëÏÈ´´½¨¸ÃÊý¾Ý¿âµÄ ......