Each When condition is tested for each row returned by the subquery?

Question

So I set the autotrace on
ramin.tb001 had 10 rows.

enter image description here

In 1 version I use equality and in second less than
I want to know is the When condions reads 10 rows from ramin.tb001 and check condition or how?

Please don't post images to show code or outcomes. Many people can't view image sites because of firewall rules (or similar) and many other people won't as a matter of principle. Please take the time to post your code (or whatever) as text. You're more likely to get a speedy and helpful answer if you do. — APC, Oct 30 '19 at 16:07
for post the result as text you could try redirect your output tio a file ..and then edit this file — ScaisEdge, Oct 30 '19 at 17:14
I’m not clear about what you are editing or what is hard about it. Can’t you just copy & paste the text from your SQL\*Plus session? — William Robertson, Oct 30 '19 at 17:31
Conceptually at least, yes it reads all 10 rows from the query and evaluates the filter for each row. I can’t see any other way for it to process the statement. What issue are you facing? — William Robertson, Oct 30 '19 at 17:46
I am about this => Each When condition is tested for each row returned by the subquery? ( multitable conditional insert all) @WilliamRobertson — , Oct 30 '19 at 18:34
Yep @KamranAbbasov you see the answer in the [excecution plan](https://stackoverflow.com/a/34975420/4808122) `FULL TABLE SCAN`without filter predicate means that all row are scanned. And BTW `INSERT ALL` with *only one* insert clause is meaningless (and examples are missleading) - use simple INSERT instead. — Marmite Bomber, Oct 30 '19 at 19:21

score 0 · Accepted Answer · answered Oct 30 '19 at 17:59

I want to know is the When condions reads 10 rows from ramin.tb001 and check condition or how?

Your first insert statement applies the condition when id = 6 and the second applies the condition when id < 10. In both cases the query reads the whole table and filters the result set by evaluating each row. How else could the query be processed?

Well, if there were a unique index on ramin.tb001.id the optimiser would probably choose to use that instead. As only ID is selected in each case, the entire query can be satisfied from the index, which would be more efficient than scanning the table. This is true even if ramin.tb001 has just the one column, because ID would be sorted in the index, but maybe not in the table. Although given that the source data has such a trivial amount of data the difference in actual performance would be negligible.

I will use index for experiment because in this table havnt primary key ( index ), and want ask about can have an ELSE clause in insert all conditional multitable insert statement ? @APC — , Oct 30 '19 at 18:38

score 0 · Answer 2 · answered Oct 30 '19 at 19:14

I do not thing that Oracle account for your special case with only one WHEN insert clause and implemented the optimizer path using index (test also shows always FULL TABLE SCANs).

The reason is that the general case deployes more insert clauses such as follows:

insert all
when id < 3 then into tb002 values(id)
when id >= 3 then into tb003 values(id)
select id from tb001;

What is Oracle doing is visible in the excecution plan

-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT    |       |    10 |   130 |     3   (0)| 00:00:01 |
|   1 |  MULTI-TABLE INSERT |       |       |       |            |          |
|   2 |   TABLE ACCESS FULL | TB001 |    10 |   130 |     3   (0)| 00:00:01 |
|   3 |   INTO              | TB002 |       |       |            |          |
|   4 |   INTO              | TB003 |       |       |            |          |
-----------------------------------------------------------------------------

You see the FULL SCAN of the source table in the line 2, i.e. all rows are considered; for each row all the INTOinsert clauses are evalueted and if TRUE the row is inserted.

If you want to implement a ELSEclause - you must use INSERT FIRST

insert first
when id < 3 then into tb002 values(id)
else into tb003 values(id)
select id from tb001

from the Oracle documentation

If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

I looked at documentation so in "conditional INSERT ALL" we can use ELSE clause too. And thanks all for helping me and for more information, will be very helpfull for me. — , Oct 30 '19 at 19:31

Each When condition is tested for each row returned by the subquery?

2 Answers2