Your "developers" should be sent back to school. I've seen bad ideas before - but this is taking two or three of them and combining them for the "super bad idea to beat your bad idea"
Your developers stink, just my opinion.
... In order to do this, they have dynamically created inner joins to retrieve records. ...
Ok, now I *really* hate them, they more than stink, they - well, use your imagination.
I'll bet they complain "the database is slow" too.
... The DBA suggested to denormalize, ...
how can you denormalize this ?!?!?!?!? I would suggest NORMALIZATION (eg: create a table please, a table with COLUMNS TO SUPPORT YOUR DATA)
Unfortunately, you did not take the 30 seconds it takes to read:
http://asktom.oracle.com/tkyte/question_policy.htm although you checked the box that said "i read it". Read #3....
sigh. no one reads anything anymore. Or if they do, I'm at a loss as to why they do the opposite....
ops$tkyte%ORA10GR2> create table "i hate your 'design'"
2 ( nm varchar2(30),
3 c1 varchar2(23),
4 c2 varchar2(15)
5 )
6 /
Table created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Martin','ChequeNo=929020','Value=2000' );
1 row created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Martin','HouseNo=27','State=TamilNadu');
1 row created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Martin','BankName=Bank of Baroda', 'Branch=T Nagar' );
1 row created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Luther','ChequeNo=922320','Value=2400' );
1 row created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Luther','BankName=UBS','Branch=Zurich' );
1 row created.
ops$tkyte%ORA10GR2> insert into "i hate your 'design'" (nm,c1,c2) values ( 'Shankar','ChequeNo=929590','Value=1200' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select nm,
2 max( decode(rn,1,c1) ) c1_1,
3 max( decode(rn,1,c2) ) c2_1,
4 max( decode(rn,2,c1) ) c1_2,
5 max( decode(rn,2,c2) ) c2_2,
6 max( decode(rn,3,c1) ) c1_3,
7 max( decode(rn,3,c2) ) c2_3,
8 max( decode(rn,4,c1) ) c1_4,
9 max( decode(rn,4,c2) ) c2_4,
10 max( decode(rn,5,c1) ) c1_5,
11 max( decode(rn,5,c2) ) c2_5,
12 max( decode(rn,6,c1) ) c1_6,
13 max( decode(rn,6,c2) ) c2_6
14 from (
15 select nm, c1, c2, row_number() over (partition by nm order by rowid) rn
16 from "i hate your 'design'"
17 )
18 group by nm
19 /
NM C1_1 C2_1 C1_2 C2_2 C1_3 C2_3 C1_4 C2_4 C1_5 C2_5 C1_6 C2_6
------------------------------ ----------------------- --------------- ----------------------- --------------- ----------------------- --------------- ----------------------- --------------- ----------------------- --------------- ----------------------- ---------------
Luther ChequeNo=922320 Value=2400 BankName=UBS Branch=Zurich
Martin ChequeNo=929020 Value=2000 HouseNo=27 State=TamilNadu BankName=Bank of Baroda Branch=T Nagar
Shankar ChequeNo=929590 Value=1200
if you think "5 will be the most", then create a view like the above with 7 sets (I used 6 sets in my EXAMPLE).
If you think "10 will be the most", then use 12 - whatever.
and if the 'developers' (and I use the word loosely here) cannot tell you what the max would be - show them this query and tell them "construct THIS - do not join N times"