Re: Short-Circuiting a MINUS operation

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Tue, 8 Apr 2014 11:48:21 +0200
Message-ID: <CAJu8R6g3LwSfcOk61zaHrCCOcEyV8J8N7Y-Q64pFqPwE4wD5Ag_at_mail.gmail.com>

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_at_gmail.com>:

> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 08 2014 - 11:48:21 CEST