RE: RE: Re[2]: sequence numbers
I wanted to take a picture with him. .. and take him out to lunch to learn from his experience ... ;-) but it turned out he lasted only for less than a week... ;) (Some developers he was working with knew a bit more Oracle than him) - Kirti -Original Message- Sent: Friday, October 11, 2002 11:49 AM To: Multiple recipients of list ORACLE-L Let's see, 1 table with 700+ columns that can grow to ~1GB that you want to iot and have in the keep pool. What are you smoking! That's one consultant that I'd HAVE to laugh in his/her face. And he/she would NOT get away with it. Dick Goulet Reply Separator Author: Rachel Carmichael <[EMAIL PROTECTED]> Date: 10/11/2002 7:19 AM it's all in the buzzwords, obviously :) --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > We were asked, not too long ago, to create one Oracle8i database with > only > *one* table with some 700+ columns. While at it, the consultant > (hired by > end user dept) also suggested that we make it an IOT using an LMT, > and since > the table will never grow over 1GB, asked if there was a way to put > it in > KEEP buffer pool. He was helping re-write/enhance some MS Access > Apps. > > Talk about knowing all the right lingo... ;) > > - Kirti > > -Original Message- > Sent: Friday, October 11, 2002 8:59 AM > To: Multiple recipients of list ORACLE-L > > > April, > > What can I say? Ouch! I feel your pain. I've been trapped in some > pretty ridiculous situations too. (Though, I think you have me beat! > A > 37 column primary key?? Really??) Well, you at least seem to have > the > proper attitude. ;-) Without a sense of humor, I'm afraid you'd go > insane in short order! ;-) > > The only other thing I can think of when people shut you down like > that > is: document. "At meeting X, on such and such a date, I identified > this > problem, and Mr. Z told me to not to worry about it." It may not > help, > but from a sanity point of view, there is a certain amount of > satisfaction in "I told you so!", even if you never verbalize > it;-) > > Hang in there, > > -Mark > > On Fri, 2002-10-11 at 08:43, April Wells wrote: > > Mark... > > > > If this were the MOST serious design flaw in the whole mess, I > wouldn't > care > > so much. There is a point where you just shut up (gee, I have been > TOLD > to > > do that in meetings) and wait till it breaks (or worse, one of our > clients > > buys it and we have to TRY to implement). I am the funny one... > the one > to > > laugh at and make fun of because I keep trying to tell them that > you can't > > do things. You can't have a totally denormalized Oracle table if > there > 1500 > > columns in it... yes queries will fly on a table that can't be > built. You > > can't have 37 columns in a primary key. Date really isn't an > acceptable > > name for a column. > > > > April Wells > > Oracle DBA > > Keep yourself well oiled with life, laughter, new ideas and action. > > Otherwise you will rust out. _Anonymous > > > > > > -Original Message- > > Sent: Thursday, October 10, 2002 7:34 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi Dick, > > > > I have to disagree with you here. Particularly in the case where > this > > sequence will see any sort of concurrency, from multiple concurrent > > sessions accessing it. This is due to the serialization on the SQ > > enqueue. This will cause far worse scalability issues than any > I/O. > > Not that I/O is insignificant, but in this situation, serialization > on > > the enqueue will be the real showstopper for scalability. > > > > As to losing the cached values, well, so what? If your design is > such > > that it's important to have an unbroken contiguous sequence of > numbers > > with no gaps, then I would argue that is a serious design flaw. > Also, > > if that's your requirement, then a sequence is not appropriate, > since it > > can and will end up causing gaps, the first time you roll back a > > transaction. > > > > Finally, as to sequences losing cached values, unless your instance > > crashes or does a shutdown abort, Oracle should not loose any > sequence > > values. > > > > -Mark > > > > > > > > On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > > > Actually there is no IO penalty since Oracle has to treat the > sequence > > just like > > > any table with the old LRU algorithm. I have several sequences > with a > > cache of > > > 0 and they perform as well as those with a cache value. The big > > difference is > > > when you shut down the database and all of those cached values > end up in > > the > > > trash. > > > > > > Dick Goulet > > > > > > Reply Separator > > > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > > > Date: 10/10/2002 1:38 PM > > > > > > I think that you will have an update to the sequence number EVERY > time > > instead > > > of every 20 times. That's mean
RE: Re[2]: sequence numbers
But the DOCUMENTATION says
8-0
April Wells
Oracle DBA
Keep yourself well oiled with life, laughter, new ideas and action.
Otherwise you will rust out. _Anonymous
-Original Message-
Sent: Friday, October 11, 2002 10:20 AM
To: Multiple recipients of list ORACLE-L
it's all in the buzzwords, obviously :)
--- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote:
> We were asked, not too long ago, to create one Oracle8i database with
> only
> *one* table with some 700+ columns. While at it, the consultant
> (hired by
> end user dept) also suggested that we make it an IOT using an LMT,
> and since
> the table will never grow over 1GB, asked if there was a way to put
> it in
> KEEP buffer pool. He was helping re-write/enhance some MS Access
> Apps.
>
> Talk about knowing all the right lingo... ;)
>
> - Kirti
>
begin 666 InterScan_Disclaimer.txt
M5&AE(&EN9F]R;6%T:6]N(&-O;G1A:6YE9"!I;B!T:&ES(&4M;6%I;"!I3L@:70@;6%Y(&%L2!P2!A;GEO;F4@;W1H97(@=&AA
M;B!T:&4@:6YT96YD960@2!B92!I;&QE9V%L+B @268@>6]U(&AA=F4@7-T96US+"!)
M;F,N(&AA2!R96%S;VYA8FQE('!R96-A=71I;VX@=&\@
M96YS=7)E('1H870@86YY(&%T=&%C:&UE;G0@=&\@=&AI6]U(&-Ahttp://www.orafaq.com
--
Author: April Wells
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: Re[2]: sequence numbers
it's all in the buzzwords, obviously :) --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > We were asked, not too long ago, to create one Oracle8i database with > only > *one* table with some 700+ columns. While at it, the consultant > (hired by > end user dept) also suggested that we make it an IOT using an LMT, > and since > the table will never grow over 1GB, asked if there was a way to put > it in > KEEP buffer pool. He was helping re-write/enhance some MS Access > Apps. > > Talk about knowing all the right lingo... ;) > > - Kirti > > -Original Message- > Sent: Friday, October 11, 2002 8:59 AM > To: Multiple recipients of list ORACLE-L > > > April, > > What can I say? Ouch! I feel your pain. I've been trapped in some > pretty ridiculous situations too. (Though, I think you have me beat! > A > 37 column primary key?? Really??) Well, you at least seem to have > the > proper attitude. ;-) Without a sense of humor, I'm afraid you'd go > insane in short order! ;-) > > The only other thing I can think of when people shut you down like > that > is: document. "At meeting X, on such and such a date, I identified > this > problem, and Mr. Z told me to not to worry about it." It may not > help, > but from a sanity point of view, there is a certain amount of > satisfaction in "I told you so!", even if you never verbalize > it;-) > > Hang in there, > > -Mark > > On Fri, 2002-10-11 at 08:43, April Wells wrote: > > Mark... > > > > If this were the MOST serious design flaw in the whole mess, I > wouldn't > care > > so much. There is a point where you just shut up (gee, I have been > TOLD > to > > do that in meetings) and wait till it breaks (or worse, one of our > clients > > buys it and we have to TRY to implement). I am the funny one... > the one > to > > laugh at and make fun of because I keep trying to tell them that > you can't > > do things. You can't have a totally denormalized Oracle table if > there > 1500 > > columns in it... yes queries will fly on a table that can't be > built. You > > can't have 37 columns in a primary key. Date really isn't an > acceptable > > name for a column. > > > > April Wells > > Oracle DBA > > Keep yourself well oiled with life, laughter, new ideas and action. > > Otherwise you will rust out. _Anonymous > > > > > > -Original Message- > > Sent: Thursday, October 10, 2002 7:34 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Hi Dick, > > > > I have to disagree with you here. Particularly in the case where > this > > sequence will see any sort of concurrency, from multiple concurrent > > sessions accessing it. This is due to the serialization on the SQ > > enqueue. This will cause far worse scalability issues than any > I/O. > > Not that I/O is insignificant, but in this situation, serialization > on > > the enqueue will be the real showstopper for scalability. > > > > As to losing the cached values, well, so what? If your design is > such > > that it's important to have an unbroken contiguous sequence of > numbers > > with no gaps, then I would argue that is a serious design flaw. > Also, > > if that's your requirement, then a sequence is not appropriate, > since it > > can and will end up causing gaps, the first time you roll back a > > transaction. > > > > Finally, as to sequences losing cached values, unless your instance > > crashes or does a shutdown abort, Oracle should not loose any > sequence > > values. > > > > -Mark > > > > > > > > On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > > > Actually there is no IO penalty since Oracle has to treat the > sequence > > just like > > > any table with the old LRU algorithm. I have several sequences > with a > > cache of > > > 0 and they perform as well as those with a cache value. The big > > difference is > > > when you shut down the database and all of those cached values > end up in > > the > > > trash. > > > > > > Dick Goulet > > > > > > Reply Separator > > > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > > > Date: 10/10/2002 1:38 PM > > > > > > I think that you will have an update to the sequence number EVERY > time > > instead > > > of every 20 times. That's mean I/o for every nextval. > > > > > > Yechiel Adar > > > Mehish > > > - Original Message - > > > From: Tim Gorman > > > To: Multiple recipients of list ORACLE-L > > > Sent: Thursday, October 10, 2002 7:43 PM > > > Subject: Re: sequence numbers > > > > > > > > > CACHE 20 is the default, so if you remove the clause, it will > have > > absolutely > > > no impact on performance or anything else... > > > > > > ...of course, I get the feeling that that wasn't the gist of > your > > question, > > > was it? > > > - Original Message - > > > From: April Wells > > > To: Multiple recipients of list ORACLE-L > > > Sent: Wednesday, October 09, 2002 8:54 AM > > > Subject: sequence numbers >
RE: Re[2]: sequence numbers
We were asked, not too long ago, to create one Oracle8i database with only *one* table with some 700+ columns. While at it, the consultant (hired by end user dept) also suggested that we make it an IOT using an LMT, and since the table will never grow over 1GB, asked if there was a way to put it in KEEP buffer pool. He was helping re-write/enhance some MS Access Apps. Talk about knowing all the right lingo... ;) - Kirti -Original Message- Sent: Friday, October 11, 2002 8:59 AM To: Multiple recipients of list ORACLE-L April, What can I say? Ouch! I feel your pain. I've been trapped in some pretty ridiculous situations too. (Though, I think you have me beat! A 37 column primary key?? Really??) Well, you at least seem to have the proper attitude. ;-) Without a sense of humor, I'm afraid you'd go insane in short order! ;-) The only other thing I can think of when people shut you down like that is: document. "At meeting X, on such and such a date, I identified this problem, and Mr. Z told me to not to worry about it." It may not help, but from a sanity point of view, there is a certain amount of satisfaction in "I told you so!", even if you never verbalize it;-) Hang in there, -Mark On Fri, 2002-10-11 at 08:43, April Wells wrote: > Mark... > > If this were the MOST serious design flaw in the whole mess, I wouldn't care > so much. There is a point where you just shut up (gee, I have been TOLD to > do that in meetings) and wait till it breaks (or worse, one of our clients > buys it and we have to TRY to implement). I am the funny one... the one to > laugh at and make fun of because I keep trying to tell them that you can't > do things. You can't have a totally denormalized Oracle table if there 1500 > columns in it... yes queries will fly on a table that can't be built. You > can't have 37 columns in a primary key. Date really isn't an acceptable > name for a column. > > April Wells > Oracle DBA > Keep yourself well oiled with life, laughter, new ideas and action. > Otherwise you will rust out. _Anonymous > > > -Original Message- > Sent: Thursday, October 10, 2002 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Hi Dick, > > I have to disagree with you here. Particularly in the case where this > sequence will see any sort of concurrency, from multiple concurrent > sessions accessing it. This is due to the serialization on the SQ > enqueue. This will cause far worse scalability issues than any I/O. > Not that I/O is insignificant, but in this situation, serialization on > the enqueue will be the real showstopper for scalability. > > As to losing the cached values, well, so what? If your design is such > that it's important to have an unbroken contiguous sequence of numbers > with no gaps, then I would argue that is a serious design flaw. Also, > if that's your requirement, then a sequence is not appropriate, since it > can and will end up causing gaps, the first time you roll back a > transaction. > > Finally, as to sequences losing cached values, unless your instance > crashes or does a shutdown abort, Oracle should not loose any sequence > values. > > -Mark > > > > On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > > Actually there is no IO penalty since Oracle has to treat the sequence > just like > > any table with the old LRU algorithm. I have several sequences with a > cache of > > 0 and they perform as well as those with a cache value. The big > difference is > > when you shut down the database and all of those cached values end up in > the > > trash. > > > > Dick Goulet > > > > Reply Separator > > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > > Date: 10/10/2002 1:38 PM > > > > I think that you will have an update to the sequence number EVERY time > instead > > of every 20 times. That's mean I/o for every nextval. > > > > Yechiel Adar > > Mehish > > - Original Message - > > From: Tim Gorman > > To: Multiple recipients of list ORACLE-L > > Sent: Thursday, October 10, 2002 7:43 PM > > Subject: Re: sequence numbers > > > > > > CACHE 20 is the default, so if you remove the clause, it will have > absolutely > > no impact on performance or anything else... > > > > ...of course, I get the feeling that that wasn't the gist of your > question, > > was it? > > - Original Message - > > From: April Wells > > To: Multiple recipients of list ORACLE-L > > Sent: Wednesday, October 09, 2002 8:54 AM > > Subject: sequence numbers > > > > > > I have been given create scripts for sequences to be used in tables > that > > will be loaded via bulk loads. How huge is the potential performance hit > if I > > take out the cache 20? > > > > April Wells > > Oracle DBA > > There is neither good nor bad, but thinking makes it so. -Shakespeare > > > > > > > > > > > > > > > > > > > style="FONT: 10pt Times New Roman; MA
RE: Re[2]: sequence numbers
April, What can I say? Ouch! I feel your pain. I've been trapped in some pretty ridiculous situations too. (Though, I think you have me beat! A 37 column primary key?? Really??) Well, you at least seem to have the proper attitude. ;-) Without a sense of humor, I'm afraid you'd go insane in short order! ;-) The only other thing I can think of when people shut you down like that is: document. "At meeting X, on such and such a date, I identified this problem, and Mr. Z told me to not to worry about it." It may not help, but from a sanity point of view, there is a certain amount of satisfaction in "I told you so!", even if you never verbalize it;-) Hang in there, -Mark On Fri, 2002-10-11 at 08:43, April Wells wrote: > Mark... > > If this were the MOST serious design flaw in the whole mess, I wouldn't care > so much. There is a point where you just shut up (gee, I have been TOLD to > do that in meetings) and wait till it breaks (or worse, one of our clients > buys it and we have to TRY to implement). I am the funny one... the one to > laugh at and make fun of because I keep trying to tell them that you can't > do things. You can't have a totally denormalized Oracle table if there 1500 > columns in it... yes queries will fly on a table that can't be built. You > can't have 37 columns in a primary key. Date really isn't an acceptable > name for a column. > > April Wells > Oracle DBA > Keep yourself well oiled with life, laughter, new ideas and action. > Otherwise you will rust out. _Anonymous > > > -Original Message- > Sent: Thursday, October 10, 2002 7:34 PM > To: Multiple recipients of list ORACLE-L > > > Hi Dick, > > I have to disagree with you here. Particularly in the case where this > sequence will see any sort of concurrency, from multiple concurrent > sessions accessing it. This is due to the serialization on the SQ > enqueue. This will cause far worse scalability issues than any I/O. > Not that I/O is insignificant, but in this situation, serialization on > the enqueue will be the real showstopper for scalability. > > As to losing the cached values, well, so what? If your design is such > that it's important to have an unbroken contiguous sequence of numbers > with no gaps, then I would argue that is a serious design flaw. Also, > if that's your requirement, then a sequence is not appropriate, since it > can and will end up causing gaps, the first time you roll back a > transaction. > > Finally, as to sequences losing cached values, unless your instance > crashes or does a shutdown abort, Oracle should not loose any sequence > values. > > -Mark > > > > On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > > Actually there is no IO penalty since Oracle has to treat the sequence > just like > > any table with the old LRU algorithm. I have several sequences with a > cache of > > 0 and they perform as well as those with a cache value. The big > difference is > > when you shut down the database and all of those cached values end up in > the > > trash. > > > > Dick Goulet > > > > Reply Separator > > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > > Date: 10/10/2002 1:38 PM > > > > I think that you will have an update to the sequence number EVERY time > instead > > of every 20 times. That's mean I/o for every nextval. > > > > Yechiel Adar > > Mehish > > - Original Message - > > From: Tim Gorman > > To: Multiple recipients of list ORACLE-L > > Sent: Thursday, October 10, 2002 7:43 PM > > Subject: Re: sequence numbers > > > > > > CACHE 20 is the default, so if you remove the clause, it will have > absolutely > > no impact on performance or anything else... > > > > ...of course, I get the feeling that that wasn't the gist of your > question, > > was it? > > - Original Message - > > From: April Wells > > To: Multiple recipients of list ORACLE-L > > Sent: Wednesday, October 09, 2002 8:54 AM > > Subject: sequence numbers > > > > > > I have been given create scripts for sequences to be used in tables > that > > will be loaded via bulk loads. How huge is the potential performance hit > if I > > take out the cache 20? > > > > April Wells > > Oracle DBA > > There is neither good nor bad, but thinking makes it so. -Shakespeare > > > > > > > > > > > > > > > > > > > style="FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px"> > > I think that you will have an update to the > > sequence number EVERY time instead of every 20 times. That's mean I/o for > every > > nextval. > > > > Yechiel AdarMehish > > > style="BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: > 0px; > > PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> > > - Original Message - > >> style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: > black">From: > > mailto:Tim@;SageLogix.com" [EMAIL PROTECTED]>Tim > Gorman > > > >
RE: Re[2]: sequence numbers
Mark... If this were the MOST serious design flaw in the whole mess, I wouldn't care so much. There is a point where you just shut up (gee, I have been TOLD to do that in meetings) and wait till it breaks (or worse, one of our clients buys it and we have to TRY to implement). I am the funny one... the one to laugh at and make fun of because I keep trying to tell them that you can't do things. You can't have a totally denormalized Oracle table if there 1500 columns in it... yes queries will fly on a table that can't be built. You can't have 37 columns in a primary key. Date really isn't an acceptable name for a column. April Wells Oracle DBA Keep yourself well oiled with life, laughter, new ideas and action. Otherwise you will rust out. _Anonymous -Original Message- Sent: Thursday, October 10, 2002 7:34 PM To: Multiple recipients of list ORACLE-L Hi Dick, I have to disagree with you here. Particularly in the case where this sequence will see any sort of concurrency, from multiple concurrent sessions accessing it. This is due to the serialization on the SQ enqueue. This will cause far worse scalability issues than any I/O. Not that I/O is insignificant, but in this situation, serialization on the enqueue will be the real showstopper for scalability. As to losing the cached values, well, so what? If your design is such that it's important to have an unbroken contiguous sequence of numbers with no gaps, then I would argue that is a serious design flaw. Also, if that's your requirement, then a sequence is not appropriate, since it can and will end up causing gaps, the first time you roll back a transaction. Finally, as to sequences losing cached values, unless your instance crashes or does a shutdown abort, Oracle should not loose any sequence values. -Mark On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > Actually there is no IO penalty since Oracle has to treat the sequence just like > any table with the old LRU algorithm. I have several sequences with a cache of > 0 and they perform as well as those with a cache value. The big difference is > when you shut down the database and all of those cached values end up in the > trash. > > Dick Goulet > > Reply Separator > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > Date: 10/10/2002 1:38 PM > > I think that you will have an update to the sequence number EVERY time instead > of every 20 times. That's mean I/o for every nextval. > > Yechiel Adar > Mehish > - Original Message - > From: Tim Gorman > To: Multiple recipients of list ORACLE-L > Sent: Thursday, October 10, 2002 7:43 PM > Subject: Re: sequence numbers > > > CACHE 20 is the default, so if you remove the clause, it will have absolutely > no impact on performance or anything else... > > ...of course, I get the feeling that that wasn't the gist of your question, > was it? > - Original Message - > From: April Wells > To: Multiple recipients of list ORACLE-L > Sent: Wednesday, October 09, 2002 8:54 AM > Subject: sequence numbers > > > I have been given create scripts for sequences to be used in tables that > will be loaded via bulk loads. How huge is the potential performance hit if I > take out the cache 20? > > April Wells > Oracle DBA > There is neither good nor bad, but thinking makes it so. -Shakespeare > > > > > > > > > style="FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px"> > I think that you will have an update to the > sequence number EVERY time instead of every 20 times. That's mean I/o for every > nextval. > > Yechiel AdarMehish > style="BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; > PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> > - Original Message - > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: > mailto:Tim@;SageLogix.com" [EMAIL PROTECTED]>Tim Gorman > > To: mailto:ORACLE-L@;fatcity.com" > > [EMAIL PROTECTED]>Multiple recipients of list ORACLE-L > Sent: Thursday, October 10, 2002 7:43 > PM > Subject: Re: sequence numbers > > CACHE 20 is the default, so if you remove the clause, it > > will have absolutely no impact on performance or anything else... > > ...of course, I get the feeling that that wasn't the > gist of your question, was it? > style="BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; > PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> > - Original Message - > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: > black">From: > mailto:awells@;csedge.com" [EMAIL PROTECTED]>April Wells > > To: href="mailto:ORACLE-L@;fatcity.com" [EMAIL PROTECTED]>Multiple > recipients of list ORACLE-L > Sent: Wednesday, October 09, 2002 8:54 > AM > Subject: sequence numbers > > I have been given create scripts for > s
Re: Re[2]: sequence numbers
Hi Dick, I have to disagree with you here. Particularly in the case where this sequence will see any sort of concurrency, from multiple concurrent sessions accessing it. This is due to the serialization on the SQ enqueue. This will cause far worse scalability issues than any I/O. Not that I/O is insignificant, but in this situation, serialization on the enqueue will be the real showstopper for scalability. As to losing the cached values, well, so what? If your design is such that it's important to have an unbroken contiguous sequence of numbers with no gaps, then I would argue that is a serious design flaw. Also, if that's your requirement, then a sequence is not appropriate, since it can and will end up causing gaps, the first time you roll back a transaction. Finally, as to sequences losing cached values, unless your instance crashes or does a shutdown abort, Oracle should not loose any sequence values. -Mark On Thu, 2002-10-10 at 18:18, [EMAIL PROTECTED] wrote: > Actually there is no IO penalty since Oracle has to treat the sequence just like > any table with the old LRU algorithm. I have several sequences with a cache of > 0 and they perform as well as those with a cache value. The big difference is > when you shut down the database and all of those cached values end up in the > trash. > > Dick Goulet > > Reply Separator > Author: "Yechiel Adar" <[EMAIL PROTECTED]> > Date: 10/10/2002 1:38 PM > > I think that you will have an update to the sequence number EVERY time instead > of every 20 times. That's mean I/o for every nextval. > > Yechiel Adar > Mehish > - Original Message - > From: Tim Gorman > To: Multiple recipients of list ORACLE-L > Sent: Thursday, October 10, 2002 7:43 PM > Subject: Re: sequence numbers > > > CACHE 20 is the default, so if you remove the clause, it will have absolutely > no impact on performance or anything else... > > ...of course, I get the feeling that that wasn't the gist of your question, > was it? > - Original Message - > From: April Wells > To: Multiple recipients of list ORACLE-L > Sent: Wednesday, October 09, 2002 8:54 AM > Subject: sequence numbers > > > I have been given create scripts for sequences to be used in tables that > will be loaded via bulk loads. How huge is the potential performance hit if I > take out the cache 20? > > April Wells > Oracle DBA > There is neither good nor bad, but thinking makes it so. -Shakespeare > > > > > > > > > style="FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px"> > I think that you will have an update to the > sequence number EVERY time instead of every 20 times. That's mean I/o for every > nextval. > > Yechiel AdarMehish > style="BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; > PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> > - Original Message - > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: > mailto:[EMAIL PROTECTED]"; [EMAIL PROTECTED]>Tim Gorman > > To: mailto:[EMAIL PROTECTED]"; > > [EMAIL PROTECTED]>Multiple recipients of list ORACLE-L > Sent: Thursday, October 10, 2002 7:43 > PM > Subject: Re: sequence numbers > > CACHE 20 is the default, so if you remove the clause, it > > will have absolutely no impact on performance or anything else... > > ...of course, I get the feeling that that wasn't the > gist of your question, was it? > style="BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; > PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> > - Original Message - > style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: > black">From: > mailto:[EMAIL PROTECTED]"; [EMAIL PROTECTED]>April Wells > > To: href="mailto:[EMAIL PROTECTED]"; [EMAIL PROTECTED]>Multiple > recipients of list ORACLE-L > Sent: Wednesday, October 09, 2002 8:54 > AM > Subject: sequence numbers > > I have been given create scripts for > sequences to be used in tables that will be loaded via bulk loads. How > > huge is the potential performance hit if I take out the cache > 20? > > April Wells face="Courier New">Oracle DBA class=841194713-09102002>T class=841194713-09102002>here is neither good nor bad, but thinking makes it > > so. > -Shakespeare > -- -- Mark J. Bobak Oracle DBA [EMAIL PROTECTED] "It is not enough to have a good mind. The main thing is to use it well." -- Rene Descartes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark J. Bobak INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE
