5/8/2023 0 Comments Oracle scratchpad![]() ![]() ![]() I can simply modify the PK or Unique Key constraint to use the new index with the USING INDEX clause: SQL> alter table ziggy modify constraint ziggy_pk using index ziggy_id_i2 Īnd then switch the visibility of the two indexes: SQL> alter index ziggy_id_i1 invisible Now comes the interesting bit (for me anyways). (Note the following also applies for Unique constraints as well). With 12c, I can pre-create another index with a different characteristic on the same column, I just have to initially make it Invisible: SQL> create unique index ziggy_id_i2 on ziggy(id) invisible online I then subsequently decide to replace the existing Non-Unique index policing the PK constraint with say a Unique Index instead. SQL> alter table ziggy add constraint ziggy_pk primary key(id) SQL> create index ziggy_id_i1 on ziggy(id) SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level commit Partition zigg圓 values less than (maxvalue)) Partition ziggy2 values less than (2000), (partition ziggy1 values less than (1000), I’m just going to setup the same demo as I used in the initial multiple indexes discussion where I have a Non-Unique index policing the PK constraint of a table: SQL> create table ziggy (id number, name varchar2(30)) Well, as Jonathan described, there’s an easier alternative that doesn’t require so much stuffing around with the PK constraint. I previously blogged about the new 12c capability of having Multiple Indexes On The Same Column List and demonstrated how it was now possible to “quickly” swap the index on say a Primary Key constraint by pre-building a new index (say a Unique index to replace an existing Non-Unique index), then drop/disable the constraint and old index, make the new index visible and re-applying the PK constraint. Reading a recent posting of his on Modifying Primary Keys was one such moment where I went “wow, I didn’t know you could do that” !! ![]() I doubt many folks who read my blog don’t already know what a fantastic source of information this is. One of the places I constantly turn to in order to learn something new is Jonathan Lewis’s Oracle Scratchpad blog. One of these key habits I believe is the constant need to keep learning and to keep growing professionally. One of these days, I might blog about a presentation I put together a while ago on the common habits of highly successful DBAs (or technologists in general). I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. Modify Primary Key Using Index (Learning To Fly) FebruPosted by Richard Foote in 12c, Modify Primary Key Using Index, Oracle Indexes, Primary Key, Richard's Musings. ![]()
0 Comments
Leave a Reply. |