MIHIR KAR used Ask the Experts™
Hi Expert,

We create sequence with Trigger has good idea or simply used sequence start with 1, increment by 1 in oracle.

Please suggest which one is good choice and why ??

Thanks in Advance .
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
sequence start with 1, increment by 1 in oracle
This one is good.

But before that please tell us your complete requirement? What you are trying to achieve?
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016
>> We create sequence with Trigger
You create a sequence by means of a trigger? A sequence is usually not created by a trigger but during setup or modification of a table.
Most Valuable Expert 2012
Distinguished Expert 2018
I assume by trigger you mean go find the greatest value and add 1 to it.  That is HORRIBLE and should NEVER be done.

Why:  It can take a lot of SQL time to generate the MAX and it doesn't get you anything.   They can also lead to duplicate values.

Oracle sequences are fast to increment and cannot generate duplicates.

Assuming the next part of the question:
Neither by themselves will generate gapless numbers.  It is rare that you MUST have them.  Most of the time DBAs/Developers/Analysts just like them but really don't have a requirement to have them.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial