SQL> set autotrace on SQL> select * from aa union all select * from bb; MAF A ---------- -- 1 A1 2 A2 3 A3 1 B1 2 B2 4 B4已选择6行。 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 UNION-ALL 2 1 TABLE ACCESS (FULL) OF 'AA' 3 1 TABLE ACCESS (FULL) OF 'BB'
explain plan显示的结果是先执行表2,再执行表1 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE--优化器默认为CHOOSE 1 0 UNION-ALL--UNION结果集 2 1 TABLE ACCESS (FULL) OF 'AA'--对AA做全表扫描,执行表1 3 1 TABLE ACCESS (FULL) OF 'BB'--对BB做全表扫描,执行表2
up,studying... 请问Execution Plan如何用??请指教!!
union 与or是看数据量执行哪个快,根据实际情况,不能一概而论.
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 UNION-ALL 2 1 TABLE ACCESS (FULL) OF 'AA' 3 1 TABLE ACCESS (FULL) OF 'BB'谁来具体解释一下该信息?
抱歉,前面说错了 事实上应该正好反过来 先执行表1,再执行表2 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE--优化器默认为CHOOSE 1 0 UNION-ALL--UNION结果集 2 1 TABLE ACCESS (FULL) OF 'AA'--对AA做全表扫描,执行表1 3 1 TABLE ACCESS (FULL) OF 'BB'--对BB做全表扫描,执行表2正确的步骤是:2->3->1->0 下午用pl/sql developer中试过explain plan
CREATE OR REPLACE FORCE VIEW LANGFANG.KC_V_PRJDOCMANAGE (NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC) AS SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE; UNION SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE; UNION SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE; 我这段sql在oracle里,只能得到表一的是数据是为什么呀? 得不到后面两个表的数据,
CREATE OR REPLACE FORCE VIEW LANGFANG.KC_V_PRJDOCMANAGE (NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC) AS SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE UNION SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE UNION SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE; 我这段sql在oracle里,不能实现是因为GRAPHIC是long raw类型, 不知道为什么这个类型在这里不可以用来创建视图,哪位大哥能给点意见
CREATE OR REPLACE FORCE VIEW LANGFANG.KC_V_PRJDOCMANAGE (NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC) AS SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE; UNION SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE; UNION SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME, TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE; 我这段sql在oracle里,只能得到表一的是数据是为什么呀? 得不到后面两个表的数据, ============================================================================== 可以试试把第一个和第二个Select语句后面的 ;去掉, 把union改成union All
SQL> select * from aa union all select * from bb; MAF A
---------- --
1 A1
2 A2
3 A3
1 B1
2 B2
4 B4已选择6行。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 UNION-ALL
2 1 TABLE ACCESS (FULL) OF 'AA'
3 1 TABLE ACCESS (FULL) OF 'BB'
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE--优化器默认为CHOOSE
1 0 UNION-ALL--UNION结果集
2 1 TABLE ACCESS (FULL) OF 'AA'--对AA做全表扫描,执行表1
3 1 TABLE ACCESS (FULL) OF 'BB'--对BB做全表扫描,执行表2
请问Execution Plan如何用??请指教!!
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 UNION-ALL
2 1 TABLE ACCESS (FULL) OF 'AA'
3 1 TABLE ACCESS (FULL) OF 'BB'谁来具体解释一下该信息?
事实上应该正好反过来
先执行表1,再执行表2
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE--优化器默认为CHOOSE
1 0 UNION-ALL--UNION结果集
2 1 TABLE ACCESS (FULL) OF 'AA'--对AA做全表扫描,执行表1
3 1 TABLE ACCESS (FULL) OF 'BB'--对BB做全表扫描,执行表2正确的步骤是:2->3->1->0
下午用pl/sql developer中试过explain plan
(NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC)
AS
SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE;
UNION
SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE;
UNION
SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE;
我这段sql在oracle里,只能得到表一的是数据是为什么呀?
得不到后面两个表的数据,
(NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC)
AS
SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE
UNION
SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE
UNION
SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE;
我这段sql在oracle里,不能实现是因为GRAPHIC是long raw类型,
不知道为什么这个类型在这里不可以用来创建视图,哪位大哥能给点意见
(NO, HS_NAME, DRAWINGNAME, TYPE, GRAPHIC)
AS
SELECT PROJECTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM KC_PROJECTDRAWMANAGE;
UNION
SELECT EQUIPMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_EQUIPMENTFILEMANAGE;
UNION
SELECT DOCUMENTNO as no, NO as hs_name, DRAWINGNAME,
TYPE, GRAPHIC FROM LANGFANG.KC_RUNRECORDMANAGE;
我这段sql在oracle里,只能得到表一的是数据是为什么呀?
得不到后面两个表的数据,
==============================================================================
可以试试把第一个和第二个Select语句后面的 ;去掉, 把union改成union All