SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql query to create dummy rows (performance)

ora1001Oct 26 2015 — edited Oct 26 2015

Database: Oracle 11g

with tab1 as
(select 1 sequence_num,'p1' productid, 'jm1' job_master_id, 'jm11' job_id, 'insert' status
```
 from dual  
```
union all
select 2 sequence_num,'p1' productid, 'jm1' job_master_id, 'jm12' job_id, 'insert' status
```
 from dual  
```
union all
select 3 sequence_num, 'p1' productid,'jm1' job_master_id, 'jm13' job_id, 'insert' status
```
 from dual  
```
union all
select 4 sequence_num, 'p1' productid, null job_master_id, 'jm1' job_id, 'master_removed' status
```
 from dual  
```
```
 union all  
```

 select 1 sequence\_num,'p2' productid, 'jm2' job\_master\_id, 'jm21' job\_id, 'insert' status

```
 from dual  
```
```
 )  
```
select * from tab1

the above is a transaction table for a given productid. for every master job there are child jobs and for each child job there is an entry for insertion. when we remove master job we only store one record for the entire master job id. However in some reports we need to show each child job id with a status being removed like the below. basically for every logical record that's created the deleted entry will have the sequence number of the master entry. in this example sequence# 4. tab1 is a very big table.

my query:

with tab1 as
(select 1 sequence_num,'p1' productid, 'jm1' job_master_id, 'jm11' job_id, 'insert' status
```
 from dual 
```
union all
select 2 sequence_num,'p1' productid, 'jm1' job_master_id, 'jm12' job_id, 'insert' status
```
 from dual 
```
union all
select 3 sequence_num, 'p1' productid,'jm1' job_master_id, 'jm13' job_id, 'insert' status
```
 from dual 
```
union all
select 4 sequence_num, 'p1' productid, null job_master_id, 'jm1' job_id, 'master_removed' status
```
 from dual 
```
```
 union all 
```

 select 1 sequence\_num,'p2' productid, 'jm2' job\_master\_id, 'jm21' job\_id, 'insert' status

```
 from dual 
```
```
 ) 
```
select * from (
select a.*, 'physical' record from tab1 a
union all
select del.sequence_num,
```
    b.productid, 
```
```
    b.job\_master\_id, 
```
```
    b.job\_id, 
```
```
    del.status, 
```
```
    'logical' record 
```
from
tab1 del, tab1 b
where del.productid = b.productid
and del.job_id = b.job_master_id
and del.status = 'master_removed'
and b.status = 'insert')final
order by final.productid, final.job_master_id, final.job_id

Can we rewrite this query with just one single select with out three full table scans on the same table? Something using connectby or row connector.......thanks in advance...

This post has been answered by Solomon Yakobson on Oct 26 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 23 2015

Added on Oct 26 2015

1 comment

1,618 views