drop table t2 purgeTable dropped.
drop table t1 purgeTable dropped.
create table t2
as
select rownum id, object_id, object_name, object_type, rpad('x',100) padding
from all_objects
where rownum <= 5000 --> comment to avoid wordpress format issue
and mod(object_id,2) = 1 --> odd numbers only Table created.
create table t1
as
select rownum id, object_id, object_name, object_type, rpad('x',100) padding
from all_objects
where rownum <= 5000 --> comment to avoid wordpress format issue
and mod(object_id,2) = 0 --> even numbers only Table created.
explain plan for
select max(object_name) from t1 X join t2 X using (object_id)ORA-00918: column ambiguously definedMore Details: https://docs.oracle.com/error-help/db/ora-00918
explain plan for
select max(object_id) from t1 X join t2 X using (object_id)Statement processed.
select * from table(dbms_xplan.display)| PLAN_TABLE_OUTPUT | Plan hash value: 4259280259 | ------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 1 | 5 | 184K (1)| 00:00:08 | | | 1 | SORT AGGREGATE | | 1 | 5 | | | | | 2 | MERGE JOIN CARTESIAN| | 25M| 119M| 184K (1)| 00:00:08 | | | 3 | TABLE ACCESS FULL | T2 | 5000 | 25000 | 39 (0)| 00:00:01 | | | 4 | BUFFER SORT | | 5000 | | 184K (1)| 00:00:08 | | | 5 | TABLE ACCESS FULL | T1 | 5000 | | 37 (0)| 00:00:01 | | ------------------------------------------------------------------------------ |
|---|
select count(*) from t1 X join t2 X using (object_id)| COUNT(*) | 25000000 |
|---|