Oracle - Sync Sequence nextval using CONNECT - side effects?

Question

DB

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Action

After a data migration - from an old table to a new table - we created a new sequence and executed this command to sync it's next value to be equal to table maximum id.

SQL

-- SYNC SEQUENCE WITH TABLE
SELECT level, {SEQUENCE_NAME}.NEXTVAL
FROM dual
CONNECT BY level <= (
 SELECT MAX(ID)
 FROM {TABLE}
);

Question

1. Is there any side effect on calling this SQL - since the keyword CONNECT is used to make connections to DB ?
2. Is there a more efficient way to do such task?

Useful Links

Oracle - CONNECT Keywork - Reference

Answer 1

CONNECT in this context has nothing to do with database connections.

hierarchical_query_clause ::=

You can change the increment of a sequence to set it to a required value. Example:

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 increment by 100;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> alter sequence s1 increment by 1;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102

You can also use the undocumented RESTART clause, but that does not work in 11.2:

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 30 12:44:26 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create sequence s1;

Sequence created.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
         1

SQL> alter sequence s1 restart start with 101;

Sequence altered.

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       101

SQL> select s1.nextval from dual;

   NEXTVAL
----------
       102