Discarding the Output of a Select Statement
Posted by David Aldridge on 2008-04-01
I’ve wondered before about a convenient way to tackle a particular problem — you want to run a select for a big bunch of rows, maybe for measuring wait events for a tricky query, but you want to discard the output so you don’t incur the network traffic and waits to the client. You could use autotrace in sql*plus, but how about something more generic? I’ve seen queries pushed to an in-line view with a 1=0 predicate and a no_push_pred (?) hint, and even seen people erroneously replace the projected rows with a simple count(*) — the latter often affecting the execution plan as an unintended side-effect. But it’s not like you can just pipe it to /dev/null, and you want something pretty tool independent.
Then I thought of this:
insert all when 1=0 then into s_order select * from s_order where ... /
It simply discards all the rows projected from the select statement, and the optimizer doesn’t push the 1=0 condition into the select statement as a plan-modifying predicate either (10.2).
explain plan for insert into s_order select * from s_order / select * from table(dbms_xplan.display) / ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 11M| 5052M| 446K (1)| 02:13:56 | | 1 | TABLE ACCESS FULL| S_ORDER | 11M| 5052M| 446K (1)| 02:13:56 | ----------------------------------------------------------------------------- explain plan for insert all when 1=0 then into s_order select * from s_order / select * from table(dbms_xplan.display) / ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 11M| 5052M| 446K (1)| 02:13:56 | | 1 | MULTI-TABLE INSERT | | | | | | | 2 | INTO | S_ORDER | | | | | | 3 | TABLE ACCESS FULL| S_ORDER | 11M| 5052M| 446K (1)| 02:13:56 | ------------------------------------------------------------------------------- explain plan for insert all when 1=0 then into s_order select * from s_order where 1=0 / select * from table(dbms_xplan.display) / -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 445 | 0 (0)| | | 1 | MULTI-TABLE INSERT | | | | | | | 2 | INTO | S_ORDER | | | | | |* 3 | FILTER | | | | | | | 4 | TABLE ACCESS FULL| S_ORDER | 11M| 5052M| 446K (1)| 02:13:56 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(NULL IS NOT NULL) explain plan for insert into s_order select * from s_order where 1=0 / select * from table(dbms_xplan.display) / ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | 1 | 445 | 0 (0)| | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| S_ORDER | 11M| 5052M| 446K (1)| 02:13:56 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL)
So if you have a use for a filter on an insert statement that does not affect the optimizer’s execution plan (maybe for some esoteric optimizer problem?) then you could also use it for that.
Maybe there are more elegant solutions: after all this does require that you first make available a table with the correct columns and datatypes (with a CTAS and 1=0 condition, possibly). I bet there’s some other good ways though … ?
2008-04-02 at 10:07 am
Cute.
Personally, i test by shrinking the SQL*Plus window. Yes, yes, i know.
If i really cared, wouldn’t a PL/SQL routine be best? Just put the returns into variables and throw it away. (That’s what Richard did in his example here http://richardfoote.wordpress.com/2008/03/31/larger-block-index-tablespace-and-small-index-scans-performance-improvement-let-down/)
2008-04-02 at 10:15 am
I think that in Richard’s case that choice was dictated by needing to run several thousands of select statements, but the principle stil works the same I suppose. You’d have to do some bulk collect thing … that might be too much like hard work for me.
2008-04-02 at 12:19 pm
I think there is the same side effect with the INSERT ALL method as with the COUNT(*) one.
I tested in a 10.1 instance.
First run: execute the following as is
Second run: uncomment the optimizer_mode statement.
My observations:
With the default optimizer_mode setting, the INSERT ALL did what my target statement naturally wanted to do; the COUNT(*) did not.
With the optimizer_mode set to first_rows, the INSERT ALL did not what the target statement naturally wanted to do; the COUNT(*) did.
create table t as select object_id a, object_name b, object_type c from all_objects;
create index c on t (c);
insert into t select * from t order by dbms_random.random;
insert into t select * from t order by dbms_random.random;
insert into t select * from t order by dbms_random.random;
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
-- just to show the clustering factor stays roughly the same
select blevel, leaf_blocks, distinct_keys
,avg_leaf_blocks_per_key,avg_data_blocks_per_key
,clustering_factor,num_rows
from user_indexes where index_name='C';
-- uncomment this on the second run
--alter session set optimizer_mode=first_rows;
-- this is the target statement
explain plan for select * from t where c='SYNONYM';
select * from table(dbms_xplan.display());
explain plan for select /*+ first_rows */ * from t where c='SYNONYM';
select * from table(dbms_xplan.display());
explain plan for insert all when 1=0 then into t select * from t where c='SYNONYM';
select * from table(dbms_xplan.display());
explain plan for select count(*) from ( select * from t where c='SYNONYM' );
select * from table(dbms_xplan.display());
drop table t purge;
2008-04-02 at 2:41 pm
I think the first_rows issue is to be expected. Oracle probably overrides first_rows with all_rows when performing an insert. Something to bear in mind.
The change I usually see when people use a count(*) is that Oracle naturaly gets more index-happy. The count(*) can often be satisfied from an index, whereas the projecting the contents of columns is more likely to require table access.
2008-04-02 at 5:38 pm
What about:
select count(*) from (select /*+ materialize */ * from s_order where ...);2008-04-02 at 6:12 pm
Hmmm, is that hint valid in the context of an in-line view? It produces a different execution plan to the subquery factoring alternative and is the same as if it weren’t there …
explain plan for select count(*) from (select /*+ materialize */ * from s_order);
select * from table(dbms_xplan.display);
explain plan for select count(*) from (select * from s_order);
select * from table(dbms_xplan.display);
explain plan for
with rowset as(select /*+ materialize */ * from s_order)
select count(*) from rowset;
select * from table(dbms_xplan.display);
These give me:
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 443K (1)| 02:13:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| S_ORDER | 11M| 443K (1)| 02:13:01 |
———————————————————————-
and
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 443K (1)| 02:13:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| S_ORDER | 11M| 443K (1)| 02:13:01 |
———————————————————————-
and
———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | | 532K (2)| 02:39:50 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | S_ORDER | 11M| 5052M| 446K (1)| 02:13:56 |
| 4 | SORT AGGREGATE | | 1 | | | |
| 5 | VIEW | | 11M| | 86334 (4)| 00:25:55 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6600_36AEF106 | 11M| 5052M| 86334 (4)| 00:25:55 |
———————————————————————————————————-
It’s interesting that the cost of the full table scan varies there between 443K and 446K. I wonder why?
2008-04-03 at 9:49 am
Very interesting thread and results.
Thanx David.
2008-04-03 at 6:30 pm
Btw, Jonathan Lewis has done few interesting posts on similar topic (but not exactly with the same purpose in mind) and there is another hint he was talking about - NO_MERGE.
2008-04-04 at 11:51 am
[...] Aldridge, the Oracle Sponge, looks into discarding the output of a select statement: “. . . you want to run a select for a big bunch of rows, maybe for measuring [...]