Be careful when using with in Oracle -- predicate cannot be promoted, oracle Predicate
Today, we can see that a SQL statement uses with in large quantities. For logic clarity, developers cache some result sets first with, and there are many places to use this result set later, the original SQL statement takes more than two hours. The optimization method is to put the first cached SQL statement in use, and the optimization is 12 s.
The following is a simulation of this scenario, so you don't have to worry about the meaning of SQL. The SQL abstraction at that time is like this. We can see that SQL1 first caches the results in Statement a. When statement B is used, the index on object_id is not used, and it is only used for filtering in the result set. To put it simply, the predicate of SQL1 is not advancing with that of sql2. Therefore, in terms of SQL writing, the pursuit of clarity in writing and performance should be balanced.
-- Manufacturing data
SQL> drop table test purge;
SQL> create table test as select * from dba_objects;
SQL> create index ind_t_object_id on test (object_id) nologging;
SQL> exec dbms_stats.gather_table_stats (user, 'test', cascade => true );
SQL> set autotrace traceonly
-- SQL1, no predicate advance before optimization
SQL> with a as (select * from test where object_type = 'table '),
B as (select count (1) from a where object_id <10 ),
C as (select count (1) from a where object_id> = 10 and object_id <20)
Select (select * from B) bc,
(Select * from c) cc
From dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 2659770981
Bytes ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 198 (1) | 00:00:03 |
| 1 | VIEW | 1 | 13 | 6 (0) | 00:00:01 |
| 2 | sort aggregate | 1 | 13 |
| * 3 | VIEW | 1600 | 20800 | 6 (0) | 00:00:01 |
| 4 | table access full | SYS_TEMP_0FD9D66 | 1600 | 154K | 6 (0) | 00:00:01 |
| 5 | VIEW | 1 | 13 | 6 (0) | 00:00:01 |
| 6 | sort aggregate | 1 | 13 |
| * 7 | VIEW | 1600 | 20800 | 6 (0) | 00:00:01 |
| 8 | table access full | SYS_TEMP_0FD9D66 | 1600 | 154K | 6 (0) | 00:00:01 |
| 9 | temp table transformation |
| 10 | load as select | SYS_TEMP_0FD9D66 |
| * 11 | table access full | TEST | 1600 | 154K | 196 (1) | 00:00:03 |
| 12 | fast dual | 1 | 2 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("OBJECT_ID" <10)
7-filter ("OBJECT_ID"> = 10 AND "OBJECT_ID" <20)
11-filter ("OBJECT_TYPE" = 'table ')
Statistics
----------------------------------------------------------
394 recursive cballs
22 db block gets
589 consistent gets
15 physical reads
600 redo size
381 bytes sent via SQL * Net to client
337 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- SQL2, After Optimization
SQL> with B as (select count (1) from test where object_id <10 ),
C as (select count (1) from test where object_id> = 10 and object_id <20)
Select (select * from B) bc,
(Select * from c) cc
From dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 1155001961
Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 2 (0) | 00:00:01 |
| 1 | VIEW | 1 | 13 | 2 (0) | 00:00:01 |
| 2 | sort aggregate | 1 | 5 |
| * 3 | index range scan | IND_T_OBJECT_ID | 6 | 30 | 2 (0) | 00:00:01 |
| 4 | VIEW | 1 | 13 | 2 (0) | 00:00:01 |
| 5 | sort aggregate | 1 | 5 |
| * 6 | index range scan | IND_T_OBJECT_ID | 9 | 45 | 2 (0) | 00:00:01 |
| 7 | fast dual | 1 | 2 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("OBJECT_ID" <10)
6-access ("OBJECT_ID"> = 10 AND "OBJECT_ID" <20)
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
4 consistent gets
0 physical reads
0 redo size
381 bytes sent via SQL * Net to client
337 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
