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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY
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 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
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
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
+;G0N#0H-@T*#0H
end
--
Please see the official ORACLE-L FAQ: http://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: Re[2]: sequence numbers
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 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
RE: RE: Re[2]: sequence numbers
: 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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; === message truncated === __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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
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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:Tim;SageLogix.com [EMAIL PROTECTED]Tim Gorman/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:ORACLE-L;fatcity.com [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Thursday, October 10, 2002 7:43 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: sequence numbers/DIV DIVBR/DIV DIVFONT face=ArialCACHE 20 is the default, so if you remove the clause, it will have absolutely no impact on performance or anything else.../FONT/DIV DIVFONT face=Arial/FONTnbsp;/DIV DIVFONT face=Arial...of course, I get the feeling that that wasn't the gist of your question, was it?/FONT/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px
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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND
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 performancehit if I take out the cache 20? April Wells Oracle DBAThere is neither good nor bad, but thinking makes it so. -Shakespeare
Re: sequence numbers
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 - 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 performancehit if I take out the cache 20? April Wells Oracle DBAThere is neither good nor bad, but thinking makes it so. -Shakespeare
Re[2]: sequence numbers
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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Tim Gorman/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Thursday, October 10, 2002 7:43 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: sequence numbers/DIV DIVBR/DIV DIVFONT face=ArialCACHE 20 is the default, so if you remove the clause, it will have absolutely no impact on performance or anything else.../FONT/DIV DIVFONT face=Arial/FONTnbsp;/DIV DIVFONT face=Arial...of course, I get the feeling that that wasn't the gist of your question, was it?/FONT/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]April Wells/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Wednesday, October 09, 2002 8:54 AM/DIV DIV style=FONT: 10pt arialBSubject:/B sequence numbers/DIV DIVBR/DIV DIVSPAN class=841194713-09102002I have been given create scripts for sequences to be used in tables that will be loaded via bulk loads.nbsp; How huge is the potential performancenbsp;hit if I take out the cache 20?/SPAN/DIV DIVnbsp;/DIV PFONT face=Courier NewApril Wells/FONT BRFONT face=Courier NewOracle DBAnbsp;/FONTBRSPAN class=841194713-09102002FONT face=Courier NewTSPAN class=841194713-09102002here is neither good nor bad, but thinking makes it so. -Shakespeare/SPAN/FONT/SPAN/P/BLOCKQUOTE/BLOCKQUOTE/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: sequence numbers
There is no read penalty, but there is a indirect write penalty. Every nextval operation will start a recursive transaction that will generate redo and that needs to be flushed to disk. Anjo. [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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Tim Gorman/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Thursday, October 10, 2002 7:43 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: sequence numbers/DIV DIVBR/DIV DIVFONT face=ArialCACHE 20 is the default, so if you remove the clause, it will have absolutely no impact on performance or anything else.../FONT/DIV DIVFONT face=Arial/FONTnbsp;/DIV DIVFONT face=Arial...of course, I get the feeling that that wasn't the gist of your question, was it?/FONT/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]April Wells/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Wednesday, October 09, 2002 8:54 AM/DIV DIV style=FONT: 10pt arialBSubject:/B sequence numbers/DIV DIVBR/DIV DIVSPAN class=841194713-09102002I have been given create scripts for sequences to be used in tables that will be loaded via bulk loads.nbsp; How huge is the potential performancenbsp;hit if I take out the cache 20?/SPAN/DIV DIVnbsp;/DIV PFONT face=Courier NewApril Wells/FONT BRFONT face=Courier NewOracle DBAnbsp;/FONTBRSPAN class=841194713-09102002FONT face=Courier NewTSPAN class=841194713-09102002here is neither good nor bad, but thinking makes it so. -Shakespeare/SPAN/FONT/SPAN/P/BLOCKQUOTE/BLOCKQUOTE/BODY/HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
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 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META content=text/html; charset=iso-8859-1 http-equiv=Content-Type META content=MSHTML 5.00.2314.1000 name=GENERATOR STYLE/STYLE /HEAD BODY bgColor=#ff style=FONT: 10pt Times New Roman; MARGIN-LEFT: 2px; MARGIN-TOP: 2px DIVFONT face=Arial size=3I 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./FONT/DIV DIVnbsp;/DIV DIVYechiel AdarBRMehish/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Tim Gorman/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Thursday, October 10, 2002 7:43 PM/DIV DIV style=FONT: 10pt arialBSubject:/B Re: sequence numbers/DIV DIVBR/DIV DIVFONT face=ArialCACHE 20 is the default, so if you remove the clause, it will have absolutely no impact on performance or anything else.../FONT/DIV DIVFONT face=Arial/FONTnbsp;/DIV DIVFONT face=Arial...of course, I get the feeling that that wasn't the gist of your question, was it?/FONT/DIV BLOCKQUOTE dir=ltr style=BORDER-LEFT: #00 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px DIV style=FONT: 10pt arial- Original Message - /DIV DIV style=BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: blackBFrom:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]April Wells/A /DIV DIV style=FONT: 10pt arialBTo:/B A href=mailto:[EMAIL PROTECTED]; [EMAIL PROTECTED]Multiple recipients of list ORACLE-L/A /DIV DIV style=FONT: 10pt arialBSent:/B Wednesday, October 09, 2002 8:54 AM/DIV DIV style=FONT: 10pt arialBSubject:/B sequence numbers/DIV DIVBR/DIV DIVSPAN class=841194713-09102002I have been given create scripts for sequences to be used in tables that will be loaded via bulk loads.nbsp; How huge is the potential performancenbsp;hit if I take out the cache 20?/SPAN/DIV DIVnbsp;/DIV PFONT face=Courier NewApril Wells/FONT BRFONT
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 performancehit if I take out the cache 20? April Wells Oracle DBAThere is neither good nor bad, but thinking makes it so. -Shakespeare CSUA 2002: Imagine the Possibilities! Corporate Systems Annual Users Association Conference When: October 2-4, 2002 Where: Caribe Royale Resort Orlando, FL USA For more information go to www.csedge.com The information contained in this e-mail is strictly confidential and for the intended use of the addressee only; it may also be legally privileged and/or price sensitive. Notice is hereby given that any disclosure, use or copying of the information by anyone other than the intended recipient is prohibited and may be illegal. If you have received this message in error, please notify the sender immediately by return e-mail. Corporate Systems, Inc. has taken every reasonable precaution to ensure that any attachment to this e-mail has been swept for viruses. We accept no liability for any damage sustained as a result of software viruses and advise you carry out your own virus checks before opening any attachment.
Re: sequence numbers
If the sequence is not cached then Oracle has to generate a new sequence number for every record inserted (CPU cycles). NOORDER is the default so that won't slow you up. If you're doing bulk loads why not cache the sequence numbers? Is it important that there be no gaps? Even with NOCACHE you can get gaps. If a sequence number is generated but the row (insert or create) is not COMMITed the sequence number is not rolled back. Unless there are fewer than 100 rows to be inserted I'd go with some caching. April Wells awells To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @csedge.com cc: Sent by: rootSubject: sequence numbers 10/09/2002 10:54 AM Please respond to ORACLE-L 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 Attachment Removed : InterScan_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day 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: sequence numbers
This was more of a defense question than anything
I read the docs, but without knowing what volume we will be expecting, I
don't want stuff being aged out either. I don't guess making the
sequence_cache_entries double what I figure we will need will cause anything
horrible... but It was put to me to try (without really TRYING) to determine
what the ramifications were.
I'm really not so worried about gaps... more loosing the lost numbers. This
will be a warehouse, and with the way things are being defined, I think we
could run out if we load the way we anticipate with the way these are being
built.
Some tables may not have any records loaded, or less than 100... but that
may be client specific, too...
Do you get the feeling that business rules should have come somewhat earlier
in the project???
=)
Thank you Thomas...
April Wells
Oracle DBA
Keep yourself well oiled with life, laughter, new ideas and action.
Otherwise you will rust out. _Anonymous
-Original Message-
Sent: Wednesday, October 09, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L
If the sequence is not cached then Oracle has to generate a new sequence
number for every record inserted (CPU cycles). NOORDER is the default so
that won't slow you up. If you're doing bulk loads why not cache the
sequence numbers? Is it important that there be no gaps? Even with
NOCACHE you can get gaps. If a sequence number is generated but the row
(insert or create) is not COMMITed the sequence number is not rolled back.
Unless there are fewer than 100 rows to be inserted I'd go with some
caching.
April Wells
awells To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
@csedge.com cc:
Sent by: rootSubject: sequence numbers
10/09/2002
10:54 AM
Please
respond to
ORACLE-L
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
Attachment Removed : InterScan_Disclaimer.txt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
+;G0N#0H-@T*#0H
end
--
Please see the official ORACLE-L FAQ: http://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: sequence numbers
Order and noorder options are relevant only for the OPS/RAC configurations because that is the only way that the sequence numbers may be returned out of order. If the sequence is not cached, then you'll suffer a logical read and a physical write each time you query the sequence. What will be a performance impact? It depends primarily on you disk configuration, whether your database is a file system one or is it on raw devices and how much cache are you having on your disk controllers. -Original Message- From: Thomas Day [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 11:54 AM To: Multiple recipients of list ORACLE-L Subject: Re: sequence numbers If the sequence is not cached then Oracle has to generate a new sequence number for every record inserted (CPU cycles). NOORDER is the default so that won't slow you up. If you're doing bulk loads why not cache the sequence numbers? Is it important that there be no gaps? Even with NOCACHE you can get gaps. If a sequence number is generated but the row (insert or create) is not COMMITed the sequence number is not rolled back. Unless there are fewer than 100 rows to be inserted I'd go with some caching. April Wells awells To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @csedge.com cc: Sent by: rootSubject: sequence numbers 10/09/2002 10:54 AM Please respond to ORACLE-L 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 Attachment Removed : InterScan_Disclaimer.txt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas Day 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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: sequence numbers
Increasing sequence_cache_entries will minimize the demand for SQ enqueue
that Oracle uses to internally bump the seq numbers.
- Kirti
-Original Message-
Sent: Wednesday, October 09, 2002 11:29 AM
To: Multiple recipients of list ORACLE-L
This was more of a defense question than anything
I read the docs, but without knowing what volume we will be expecting, I
don't want stuff being aged out either. I don't guess making the
sequence_cache_entries double what I figure we will need will cause anything
horrible... but It was put to me to try (without really TRYING) to determine
what the ramifications were.
I'm really not so worried about gaps... more loosing the lost numbers. This
will be a warehouse, and with the way things are being defined, I think we
could run out if we load the way we anticipate with the way these are being
built.
Some tables may not have any records loaded, or less than 100... but that
may be client specific, too...
Do you get the feeling that business rules should have come somewhat earlier
in the project???
=)
Thank you Thomas...
April Wells
Oracle DBA
Keep yourself well oiled with life, laughter, new ideas and action.
Otherwise you will rust out. _Anonymous
-Original Message-
Sent: Wednesday, October 09, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L
If the sequence is not cached then Oracle has to generate a new sequence
number for every record inserted (CPU cycles). NOORDER is the default so
that won't slow you up. If you're doing bulk loads why not cache the
sequence numbers? Is it important that there be no gaps? Even with
NOCACHE you can get gaps. If a sequence number is generated but the row
(insert or create) is not COMMITed the sequence number is not rolled back.
Unless there are fewer than 100 rows to be inserted I'd go with some
caching.
April Wells
awells To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
@csedge.com cc:
Sent by: rootSubject: sequence numbers
10/09/2002
10:54 AM
Please
respond to
ORACLE-L
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
Attachment Removed : InterScan_Disclaimer.txt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
+;G0N#0H-@T*#0H
end
--
Please see the official ORACLE-L FAQ: http://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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City
RE: sequence numbers
LOL --- Actually, I can't laugh too much because this is, sadly, too true!
April Wells
awells To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
@csedge.com cc:
Sent by: rootSubject: RE: sequence numbers
10/09/2002
12:28 PM
Please
respond to
ORACLE-L
snip
Do you get the feeling that business rules should have come somewhat
earlier
in the project???
=)
Thank you Thomas...
April Wells
Oracle DBA
Keep yourself well oiled with life, laughter, new ideas and action.
Otherwise you will rust out. _Anonymous
-Original Message-
Sent: Wednesday, October 09, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L
If the sequence is not cached then Oracle has to generate a new sequence
number for every record inserted (CPU cycles). NOORDER is the default so
that won't slow you up. If you're doing bulk loads why not cache the
sequence numbers? Is it important that there be no gaps? Even with
NOCACHE you can get gaps. If a sequence number is generated but the row
(insert or create) is not COMMITed the sequence number is not rolled back.
Unless there are fewer than 100 rows to be inserted I'd go with some
caching.
April Wells
awells To: Multiple recipients of
list
ORACLE-L [EMAIL PROTECTED]
@csedge.com cc:
Sent by: rootSubject: sequence numbers
10/09/2002
10:54 AM
Please
respond to
ORACLE-L
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
Attachment Removed : InterScan_Disclaimer.txt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
begin 666 InterScan_Disclaimer.txt
M5AE(EN9F]R;6%T:6]N(-O;G1A:6YE9!I;B!T:ES(4M;6%I;!IR!S
M=')I8W1L2!C;VYF:61E;G1I86P@86YD(9OB!T:4@:6YT96YD960@=7-E
M(]F('1H92!A91R97-S964@;VYL3L@:70@;6%Y(%LV\@8F4@;5G86QL
M2!PFEV:6QE9V5D(%N9]OB!PFEC92!S96YS:71I=F4N(!.;W1I8V4@
M:7,@:5R96)Y(=I=F5N('1H870@86YY(1IV-L;W-UF4L('5S92!OB!C
M;W!Y:6YG(]F('1H92!I;F9OFUA=EO;B!B2!A;GEO;F4@;W1H97(@=AA
M;B!T:4@:6YT96YD960@F5C:7!I96YT(ES('!R;VAI8FET960@86YD(UA
M2!B92!I;QE9V%L+B @268@6]U(AA=F4@F5C96EV960@=AIR!M97-S
M86=E(EN(5RF]R+!P;5AV4@;F]T:69Y('1H92!S96YD97(@:6UM961I
M871E;'D@8GD@F5T=7)N(4M;6%I;X*D-OG!OF%T92!37-T96US+!)
M;F,N(AAR!T86ME;B!E=F5R2!R96%S;VYA8FQE('!R96-A=71I;VX@=\@
M96YS=7)E('1H870@86YY(%T=%C:UE;G0@=\@=AIR!E+6UA:6P@:%S
M()E96X@W=E'0@9F]R('9IG5S97,N(!792!A8V-E'0@;F\@;EA8FEL
M:71Y(9OB!A;GD@9%M86=E('-UW1A:6YE9!AR!A(')EW5L=!O9B!S
M;V9T=V%R92!V:7)UV5S(%N9!A9'9IV4@6]U(-AG)Y(]U=!Y;W5R
M(]W;B!V:7)UR!C:5C:W,@8F5F;W)E(]P96YI;F@86YY(%T=%C:UE
+;G0N#0H-@T*#0H
end
--
Please see the official ORACLE-L FAQ: http
RE: sequence numbers
I'm really not so worried about gaps... more loosing the lost numbers.
This
will be a warehouse, and with the way things are being defined, I think
we
could run out if we load the way we anticipate with the way these are
being
built.
April,
Do the math. :)
I did this exercise once to allay developers and mgrs fears that
using a sequence would cause problems down the road when the
sequence 'ran out'.
We standardized on a 12 digit integer for primary keys.
12 digits allows a maximum key value of (10 * 10^11)-1, or .
If you were inserting 1000 rows per second, you sequence would
run out of values in 9.999 seconds.
( ( 10 * 10^11 ) -1 ) / 1000 = 9.999
If your database did this 24*7 for 31.7 years, the sequence would indeed
run out of values:
9.999 / ( 365 * 24 * 60 * 60 ) = 31.7
If that's not enough time, use more digits.
The following ( very short ) perl script can help you determine how
big is big enough.
=
#!/usrbin/perl
my $secondsPerYear = 365 * 24 * 60 * 60;
my $insertsPerSecond = 1000;
foreach my $digits ( 12 .. 38 ) {
my $yearsToExhaustion = (
(
( 10 * 10**($digits-1) ) -1
) / $insertsPerSecond
) / $secondsPerYear;
printf(Digits: %3i Years: %36.4f\n, $digits, $yearsToExhaustion);
}
=
Jared
April Wells [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/09/2002 09:28 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: sequence numbers
This was more of a defense question than anything
I read the docs, but without knowing what volume we will be expecting, I
don't want stuff being aged out either. I don't guess making the
sequence_cache_entries double what I figure we will need will cause
anything
horrible... but It was put to me to try (without really TRYING) to
determine
what the ramifications were.
I'm really not so worried about gaps... more loosing the lost numbers.
This
will be a warehouse, and with the way things are being defined, I think we
could run out if we load the way we anticipate with the way these are
being
built.
Some tables may not have any records loaded, or less than 100... but that
may be client specific, too...
Do you get the feeling that business rules should have come somewhat
earlier
in the project???
=)
Thank you Thomas...
April Wells
Oracle DBA
Keep yourself well oiled with life, laughter, new ideas and action.
Otherwise you will rust out. _Anonymous
-Original Message-
Sent: Wednesday, October 09, 2002 10:54 AM
To: Multiple recipients of list ORACLE-L
If the sequence is not cached then Oracle has to generate a new sequence
number for every record inserted (CPU cycles). NOORDER is the default so
that won't slow you up. If you're doing bulk loads why not cache the
sequence numbers? Is it important that there be no gaps? Even with
NOCACHE you can get gaps. If a sequence number is generated but the row
(insert or create) is not COMMITed the sequence number is not rolled back.
Unless there are fewer than 100 rows to be inserted I'd go with some
caching.
April Wells
awells To: Multiple recipients of
list
ORACLE-L [EMAIL PROTECTED]
@csedge.com cc:
Sent by: rootSubject: sequence numbers
10/09/2002
10:54 AM
Please
respond to
ORACLE-L
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
Attachment Removed : InterScan_Disclaimer.txt
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
--
Please see the official ORACLE-L FAQ: http://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
Re: resetting sequence numbers
Yeah? What if he had dropped the sequence, recreated it, and goddamn application didn't work because he had lost goddamn grants? Good if he knew what were these grants. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 05, 2002 4:20 PM ah jeez, you ferrin car drivers, always making thangs complicated when all you need is a big hammer cubic inch approach. What next? Setting maxvalue equal to the current number and selecting one number? Renaming the sequence and creating a synonym to point to another? Asking WHY he needs to do this, only to find out that the code is flawed since it should not rely on absolute serialization from a sequence in the first place, for god's sake? Don't you know that promoting alternate lifestyle DBA ideas you are encouraging the inexperienced to question authority and addin' to the moral corruption of Ameruca? Stop poisonin the newbies with fancy ideas about reverse select and such as that. If recreate was good enuf for us, it is darn sure good enuf for them! end jingoistic rural reactionary rant I slay me. I am gonna be at work in one hour, and then we can DO STATS! --- Larry Elkins [EMAIL PROTECTED] wrote: Jack, But then you have to redo grants, any invalidations. No big deal automating. But if you set the increment to a negative to take you right back down to zero, then set the increment to 1 (or whatever it was), there you go, no dropping the sequence. 10 to 1 odds Jared posts a nice little script that automates this second approach ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey Sent: Friday, July 05, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: resetting sequence numbers drop sequence, recreate sequence, rinse, repeat. Jack --- Malik, Fawzia [EMAIL PROTECTED] wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: resetting sequence numbers
See: http://www.cybcon.com/~jkstill/util/reset_sequence/reset_sequence.html Jared On Friday 05 July 2002 05:23, Malik, Fawzia wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: resetting sequence numbers
Alexandre, Take it easy, just a little friday humor. All ways have their advantages and disadvantages. jack --- Alexandre Gorbatchev [EMAIL PROTECTED] wrote: Yeah? What if he had dropped the sequence, recreated it, and goddamn application didn't work because he had lost goddamn grants? Good if he knew what were these grants. Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 05, 2002 4:20 PM ah jeez, you ferrin car drivers, always making thangs complicated when all you need is a big hammer cubic inch approach. What next? Setting maxvalue equal to the current number and selecting one number? Renaming the sequence and creating a synonym to point to another? Asking WHY he needs to do this, only to find out that the code is flawed since it should not rely on absolute serialization from a sequence in the first place, for god's sake? Don't you know that promoting alternate lifestyle DBA ideas you are encouraging the inexperienced to question authority and addin' to the moral corruption of Ameruca? Stop poisonin the newbies with fancy ideas about reverse select and such as that. If recreate was good enuf for us, it is darn sure good enuf for them! end jingoistic rural reactionary rant I slay me. I am gonna be at work in one hour, and then we can DO STATS! --- Larry Elkins [EMAIL PROTECTED] wrote: Jack, But then you have to redo grants, any invalidations. No big deal automating. But if you set the increment to a negative to take you right back down to zero, then set the increment to 1 (or whatever it was), there you go, no dropping the sequence. 10 to 1 odds Jared posts a nice little script that automates this second approach ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey Sent: Friday, July 05, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: resetting sequence numbers drop sequence, recreate sequence, rinse, repeat. Jack --- Malik, Fawzia [EMAIL PROTECTED] wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an === message truncated === __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself
Re: resetting sequence numbers
Close Larry. I posted the URL to the nice little script. :) Jared On Friday 05 July 2002 06:19, Larry Elkins wrote: Jack, But then you have to redo grants, any invalidations. No big deal automating. But if you set the increment to a negative to take you right back down to zero, then set the increment to 1 (or whatever it was), there you go, no dropping the sequence. 10 to 1 odds Jared posts a nice little script that automates this second approach ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey Sent: Friday, July 05, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: resetting sequence numbers drop sequence, recreate sequence, rinse, repeat. Jack --- Malik, Fawzia [EMAIL PROTECTED] wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: resetting sequence numbers
Dropping the sequence is unncessary. see http://www.cybcon.com/~jkstill/util/reset_sequence/reset_sequence.html Jared On Friday 05 July 2002 06:19, Alexandre Gorbatchev wrote: Fawzia, RTFM: To restart the sequence at a different number, you must drop and re-create it. HTH Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, July 05, 2002 2:23 PM Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: resetting sequence numbers
On Friday 05 July 2002 07:23, Mercadante, Thomas F wrote: Larry, I'm not as good as Jared, but maybe a little faster! Check the function below out: Faster? I published this 2 years ago. ;) Jared CREATE OR REPLACE FUNCTION Reset_Seq(in_seq_name IN VARCHAR2) RETURN NUMBER IS current_value NUMBER; TYPE refRS IS REF CURSOR; p_refRS refRS; select_str VARCHAR2(300) := 'select ' || in_seq_name || '.nextval from dual'; BEGIN SELECT last_number INTO current_value FROM USER_SEQUENCES WHERE sequence_name = UPPER(in_seq_name); EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || in_seq_name || ' INCREMENT BY ' || (current_value-1) * -1; OPEN p_refRS FOR select_str; FETCH p_refRS INTO current_value; CLOSE p_refRS; EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || in_seq_name || ' INCREMENT BY 1'; RETURN 1; END; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, July 05, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Jack, But then you have to redo grants, any invalidations. No big deal automating. But if you set the increment to a negative to take you right back down to zero, then set the increment to 1 (or whatever it was), there you go, no dropping the sequence. 10 to 1 odds Jared posts a nice little script that automates this second approach ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jack Silvey Sent: Friday, July 05, 2002 8:03 AM To: Multiple recipients of list ORACLE-L Subject: Re: resetting sequence numbers drop sequence, recreate sequence, rinse, repeat. Jack --- Malik, Fawzia [EMAIL PROTECTED] wrote: Hi, Is there a way of resetting sequence numbers back to zero???The application we have needs to have the sequence numbers reset... Thanks in advance Rgds Fawzia ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Malik, Fawzia INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
