0

I'm trying to generate a sorted GUID as a primary key in Oracle. In SQL Server I could use one of the following to sort the rows physically

  1. By clustered primary key as a unique identifier.
  2. By NEWSEQUENTIALID.

I have searched for an Oracle equivalent but failed to find a solution. I know about Is there a way to create an auto-incrementing Guid Primary Key in an Oracle database?, but there's no indication whether SYS_GUID() is sorted.

How could I create a sequential primary key in Oracle?

Community
  • 1
  • 1
YOusaFZai
  • 698
  • 5
  • 21
  • please refer to [question](http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2570145300346198113) – eatSleepCode Dec 26 '13 at 07:08
  • thanx. but its not helpful. – YOusaFZai Dec 26 '13 at 07:16
  • @YaroslavShabalin question is duplicate but solution provided isn't helpful. – YOusaFZai Dec 26 '13 at 07:38
  • 1
    Then please specify your exact problem and explain why that solution is not appropriate. By the time I see that you want to generate sorted GUID as primary key and the question I linked to IMHO answers your question. – Yaroslav Shabalin Dec 26 '13 at 07:52
  • in solution you referred is only generating guid that was not what i required i want to sort them physically as done by clustered Primary key in SQL. – YOusaFZai Dec 26 '13 at 07:57
  • 1
    @YOusaFZai To sort records physically take a look at index organized tables or to sorted hash clusters in Oracle. – Yaroslav Shabalin Dec 26 '13 at 08:01
  • 1
    Firstly can you please stop using the phrase SQL when you mean SQL Server/T-SQL; it's not helpful. SQL is simply a language used by many RDBMS, Oracle being one of them so saying you can do something in SQL but not in Oracle is saying that you can do something in SQL but not in SQL. As you can see, this is confusing. – Ben Dec 26 '13 at 09:08
  • @Ben thanx for correcting me. its my bad.... have any solution for above mentioned problem? – YOusaFZai Dec 26 '13 at 09:25
  • 2
    This question is not a duplicate. It builds on the question it was marked as a duplicate of and asks other things. – Ben Dec 26 '13 at 11:14
  • @Ben Surely it is not duplicate when it comes to comments, but in the way OP initially asked - it is definitely duplicate. Mainly because it is totally unclear that OP needs sorted physical storage (unless somebody knows well both MS SQL Server and Oracle). If OP could add some details (I don't know if it is possible now when it is marked as dup) then along with your answer it would be nice contribution to SO. – Yaroslav Shabalin Dec 26 '13 at 14:31
  • It's possible @Yaroslav, and it doesn't have to be the OP. I moved all the comments into the question... if you look at the edit history. – Ben Dec 26 '13 at 17:30

2 Answers2

4

If you want to create a GUID then SYS_GUID() is what you should be using, you can create this in a table as per the linked question. It's unclear from the documentation whether SYS_GUID() is incrementing. It might be but that's not really a statement that imparts trust.

The next part of your question (and some comments) keeps asking about clustered primary keys. This concept does not exist in the same way in Oracle as it does in SQL Server and Sybase. Oracle does have indexed organized tables (IOT) ...

... a table stored in a variation of a B-tree index structure... rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. Thus, the index is the data, and the data is the index.

There are plenty of uses for IOTs but it's worth bearing in mind you're altering the physical structure of the database on the disk for "performance reasons". You're doing the ultimate of all premature optimizations using something that has both negative and positive aspects. Read the documentation and be sure that this is what you want to do.

I would generally use an IOT only when you don't care about DML performance but when you do a lot of range scans, or you need to order by the primary key. You create an IOT in the same way as you would an ordinary table, but because everything you want is now part of the table everything goes in your table definition:

create table test_table (
   id raw(32) default sys_guid()
 , a_col varchar2(50)
 , constraint pk_my_iot primary key (id)
   ) organization index;

It's worth noting that even with an IOT you must use an explicit ORDER BY in order to guarantee returned order. However, because of the way this is stored Oracle can table a few short cuts:

select *
  from ( select * 
           from test_table 
           order by id )
 where rownum < 2

SQL Fiddle.

As with everything, test, don't assume that this is the structure you want.

Ben
  • 51,770
  • 36
  • 127
  • 149
1

Oracle has as SYS_GUID() function, which generates a 16-byte RAW datatype. But, I'm not sure what you mean by "sorted GUID". Can you elaborate?

Do you mean you need each generated GUID to sort "after" the previously generated GUID? I looked at the SYS_GUID() function, and it seems to generate GUIDs in sorted order, but looking at the documentation, I don't see anything that says that is guaranteed.

If I understand your question correctly, I'm not sure it's possible.

You may be able to use SYS_GUID() and prepend a sequence, to get your desired sort order?

Can you explain more about your use case?

Adding the following in response to comment:

Ok, now I think I understand. What I think you want, is something called an IOT, or Index Organized Table, in Oracle. It's a table that has an index strucure, and all data is clustered, or grouped by the primary key. More information is available here: http://docs.oracle.com/cd/E16655_01/server.121/e17633/indexiot.htm#CNCPT721

I think that should do what you want.

Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67