Hi Tom,
Recently we had a friendly discussion at work about the following scenario regarding performance issues. Just wonder how it affects.
select
A.column_a,
(
Select count(*) from table_b where flag = 'Y'
) QTY
from table_a A
where
(
Select count(*) from table_b where flag = 'Y'
) > 0
This first scenario I have two counting subqueries in select and where clause.
select * from (
select
A.column_a,
(
Select count(*) from table_b where flag = 'Y'
) QTY
from table_a A
)
where QTY > 0
The second query only has one counting subquery inside another subquery.
Wonder how Oracle handle it and what is the best practice to write this type of queries.
Thank you for you explanation.
JL.
Well...lets do some tests
SQL> create table table_a as select * from dba_Objects;
Table created.
SQL> create table table_b as select * from dba_Objects;
Table created.
SQL>
SQL>
SQL> set arraysize 100
SQL> set autotrace traceonly stat
SQL> set timing on
SQL> select
2 A.object_name,
3 (
4 Select count(*) from table_b where created is not null
5 ) QTY
6 from table_a A
7 where
8 (
9 Select count(*) from table_b where created is not null
10 ) > 0;
99190 rows selected.
Elapsed: 00:00:00.22
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
4321 consistent gets
3328 physical reads
0 redo size
2356913 bytes sent via SQL*Net to client
11453 bytes received via SQL*Net from client
993 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99190 rows processed
SQL>
SQL> select * from (
2 select
3 A.object_name,
4 (
5 Select count(*) from table_b where created is not null
6 ) QTY
7 from table_a A
8 )
9 where QTY > 0;
99190 rows selected.
Elapsed: 00:00:00.12
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4316 consistent gets
0 physical reads
0 redo size
2356913 bytes sent via SQL*Net to client
11453 bytes received via SQL*Net from client
993 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99190 rows processed
So for the case where the subquery returns rows, the work (consistent gets) is equivalent. Lets look now at when the subquery does not return rows.
SQL> select
2 A.object_name,
3 (
4 Select count(*) from table_b where created < date '1900-01-01'
5 ) QTY
6 from table_a A
7 where
8 (
9 Select count(*) from table_b where created < date '1900-01-01'
10 ) > 0;
no rows selected
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1669 consistent gets
0 physical reads
0 redo size
412 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
SQL> select * from (
2 select
3 A.object_name,
4 (
5 Select count(*) from table_b where created < date '1900-01-01'
6 ) QTY
7 from table_a A
8 )
9 where QTY > 0;
no rows selected
Elapsed: 00:00:00.02
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3338 consistent gets
0 physical reads
0 redo size
412 bytes sent via SQL*Net to client
541 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
So there is a difference in this case. If we look at the execution plans for the various scenarios:
SQL>
SQL> set autotrace traceonly explain
SQL> select
2 A.object_name,
3 (
4 Select count(*) from table_b where created is not null
5 ) QTY
6 from table_a A
7 where
8 (
9 Select count(*) from table_b where created is not null
10 ) > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 1859181919
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99190 | 2421K| 596 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TABLE_B | 99191 | 774K| 297 (1)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| TABLE_A | 99190 | 2421K| 297 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| TABLE_B | 1 | 8 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATED" IS NOT NULL)
3 - filter( EXISTS (SELECT 0 FROM "TABLE_B" "TABLE_B" WHERE
"CREATED" IS NOT NULL))
5 - filter("CREATED" IS NOT NULL)
SQL>
SQL> select * from (
2 select
3 A.object_name,
4 (
5 Select count(*) from table_b where created is not null
6 ) QTY
7 from table_a A
8 )
9 where QTY > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 984749368
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99190 | 7652K| 594 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TABLE_B | 99191 | 774K| 297 (1)| 00:00:01 |
|* 3 | VIEW | | 99190 | 7652K| 594 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TABLE_A | 99190 | 2421K| 297 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATED" IS NOT NULL)
3 - filter("QTY">0)
SQL>
SQL>
SQL> select
2 A.object_name,
3 (
4 Select count(*) from table_b where created < date '1900-01-01'
5 ) QTY
6 from table_a A
7 where
8 (
9 Select count(*) from table_b where created < date '1900-01-01'
10 ) > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 1859181919
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99190 | 2421K| 891 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TABLE_B | 1 | 8 | 297 (1)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| TABLE_A | 99190 | 2421K| 297 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| TABLE_B | 1 | 8 | 297 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATED"<TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
3 - filter( EXISTS (SELECT 0 FROM "TABLE_B" "TABLE_B" WHERE
"CREATED"<TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
5 - filter("CREATED"<TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
SQL>
SQL> select * from (
2 select
3 A.object_name,
4 (
5 Select count(*) from table_b where created < date '1900-01-01'
6 ) QTY
7 from table_a A
8 )
9 where QTY > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 984749368
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99190 | 7652K| 594 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| TABLE_B | 1 | 8 | 297 (1)| 00:00:01 |
|* 3 | VIEW | | 99190 | 7652K| 594 (1)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TABLE_A | 99190 | 2421K| 297 (1)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("CREATED"<TO_DATE(' 1900-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
3 - filter("QTY">0)
SQL>
SQL>
we do see some differences, which means there isn't really a "one size fits all" answer. It will depend on the data volumes for table_a and table_b.