一直学习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;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 方便使用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;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_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_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; >>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.....;




由于是杂记 想到什么就写的什么 可能有点乱 请大家多包涵