第十一周：ORA 报错集锦（持续更新）

45

Oracle 常用表和查询

-- 查询当前用户
SELECT user FROM dual;
-- 查询所有用户
SELECT * FROM all_users;
==============================================
-- 包括系统表
SELECT * FROM dba_tables;
-- 所有用户的表
SELECT * FROM all_tables;
-- 查询指定用户表
SELECT * FROM all_tables WHERE owner = 'user_name';
-- 当前用户表
SELECT * FROM user_tables;
-- 查询表结构
SELECT * FROM all_tab_columns WHERE table_name = 'table_name';
SELECT * FROM user_tab_columns WHERE table_name = 'table_name';
SELECT owner, table_name, column_name, data_type, data_length FROM  all_tab_columns WHERE table_name = 'table_name';
--模板
select t1.owner,t1.table_name,t1.column_name,t2.comments,t1.data_type from
(select * from all_tab_columns where owner = 'ECIF' and table_name = 'table_name') t1
left join
(select * from all_col_comments where owner = 'ECIF' and table_name = 'table_name') t2
on t1.column_name = t2.column_name
order by t1.column_id;

SQL 优化

  1. SELECT 子句中避免使用 *。数据量大的时候选择所有列会导致查询速度变慢，另外在多表连接查询时也会造成更大的开销。

  2. WHERE 子句中比较符号左侧避免出现表达式和函数操作，会导致数据库引擎放弃使用索引进行全表扫描。

  3. WHERE 子句中避免使用 OR 操作符，会导致数据库引擎放弃使用索引进行全表扫描，可以考虑使用 UNION 代替。

  4. WHERE 子句中避免使用 != 和 <> 操作符，会导致数据库引擎放弃使用索引进行全表扫描。

  5. WHERE 子句中避免使用 IN 和 NOT IN 操作符，会导致数据库引擎放弃使用索引进行全表扫描，连续的值可以用 BETWEEN AND 代替，其他情况可以考虑使用 EXISTS 代替。

  6. WHERE 子句中避免使用 LIKE 操作符，会导致数据库引擎放弃使用索引进行全表扫描，可以考虑全文检索。

  7. WHERE 子句中避免使用参数，会导致数据库引擎放弃使用索引进行全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择，然而如果在编译时建立访问计划，变量的值还是未知的，无法作为索引选择的输入项。可以考虑强制使用索引查询。

  8. WHERE 子句中避免对字段进行 NULL 值判断，会导致数据库引擎放弃使用索引进行全表扫描，可以在 NULL 值的列上设置默认值。

  9. 尽量使用数字型字段，因为数据库引擎在处理查询和连接时会逐个比较字符串中每个字符，而对于数字型而言只需要比较一次就够了。

  10. 避免查询全表数据

    根据实际需求，限制数据返回的行数，尽量避免向客户端返回大量数据。

锁和阻塞

  1. 查询目前锁对象信息

    SELECT SID, SERIAL#, USERNAME, SCHEMANAME, OSUSER, MACHINE, TERMINAL, PROGRAM, OWNER, OBJECT_NAME, OBJECT_TYPE, O.OBJECT_ID FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S WHERE O.OBJECT_ID = L.OBJECT_ID AND S.SID = L.SESSION_ID;

  2. Oracle kill session

    alter system kill session '&sid,&serial#';

表/分区/索引

  1. 尽量避免全表扫描，首先应考虑在 WHERE 子句及 ORDER BY 涉及的列上建立索引。

  2. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须用到该索引中的第一个字段作为条件时才能保证系统使用该索引，并且应尽可能地让字段顺序与索引顺序一致。

存储过程

  1. 理解代码

    • 干什么的

    • 用到了什么

    • 哪里用到了

  2. 分析代码

    按照业务逻辑，事务控制等对代码进行分块。

    • 分拆

    • 聚合

  3. 改写代码

    分好块的代码再进行 SQL 优化。

  4. 管理代码

ORA-00979 not a GROUP BY expression

在带有 group by 子句的查询语句中，在 select 列表中指定的列，要么是在 group by 子句中指定的列，要么包含在聚合函数中，并且不能用别名。

很好理解，用分组查询查出来的列，要么是分组本身，要么是按分组进行聚合运算，不可能查一个其他的列。

不能用别名，就涉及到 Oracle SELECT 语句的执行顺序了

  1. FROM 子句组装来自不同数据源的数据；

  2. WHERE 子句基于指定的条件对记录行进行筛选；

  3. GROUP BY 子句将数据划分为多个分组；

  4. 使用聚合函数进行计算；

  5. 使用 HAVING 子句筛选分组；

  6. 计算所有的表达式；

  7. SELECT 的字段；

  8. 使用 ORDER BY 对结果集进行排序。

可以看到，GROUP BY 字句的执行顺序在 SELECT 之前，当然无法使用 SELECT 的别名了，同理，ORDER BY 可以使用别名。

比如，按部门查询 emp 表的职员信息

select deptno, empno from emp group by deptno;

报错，按部门查询了怎么还能查到每个 empno，正确写法为

select deptno, count(empno) from emp group by deptno;

ORA-01403 no data found

很常见，也很直白，未找到数据，定位到出错代码，给它一个判空就行了。

select empno into v_empno from emp where empno = i_empno; 
if v_empno is null then
  dbms_output.put_line('员工不存在!');
end if;

还是报未找到数据，因为 select 语句还是用到了 v_empno 这个变量，正确写法应该是这样

select count(empno) into v_count from emp where empno = i_empno; 
if v_count < 1 then
  dbms_output.put_line('员工不存在!');
else
  select empno into v_empno from emp where empno = i_empno;
end if;

其实所有涉及到查询的地方都应该考虑到对空值的判断。

This Week：3 hrs 26 mins

Languages：

Java：1 hrs 43 mins

XML：46 mins

YAML：40 mins

Protocol Buffer：10 mins

Gradle：4 mins

protobuf：2 min

Projects：

c5-live：3 hrs 26 mins