Discarding the Output of a Select Statement

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 … ?

About these ads

9 thoughts on “Discarding the Output of a Select Statement

  1. 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.

  2. 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;

  3. 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.

  4. 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?

  5. 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.

  6. Pingback: Log Buffer #91: a Carnival of the Vanities for DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s