ORACLEµÄ±í·ÖÎö²ßÂÔ
¶Ô±í½øÐзÖÎö£¬Í¨³£Çé¿öÏ¿ÉÒÔ¶Ô±í£¬Ë÷Òý£¬ÁнøÐе¥¶À·ÖÎö£¬»òÕß½øÐÐ×éºÏ·ÖÎö£¬µ«ÕâÈýÕßÄÄЩÊÇÏà¶ÔÖØÒªµÄ£¬ÄÄЩ·ÖÎöÏԵò»ÄÇôÖØÒª£¿Í¨¹ý±¾ÆªÎÄÕµÄʵÑéÏàÐÅ´ó¼ÒÒ²»á¶ÔÖ±·½Í¼ÓиüÒ»²½µÄÁ˽â.
1.Ê×ÏÈ´´½¨²âÊÔ±í,²¢²åÈë100000ÌõÊý¾Ý
SQL> create table test(id number,nick varchar2(30));
Table created.
SQL> begin
2 for i in 1..100000 loop
3 insert into test(id) values(i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
¸üÐÂnick×ֶΣ¬Ê¹Êý¾Ý·¢ÉúÑÏÖØÇãб
SQL> update test set nick='abc' where rownum<99999;
99998 rows updated.
SQL> commit;
Commit complete.
SQL> create index idx_test_nick on test(nick);
Index created.
SQL> update test set nick='def' where nick is null;
2 rows updated.
SQL> commit;
Commit complete.
--Ö»¶ÔË÷Òý½øÐзÖÎö
SQL> analyze index idx_test_nick compute statistics;
Index analyzed.
SQL> select index_name,LEAF_BLOCKS,DISTINCT_KEYS,NUM_ROWS from user_indexes where index_name='IDX_TEST_NICK';
INDEX_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ ----------- ------------- ----------
IDX_TEST_NICK 210 2 100000
SQL> select COLUMN_NAME,NUM_BUCKETS,num_distinct from USER_tab_columns where table_name='TEST';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT
------------------------------
Ïà¹ØÎĵµ£º
Ò»¡¢±³¾°½éÉÜ
¡¡¡¡
¡¡¡¡½á¹¹»¯²éѯÓïÑÔ(Structured Query Language£¬¼ò³ÆSQL)ÊÇÓÃÀ´·ÃÎʹØϵÐÍÊý¾Ý¿âÒ»ÖÖͨÓÃÓïÑÔ£¬ÊôÓÚµÚËÄ´úÓïÑÔ£¨4GL£©£¬ÆäÖ´ÐÐÌصãÊǷǹý³Ì»¯£¬¼´²»ÓÃÖ¸Ã÷Ö´ÐеľßÌå·½·¨ºÍ;¾¶£¬¶øÊǼòµ¥µØµ÷ÓÃÏàÓ¦Óï¾äÀ´Ö±½ÓÈ¡µÃ½á¹û¼´¿É¡£ÏÔÈ»£¬ÕâÖÖ²»¹Ø×¢ÈκÎʵÏÖϸ½ÚµÄÓïÑÔ¶ÔÓÚ¿ª·¢ÕßÀ´ËµÓÐ׿«´óµÄ±ãÀû¡£È»¶ø£¬Ó ......
˵µ½Èí½âÎö£¨soft prase
£©ºÍÓ²½âÎö£¨
hard prase
£©£¬¾Í²»Äܲ»ËµÒ»ÏÂ
Oracle
¶Ô
sql
µÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»Ìõ
sql
Óï¾ä½»¸¶
Oracle
£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûÇ°£¬
Oracle
¶Ô´Ë
sql
½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨
syntax check
£©
&nb ......
-- create by zh
-- n ÊÇ×÷ÎïµÄʱ¼ä,x ÊÇÏ£ÍûÔÚ¼¸µã³ÉÊì,·µ»Ø²¥ÖÖµÄʱ¼ä
with t as
(
select 64 n,9 x from dual union all
select 64 n,13 x from dual union all
select 64 n,17 x from dual union all
select 64 n,20 x from dual
)
select '³ÉÊìʱ¼ä:' || lpad(to_char(n),4,' ' ......
1.²éѯ±í¿Õ¼äµÄʹÓÃÇé¿ö£¬ÒÔMΪµ¥Î»
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
&nbs ......
³£Óõļ¸¸öÊý¾Ý×ֵ䣺
user_objects : ¼Ç¼ÁËÓû§µÄËùÓжÔÏ󣬰üº¬±í¡¢Ë÷Òý¡¢¹ý³Ì¡¢ÊÓͼµÈÐÅÏ¢£¬ÒÔ¼°´´½¨Ê±¼ä£¬×´Ì¬ÊÇ·ñÓÐЧµÈÐÅÏ¢£¬ÊÇ·ÇDBAÓû§µÄ´ó±¾Óª¡£ÏëÖªµÀ×Ô¼ºÓÐÄÄЩ¶ÔÏó£¬ÍùÕâÀï²é¡£
user_source :°üº¬ÁËϵͳÖжÔÏóµÄÔÂ룬Èç´æ´¢¹ý³Ì£¬FUNCTION¡¢PROCEDURE¡¢PACKAGEµÈÐÅÏ¢
cat»òTab £º°üº¬µ±Ç°Óû§ËùÓеÄÓû§ºÍ ......