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