bc8a0

一直学习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 doesnt 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 administrators 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…..;