Home » SQL & PL/SQL » SQL & PL/SQL » inserting more than one record in a single transaction (oracl1 0g 10.2.0.3 on linux 64 bit)
inserting more than one record in a single transaction [message #432265] Mon, 23 November 2009 05:35 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
Could anybody tell me that currently my log tables in oracle has 22 columns out of 22, 20 belongs to rate from different lenders like

uuid,app id,lender1,lender2,lender3 .............,lender20
1,123,lender1_rate,lender2_rate,lender3_rate..............
In order to make it more readable and in order to include new lender dynamically we are changing this table to row wise so that we do not need to include new lender column in table

uuid,app,lender
1,123,lender1_rate
1,123,lender2_rate
1,123,lender3_rate
.
.
.
.
1,123,lender20_rate

but it will create instead of one record, 20 records for each app in a transaction.

Is it costly for insert. If it is then what should be the way.
Re: inserting more than one record in a single transaction [message #432267 is a reply to message #432265] Mon, 23 November 2009 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really need 20 rows for each?
Do you often need to add more lender?

Regards
Michel
Re: inserting more than one record in a single transaction [message #432268 is a reply to message #432265] Mon, 23 November 2009 05:41 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
sometimes we need to add new lenders but it is quite hassle to include new lenders in reports
Re: inserting more than one record in a single transaction [message #432270 is a reply to message #432268] Mon, 23 November 2009 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer my 2 questions.
How does it change you report display if the date are stored in columns or rows?

Regards
Michel
Re: inserting more than one record in a single transaction [message #432281 is a reply to message #432270] Mon, 23 November 2009 06:55 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Yes we often need to have new lenders

if you are counting how many and which lenders have offered rates then how do you include new lender in report when your query only have existing column
Re: inserting more than one record in a single transaction [message #432285 is a reply to message #432265] Mon, 23 November 2009 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think your new design is a better one.

What exactly are you asking? Inserts are not going to be any more expensive in this table thant they would be in any other table.
Re: inserting more than one record in a single transaction [message #432340 is a reply to message #432285] Mon, 23 November 2009 12:20 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
will they be creating more redo logs, because more insert .
May i use direct loading when i am inserting like /*APPEND*/ AND If i am using direct load then what are the pros and cons.


Re: inserting more than one record in a single transaction [message #432353 is a reply to message #432340] Mon, 23 November 2009 15:56 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
APPEND hint has little usage for inserts. In fact it could affect only compression (AFAIK).
Your new design seems a better one - but it depends on many factors. My guess is that currently you got rows with a lot of NULLs. If that it the case then new design looks quite good.
If every insert has full set of data then you might seek some other solutions (I know that there are problems of too-many-columns, adding a column and some other in Oracle).
Read about normalization - it might be a good idea to separate common parts into a separate table. Or to use a nested table.
I have almost forgotten about mentioning one important thing: bulk insert. Please, read about it.

It seems that your problem might be a good task for experienced professional DBA (which I am not) - but be prepared for quite high price for professional help.

[Updated on: Mon, 23 November 2009 15:57]

Report message to a moderator

Re: inserting more than one record in a single transaction [message #432362 is a reply to message #432340] Mon, 23 November 2009 17:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many rows do you currently insert into this table each day, and what is the average number of rate columns that are populated?
Re: inserting more than one record in a single transaction [message #432397 is a reply to message #432362] Mon, 23 November 2009 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good luck to have an answer on this I asked him this twice with no reply.

Regards
Michel
Re: inserting more than one record in a single transaction [message #432400 is a reply to message #432397] Mon, 23 November 2009 23:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe JRow's positive approach encourages people more to answer..
Re: inserting more than one record in a single transaction [message #432404 is a reply to message #432400] Mon, 23 November 2009 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but there is no negative word from me in this topic.
Also if he does not answer, he has no answer, it's up to him, I have no problem he stays with his problem.

Regards
Michel
Re: inserting more than one record in a single transaction [message #432421 is a reply to message #432362] Tue, 24 November 2009 02:55 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Sorry for late reply
Each day we insert in average 500 records
Average rate column populated are 9

Re: inserting more than one record in a single transaction [message #432422 is a reply to message #432421] Tue, 24 November 2009 03:02 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
MIFI wrote on Tue, 24 November 2009 09:55
Sorry for late reply
Each day we insert in average 500 records
Average rate column populated are 9

500 rows of data per day? Not 500 000 000 of rows? Then redo-logs should not matter.
If I understand it correctly and you add ~9 columns per day then expect significant issues sooner than later. Seems that you should change the organization of that table from horizontal to vertical ASAP. You should be able to insert the rows one by one and it seems that there is no reason to worry about the performance - unless your Oracle is running on some fancy watch instead of at least PC.
Re: inserting more than one record in a single transaction [message #432424 is a reply to message #432421] Tue, 24 November 2009 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The anmount of data that you are going to create is insignificant.
Stop worrying about Redo and transaction times, put your new solution in, and move on.
Re: inserting more than one record in a single transaction [message #432520 is a reply to message #432424] Tue, 24 November 2009 13:06 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
so it means if i go for verticle thing each day we will have 500*20 records expected, because developer wants to insert a record even if lender rate is 0.
Re: inserting more than one record in a single transaction [message #432521 is a reply to message #432520] Tue, 24 November 2009 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MIFI wrote on Tue, 24 November 2009 20:06
so it means if i go for verticle thing each day we will have 500*20 records expected, because developer wants to insert a record even if lender rate is 0.

Why?

Regards
Michel

Re: inserting more than one record in a single transaction [message #432601 is a reply to message #432521] Wed, 25 November 2009 02:56 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
because they some times take out lender from panel. there is a difference between lender is not in a panel and lender is in panel but did not offer particular loan so if the lander rate is 0 it means lender was in the panel but did not offer loan
Re: inserting more than one record in a single transaction [message #432603 is a reply to message #432601] Wed, 25 November 2009 03:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1000 records a day isn't exactly thrashing the database.
As long as the table is indexed so that you can find the latest records easily you'll not have a problem.
Re: inserting more than one record in a single transaction [message #432606 is a reply to message #432601] Wed, 25 November 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what happen if you add a 21th lender?
Have you to add a row for each uuid/app previously entered?
Otherwise there is not the same number of rows for each one.

Regards
Michel
Re: inserting more than one record in a single transaction [message #432607 is a reply to message #432606] Wed, 25 November 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why would that matter?
If you add a new lender today, why would you be looking for details of what rate that lender was lending 3 weeks ago?
Re: inserting more than one record in a single transaction [message #432609 is a reply to message #432607] Wed, 25 November 2009 03:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why would that matter?

If the question is for me it does not matter.
But as OP said:
Quote:
developer wants to insert a record even if lender rate is 0.

So it means (to me) that "developer" wants the same number of rows for each item (currently line).

By the way, it was the purpose of my very first answer:
Quote:
Do you really need 20 rows for each?


Regards
Michel
Re: inserting more than one record in a single transaction [message #432611 is a reply to message #432609] Wed, 25 November 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The OP also said Quote:
there is a difference between lender is not in a panel and lender is in panel but did not offer particular loan so if the lander rate is 0 it means lender was in the panel but did not offer loan
Re: inserting more than one record in a single transaction [message #432615 is a reply to message #432611] Wed, 25 November 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sentence that I don't clearly understand when I compared it with others. ./fa/3314/0/
So back to my first question (which only a yes/no answer):
Quote:
Do you really need 20 rows for each?

And to my last one (same yes/no answer):
Quote:
And what happen if you add a 21th lender?
Have you to add a row for each uuid/app previously entered?


Regards
Michel
Re: inserting more than one record in a single transaction [message #432619 is a reply to message #432265] Wed, 25 November 2009 04:30 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Seems like we have a very long discussion about inserting couple of rows. Probably ~0.001 second of database processing and several hours of our writing and thinking...

I believe that the answer was already provided. If you wish to discuss technical abstract things then another thread might be created.

MIFI,
Please go with vertical structure with static number of table columns. Let us know if anything else is not clear.
Re: inserting more than one record in a single transaction [message #432620 is a reply to message #432619] Wed, 25 November 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please go with vertical structure with static number of table columns. Let us know if anything else is not clear.

This is your opinion but the best solution depends on the answer of my questions.
And you haven't them, have you?

Regards
Michel
Re: inserting more than one record in a single transaction [message #432639 is a reply to message #432620] Wed, 25 November 2009 07:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only question I can see that hasn't been answered is Quote:
How does it change you report display if the date are stored in columns or rows?
.
Which question were you thinking of?

The single lender per record structure is inherantly more flexible, and doesn't require DDL changes every time you need an extra dealer.
Re: inserting more than one record in a single transaction [message #432640 is a reply to message #432620] Wed, 25 November 2009 07:11 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
[quote title=Michel Cadot wrote on Wed, 25 November 2009 11:37]Quote:
And you haven't them, have you?

No, I don't.
Re: inserting more than one record in a single transaction [message #432647 is a reply to message #432639] Wed, 25 November 2009 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Wed, 25 November 2009 14:06
The only question I can see that hasn't been answered is Quote:
How does it change you report display if the date are stored in columns or rows?
.
Which question were you thinking of?

Michel Cadot wrote on Wed, 25 November 2009 11:13
So back to my first question (which only a yes/no answer):
Quote:
Do you really need 20 rows for each?

And to my last one (same yes/no answer):
Quote:
And what happen if you add a 21th lender?
Have you to add a row for each uuid/app previously entered?

But you're right this question was also not answered but it is just a roundabout way to get answer of the other ones.

Regards
Michel

[Updated on: Wed, 25 November 2009 07:57]

Report message to a moderator

Re: inserting more than one record in a single transaction [message #432657 is a reply to message #432265] Wed, 25 November 2009 09:28 Go to previous message
David Ashcroft
Messages: 1
Registered: November 2009
Location: CANADA
Junior Member
HI
Semantically, RDBMS table rows and columns are not alike flat file tables or statistical tables,
these are inevitably designed to fulfill the relational manifest between tables, between rows and between columns,
to facilitate SQL data manipulation. The flat file tables of legacy system are orthogonally different,
adding a column instead of rows is devastating the relational semantics and so SQL efforts.
Data Modeling is the key to its redemption.
If desired I can provide data modeling e-monograph links other than Handsonerp.

Thanks & regards
David Ashcroft
Previous Topic: last 2
Next Topic: Permission to create views on V$
Goto Forum:
  


Current Time: Sun Sep 25 14:31:29 CDT 2016

Total time taken to generate the page: 0.16603 seconds