Re: How to get a Hash Anti-Join to take?

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<_TLta.12006$pK2.15734_at_news.indigo.ie>...
> Snippety Snip
>
> It only works when there's NOT NULL constraints... not a condition...
>
> i.e. where <X> is not null in the query will not get the HJ to work. it
> must be a constraint.
>
> It's one of the <few> constraint-based optimizations. It used to be
> documented in 7.3 manuals but it's now left out for some reason.
>
> Otherwise you tend to get a nasty FILTER operation

hash_aj quite possibly was constraint based in 7.3. But try this little exercise in 8i,

SQL> create table object as select * from dba_objects;

Table created.

SQL> desc object

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

• all columns are nullable ****** SQL> set autotrace on explain SQL> select count(*) from object where object_name 2 not in (select /*+ hash_aj */ object_name from object);

  COUNT(*)

         0

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=66)    1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'OBJECT' (Cost=2 Card=270 Bytes=17820)
   4    2       TABLE ACCESS (FULL) OF 'OBJECT' (Cost=2 Card=270 Bytes=17820)

SQL> select count(*) from object where object_name   2 not in (select /*+ hash_aj */ object_name from object   3 where object_name is not null)
  4 and object_name is not null;

  COUNT(*)

         0

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=132)    1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (ANTI) (Cost=5 Card=267 Bytes=35284)
   3    2       TABLE ACCESS (FULL) OF 'OBJECT' (Cost=2 Card=270 Bytes=17820)
   4    2       VIEW OF 'VW_NSO_1' (Cost=2 Card=270 Bytes=17820)
   5    4         TABLE ACCESS (FULL) OF 'OBJECT' (Cost=2 Card=270 Bytes=17820)