📄 oracle开发技巧-13.htm

📁 微软数据库开发梦工场多媒体教学-oracle篇.rar,是多媒体教学的
💻 HTM
字号:
<body bgcolor="#000000">
<font color="#FFFFFF"> <font color="#00CC00">导出创建非唯一索引脚本的方法 </font> 
<p> 在ORACLE里用逻辑备份工具exp导出数据时，如果使用默认参数, 会把索引一起导出来。当数据和索引小的时候，我们可能不太会计较导入时间; 如果数据和索引大的时候，就应该考虑导入时间的问题了。</p>
<p> 实际地说，二进制dmp备份文件里有些索引对备份是用处不大的, 导出时完全可以选择indexes=n的参数, 不备份它们。这样不仅可以缩短导入导出时间，还可以节省备份文件的存储空间。这种索引类型是非唯一（nounique）的。我们可以根据ORACLE数据字典dba_indexes和dba_ind_columns里的信息生成创建索引的脚本。在导入完成后再运行这些创建索引的脚本。</p>
<p> dba_indexes里记录了索引类型和存储参数等信息。<br>
  dba_ind_columns里记录了索引的字段信息, 它的结构如下：　</p>
<p>SQL&gt; desc dba_ind_columns;<br>
  name null? type<br>
  ----------------------------------------- -------- -------------------------<br>
  index_owner not null varchar2(30)<br>
  index_name not null varchar2(30)<br>
  table_owner not null varchar2(30)<br>
  table_name not null varchar2(30)<br>
  column_name varchar2(4000)<br>
  column_position not null number<br>
  column_length not null number<br>
  descend varchar2(4)</p>
<p> column_name记录着有索引的字段, column_position标记着字段在创建索引时的位置, descend指索引的排序, 有asc和desc<br>
  两种，而desc排序方法用的较少，本文只考虑asc的情况。</p>
<p> 步骤一：先创建一个视图index_nouniq_column_num列出非系统用户nonunique索引的用户名, 索引名和字段数量。</p>
<p>SQL&gt; create view index_nouniq_column_num as select t1.owner,t1.index_name,count(0) 
  as column_num <br>
  from dba_indexes t1,dba_ind_columns t2 where t1.uniqueness='NONUNIQUE' <br>
  and instr(t1.owner,'sys')=0 and t1.owner=t2.index_owner and t1.index_name=t2.index_name<br>
  group by t1.owner,t1.index_name order by t1.owner,column_num;</p>
<p> 步骤二：为了处理方便，建一个索引字段临时表index_columns, 它的column_names记录了以逗号分隔,顺序排列的索引字段。</p>
<p>SQL&gt; create table index_columns(<br>
  index_owner varchar2(30) not null,<br>
  index_name varchar2(30) not null,<br>
  column_names varchar2(512) not null)<br>
  tablespace users;</p>
<p> 步骤三：把只有一个字段的索引内容插入索引字段临时表index_columns。</p>
<p>SQL&gt; insert into index_columns select t1.owner,t1.index_name,t2.column_name 
  from <br>
  index_nouniq_column_num t1,dba_ind_columns t2 <br>
  where t1.column_num=1 and t1.owner=t2.index_owner and t1.index_name=t2.index_name 
  <br>
  order by t1.owner,t1.index_name;<br>
  SQL&gt; commit;</p>
<p> 步骤四：把多个字段的索引内容插入索引字段临时表index_columns。<br>
  用到以下一个函数getcloumns和过程select_index_columns。</p>
<p>函数getcloumns:<br>
  create or replace function getcloumns(<br>
  index_owner1 in varchar2,<br>
  index_name1 in varchar2,<br>
  column_nums1 in number) return varchar2<br>
  is<br>
  all_columns varchar2(512);<br>
  total_num number;<br>
  i number;<br>
  cursor c1 is select column_name from dba_ind_columns where index_owner=index_owner1 
  and<br>
  index_name=index_name1 order by column_position;<br>
  dummy c1%rowtype; <br>
  begin<br>
  total_num:=column_nums1; <br>
  open c1;<br>
  fetch c1 into dummy;<br>
  i:=0;<br>
  while c1%found loop<br>
  i:=i+1;<br>
  if (i=total_num) then<br>
  all_columns:= all_columns||dummy.column_name;<br>
  else<br>
  all_columns:= all_columns||dummy.column_name||',';<br>
  end if;<br>
  fetch c1 into dummy;<br>
  end loop;<br>
  close c1;<br>
  return all_columns;<br>
  exception<br>
  when no_data_found then<br>
  return all_columns;<br>
  end;<br>
  /</p>
<p>过程select_index_columns:<br>
  create or replace procedure select_index_columns<br>
  is<br>
  all_columns varchar2(2000);<br>
  cursor c1 is select * from index_nouniq_column_num where column_num&gt;=2;<br>
  dummy c1%rowtype; <br>
  begin<br>
  open c1;<br>
  fetch c1 into dummy;<br>
  while c1%found loop<br>
  select getcloumns(dummy.owner,dummy.index_name,dummy.column_num) into all_columns 
  from dual;<br>
  insert into index_columns values(dummy.owner,dummy.index_name,all_columns);<br>
  fetch c1 into dummy;<br>
  end loop;<br>
  commit;<br>
  close c1;<br>
  exception<br>
  when others then <br>
  rollback;<br>
  end;<br>
  /</p>
<p>SQL&gt; exec select_index_columns;</p>
<p> 执行select_index_columns过程就可以把多个字段的索引内容插入索引字段临时表了。</p>
<p> 步骤五：最后运行create_now_index.sql，根据索引字段临时表index_columns和dba_indexes在路径/oracle_backup/log<br>
  下生成创建非唯一索引脚本create_index.sql。</p>
<p>create_now_index.sql内容：</p>
<p>set heading off;<br>
  set pagesize 5000;<br>
  truncate table index_columns;<br>
  -- 把多个字段的索引内容插入索引字段临时表<br>
  exec select_index_columns;<br>
  -- 把只有一个字段的索引内容插入索引字段临时表<br>
  insert into index_columns select t1.owner,t1.index_name,t2.column_name <br>
  from index_nouniq_column_num t1,dba_ind_columns t2 <br>
  where t1.column_num=1 and t1.owner=t2.index_owner and t1.index_name=t2.index_name 
  <br>
  order by t1.owner,t1.index_name;<br>
  commit;<br>
  spool /oracle_backup/log/create_index.sql;<br>
  SELECT 'CREATE INDEX '||t1.owner||'.'||t1.index_name|| chr(10) ||' ON '||t1.table_name||<br>
  ' ('||column_names||')'|| chr(10) ||' TABLESPACE '||t1.tablespace_name|| chr(10) 
  ||<br>
  ' PCTFREE '||t1.pct_free || chr(10) ||' STORAGE (INITIAL '||t1.initial_extent||<br>
  ' NEXT '||t1.next_extent||' PCTINCREASE '||t1.pct_increase||');'|| chr(10) || 
  chr(10)<br>
  FROM dba_indexes t1,index_columns t2 <br>
  WHERE t1.owner=t2.index_owner and t1.index_name=t2.index_name <br>
  ORDER BY t1.owner, t1.table_name;<br>
  spool off;</p>
<p><br>
</p>
</font>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -