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
相关文档：
PairWise subquery:
e.g.:
select * from wf_docsort where (ndocsortid,nmoduleinfoid) in (select ndocsortid, nmoduleinfoid from wf_docsort where instr(cname,'文')>0)
the above sql is the same function as:
select * from wf_docsort where ndocsortid = (select ndocsortid from wf_docsort where ......
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).. ......
1. My test: (create and grant the sysdba to a new user by SQL*Plus)
CREATE USER FJTEST1 IDENTIFIED BY JEANJEANFANG;
GRANT SYSDBA TO FJTEST;
REVOKE SYSDBA from FJTEST;
CONNECT FJTEST1/JEANJEANFANG AS SYSDBA;
2. Using ORAPWD in windows:
C:\» ORAPWD;
(show help information)
3. to see th ......
WAITFOR
指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。
语法
WAITFOR { DELAY 'time' | TIME
'time' }
参数
DELAY
指示 Microsoft® SQL Server™ 一直等到指定的时间过去，最长可达 24 小时。
'time'
要等待的时间。可以按 datetime 数据可接受的格式指定
time，也可以用局部变量指� ......
in 的话， 如果是null 就不比较了，既不是in 也不是 not in
exists的话 因为用 = 加在条件里比较了，所以 null 是 not exists
select *
from pricetemp
where cast(商品コード as varchar(10))not in(
select shohin_cd
&nbs ......
