一直学习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.....;
由于是杂记 想到什么就写的什么 可能有点乱 请大家多包涵
[系统软件]EXP-00008: ORACLE error 904 encountered的解决方… [常用软件]PB7 连接 Oracle 的配置方法 [Web开发]oracle Export and Import 简介 [Web开发]ADO访问Oracle结果集的心得 [JAVA开发]JDBC+Hibernate将Blob数据写入Oracle [JAVA开发]J2EE应用中与Oracle数据库的连接 [JAVA开发]Oracle Application Serve_ [其他]HP-UXrx2600B.11.22Uia64安装oracle9i9.2foria64手… [其他]oracle使用杂记1 [其他]在RedhatEnterpriseserver3上安装oracle9iR2的注意…
|