一直學習oracle 希望能和大家共同探討問題 如有不對之處還請指出
index 種對null的使用
================================================================
有些情況可以用 " n/a " 代替 null
================================================================
8i 以上使用基于函數的index 可以用上 null
create table t (n number);
create index ind_n on t(n,1); // 用 t(n,'a') 更省空間
select v from t where n is null;
v
--------------------
lg
execution plan
----------------------------------------------------------
0 select statement optimizer=choose (cost=3 card=614 bytes=6140)
1 0 table 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 方便使用index
create 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 0 index (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 0 table 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_tables
select 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_block number;
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 >> 20
if 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.....;
由于是雜記 想到什么就寫的什么 可能有點亂 請大家多包涵
