Cache or nocache?

[evergreean43]

I am creating a survey in Oracle 9i database with around 35 questions and around 1000 People will input within a week time.
Please advise if nocache or cache is okay for when I create my sequence?

Here is how I would do if it is nocache?

create sequence my_seq
start with 0 increment by 1
minvalue 1
nocache cycle order;

Here is how I would do if it is cache?

create sequence my_seq
start with 0 increment by 1
minvalue 1
cache cycle order;

 

[SantaMufasa]

Evergreean,

"CACHE" is faster/more efficient since Oracle does not need to access the database each time a request comes through for a sequence number, but there is a very slight risk that a gap in your sequence numbers may result if the database gets shutdown (either gracefully or otherwise) in the middle of your cache.

But since you should not be building any logic that depends upon an unbroken string of sequence values, you should be fine.

Also, not that it makes any difference, but your CYCLE parameter means that after you reach the sequence's MAXVALUE (1 Octillion minus 1, i.e., 10[sup]27[/sup] - 1), Oracle will start over the sequence's values at "1" rather than throw an error.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

With the very low number of inserts, having cycle or nocycle will make no noticeable difference. However if you were inputing 1000 people a minute it would make a large difference. Personally, when I am using a sequence to generate a synthetic key I always use cycle. If the cached value is flushed for whatever reason, who cares. As Dave said above you have so many available sequence numbers that you will never run out of sequences. That is assuming that you did not artificially limit the size of your available sequences by making the column size small.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

...having cycle or nocycle will make no noticeable difference.

Don't you mean CACHE or NOCACHE? CYCLE versus NOCYCLE has zero effect on performance.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Sorry, I miss typed... you are right (as always)

Bill
Oracle DBA/Developer
New York State, USA