大数据面试常见问题（一）——Oracle部分
时间：2023-09-04 03:37:01
目录
1、Oracle分区类型
2.每种类型通常用于什么场景？
3.哪种分区类型使用最多？为什么？
4.组合分区用过吗？怎么搭配？
基本语法是什么？-toc" style="margin-left:40px;">5.分区表创建的基本语法是什么？
6.分区有哪些不同的操作？
7.如何查看表格分区的信息？
8.分区字段where筛选和直接读取分区内容，谁快一些？
索引的概念？
10.索引有哪些不同类型？
11.什么场景通常使用不同的索引？
12.普通索引和位图索引有什么区别？
13.索引在什么情况下会失效？
14.索引的优缺点？
15.哪些列应该建立索引？哪些列不应该建立索引？
16.唯一的索引和主键索引有什么区别？
17.如何查看表格中不同的索引？
本地索引和全局索引在索引中有什么区别？
19、oracle如果里面有一个sql语句运行缓慢，怎么办？
20、oracle有哪些常见的数据类型？
21、varchar 和 varchar2 的区别？
22、minus的含义是什么？
23、在左连接里面，如果使用and筛选和用where筛选，区别是什么？
24、左边表有身份证信息，右边也是身份证信息，查询左边有但是右边没有的身份证：
25、经常用到的函数有哪些？
26、伪列有哪些有什么用？
27、表空间怎么创建、怎么查看、有什么作用？
28、执行计划里面经常要查看哪些内容？
29、什么是视图？优点缺点是什么？
30、什么是物化视图？和视图的区别是什么？
31、存储过程由哪些代码构成？
32、什么时候写存储过程？
33、存储过程和函数的区别？
34、数据仓库是用什么方法来保存历史数据的
35、拉链表是什么结构？什么是缓慢变化维？
36、Oracle数据库的端口号是多少
37、Oracle 数据库日常维护？
38、什么是伪列，有哪几种，区别在那里？
39、如何使用ROWID去重？
40、如何分页查询？
41、SQL执行顺序？
42、如何对重复数据去重？
43、常用的SQL函数有哪些？
44、常见的分析函数（开窗函数）有哪些？
45、如何列转行、行转列？
46、分析函数和聚合函数的区别？
47、分析函数的使用需要注意什么？
48、in与not in,exists与not exists的区别以及性能分析？
49、ON和WHERE中写过滤条件的区别是什么？
50、count(*) 和 count(1)和count(列名)区别？
51、什么是同比和环比？如何用SQL实现同比环比的计算？
52、Union和union all 的区别？
53、Oracle where 子句中多个and和or时，执行顺序是怎么样的？
54、知道varchar与varchar2的区别吗？空值怎么转换及区别？
55、Delete和truncate的区别，和drop又有什么区别？
56、知道怎么授权吗？取消授权呢？
57、什么是数据字典？如何查询系统数据字典？
58、Oracle SQL语句由什么命令组成？
59、什么是事务？事务有哪些特点？
60、什么是锁，什么是死锁？
61、如果发生死锁，如何处理：
62、什么是表空间？系统常见的表空间有哪些？
63、什么是同义词？有什么作用？
64、什么是序列？常用在什么地方？
65、表分区的概念？
66、什么时候使用表分区？
67、表分区的优点和缺点？
68、如何把一张没有分区的表转化为分区表？
69、什么是游标，如何使用？
70、df游标的属性有哪些？
71、动态sql如何实现变量绑定？好处是什么？
72、如何创建存储过程？
73、存储过程参数的区别？
74、常用的异常都有哪些？
75、如何找到存储过程哪个环节执行的快慢？
76、 包的组成？
77、什么是全局变量？
78、常用的SQL语句优化？
79、优化器是什么？有哪几种？
80、表的扫描方式有哪些？优缺点是什么？
81、表的关联方式有哪些？优缺点是什么？
82、什么是并行，如何通过并行增加数据处理速度？加多少并发？
83、如何看hints 是否被调用？Hints原理
84、常见的hints优化器有哪些？
85、有两张很大的表关联，怎么做才能关联性能好？
86、大表和小表连接如何进行优化？
87、Oracle内部执行机制?
88、执行计划执行顺序
1、Oracle分区类型
范围分区，列表分区，散列分区，组合分区
2、每种类型一般是用在什么场景下？
范围分区：用指定的分区键决定的范围进行分区，最为常用，分区键常采用日期。
列表分区：某列的值只有几个，可以采用列表分区。
散列分区：通过指定分区数量或编号来均匀分布数据的一种分区类型，分区数量常采用2的N次方；当列的值没有合适的条件时，采用散列分区；
组合分区:范围分区和列表分区组合；范围分区和散列分区组合，分区中的分区被称为子分区；
3、哪种分区类型使用的最多？为什么？
看具体情况：
如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。
如果需要数据的均匀分布，那么可以考虑使用HASH分区。
如果数据的值可以很好地对应于某个分区，那么就可以考虑使用列表分区。
在上面的原则基础上，再结合性能的影响因素，来最终确定使用哪种类型的分区。
4、有没有用过组合分区？怎么搭配的？
范围+列表
范围+散列
列表+散列
5、分区表创建的基本语法是什么？
Create table 表名
（字段名，字段类型……）
partition by range(字段名)
（partition 子分区名 values less than (某个日期) ）
6、分区还有哪些不同的操作？
添加分区，删除分区，截断分区，合并分区，拆分分区，重命名分区
7、怎么去查看表格分区的信息？
select * from USER_TAB_PARTITIONS a where a.table_name = '表名';
8、对分区字段进行where筛选和直接读取分区内容，谁快一些？
select * from fq_emp_list where depnto=10;
select * from fq_emp_list partition(d10); -- 分区更快些
9、索引的概念？
索引相当于目录，是对某些特定列中的数据进行排序，生成索引表，该列作为WHERE条件时，扫描索引列，根据ROWID快速定位具体记录，提高查询效率。
10、索引有哪些不同的类型？
主键 唯一 普通 组合 函数 位图
分区索引分为本地和全局
11、不同的索引一般用在什么场景下？
唯一索引：当某列任意两行数据不相同，建立PRIMARY KEY主键和UNIQUE CONSTRAINT唯一约束时（索引自动建立）。
组合索引：当多列经常一起出现在WHERE条件中，创建索引。
位图索引：当一列有大量重复数据时建立。
函数索引：在WHERE 条件语句中包含函数或表达式时建立。
12、普通索引和位图索引有什么区别？
普通索引：列的内容没有什么特点，但是经常需要被进行查询创建：
位图索引：列中有非常多的重复的值时候创建。例如某列保存了 “性别”信息创建：
13、索引在什么情况下会失效？
1. 经常需要被修改的列---重建索引
2. 数据发生了隐性的转换---新建针对该列的函数索引
3. 公式放在了等号的左边---把公式放在右边
4. 查询的时候使用!= not 空值查询
7. 组合索引没有使用第一列的列查询
8. 查询的时候，使用了非函数索引的函数
14、索引的优缺点？
索引优点：创建唯一索引，可以保证数据库中每一行数据的唯一性；可以加快数据的检索速度；可以加速表和表之间的连接；
索引缺点：创建和维护索引要耗费大量时间；索引会占物理存储空间；对表中数据进行操作，索引也要动态维护；
15、哪些列该建立索引？哪些列不应建立索引？
建立索引：经常需要搜索、排序的列；关联字段；
不该建立索引：很少使用的列；只有很少数据值的列；定义为TEXT,IMAGE,BIT数据类型的列；
16、唯一索引和主键索引有什么区别
主键是不能自己独立创建，和主键约束一起创建的；
唯一可以和约束一起创建，也可以单独创建。
主键是保证字段不能空不重复，唯一只能保证不重复。
17、怎么查看一个表格有哪些不同的索引？
select * from user_indexes where table_name='表名';
18、索引中本地索引和全局索引的区别是什么？
本地索引不能创建唯一索引的类型，因为每个小的索引只能保证每个分区的数据是唯一的，不能保证整个表格的数据是唯一的。
全局索引不能创建位图索引的类型，统一管理的索引，无法对整个表格中，每个独立的分区的数据进行识别和判断。
19、oracle里面，如果有一个sql语句运行很慢，怎么办？
1. 先查看执行计划，查看句子消耗的资源和内部运行的逻辑，查看语句是否运行复杂等，
查看每个子句运行的过程
2. 给数据量非常大的表格建分区（emp表为例，2个G是8000多万）
3. 给查询的字段进行索引的创建
4. 查看有没有导致索引失效的语句
5. Or会导致表格重复被读取，从而效率低下
6. sql语句频繁的对硬盘进行了读写，可以把语句写成代码块（例如存储过程）让硬盘的读写次数变少
7. sql语句可能会在客户端和服务器之间来回的进行数据的交互，这个时候也可以写成代码块减少客户端和服务器之间的交互次数
8. 还可以使用优化器里面的parallel进行sql语句的并行处理
20、oracle有哪些常见的数据类型？
integer number char varchar2 date blob clob
21、varchar 和 varchar2 的区别？
oracle里面，varchar默认就是varchar2格式
22、minus的含义是什么？
查询第一个表格有但是第二个表格没有的数据
23、在左连接里面，如果使用and筛选和用where筛选，区别是什么？
a b
1 1
2 2
3 4
select * from a left join b on a.id=b.id and a.id=2;
3 2 1
2 2
1 null
3 null
select * from a left join b on a.id=b.id where a.id=2;
2 2
24、左边表有身份证信息，右边也是身份证信息，查询左边有但是右边没有的身份证：
select * from a
minus
select * from b;
select * from a where id not in (select * from b);
select * from a left join b on a.id=b.id where b.id is null;
25、经常用到的函数有哪些？
聚合函数 max min avg sum count
单行函数
数字 abs round trunc floor ceil power mod
字符串 substr concat instr replace length wm_concat translate ltrim rtrim lpad rpad
upper lower initcap
分析函数
排名 row_number rank dense_rank
平移 lag lead
逻辑函数 nvl nvl2 decode case...when... pivot
正在上传…重新上传取消
将字符串数字转换成数值型数字：to_number()
将数字类型转换成字符串：to_char()
26、伪列有哪些有什么用？
rowid 利用唯一性进行表格数据的去重操作；索引也是使用rowid进行行数据查询的
rownum 进行表格数据的分页查询
27、表空间怎么创建、怎么查看、有什么作用？
create tablespace 名字 datafile '' size xxxM;
select * from user_tablespaces;
将表格或者分区和电脑上的数据文件进行绑定的一个数据库对象。
28、执行计划里面经常要查看哪些内容？
执行计划是一条查询语句在Oracle中的执行过程或访问路径。
执行完一条select语句后，在PLSQL执行计划窗口查看，或按F5即可查看刚执行的这条查询语句的执行计划。
或者在命令窗口输入：
SQL> explain for select * from emp
SQL> ;
Explained
SQL> select * from table(dbms_xplan.display());
主要看以下内容：
- 执行顺序：缩进最多的先执行；同一级的动作遵循最上最右先执行；
- 表扫描方式：全表扫描、索引扫描
- 关联机制：嵌套循环、哈希连接、排序-合并连接
- 耗费（COST）、CPU耗费：Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好
29、什么是视图？优点缺点是什么？
视图就是一张或多张表上的预定义查询。
作用：减少子查询的复杂性；提高运行效率；可以仅提供视图数据，提高数据安全性。视图以定义的方式存储在数据库中，不占用表空间。
30、什么是物化视图？和视图的区别是什么？
视图固定为表称为物化，物化视图是当时建立视图时固定下来的数据，如果原表数据有更新，物化视图中的数据是不变的。
物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。
普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。
31、存储过程由哪些代码构成？
create or replace procedure 名字(参数 类型)
as
声明
begin
执行
exception
异常
end;
32、什么时候写存储过程？
1.使用存储过程来对sql语句进行优化，减少硬盘的读写和与服务器之间的交互次数；
2.复杂的逻辑使用存储过程；
3.一些数据库的固定操作；
4.表格的数据抽取的ETL操作。
33、存储过程和函数的区别？
1.返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
2.调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用.
函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
34、数据仓库是用什么方法来保存历史数据的
数仓仓库保存历史数据通常是在dw层或者是hdw层 创建拉链表来保存历史数据
每次更新数据都会通过update进行逻辑删除，然后将最新的数据插入到拉链表中，并将历史数据更新成无效状态，将最新的数据更新为有效状态
35、拉链表是什么结构？什么是缓慢变化维？
拉链表：是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。
维度建模的数据仓库中，有一个概念叫Slowly Changing Dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流失发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题称为处理缓慢变化维的问题，有时也简称为处理SCD的问题。
处理缓慢变化维的方法通常分为三种方式：
第一种方式是直接覆盖原值。这样处理，最容易实现，但是没有保留历史数据，无法分析历史变化信息。第一种方式通常简称为“TYPE 1”。
第二种方式是添加维度行。这样处理，需要代理键的支持。实现方式是当有维度属性发生变化时，生成一条新的维度记录，主键是新分配的代理键，通过自然键可以和原维度记录保持关联。第二种方式通常简称为“TYPE 2”。
第三种方式是添加属性列。这种处理的实现方式是对于需要分析历史信息的属性添加一列，来记录该属性变化前的值，而本属性字段使用TYPE 1来直接覆盖。这种方式的优点是可以同时分析当前及前一次变化的属性值，缺点是只保留了最后一次变化信息。第三种方式通常简称为“TYPE 3”。
36、Oracle数据库的端口号是多少
默认是1521
37、Oracle 数据库日常维护？
检查Oracle实例状态
检查Oracle服务进程
检查Oracle监听进程；
select+instance_name,host_name,startup_time,status,database_status from v$instance;
status是oracle实例状态必须为open。database_status是数据库状态，必须为active。
38、什么是伪列，有哪几种，区别在那里？
ROWID:表中的每一行在数据文件中都有一个唯一物理地址，ROWID伪列返回的就是该行的物理地址,使用ROWID可以快速的定位表中的某一行,所以可以用来查重。
ROWNUM，为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推，通过ROWNUM伪列可以限制查询返回的行数。
39、如何使用ROWID去重？
DELETE FROM 表名 别名WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM 表名 别名 GROUP BY 列名)
如果想通过ROWID去重，那么在 delete 重复数据时，需要group by 表的所有字段。如果只group by 表的个别字段，那么会造成误删除。
40、如何分页查询？
可以用伪列rownum进行分页查询
--举例：第一页显示前5行
select ROWNUM,e.* from emp e
where rownum<=5--查询前5行，不能大于！
---第二页显示6-10
select * from
(
select ROWNUM rn,e.* from emp e
) t where t.rn>5 and t.rn<11
--第三页显示11-14
select * from
(
select ROWNUM rn,e.* from emp e
) t where t.rn>10 and t.rn<15
41、SQL执行顺序？
（1）from来自不同数据源的数据；
（2）where基于指定的条件对记录行进行筛选；
（3）group by子句将数据划分为多个分组；
（4）使用聚合函数进行计算；
（5）使用having子句筛选分组；
（6）计算所有的表达式；
（7）select 的字段；
（8）使用order by对结果集进行排序。
42、如何对重复数据去重？
distinct、exists、group by 、rowid，其中distinct性能最差
43、常用的SQL函数有哪些？
字符串函数、日期函数、数值函数、NVL、decode、case when等。
44、常见的分析函数（开窗函数）有哪些？
聚合类分析函数：MAX(),MIN(),SUM(),AVG(),COUNT()
排名类的分析函数：RANK()1224,DENSE_RANK()1223与ROW_NUMBER()1234：--分析函数中排序
位移分析函数：LAG()与LEAD()：求之前或之后的第N行
45、如何列转行、行转列？
列转行：用分析函数lead/lag；用DECODE；用CASE WHEN;部分关联；
行转列：pivot(聚合函数(有数据的列) for 想转置的列名 in (该列名中的数据));
