ORA-00933 错误与 Union all

ORA-00933 错误与 Union all (2010-08-12 15:55:59)

转载

ORA-00933 错误与 Union all

2009-05-12 14:18

union all 连接的两个结果集，只能最后一个结果集中允许出现order by子句，但是该order by子句对整个结果集都产生影响。 步骤一：创建测试表a,b SQL> set autot off SQL> create table a as select rownum as id, rownum as name from dual connect by level < 10; Table created. SQL> create table b as select rownum+5 as id, rownum+5 as name from dual connect by level < 10; Table created. SQL> select * from a; ID NAME ---------- ---------- 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 9 rows selected. SQL> select * from b; ID NAME ---------- ---------- 6 6 7 7 8 8 9 9 10 10 11 11 12 12 13 13 14 14 9 rows selected. 步骤二：测试查询语句 SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME 2 FROM a 3 LEFT JOIN b ON a.id = b.id 4 5 UNION ALL 6 SELECT a.id, a.NAME, b.id, b.NAME 7 FROM b 8 LEFT JOIN a ON a.id = b.id 9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) 10 ORDER BY aid, bid 11 ; AID NAME BID NAME ---------- ---------- ---------- ---------- 1 1 2 2 3 3 4 4 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 10 10 11 11 AID NAME BID NAME ---------- ---------- ---------- ---------- 12 12 13 13 14 14 14 rows selected. SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME 2 FROM a 3 LEFT JOIN b ON a.id = b.id 4 ORDER BY aid 5 UNION ALL 6 SELECT a.id, a.NAME, b.id, b.NAME 7 FROM b 8 LEFT JOIN a ON a.id = b.id 9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) 10 ORDER BY aid, bid; UNION ALL * ERROR at line 5: ORA-00933: SQL command not properly ended 步骤三：检查执行计划，发现先UNION-ALL 再进行排序操作SQL> set autot on explain; SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME 2 FROM a 3 LEFT JOIN b ON a.id = b.id 4 5 UNION ALL 6 SELECT a.id, a.NAME, b.id, b.NAME 7 FROM b 8 LEFT JOIN a ON a.id = b.id 9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) 10 ORDER BY aid, bid 11 / AID NAME BID NAME ---------- ---------- ---------- ---------- 1 1 2 2 3 3 4 4 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 10 10 11 11 AID NAME BID NAME ---------- ---------- ---------- ---------- 12 12 13 13 14 14 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3394439325 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 533 | 18 (17)| 00:00:01 | | 1 | SORT ORDER BY | | 10 | 533 | 17 (65)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 9 | 468 | 7 (15)| 00:00:01 | | 4 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | B | 9 | 234 | 3 (0)| 00:00:01 | |* 6 | HASH JOIN OUTER | | 1 | 65 | 10 (10)| 00:00:01 | |* 7 | HASH JOIN ANTI | | 1 | 39 | 7 (15)| 00:00:01 | | 8 | TABLE ACCESS FULL| B | 9 | 234 | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS FULL| A | 9 | 117 | 3 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."ID"="B"."ID"(+)) 6 - access("A"."ID"(+)="B"."ID") 7 - access("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME 2 FROM a 3 LEFT JOIN b ON a.id = b.id 4 5 UNION ALL 6 (SELECT a.id, a.NAME, b.id, b.NAME 7 FROM b 8 LEFT JOIN a ON a.id = b.id 9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) 10 ORDER BY aid, bid 11 ) 12 / ORDER BY aid, bid * ERROR at line 10: ORA-00907: missing right parenthesis 尝试把后面的查询语句括起来，再排序，执行计划中仍然是先Union-all再排序 SQL> SELECT a.id aid, a.NAME, b.id bid, b.NAME 2 FROM a 3 LEFT JOIN b ON a.id = b.id 4 5 UNION ALL 6 (SELECT a.id, a.NAME, b.id, b.NAME 7 FROM b 8 LEFT JOIN a ON a.id = b.id 9 WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.id = b.id) ) 10 ORDER BY aid, bid; AID NAME BID NAME ---------- ---------- ---------- ---------- 1 1 2 2 3 3 4 4 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 10 10 11 11 AID NAME BID NAME ---------- ---------- ---------- ---------- 12 12 13 13 14 14 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3394439325 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10 | 533 | 18 (17)| 00:00:01 | | 1 | SORT ORDER BY | | 10 | 533 | 17 (65)| 00:00:01 | | 2 | UNION-ALL | | | | | | |* 3 | HASH JOIN OUTER | | 9 | 468 | 7 (15)| 00:00:01 | | 4 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | B | 9 | 234 | 3 (0)| 00:00:01 | |* 6 | HASH JOIN OUTER | | 1 | 65 | 10 (10)| 00:00:01 | |* 7 | HASH JOIN ANTI | | 1 | 39 | 7 (15)| 00:00:01 | | 8 | TABLE ACCESS FULL| B | 9 | 234 | 3 (0)| 00:00:01 | | 9 | TABLE ACCESS FULL| A | 9 | 117 | 3 (0)| 00:00:01 | | 10 | TABLE ACCESS FULL | A | 9 | 234 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."ID"="B"."ID"(+)) 6 - access("A"."ID"(+)="B"."ID") 7 - access("A"."ID"="B"."ID") Note ----- - dynamic sampling used for this statement

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1724534/viewspace-1057401/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1724534/viewspace-1057401/