How can we manage the gap in a primary key column created
by a sequence?
Ex:a company has empno as primary key generated by a
sequence and some employees leaves in between.What is the
best way to manage this gap?
Answers were Sorted based on User's Feedback
Answer / poornima
using cursor we find the gap in sequence, but this method
is quite complicated and slow process...
so we use some analyical fuctions such as lead and lag as
{LEAD | LAG} (value_expression, offset, default)
OVER ([PARTITION BY expr] ORDER BY expr)
Is This Answer Correct ? | 5 Yes | 0 No |
yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));
Table created.
now if you have the data in the table like follows.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt
now just do the following simple statement.
SQL> update test set sno=rownum;
5 rows updated.
now here is the o/p for eliminating the gap generated by the
sequence.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding
suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below
procedure
SQL> update test set sno=sql.nextval;
NOTE:sql is the sequence.
this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by
clause.
Is This Answer Correct ? | 1 Yes | 3 No |
1.What is inline function in oracle and its purpose? 2.What is the equivalent operator for "different from pattern" in oracle? 3. If you define a variable in oracle, how it will be available? [a. Until database shut down b. Until table deleted c. until session get expired]
What are advantages fo Stored Procedures?
How to transfer database(500 gb) of oracle enterprise edition to standard edition downtime is only 1 hour not using exp/imp option ?
SQLERRM is a a. Constraint b. Pre Defined Exception c. Pseduocolumn d. Constant e. None of Above.
what are indexes..how many types of index's are there and what are they?
7 Answers Green Info Solutions, TCS,
can u plz provide me oca sql dumps please i need them
Why use resource manager in Oracle?
How can we create the complete backup of data in the oracle.
0 Answers IPEC, Satyam, SunTec,
20. Using a set operator, display the client number of all clients who have never placed an order.
24. Display the order number for all orders whose average item cost is greater than the overall average item cost across all orders.
What is ceil and floor in oracle?
how will I find the first 5 highest salaried employees in each dept in oracle.