Existence

Here’s a posting on OTN that demonstrates a piece of SQL that uses inline scalar subqueries which are all “existence” tests to produce (presumably) a set of flags describing the state of a particular item of data.

I’ve linked to it because I contributed a comment about the implications of the cost figures that appeared in the execution plan for two of the “exists” subqueries. Essentially “existence” is optimized as a “first_rows(1)” operation – which results in two lines of the plan showing two different costs for table scans of the same table.

Update 30th Dec:
If you follow the OTN note you’ll see that the original poster was confused by my comments about the relative costs of the two tablescans, so I’ve whipped up a quick script to clarify the point. It uses my typical “reproducible” setup of 1MB uniform extents, 8KB block size, freelist management and disabling system statistics. Here’s the starting data set:

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	rownum			id,
	mod(rownum,1000)	n_1000,
	mod(rownum,5000)	n_5000,
	mod(rownum,10000)	n_10000,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 100000
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt 	 => 'for all columns size 1'

	);
end;
/

Notice that any given value of n_1000 appears every 1,000 rows, any given value of n_5000 appears every 5,000 rows, and any given value for n_10000 appears every 10,000 rows. Think about the effect this has on Oracle’s prediction of how much work it has to do when asked to find a particular row under first_rows(1) optimisation (which is the optimisation strategy triggered by the “rownum = 1″ predicates below):

set autotrace traceonly explain

select count(*) from t1;
select id from t1 where	n_1000  =  500 and rownum = 1;
select id from t1 where	n_5000  = 2500 and rownum = 1;
select id from t1 where	n_10000 = 5000 and rownum = 1;

set autotrace off

The more rows you have to scan (on average) to find a given value, the more costly the tablescan becomes. (The initial select count(*) is there to demonstrate Oracle’s estimate of the cost of scanning the whole table).

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost  |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   283 |
|   1 |  SORT AGGREGATE    |      |     1 |       |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   283 |
---------------------------------------------------

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     5 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |     5 |
-----------------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - filter("N_1000"=500)

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    16 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |    16 |
-----------------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - filter("N_5000"=2500)

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |    30 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |    30 |
-----------------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - filter("N_10000"=5000)

And now, check the costs of the tablescans in the existence subqueries for the following:

select
	(select 1 from dual where exists (select null from t1 where n_1000  =  500)) n_1000,
	(select 1 from dual where exists (select null from t1 where n_5000  = 2500)) n_5000,
	(select 1 from dual where exists (select null from t1 where n_10000 = 5000)) n_10000
from
	dual
;

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |       |     2 |
|*  1 |  FILTER            |      |       |       |       |
|   2 |   FAST DUAL        |      |     1 |       |     2 |
|*  3 |   TABLE ACCESS FULL| T1   |     2 |     8 |     5 |
|*  4 |  FILTER            |      |       |       |       |
|   5 |   FAST DUAL        |      |     1 |       |     2 |
|*  6 |   TABLE ACCESS FULL| T1   |     2 |     8 |    16 |
|*  7 |  FILTER            |      |       |       |       |
|   8 |   FAST DUAL        |      |     1 |       |     2 |
|*  9 |   TABLE ACCESS FULL| T1   |     2 |     8 |    30 |
|  10 |  FAST DUAL         |      |     1 |       |     2 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_1000"=500))
   3 - filter("N_1000"=500)
   4 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_5000"=2500))
   6 - filter("N_5000"=2500)
   7 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_10000"=5000))
   9 - filter("N_10000"=5000)