sql视图和索引.序列

create table account_90
as
    select * from ACCOUNT where 1=2;

可以从已有表的基础上创建一个表

什么是view

• 视图在数据库中不存储数据值,即不占空间
• 只在系统表中存储对视图的定义
• 视图实际就是一条select语句
• 类似windows中的快捷方式

create or replace  view xxx
as select a from t10

创建一张名为xxx的视图,来自t10的列名为a(可以多列,可以*号)的数据

视图的应用场景

• 简化操作,屏蔽了复杂的sql语句,直接对视图操作
• 控制权限,只允许查询一张表中的部分数据.解决方法:对其创建视图,授予用户读视图的权限,而非读表的权限
• 通过视图将多张表union all成一张逻辑表,作为单独一个数据库对象,实现表的超集

视图的分类

• 简单视图
  ① 基于单张表并且不包含函数或表达式的视图,在该视图上可以执行dml语句(即可执行增、删、改操作)
• 复杂视图
  ① 包含函数、表达式或者分组数据的视图,在该视图上执行dml语句是必须要符合特定条件
  ②在定义复杂视图时必须为函数或表达式定义别名
• 连接视图
  ① 基于多个表建立的视图,一般来说不会在该视图上执行insert、update、delete操作

create or replace  view xx
as
    select a.REAL_NAME,c.NAME
from ACCOUNT a join SERVICE b
on a.ID=b.ACCOUNT_ID
join COST c
on b.COST_ID=c.ID
with read only;

也就像select语句
只是增加了一行create or replace view xx as
with read only约束,只读视图

索引(INDEX)

创建index

create index xx on table_name(colname);

create index xx on service(account_id);

delete from tabname 时间长
truncate table tabname 时间短
区别在于是否写undo segment
delete不释放表占用的空间
truncate释放表占用的空间

create table tests (
    c1 number ,c number
);
insert into tests values (11,1);
insert into tests values (12,1);
insert into tests
select * from tests;
select * from tests;
select rowid ,c1,c from tests;
delete from tests a
where rowid<(
    select max(rowid) from tests b
    where a.C1=b.C1
    and a.C=b.c
    )

create unique  index te on tests (c1);

相同

alter table tests
add unique (c1);

创建索引就和约束一样,约束名就是索引名字

利用rowid去重,rowid<select max(rowid)也就是说,去掉where a.C1=b.C1 and a.C=b.c下,比较小的rowid行,所以也可以换成> min .对于所有的重复记录,留下rowid最大/最小的那条记录.
在oracle看来,没有重复记录(每条记录都有rowid)

扫描表的方式

• 通过rowid来扫描数据
  -rowid: 标识一条记录的物理位置
• rowid包含如下信息:
  -该记录属于哪张表(哪个数据库对象):data_object_id
  -该记录在哪个数据文件里:block_id
  -该记录在数据块里是第几条记录:row_id

索引的结构

• B*tree索引由根块(root block)、分支块(branch block)、叶子块(leaf block)组成
• 根块下面是分支块,用于导航结构,包含了索引列范围和另一非根块(可以是分支块或叶子块)的地址

select real_name form account
where id=1011;
id列没建索引,用FTS(全表扫描)
id列建索引,找索引,从root块通过几步找到叶子块,找index entry,获得rowid,select变成where rowid=‘xxx’;

为什么要使用索引

• oracle server通过rowid快速定位到要找的行
• 通过rowid定位数据能有效降低读取数据块的数量
• 索引的使用和维护是自动的,一般情况不需要用户干预

那些列适合建索引

• 经常出现在where子句的列
• 经常用于表连接的列
• 该列是高基数数据列(高基数数据列是指有很多不同的值)
• 该列包含许多null值—索引不计空值
• 表很大,查询的结果集小
• 主键(PK)列,唯一键(UK)列 (不建也有)
• 外键(FK)列
• 经常需要排序(order by)和分组(group by)的列
• 索引不是万能的

索引用不了的写法

• 函数导致索引用不了
  where upper(colname)=‘carmen’

• 表达式导致索引用不了
  where colname*12=12000 ->'colname=1000'

• 部分隐式数据类型导致索引用不了
  where colname1=2 (c1为varchar2类型)-> '2'

• like和substr
  where colname like ‘CA%’ √
  where substr(colname,1,2)=‘CA’ ×

• 查询所有的null值
  where colname is null

• 否定形式
  not in
  !=

创建函数索引

因为函数会导致可能用不了,那么可以指定函数来建立索引
create index txx on tests(round(c1));

思考

index的工作原理
为什么要键index
哪些列适合建index
哪些sql写法会让index失效
索引的类型
create index alter index drop index
drop掉table索引没了,view还有.

怎么做到有唯一键约束而插入值时是唯一值

• 使用oracle提供的数据库对象sequence:序列号
• or自己写代码实现

小技巧-查目标文件是什么类型

select object_type from user_objects
where object_name='ACCOUNT';

create table test(c1 number primary key );
create sequence s1
start with 1
increment by 1
maxvalue 5
cycle
cache 4
select s1.nextval from dual;

创建一个sequence 序列
start with 1不用解释 increment by 1一次加1 maxvalue 5最大值是5,默认是integer最大值 cycle与之相对no cycle(默认)是否循环 ,如果要循环的话cache必须必maxvalue小,实际上循环和唯一是矛盾的.
select s1.nextval from dual;检测