大数据面试题

使用with as有如下好处

1、可以轻松构建一个临时表，通过对这个表数据进行再处理。但是他比临时表更强大，临时表在会话结束才会自动被P清除，但with as临时表查询完成后就被清除了

2、复杂的查询会产生很大的sql，with as语法可以把一些公共查询提出来，也可以作为一个中间结果，可以使整个sql语句显得有条理些，提高可读性

事务的几个特征：

原子性：每一个事务都是最小的功能模块

一致性：同时成功同时失败

隔离性：同时操作事务的时候，事务之间不会互相影响

持久性：通过事务操作的数据，是永久保存的

数据库的锁：

为了避免多个用户对同一个数据同时进行操作，操作数据的时候数据保存出错产生脏数据

从锁的范围上看

行锁：

A用户在对数据做dml操作的时候，数据会被上锁，B用户不能对这个数据所在的行同时进行操作

表锁：

A用户在对数据做dml操作的时候，数据所在的表会被上锁，B用户不能对表做ddl（除了新增表格结构之外）的操作

从上锁的主动性和被动性看

悲观锁：通过for update提前占用数据的资源

select 语句 + for update;

select * from scott.emp where ename='SMITH' for update;

乐观锁：数据库默认的就是乐观锁

数据库的死锁：

同时操作的进程里面，每个进程都互相占用了对方的资源，谁都无法释放资源和成功跑完

怎么解决死锁的问题？

--查看所有被上了锁的对象  69539

select * from v$locked_object;

--找到锁的对象，是在哪个表上面  根据自己找到的这个表的名字，回去找session_id  131

select * from dba_objects where object_id=69539;

--找锁的id的  serial#  9555

select * from v$session where sid=131;

--根据这两个编号，杀掉这个锁

alter system kill session '131,9555';

分区表和表分区：***

表分区：将一个表里面，所有的拥有相同特征的数据，在硬盘上规划出一块区域，整合在一起

分区表：拥有表分区的划分规律的表格

表分区的类型划分：

1. 散列分区：hash 用来划分那么没有什么规律的列

create table emp_hash(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by hash(ename) partitions 4;

通过数据库的内部的哈希算法，将所有的行，根据名字的内容，一共放到4个不同的分区中进行分开的保存。哈希算法的分区的数量，最好是写2的次方数。

2. 列表分区  list

create table emp_list(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by list(deptno)

(

    partition dept10 values(10),

    partition dept20 values(20),

    partition dept30 values(30)

);

当某个列的值，是大量的重复的相同数据数据，使用列表分区，对这个列进行特征和内容的归纳和划分，将拥有相同数据的行，放在同一个分区中

3. 范围分区  range

create table emp_range(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by range(sal)

(

  partition sal_0_1000 values less than (1001),

  partition sal_1001_2000 values less than (2001),

  partition sal_2001_3000 values less than (3001),

  partition sal_3001 values less than (maxvalue)

);

日期分区

  partition s20201125 values less than(date'2020-11-26'),

  partition s20201126 values less than(date'2020-11-27')

数字类型、日期类型的数据，例如金额、时间、年龄等等，将某个范围内的数据，存放在一起，那么就使用范围分区

给表格添加140W行的数据：

declare

begin

  for i in 1..100000 loop

      insert into emp_range select * from scott.emp;

      commit;

  end loop;

end;

如果一个表的数据越来越大，查询很慢的话，重新创建一个分区表，复制原有的数据，删除旧表。

4. 组合分区

create table 表名(

列名 数据类型

)partition by 父分区类型(列名)

subpartition by 子分区类型(列名)

(

    partition 父分区名字 values 分区规则(

        subpartition 子分区名字 values 分区规则，

        subpartition 子分区名字 values 分区规则，

        ...

    )

);

create table emp_zuhe(

empno number,

ename varchar2(20),

job varchar2(20),

mgr number,

hiredate date,

sal number(7,2),

comm number(7,2),

deptno number,

mobile char(11)

)partition by list(deptno)

subpartition by range(sal)

(

   partition d10 values(10)(

     subpartition sal1000_10 values less than (1001),

     subpartition sal2000_10 values less than (2001),

     subpartition salmax_10 values less than (maxvalue)

   ),

   partition d20 values(20)(

     subpartition sal1000_20 values less than (1001),

     subpartition sal2000_20 values less than (2001),

     subpartition salmax_20 values less than (maxvalue)

   ),

   partition d30 values(30)(

     subpartition sal1000_30 values less than (1001),

     subpartition sal2000_30 values less than (2001),

     subpartition salmax_30 values less than (maxvalue)

   )

);

首先划分父分区，设置父分区的规则，然后在父分区中划分子分区的规则，这样可以在同时对两个列进行查询的时候，有效的减少查询需要消耗的资源和时间。

2000W以上的数据，就需要划分分区表了。

查看分区

select * from user_indexes;    --查看所有的索引

select * from user_tables;      --查看所有的表

select * from user_tab_partitions;    --查看所有的分区表

select * from  user_procedures

alter table 表名 drop partition 分区名;

alter table 表名 add partition 分区名 values 设置的规则;

数据库的索引：index

什么是索引：就是每一个表格的目录结构，帮你快速的定位表格中的数据在表格里的位置

添加索引的语法：

create index 索引名 on 表名(列名);

索引有哪些类型：

1. 主键索引：在创建表格的时候，添加了主键约束，那么就会自动生成主键索引

alter table emp_idx add constraint pri_empno primary key(empno);

通过主键约束的创建，来生成这个主键索引

2. 唯一索引：

2.1 在创建表格的时候，会根据唯一约束自动的生成唯一索引

alter table emp_idx add constraint uni_empno unique(empno);

2.2 在没有约束的前提下，手动创建唯一索引，唯一索引不会反过去创建唯一约束

create unique index idx_emp_empno on emp_idx(empno);

3. 普通索引：这个列经常需要被查询，但是这个列又没有什么特点

create index idx_nor_ename on emp_idx(ename);

4. 组合索引：和普通索引相同，有多个列需要同时被查询，但是这些列也没有什么特点，那么就放在一起，创建一个组合索引

create index idx_sal_job on emp_idx(sal,job);

create index 索引名 on 表名(a,b,c);

abc

ab

ac

a

查询组合索引，一定需要包含组合的第一个列

5. 函数索引：如果你的列，是需要被使用函数计算之后再用来查询，那么计算过程需要被写在索引里面

create index idx_func_hiredate on emp_idx(to_char(hiredate,'yyyy'));

6. 位图索引：列的内容是由大量的重复的内容组成的  bitmap

create bitmap index idx_emp_deptno on emp_idx(deptno);

7. 分区表索引

7.1 本地索引

create index idx_hash_ename on emp_hash(ename) local;

7.2 全局索引

create index idx_hash_job on emp_hash(job) global;

本地和全局的区别？

--本地索引的话，在创建唯一索引的时候，在整个表中，可能会出现重复信息，在一个分区里面，值保证是唯一的，全局索引是整个表中，数据唯一存在

--删除了一个分区或者新增了一个分区，那么全局的索引会失效，需要你删除了索引然后重新建立，本地的索引是不会失效的

删除索引：

drop index 索引名;

drop index idx_hash_job;

什么时候用哪种索引

普通的索引和位图的索引，区别在哪？

主键、唯一、普通、组合、函数（普通的normal类型）  树状结构  b树索引，btree索引

位图（bitmap）  二维表结构

普通索引是通过根节点、分支节点、叶子节点、行数据四个节点块，一层层进行数据范围的筛选。

位图的索引是在二维表中，通过0和1来存储数据内容。

每一种索引都是通过rowid来搜索数据的。

索引是不是越多越好？

一个表不要建超过5个索引；索引的数量，不要超过列的数量的15%。

索引一定是建立上反复的经常被查询的列上。

索引会加快dql的速度，但是会降低dml的速度。

有一个2000W数据的表格，要接着往里面添加2000W的数据，怎么样操作最快？

1.先删除所有的索引

2.插入2000W数据

3.重建索引

索引在什么时候会失效？

1. 经常需要被修改的列，索引是一直处于失效状态的

2. 数据发生了隐性的转换

select * from emp_idx where to_char(hiredate,'yyyy')=1981;

3. 计算的时候，公式放在了等号的左边

select * from emp_idx where sal+1000=4000 and job='MANAGER'

4. 查询的时候使用不等于

select * from emp_idx where deptno!=20

5. 查询的时候使用了not

select * from emp_idx where not deptno=20

6. 空值查询

select * from emp_idx where ename is null

7. 组合索引没有使用第一列的列查询

8. 查询的时候，使用了非函数索引的函数

什么是索引？

索引有哪些类型？分别在什么时候会使用到不同的索引？

索引是不是越多越好？

大量数据的表格插入，怎么操作会更快？

索引在什么时候会失效？

表连接的时候，通过执行计划查看到的表连接内部的逻辑：

表连接有三种内部的连接方式（查询内容的三种方法）：

hash join  哈希连接

nested loops  nl嵌套连接

merge join   排序归并

什么时候会出现这个hash join？

两个表格列的等值筛选

什么时候会出现NL嵌套连接？

1. 两个表的列，进行非等值筛选；

2. A表和B表的数据，相差很大，并且表连接的列都是有索引的，这个时候的等值连接就不会变成hash join，而是会使用nl嵌套连接。

什么时候会出现merge join 排序归并？

两个表的连接，筛选条件不是对列和列进行筛选，而是对列和值进行筛选

hints优化器：用来强行改变表格内部运行逻辑的方法

写在select后面，写在列名的前面

/*+  优化器的内容 */

改变表连接的逻辑：

/* +use_hash(a b) */ 强制使用哈希连接

/* +use_merge(a b)*/ 强制使用排序归并

/* +use_nl(a b) */ 强制使用嵌套连接

改变表格读取的顺序

/* +leading(a b) */ a一定要是小表（驱动表），b一定是大表（匹配表）

改变查询表格的方法

/*+ full(a) */ 强制全表扫描

/*+ index(表名  索引名) */ 强制使用索引

并发的使用

/*+ parallel(8) */ 强制使用并行的资源，来执行这个sql语句

在oracle中怎么去优化查询的sql语句/怎么优化数据库？

1.要先通过执行计划看一下sql语句执行的效率

2.查看表格的数量，如果数据量很大，就先给表格进行分区表的设置

3.给表格建适当的索引

4.检查索引失效的语句

5.使用嵌套去替换部分连接的场景

查询SMITH在哪里上班

联合查询14  4  56行

嵌套查询14  4  18行

6.使用临时表去存储一个或者多个大表筛选的结果，然后使用临时表进行表格的连接

7.or的效率比较低，可以使用union all去进行替换

8. 尽量少使用union   minus   intersect等集合的运算，这些都是效率很差的关键字

9.使用多线程的并发的优化器去加速执行的速度

oracle的视图：view  一个或者多个表查询的结果，这个结果就是一个虚拟的表，每次运行视图，都会重新运行视图中的那个查询语句

create view 视图名 as

select 语句

with read only;

视图的作用：

1. 将一个复杂的select语句，放入到一个视图里面，简化日常的操作

2. 可以隐藏一部分原表的列，简化别人在操作表的时候的业务逻辑

3. 可以添加with read only，增加表的安全性

4. 视图可以节省网络流量

视图的缺点：

对拥有复杂逻辑的视图，再次表连接等操作，会导致语句更加复杂，运行效率很慢。

oracle的物化视图：是一个真实的物理的表格，将一个或多个表格查询的结果，当成一个新的表格来保存，物化视图的表格，会对原表的数据进行同步，物化视图的表格，本身就是不能更改的

物化视图也叫做  快照表。

create materialized view 物化视图名字

refresh on commit | demand

start with 时间点 next 下一次更新的时间点

as

select 语句;

on commit：类似于实时更新，原表提交数据时更新

create materialized view stu_nan

refresh on commit

as

select * from student where ssex='男' and sage>20;

on demand：定时更新

create materialized view stu_nv_20

refresh on demand

start with sysdate

next to_date(to_char(sysdate+1,'yyyy-mm-dd')||' 10:00:00','yyyy-mm-dd hh24:mi:ss')

as

select * from student where ssex='女' and sage>20;

表格更新的方法：

1. complete 完全刷新，整个表格全部都更新一次数据

2. fast 快速刷新，更新你变更部分的数据

3. force 默认的更新方式，默认的更新方法就是fast

4. never 不要更新表格

declare

begin

dbms_mview.refresh('物化视图的名字','更新的方法');

end;

视图和物化视图的区别是什么？

1. 视图是一张虚拟的表格，不会占用磁盘空间，物化视图是一个真实的表格，会占用磁盘空间

2. 视图为了简化select操作用的，物化视图也叫做快照表，是为了保存某个表格或者是select语句在某个时间点的数据而存在的

3. 视图是实时更新的，物化视图有两个刷新表格内容的方法，一种是提交数据的时候更新，第二种是定时更新

4. 当原表非常大的时候，视图不会减少查询时间，物化视图是会减少查询时间的

5. 可以在物化视图上建立索引，进一步加快查询速度

☐

拉链表，缓慢变化维：

拉链表就是一张普通的表格，这个表格会保存你每一次数据前后变更的状态。

1.以行的方式保存历史数据和变化状态

有开始时间和结束时间还有变更状态这三列，用来记录每一行数据前后变更的状态和顺序，这三列叫做缓慢变化维。

优点：可以保存所有的历史记录

缺点：表格的数据会特别的多

2.以列的方式保存最近的历史数据

会创建列的旧数据的备份列，会保存最近一次的变更的数据，和数据变更的时间

优点：可以保证表格的数据量不会很大

缺点：看不到详细的变化状态

sql语句的分类：dcl（权限的操作  grant  revoke）  ddl（对数据库对象的操作：用户 表 索引 视图 物化视图 create alter drop truncate）  dml(对表里面的数据的操作  insert  update  delete)  dql(表数据的查询  select)

数据类型：integer  number  number(总长度,小数精度)  char(长度)  varchar2(长度)

date  blob  clob

约束条件：primary key    unique    not null   check   foreign key

常用函数有哪些？

聚合函数：max  min  avg  sum  count

单行函数：数字  round  trunc  abs  floor（6.3 变6）  ceil(6.1 变7）   mod   power

            字符串   substr  concat  replace  length  

      日期  last_day   months_between   add_months    sysdate

      转换  to_date  to_char   to_number

分析函数：row_number   rank   dense_rank   lag   lead  + over()

行列转换：pivot

空值处理：nvl    nvl2

逻辑相关：decode

字符串的行列转换：wm_concat()

将一个列的字符串，以行的方式拼接起来，中间用逗号隔开。