Hello,
Want to split a record into 2 records and have created the test data as well as my first 2 select statements that accomplish splitting the records.
https://livesql.oracle.com/apex/livesql/s/c7den2h6h4xfynztri9kvkoge Both attempts do a cartesian join, in the first case against all data and second to only the data that matches the split criteria (col2n>col1n).
My data_rows tables will have millions of records and the expectation is only a few thousand will meet the split criteria.
Is there a better way of doing the split without doing a cartesian join?
Thanks!
Isaac
Well your cartesian join is just 2 rows from dual so I wouldn't worry too much. I'd avoid the union all approach: this is like to result in two full table scans.
You can turn it into a join with a case expression. This:
- compares your columns
- returns 1 or 2 as appropriate
- joins this to the dual rows
create table data_row
(
col1 varchar2(30)
,col2 varchar2(20)
,col1n number
,col2n number
,col3c varchar2(20)
);
insert into data_row
values ('plant','cactus',3,4,'special plant');
insert into data_row
values ('plant','mint',3,2,'plant');
with rws as (
select rownum r from dual connect by level <= 2
)
select *
from data_row
join rws
on case when col2n > col1n then 2 else 1 end <= rws.r;
COL1 COL2 COL1N COL2N COL3C R
------------------------------ -------------------- ---------- ---------- -------------------- ----------
plant mint 3 2 plant 1
plant cactus 3 4 special plant 2
plant mint 3 2 plant 2
Or you can view it in LiveSQL at:
https://livesql.oracle.com/apex/livesql/file/content_C7JRMG59WU7K8BVE3F837F9VW.html