Oracle数据库面试题
1.取出表中第31到40行的记录
mysql方案:select * from t order by id limit 30,10
oracle方案:
select t2.*
from (select rownum r,t1.* from yourtable t1 where rownum<=40) t2
where t2.r>30
2.truncate和delete有什么区别
TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同,二者均删除表中的全部行。但TRUNCATE
TABLE比DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每一行记录一项。
TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE, DELETE, DROP放在一起比较:
TRUNCATE TABLE:删除内容、释放空间但不删除定义。
DELETE TABLE:删除内容不删除定义,不释放空间。
DROP TABLE:删除内容和定义,释放空间。
3. oracle的rowid和rownum有什么区别
rowid是唯一标识一条记录物理位置的一个id。
rownum是oracle系统为查询返回的行顺序分配的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,且rownum不能以任何表的名称作为前缀。
4.Oracle函数(Function)与存储过程(Procedure)的区别
function
的返回值永远只有一个,而procedure的返回值却可以不只一个。function所有的参数都是input信号,而procedure的参数却可以
是input、output,甚至inout。function的返回值置于参数声明之外,而procedure的返回值是放在参数声明之内的。
5.删除一张表重复记录(ID 是自增唯一,重复记录:其他字段都是一样)
delete from aa where id not in (select min(id) from aa group by name,age);
6.一个表中的 id 有多个记录,把所有这个 id 的记录查出来,并显示共有多少条记录数。
select id, count(*) from tb group by id having count(*)>1
7.SQL优化
(1)尽量少用 IN 操作符
基本上所有的 IN 操作符都可以用 EXISTS 代替,在选择 IN 或 EXIST 操作时,要根据主子表数据量大小来具体考虑。
(2)尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。比如:a<>0改为a>0 or a<0
(3)尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当 通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用。比如用 T 表中 Column1 LIKE ‟%5400%‟
这个条件会产生全表扫描,如果改成 Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用 Column1
的索引进行两个范围的查询,性能肯定大大提 高。
(4)Where子句中避免在索引列上使用计算
substr(no,1,4)= ‘5400’,优化处理:no like ‘5400%’
trunc(hiredate)=trunc(sysdate),优化处理:hiredate >=trunc(sysdate) and hiredate <trunc(sysdate+1)
(5)用“>=”替代“>”
大 于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有 100
万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行 A>2 与
A>=3 的效果就有很大的区别了,因为 A>2 时 ORACLE 会先找出为 2 的记录索引再进行比较,而 A>=3 时
ORACLE 则直接找到=3的记录索引
(6)使用表的别名,并将之作为每列的前缀
当在sql语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间。
(7)用UNION ALL代替UNION
UNION 是最常用的集操作,使多个记录集联结成为单个集,对返回的数据行有唯一性要求,所以oracle就需要进行SORT
UNIQUE操作(与使用distinct时操作类似),如果结果集又比较大,则操作会比较慢;UNION
ALL操作不排除重复记录行,所以会快很多,如果数据本身重复行存在可能性较小时,用union all会比用union效率高很多!
8.事务的acid特性
原子性、一致性、独立性、持久性