? 表迁移(非分区表)
????? 1.权限和约束将被保持.
????? 2.表结构发生变化.
????? 3.迁移数据到其他表空间.
???? alter table scott.emp move tablespace train;
???? 索引可能失效.
????
???? 重新定义online表的信息
???? 1.修改表的存储参数
???? 2.支持并行查询
???? 3.添加和删除分区
???? 4.表空间的移动.
???? 5.重建表减少碎片.
???? 6.更改一个表的信息.
???? 7.添加或者删除列
????
???压缩索引:
????? 1.create index emp_last_name_idx
??????? on scott.emp
???? (last_name) cmpress;
????? 2.重建索引压缩
???????????????? alter index emp_last_name_idx
??????????????????? rebuild compress ;
???????????????
??????????????? analyze index t_idx validate structure;
????
????1.创建索引
????create index t_idx on t(owner,object_type,object_name) compress 2;
????备注: compress 2表示压缩2列
????
????2.分析索引
???? analyze index t_idx validate structure;
????3.查看索引统计信息
???? select name,height,lf_blks,br_blks,btree_space,opt_cmpr_count,
???? opt_cmpr_pctsave from index_stats;
????
????
????
????
????
???倒序索引
????1.create unique index emp_idx on emp(epno)
??????? reverse pctfree 30
?????storage (initial 200k
?????????????? next 200k
???????? pctincrease 0
???????? maxextents 50
???????? )
?????tablespace tbs_index;
?????? 2.alter? unique index emp_idx? rebuild reverse;???
???
???bitmap index
?????? create bitmap index dep_idx
????on dept(mgr_id)
????storage(
?????initial 200k
?????next 200k
?????pctincrease 0
?????maxextents 50
????)tablespace tbs_idx;
????
????索引组织表
?????? 有点:
????????? 快速基于主的访问表的数据.
?????? 减少必要的存储.
??????
?????? 缺点:必须有主键
??????????? 不能被集群
?????????
?????? 创建IOT表
?????????? create table country
?????( country_id int,
?????? country_name varchar(25),
?????? constraint country_id_pk primary key(country_id)
?????)organization index
?????pctthreshold 20
?????overflow tablespace users;
?????
?????查询IOT表的信息
?????? select table_name,iot_name,iot_type
???????? from dba_tables;
?????? select index_name ,index_type,tablespace_name ,table_name
??????????????????????? from dba_indexes;
????????????????????? select segment_name,tablespace_name,segment_type
??????????? from dba_segments;
??????
??????????? Mapping table
??????? create table country
??????? (
???????? country_id char(2),
???????? contry_name varchar(20),
???????? constraint country_id_pk
???????? primary key(country_id)
??????? )
??????? organization index
??????? mapping table tablespace users;
???????
???????
??????? select index_name ,pct_direct_access
?????????? from dba_indexes
???????? where pct_direct_access is not null;
????????
????analyze 语句
??????? 1.validate structure;
?????????????????????????? analyze table scott.emp validate structure;
??????????????????? 2.list chained rows
???????????????
????Data WareHouse
???????? 1.db_file_multiblock_read_count:
????