I logged a pretty bad bug where togging around the create/edit Table dialog was resetting form properties back to defaults, like your column name would go back to 'column16' if you clicked in and out of the columns page.
Hopefully that is the latest. I try to keep it up to date in the hopes that the problem was fixed. :-)
Thanks for your help.
1 person found this helpful
If the identify column is never to be published to users you could start defining it as a raw(32) and a default of sys_guid(). crude tests I have performed equate to roughly the same performance as an uncached sequence and a trigger.
SQL> SQL> CREATE TABLE test1 ( 2 ID RAW(32) DEFAULT sys_guid() 3 , n number); Table TEST1 created. Elapsed: 00:00:00.019 SQL> SQL> CREATE TABLE test2 ( 2 ID NUMBER 3 ,N NUMBER); Table TEST2 created. Elapsed: 00:00:00.015 SQL> SQL> CREATE SEQUENCE sequence2 2 START WITH 1 3 INCREMENT BY 1; Sequence SEQUENCE2 created. Elapsed: 00:00:00.005 SQL> SQL> CREATE TRIGGER test2_BI 2 BEFORE INSERT 3 ON test2 4 for each row 5 BEGIN 6 :new.id := sequence2.nextval; 7 END; 8 / Trigger TEST2_BI compiled Elapsed: 00:00:00.061 SQL> SQL> SQL> BEGIN 2 -- testing default on sys_guid 3 FOR x IN 1..10 4 loop 5 FOR I IN 1..10000 6 LOOP 7 insert into test1 (n) values (ora_hash(systimestamp)); 8 END LOOP; 9 COMMIT; 10 END LOOP; 11 commit; 12 end; 13 /
SQL> BEGIN 2 -- testing default on sys_guid 3 FOR x IN 1..10 4 loop 5 FOR I IN 1..10000 6 LOOP 7 insert into test1 (n) values (ora_hash(systimestamp)); 8 END LOOP; 9 COMMIT; 10 END LOOP; 11 commit; 12 end; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:09.828
SQL> BEGIN 2 -- testing default on sys_guid 3 FOR x IN 1..10 4 loop 5 FOR I IN 1..10000 6 LOOP 7 insert into test1 (n) values (ora_hash(systimestamp)); 8 END LOOP; 9 COMMIT; 10 END LOOP; 11 commit; 12 end; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:10.032
SQL> BEGIN 2 -- testing default on sequence 3 FOR x IN 1..10 4 loop 5 FOR I IN 1..10000 6 LOOP 7 insert into test2 (n) values (ora_hash(systimestamp)); 8 END LOOP; 9 COMMIT; 10 END LOOP; 11 commit; 12 END; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:16.103
SQL> BEGIN 2 -- testing default on sequence 3 FOR x IN 1..10 4 loop 5 FOR I IN 1..10000 6 LOOP 7 insert into test2 (n) values (ora_hash(systimestamp)); 8 END LOOP; 9 COMMIT; 10 END LOOP; 11 commit; 12 END; 13 / PL/SQL procedure successfully completed. Elapsed: 00:00:09.455
My personal preference is for sys_guid() as you never have to worry about 2 additional database objects (sequence and trigger) and it is safe to be unique across all databases.
Thank you Paul for the idea. I never realized (or thought of) using a GUID for a primary key. It is a good thing to know.
My only concern is that sometimes I have thousands of records flowing across our network, and I would be a bit concerned how much those extra 12 bytes per record would equate to a slower performance.
But who knows, maybe the difference would be negligible...at least compared to the problems from a failed insert (with my current method that keeps failing)!
1 person found this helpful
GUID's for PK's is a bit of a religious war...there are strong opinions on both sides.
I am sure that it is. I would only use GUID if it was only ever to be used internally in the system, or if I had two way replication and needed to guarantee primary key uniqueness. There is still a time and place for sequences
Hmmm...no answer yet...so please let me ask the following...
PLEASE, for everyone who reads this who uses the "Edit Table" function within SQL Developer to add Identity Columns (Column Sequences) please post your answer to this question...
Have you ever experienced what I have described above (the Identity Column on your primary key being set back to "Type: None" although no person is changing it)?
I ask because it happens to me every single day that I do much table editing. Knowing if it is a unique problem to me, or not, would really shed some light on how to fix it.
While the GUI is a convenient method to create and build the object, I would encourage you to learn the SQL code underneath that it executes.
Perhaps your suggestion uncovers yet another religious war... (Okay, maybe not quite. )
May I ask you a question: have you learned the C++ code that executes underneath the SQL code that you execute?
To me, using the built in editor is a time saving tool, as so many other tools we all use in everyday life. And while I understand many complex things in my life, I cannot learn everything just for the sake of learning...there just isn't enough time. That is, unless I am convinced there is some added benefit to learning it.
I've tried twice, once using 18.1...what we're working on now to release later this quarter, and once using 17.4.
What I have: a predefined table with normal columns.
Right-click > EDIT.
Add a new column. Data type INT. Set up as IDENTITY. Start with 1, increment 1.
Now click around and change up other columns..increase size of a varchar2. Change default for a date to SYSDATE.
I then go and add an additional column.
Now I go back to inspect the identity column. It's still intact. And the generated DDL is also good.
You're going to need an exact, reproducible scenario for anyone here to take a look. And if you come up with one, that would be a bug. And that needs to get reported to My Oracle Support.
Thanks for trying Jeff.
I have tried to determine a reproducible sequence of events, but have not been able to do so. I realize having that would be ideal. When I have time I will try to figure it out.
The problem occurred today again and although I do not know all the events that led up to it, I learned a couple of things:
1. It seems to require the inserting of a row into the table (and then the opening of the table in the Edit function) to trigger the issue.
2. When it occurred, I checked the trigger that had been auto-created by the GUI, and it had been altered. It looked like this:
create or replace TRIGGER "PLMTEST1".LEI_EC_CHECKLISTS_TRG
BEFORE INSERT ON LEI_EC_CHECKLISTS
FOR EACH ROW
Obviously the "NULL;" line should be the code to insert the next value from the sequence. So I wonder what could have altered it?
Also, after reading Tom Kyte's suggestion here: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6575961912937
I decided I will just use a sequence alone...no trigger. So I learned how to do so using SQL (very easy) and will no longer be using the function in SQL Developer/Edit to do it for me. But I have maybe 35 other tables still using the old way, which I am in no hurry to change.
it seems that every so often when I make a small change to a table (a change like deleting an unused column) it resets the Identity Column on my primary key back to "Type: None".
Try this simple test case. First the set-up steps...
1. Open a connection (e.g., a Basic connection type, default JDBC thin driver, HR schema on a local 11g XE instance)
2. On Tables node, right-click to bring up the context menu, then click on New Table.
3. Use the default table name, say TABLE1, with the default COLUMN1 already populated.
4. Click on the toolbar's Add Column icon (green plus-sign) to add COLUMN2.
5. Return the focus to the COLUMN1 row, and update the Identity Column tab to set Type to Column Sequence.
6. Exit the editor by clicking on OK.
Now, continuing, Scenario A:
7. Edit TABLE1 again.
8. Immediately place the focus on COLUMN2 and delete it by clicking on the toolbar's Remove Column icon (red X).
9. Exit the editor by clicking on OK. The issue you are suffering from occurs (Identity Column tab will show Type is None).
Or, Scenario B:
7. Edit TABLE1 again.
8. Immediately place the focus on COLUMN1, click on the Identity Column tab and see that the column sequence is still selected.
9. Now place the focus on COLUMN2 and delete it by clicking on the toolbar's Remove Column icon (red X).
10. Exit the editor by clicking on OK. The issue you are suffering from does not occur.
If you had multiple columns identified as column sequences in the same table, possibly the Scenario B technique would also work if you checked each such column prior to deleting a column (or whatever other change "triggers" the same behavior) but I did not test it.
A prior discussion, Issues with Create Table , resulted in a bug being logged, probably the one Jeff references in his first post above, that is still open. Title is "FORUM: NEW TABLE DIALOG ADDING COLUMNS VIA DOWN ARROW CAN IGNORE USER COL NAME"