一直學習Oracle 希望能和大家共同探討問題 如有不對之處還請指出index 種對null的使用================================================================有些情況可以用 ' N/A ' 代替 NULL ================================================================8i 以上使用基于函數的index 可以用上 nullcreate table t (n number);create index ind_n on t(n,1);//; 用 t(n,'a') 更省空間select v from t where n is null;V--------------------lgExecution Plan---------------------------------------------------------- 0;;;SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=614 Bytes=6140) 1;0TABLE Access (BY INDEX ROWID) OF 'T' (Cost=3 Card=614 Bytes=6140) 2;1;;INDEX (RANGE SCAN) OF 'IND_N' (NON-UNIQUE) (Cost=3 Card=614)要記住用 CBO I doesn't need query rewrite to make that leap, it is a 'safe' operation. ==============================================================null 可以在 bitmap index 中使用==============================================================或者象下面這樣使用多列組合的index 方便使用indexcreate table t ( f_seq int, t_seq int, x char(1) );create index t_idx on t(f_seq,t_seq);select f_seq, t_seq from t where f_seq > 0 and t_seq is null;Execution Plan---------------------------------------------------------- 0;;;SELECT STATEMENT Optimizer=CHOOSE 1;0INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)select f_seq, t_seq, x from t; where f_seq > 0 and t_seq is null;Execution Plan---------------------------------------------------------- 0;;;SELECT STATEMENT Optimizer=CHOOSE 1;0TABLE ACCESS (BY INDEX ROWID) OF 'T' 2;1;;INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)=============================================================== ;表所占空間的大小;select segment_name, round(blocks*8/1024, 0) table_size from user_segments where segment_type=' TABLE'---- 你可以知道你的表的實際size (單位: M) -----------------------------------------------------------------------------------------數據字典表DBA_TABLES、ALL_TABLES、USER_TABLESselect table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables;-----------------------------------------------------------------------------------------分析一對象實際使用的塊 analyze table lg.t compute statistics;分析完后就可以看一對象實際使用的塊 select blocks,num_rows,empty_blocks,avg_space,avg_row_len from dba_tables where owner='LG' and table_name='T';select table_name, round(avg_row_len*num_rows/1024/1024, 0) data_size from user_tables; ---- 你可以知道表中大約的data size (單位: M) -----------------------------------------------------------------------------------------select count(distinct substr(rowid,1,15)) 'how many use of block' from a;這是看一個表真正使用了多少數據塊 dba_tables 的 blocks 顯示了 HWM 下不包含行的塊的數目-----------------------------------------------------------------------------------------declare l_total_blocks;;;;;number; l_total_bytes number; l_unused_blocks;;;;number; l_unused_bytes;;;;;number; l_LastUsedExtFileId number; l_LastUsedExtBlockId;;;;;number; l_last_used_blocknumber; l varchar2(128); t varchar2(128);begin l:=upper('&name'); select object_type into t from user_objects where object_name=l; dbms_space.unused_space( segment_owner;;=>USER, segment_name;;;=>l, segment_type;;;=>t, partition_name;=> null, total_blocks;;;=> l_total_blocks, total_bytes;;;;=> l_total_bytes, unused_blocks;;=> l_unused_blocks, unused_bytes;;;=> l_unused_bytes, last_used_extent_file_id => l_LastUsedExtFileId, last_used_extent_block_id => l_LastUsedExtBlockId, last_used_block => l_last_used_block; ); dbms_output.put_line(RPAD(('total_blocks: 'l_total_blocks),40,' ')'total_bytes: 'l_total_bytes); dbms_output.put_line(RPAD(('unused_blocks: 'l_unused_blocks),40,' ')'unused_bytes: 'l_unused_bytes); dbms_output.put_line(RPAD(('last_used_extent_file_id: 'l_LastUsedExtFileId),40,' ') 'last_used_extent_block_id: 'l_LastUsedExtBlockId); dbms_output.put_line('last_used_block: 'l_last_used_block);end;/ sequence<<Oracle9i Database Administrator's Guide; >>20if your application can never lose sequence numbers, then you cannot use Oracle sequences and you may choose to store sequence numbers in database tables. CREATE SEQUENCE; // 需要的系統權限create sequence lg_sequence start with 1 increment by 1 order;;;;;//保證每個序列值都比先前的大， ********在并行服務中有用 nocycle;;;//防止循環又回到初始值 NOCACHE NOORDER; 默認cache 為 20 直接 shutdown abort 后在內存中緩存的序列就會消失 startup后從上次shutdown以前的 sys.seq$ 的HIGHWATER 的值開始 最大值1.0E+27; 1后面27個零 lg_sequence.nextval lg_sequence.currval alter sequence lg_sequence //; alter sequence squ_1 increment by trunc(9999999/2); increment by 997;;//假如序列之前是2,這樣一改就是999 //是逐漸在原有的基礎上漲的oracle不支持復制 sequence-------------------------------;eXP sequence;--------------------------------------sequences are objects -- just like a table, procedure, view, package, etc. ;;;要exp sequence 那就 export a database or schema, that will get the sequences.或者;select 'create sequence ' sequence_name ' start with ' last_number+1 ';' from user_sequences where.....;由于是雜記 想到什么就寫的什么 可能有點亂 請大家多包涵