Re: Short-Circuiting a MINUS operation
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: xt.and.r@xxxxxxxxx
- Date: Tue, 8 Apr 2014 11:48:21 +0200
Sayan
Yes it works. Spot the *Starts *information of operation 4
SQL> create table t2 as select rownum n1 from dual connect by level<= 1e5;
Table created.
SQL> create table t2_bis as select * from t2 where 1 = 2;
Table created.
SQL> select /*+ opt_param('_convert_set_to_join','true') */
2 * from t2_bis minus select * from t2;
no rows selected
SQL_ID 50jpwpjdn6d3g, child number 1
-------------------------------------
select /*+ opt_param('_convert_set_to_join','true') */ * from t2_bis
minus select * from t2
Plan hash value: 1687601436
------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:00.01 |
| 1 | HASH UNIQUE | | 1 | 1 | 0
|00:00:00.01 |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 0
|00:00:00.01 |
| 3 | TABLE ACCESS FULL| T2_BIS | 1 | 1 | 0
|00:00:00.01 |
| 4 | TABLE ACCESS FULL| T2 | 0 | 100K| 0
|00:00:00.01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access(SYS_OP_MAP_NONNULL("T2_BIS"."N1")=SYS_OP_MAP_NONNULL("T2"."N1"))
And spot the use of the undocumented function *SYS_OP_MAP_NONNULL* in the
predicate part. Which obviously disappear
when the n1 column is declared not null;
SQL> alter table t2_bis modify n1 not null;
Table altered.
SQL> select /*+ opt_param('_convert_set_to_join','true') */
2 * from t2_bis minus select * from t2;
no rows selected
SQL_ID 50jpwpjdn6d3g, child number 1
-------------------------------------
select /*+ opt_param('_convert_set_to_join','true') */ * from t2_bis
minus select * from t2
Plan hash value: 1687601436
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:00.01 |
| 1 | HASH UNIQUE | | 1 | 1 | 0
|00:00:00.01 |
|* 2 | HASH JOIN ANTI | | 1 | 1 | 0
|00:00:00.01 |
| 3 | TABLE ACCESS FULL| T2_BIS | 1 | 1 | 0
|00:00:00.01 |
| 4 | TABLE ACCESS FULL| T2 | 0 | 100K| 0
|00:00:00.01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2_BIS"."N1"="T2"."N1")
Best regards
Mohamed Houri
www.hourim.wordpress.com
2014-04-08 11:33 GMT+02:00 Sayan Malakshinov <xt.and.r@xxxxxxxxx>:
> I think would be helpful hint: opt_param('_convert_set_to_join','true')
>
> Best regards,
> Sayan Malakshinov
> Senior Oracle performance tuning engineer
> PSBANK
> http://orasql.org
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
