一个隐形的笛卡尔乘积的SQL语句

Kenniu

bell6248 发表于 2016-6-14 11:56
你把2中不同语句执行的方式的执行计划贴一下， 比较一下即可

XD，查询计划很漂亮呀....

Kenniu

zergduan 发表于 2016-6-14 11:56
你确定你在sqlplus中运行的时候使用的T0 T1过滤条件和存储过程中的过滤条件一样？（变量）

完全一样....

Kenniu

我不是讲一个问题，我是在讲应该避开的一个陷阱...有人会踩雷吗？

Yong Huang

If the actual SQL cannot be posted here, change or mask sensitive table or column names and values in the SQL and then post it.

The example SQL you posted here will clearly use a cartesian join:

Test in 11.2.0.3 (not sure about your version):

SQL> create table t_test (a int, b varchar2(10), c int);

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T_TEST')

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  Select * from T_TEST T0,T_TEST T1
  3  where  T0.b='' and T1.b='';

Explained.

SQL> @?/rdbms/admin/utlxpls

Plan Table
------------------------------------------------------------------------------------------
| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------
| SELECT STATEMENT          |                    |     1 |   66 |      0 |       |       |
|  FILTER                   |                    |       |      |        |       |       |
|   MERGE JOIN CARTESIAN    |                    |     1 |   66 |      4 |       |       |
|    TABLE ACCESS FULL      |T_TEST              |     1 |   33 |      2 |       |       |
|    BUFFER SORT            |                    |     1 |   33 |      2 |       |       |
|     TABLE ACCESS FULL     |T_TEST              |     1 |   33 |      2 |       |       |
------------------------------------------------------------------------------------------

If a SQL takes more than 5 seconds to run, you may find its actual runtime stats with

set pages 1000 linesize 1000 long 10000 longc 1000
select dbms_sqltune.report_sql_monitor('&sql_id') from dual;

Please post the output here.

Another possibility is that the database completes execution just as fast as in Sqlplus. But a lot of time is wasted somewhere else, including the app server.

260600sz

去掉符号*，避免笛卡尔积

Kenniu

260600sz 发表于 2016-9-4 11:20
去掉符号*，避免笛卡尔积

这个想法好...

Yong Huang

> 去掉符号*，避免笛卡尔积

Do you mean changing
select * from ...
to something like
select 1 from ...?

That won't make any difference in the execution plan in this case. Try my code in message #14. You'll still see "MERGE JOIN CARTESIAN" in the plan.