一直学习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.....;
由于是杂记 想到什么就写的什么 可能有点乱 请大家多包涵
