原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2020/03/18/using-bug/
USING bug
— Jonathan Lewis @ 11:10 am GMT Mar 18,2020
在Oracle开发人员社区论坛上经常看到难以阅读的SQL–有时是因为它是一组混乱的子查询，有时是因为格式不好，有时是因为表和列别名的使用做得不好。最后一个要尤其指出的问题是，在查询过程中多次使用相同的表别名（通常是字母A）。
我已经说过，查询中的每个表都应该有一个不同的别名，并且在查询中使用的每个列也都应该用这个别名（这个URL中的笔记包含了一些改进）。我刚刚发现了另一个原因，为什么这么做是对的，为什么你不应该在一个查询里使用两次相同的别名。下面是在19.3.0.0上测试的一个简化的危害演示:
rem Script: using_bug.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2020
rem Purpose:
rem
rem Last tested
rem 19.3.0.0
rem
create table t2
as
select rownum id, object_id, object_name, object_type, rpad('x',100) padding
from all_objects
where rownum <= 5000
and mod(object_id,2) = 1
/
create table t1
as
select rownum id, object_id, object_name, object_type, rpad('x',100) padding
from all_objects
where rownum <= 5000
and mod(object_id,2) = 0
/
我以视图all_objects创建了两个表，其中一个表持有object_id为偶数的行，另一个表持有奇数的行，所以如果我以object_id列将这两个表连接，结果集将是空的。这里有三个连接两个表的查询–我（意外地）在三种情况下都给了两个表相同的别名X:
prompt =======================================
prompt Here's a query that might "confuse" the
prompt optimizer when we try to explain it
prompt =======================================
explain plan for
select max(object_name) from t1 X join t2 X using (object_id);
prompt ==================================
prompt So might this one, but it doesn't.
prompt ==================================
explain plan for
select max(object_id) from t1 X join t2 X using (object_id);
select * from table(dbms_xplan.display);
prompt ===================================================
prompt With this one A-rows matches E-rows: and it's NOT 0
prompt ===================================================
alter session set statistics_level = all;
set serveroutput off
set linesize 156
select count(*) from t1 X join t2 X using (object_id);
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));
在没有明确别名的情况下，第一个查询应该产生一个执行计划；但是，当两个表被赋予相同的别名时，试图explain（或运行）查询会产生错误“ORA-00918: column ambiguously defined”。
第二个查询做得更好，或者更差，这取决于您的观点。名义上，连接是完全有效的，优化器为查询生成一个执行计划。但是该计划预测了一个笛卡尔合并连接，其结果集为2500万行–这看起来不像是一个好的估计–并且该计划没有谓词信息部分。
因此，我们对第三个查询使用count(*)–以防结果集是25万行–并启用rowsource execution statistics，并在运行查询后从内存中获取计划（这将花费将近14秒的时间，CPU会崩溃）。这是输出:
COUNT(*)
----------
25000000
1 row selected.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 85ygrcg4n3ymz, child number 0
-------------------------------------
select count(*) from t1 X join t2 X using (object_id)
Plan hash value: 4259280259
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 67358 (100)| 1 |00:00:13.38 | 200 | 198 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:13.38 | 200 | 198 | | | |
| 2 | MERGE JOIN CARTESIAN| | 1 | 25M| 67358 (5)| 25M|00:00:10.55 | 200 | 198 | | | |
| 3 | TABLE ACCESS FULL | T2 | 1 | 5000 | 15 (7)| 5000 |00:00:00.01 | 100 | 99 | | | |
| 4 | BUFFER SORT | | 5000 | 5000 | 67343 (5)| 25M|00:00:04.54 | 100 | 99 | 133K| 133K| 118K (0)|
| 5 | TABLE ACCESS FULL | T1 | 1 | 5000 | 13 (0)| 5000 |00:00:00.01 | 100 | 99 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
是的，Oracle成功地生成了2500万行，而不是0行。执行计划是一个笛卡尔合并连接，这是因为优化器丢失了连接谓词（我没有提供谓词信息部分，因为没有谓词信息部分–请注意计划中没有任何操作的星号）。
有趣的是，计划中的查询块/别名部分（当我调用它时）表明的两个别名是X_0001和X_0002，因此Oracle在内部确实设法找到了两个不同的别名–但可能为时已晚。
结论
考虑一下如何合理、安全地使用表别名。修复这个示例很简单，但是一些进入生产环境的混乱SQL可能最终会遇到相同的问题，而不容易发现异常的来源。
脚注
这是Bug 25342699 : WRONG RESULTS WITH ANSI JOIN USING AND IDENTICAL TABLE ALIASES 发布在2017年1月12.1.0.2版本上，还没有修复。
