Oracle数据库易遗漏的知识点(二)

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/lxj1137800599/article/details/50519661

1.exists和in

我们区分in和exists主要是驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系

如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。

2.merge into

create table test1(eid number(10), name varchar2(20),birth varchar(255),salary number(8,2));
insert into test1 values (1001, '张三', '2015-1-8', 2300);
insert into test1 values (1002, '李四', '2013-1-18', 6600);
select * from test1;
create table test2(eid number(10), name varchar2(20),birth varchar(255),salary number(8,2));
select * from test2;

merge into test2
using test1
on (test1.eid = test2.eid)
when matched then
  update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
  insert
    (eid, name, birth, salary)
  values
    (test1.eid, test1.name, test1.birth, test1.salary);

3.参照完整性约束foreign key

建立如下两张表

CREATE TABLE person
(
pid NUMBER primary key,
name VARCHAR(30) NOT NULL ,
tel VARCHAR(50) ,
age NUMBER
) ;
CREATE TABLE book
(
bid NUMBER ,
name VARCHAR(50) ,
pid NUMBER REFERENCES person(pid) ON DELETE CASCADE
) ;
person表中有如下数据:


参照完整性约束要求:如果某个字段是外键 那么该值要么为空 要么在主键表中 有对应的主键值

所以往book中插入的数据pid要么是12,要么为null


可见,当插入的pid为13时报错

4.视图View

视图的数据来源于基础表,修改视图的数据当然就会修改基础表,建议不要修改视图数据,要修改数据直接在基础表修改。 如果要把视图设置成只读,后面加上With READ ONLY

5.索引

建立索引的优点

1.大大加快数据的检索速度;
2.创建唯一性索引,保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。

索引的缺点

1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。

索引的创建和效率

create index abc on student(sid,sname); 

create index abc1 on student(sname,sid);

这两种索引方式是不一样的

索引abc对Select * from student where sid=1; 这样的查询语句更有效

索引abc1对Select * from student where sname=‟louis‟; 这样的查询语句更有效

另外,如果经常查询x=?和y=?,那推荐使用组合index(x,y),这种情况下组合索引的效率是远高于两个单独的索引的。

6.SQL优化

SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,执行过程中访问尽量少的数据块,减少表扫描的I/O次数,尽量避免全表扫描和其他额外开销。

1.正确使用exist和in(本文第一点有说明)

2.尽量用NOT EXISTS或者外连接替代NOT IN操作符,因为NOT IN不能应用表的索引

3.尽量不用“<>”或者“!=”操作符,不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。比如:a<>0 改为 a>0 or a<0

4.在设计表时,把索引列设置为NOT NULL,判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。

5.尽量不用通配符“%”或者“_”作为查询字符串的第一个字符。当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用。比如用T表中Column1 LIKE "%5400%‟ 这个条件会产生全表扫描,如果改成Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用Column1的索引进行两个范围的查询,性能肯定大大提高。

6.WHERE后面的条件顺序要求,WHERE后面的条件,表连接语句写在最前,可以过滤掉最大数量记录的条件居后。

7.使用表的别名,并将之作为每列的前缀,这样可以减少解析时间

8.如果数据本身重复行存在可能性较小时,用union all会比用union效率高很多!

9.尽量使用cached sequences 来生成primary key :提高主键生成速度和使用性能。

10.很好地利用空间:如用VARCHAR2 数据类型代替CHAR

7.序列SEQUENCE

CREATE SEQUENCE numberIndex
START WITH 1 -- 从1开始计数
INCREMENT BY 10-- 每次加几个
MAXvalue 50-- 不设置最大值NOMaxvalue
CYCLE<span style="white-space:pre">	</span>--NOCYCLE
NOCache;--NOCache

8.PLSQL代码块

------------------declare if-else
DECLARE
  SCORE DECIMAL;
BEGIN
  SCORE := 75;
  IF (SCORE < 60) THEN
    DBMS_OUTPUT.PUT_LINE('及格');
  ELSE
    DBMS_OUTPUT.PUT_LINE('不及格');
  END IF;
END;

------------------declare case-when
 DECLARE
   SCORE INTEGER;
 BEGIN
   SCORE := TRUNC(DBMS_RANDOM.VALUE(0, 100));
   CASE
     WHEN SCORE < 60 THEN
       DBMS_OUTPUT.PUT_LINE('及格');
     ELSE
       DBMS_OUTPUT.PUT_LINE('不及格');
   END CASE;
 END;
   
----------------while
DECLARE
  T INTEGER;
BEGIN
  T := 0;
  WHILE T < 5 LOOP
    DBMS_OUTPUT.PUT_LINE(T);
    T := T + 1;
  END LOOP;
END;

也可以选择手动输入

T := &en;

9.游标

create table emp(
empno integer,
ename varchar2(20)
);
insert into emp values(1, 'll');
insert into emp values(2, 'mm');

DECLARE
  CURSOR mycur IS SELECT * FROM emp;
  empInfo emp%ROWTYPE;
BEGIN -- 游标操作使用循环,但是在操作之前必须先将游标打开
  FOR empInfo IN mycur LOOP
    DBMS_OUTPUT.put_line('雇员编号:' || empInfo.empno);
    DBMS_OUTPUT.put_line('雇员姓名:' || empInfo.ename);
  END LOOP;
END;
展开阅读全文

没有更多推荐了,返回首页