Oracle数据库表分区

1、特点：适用于海量数据

2、实例：

create tablespace tbl_sc
datafile 'd:/sc.dbf' size 20M;

create tablespace tbl_hn
datafile 'd:/hn.dbf' size 20M;

create table person
(
       no number(11) primary key,
       name varchar2(30) not null,
       acode number(4)
      
);
partition by list(acode) (
          partition code_sc values(4) tablespace tbl_sc,
          partition code_hn values(5) tablespace tbl_hn
);

insert into person values(1,'aa',4);
insert into person values(2,'bb',5);
insert into person values(3,'dd',4);
insert into person values(4,'cc',4);
insert into person values(5,'ee',5);

select * from person  where acode = 4;

3、数据库缓存

/*-----------------对库高速缓存的调整----------------*/

/**------------------------------------------
统计请求sum(pins)-------------------pins属性
统计不命中数sum(reloads)------------reloads属性
---------------------------------------------*/

select sum(pins) "请求数",sum(reloads)"不命中数" from v$librarycache;

select (sum(pins-reloads))/sum(pins) "librarycache" from v$librarycache;

/*-----------------对数据字典缓存的调整----------------*/

/**------------------------------------------------
统计请求sum(gets)--------------------------gets属性
统计不命中数sum(getmisses)------------getmisses属性
有效缓存项数------------------------------usage属性
固定缓存----------------------------------fixed属性
--------------------------------------------------*/

select sum(gets) "请求数",sum(getmisses)"不命中数" from v$rowcache;

select (sum(gets-getmisses-usage-fixed))/sum(gets) "数据字典使用率" from v$rowcache;