ORACLE中查看SQL执行计划的方法
1.首先需要建立plan table,否则不能使用
建立方法：
$oracle\rdbms\admin下有个
utlxplan.sql
其内容为:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(255),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30),
cpu_cost numeric,
io_cost numeric,
temp_space numeric,
access_predicates varchar2(4000),
filter_predicates varchar2(4000));
运行此SQL
SQL>$ORACLE/rdbms/admin/utlxplan.sql
2.使用explain plan for语句进行执行计划分析
SQL> explain plan for
2 select * from dual;
已解释。
出现上述结果，表明分析完毕，可以查看
3.可以查看执行计划了
select * from table(DBMS_XPLAN.display);
--------------------------------------------------------------------
| Id | Operation | Name
相关文档：
Confirming granted privileges
Data Dictionary View Description
ROLE_SYS_PRIVS System privileges granted to roles
ROLE_TAB_PRIVS & ......
Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE, INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation. ......
Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in dat ......
Group functions
SELECT [column,] group_function(column) ... from table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
e.g.:
SELECT department_id, job_id, SUM(salary), COUNT(employee_id) from employees GROUP BY department_id, job_id ;
SELECT [column,] group_function(column).. ......
in 的话， 如果是null 就不比较了，既不是in 也不是 not in
exists的话 因为用 = 加在条件里比较了，所以 null 是 not exists
select *
from pricetemp
where cast(商品コード as varchar(10))not in(
select shohin_cd
&nbs ......
