oracle 分区表的相关索引

局部索引

create table sale_fact_part
partition by range(year)
( partition p_1997 values less than (1998),
  partition p_1998 values less than (1999),
  partition p_1999 values less than (2000),
  partition p_2000 values less than (2001),
  partition p_max values less than (maxvalue)
)
as select * from sale_fact;

create index idx_sale_fact_part on sale_fact_part(product,year) local;

全局索引

create index idx_sale_fact_part on sale_fact_part(year) 
global partition by range(year)
( partition p_1997 values less than (1998),
  partition p_1998 values less than (1999),
  partition p_1999 values less than (2000),
  partition p_2000 values less than (2001),
  partition p_max values less than (maxvalue)
)

散列分区索引

create table sale_fact_part
partition by  hash(id)
partitions 32
as
select seq1.nextval id,f.* from sales_fact f;

create unique index idx_sale_fact_part on sale_fact_part(id) local;

压缩索引

create index idx_name 
on schema.tabname(col1,col2...)
compress N 
Storage-parameter-clause;

基于函数的索引

基于函数的索引加入了一个虚拟列，所声明的表达式值作为默认值，然后在这个虚拟机列上建立索引。

create index idx_sale_fact_part on sales_fact_part(to_char(id));

反转键索引

create unique index idx_sale_fact_part on sale_fact_part(id) global reverse;

降序索引

create index idx_sale_fact on sales_fact (product desc,year desc,week desc)

不可见索引

alter index idx_sale_part invisible;

虚拟索引

create index idx_sale_part on sales(cust_id,promo_id) nosegment;
alter session set "_use_nosegment_indexes"=true;   #开启虚拟索引

位图索引

create bitmap index idx_sale_part on sales_fact_part(country) local;

索引组织表

create table sales_iot
( col1 int,
  col2 int,
  col3 int,
  col4 int,
  primary key(col1,col2)
)
organization index;

位图联结索引

create bitmap index idx_sale_part on sales (p.prod_name,c.cust_first_name,ch.channel_desc)
from  sales s,products p,customers c,channels ch
where       s.prod_id=p.prod_id 
       and  s.cust_id = c.cust_id
       and  s.channel_id = ch.channel_id
local;

rownum的用法

select * from (
select * from sales_fact where ...
) where rownum < 100;