Re: Query with NULL

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Tue, 29 Dec 2009 16:33:35 -0800 (PST)
Message-ID: <69312754-d008-41b7-933d-4bcac5349633_at_j19g2000yqk.googlegroups.com>

Let's look at simplified query:

select * from bxat.no5
WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)

We assume that there is an index on "no0_session_id" and this index has good selectivity.

If :wp18 has a value then we want the query to use index. If :wp18 is NULL then all rows in the table
satisfy WHERE clause so we want the query to use full table scan.

Let's simplify the query even further and look at the plans:

SQL> select * from bxat.no5 where no0_session_id = :wp18;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1 Bytes=13)

   2 1 INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3 Card=1)

SQL> select * from bxat.no5 where :wp18 is null;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=42845620 Bytes=85691240)

   1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=42845620 Bytes=85691240)

Note FILTER above. You get the same plan if you run query with condition "1=0". While plan contains full table scan Oracle doesn't actually run it if condition is false - it is stopped by FILTER.

Now the original simplified query:

SQL> select * from bxat.no5 where no0_session_id = :wp18 or :wp18 is null;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84653 Card=2142282 Bytes=27849666)

   1 0 TABLE ACCESS (FULL) OF 'NO5' (Cost=84653 Card=2142282 Bytes=27849666)

Here "FILTER" disappeared, so Oracle always runs the full scan.

Finally the version with UNION:

SQL> select * from bxat.no5 where no0_session_id = :wp18   2 union
  3 select * from bxat.no5 where :wp18 is null;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=156184 Card=42845621 Bytes=85691253)

   1 0 SORT (UNIQUE) (Cost=156184 Card=42845621 Bytes=85691253)

   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'NO5' (Cost=1 Card=1
Bytes=13)
   4    3         INDEX (UNIQUE SCAN) OF 'NO5_IDX01' (UNIQUE) (Cost=3
Card=1)
   5    2       FILTER
   6    5         TABLE ACCESS (FULL) OF 'NO5' (Cost=84653

Card=42845620 Bytes=85691240)

So the trick is to keep the FILTER. Received on Tue Dec 29 2009 - 18:33:35 CST