kpvn77
asked on
Clustered index in Sybase to Oracle
Hello Experts,
Assume I have the following create table statement in my SYBASE:
create table set_ss
(
global_set_id int not null,
...
constraint unique_set unique clustered (global_set_id)
)
Now I've known that when executing this statement, a unique clustered index named unique_set is built. unique_set is used to enforce the unique constraint on global_set_id.
My question is, if I need to write a similar create table statement in Oracle, is my following way correct?
-- create a cluster
create cluster global_set_cl (int);
-- create a cluster index
create index global_set_idx ON global_set_cl;
-- create the table
create table set_ss
(
global_set_id int not null,
...
) CLUSTER global_set_cl (global_set_id);
Thanks,
kpvn
Assume I have the following create table statement in my SYBASE:
create table set_ss
(
global_set_id int not null,
...
constraint unique_set unique clustered (global_set_id)
)
Now I've known that when executing this statement, a unique clustered index named unique_set is built. unique_set is used to enforce the unique constraint on global_set_id.
My question is, if I need to write a similar create table statement in Oracle, is my following way correct?
-- create a cluster
create cluster global_set_cl (int);
-- create a cluster index
create index global_set_idx ON global_set_cl;
-- create the table
create table set_ss
(
global_set_id int not null,
...
) CLUSTER global_set_cl (global_set_id);
Thanks,
kpvn
ASKER
Thanks grant,
1) So are you saying there doesn't exist the concept of "clustered index" in Oracle? Or if it does, it is different from the basic meaning we've learnt from Database textbooks?
2) Yeah, I am trying to port Sybase to Oracle for one of my application.
2.1 I've studied data types for both Sybase and Oracle and feel that I can handle them within the context of my app.
2.2 I've heard other experts mention about temporaray tables. Well, I am not sure I follow. Could you please give a simple example for this problem?
2.3 Handling of NULLs: Again sorry for not following your comment.
3) If you've done porting from Sybase to Oracle, could you please share with me your strategies (the more detail, the better).
Many thanks,
kpvn
1) So are you saying there doesn't exist the concept of "clustered index" in Oracle? Or if it does, it is different from the basic meaning we've learnt from Database textbooks?
2) Yeah, I am trying to port Sybase to Oracle for one of my application.
2.1 I've studied data types for both Sybase and Oracle and feel that I can handle them within the context of my app.
2.2 I've heard other experts mention about temporaray tables. Well, I am not sure I follow. Could you please give a simple example for this problem?
2.3 Handling of NULLs: Again sorry for not following your comment.
3) If you've done porting from Sybase to Oracle, could you please share with me your strategies (the more detail, the better).
Many thanks,
kpvn
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks much for your discussion.
I've tried Oracle Migration Workbench, but it looks like the software is not going to handle everything 100% for us :). Do you have any experience/comment/feedbac
I've found a number of documents on Oracle website giving advices/details about migrating from Sybase to Oracle (well for older versions since they seem to stop publishing that kind of docs when they released the Workbench).
Your suggestions give me a stronger ground to negotiate and point out problems with my boss.
kpvn
I've tried Oracle Migration Workbench, but it looks like the software is not going to handle everything 100% for us :). Do you have any experience/comment/feedbac
I've found a number of documents on Oracle website giving advices/details about migrating from Sybase to Oracle (well for older versions since they seem to stop publishing that kind of docs when they released the Workbench).
Your suggestions give me a stronger ground to negotiate and point out problems with my boss.
kpvn
I am unfamilier with the Migration Workbench. I'm guessing it isn't going to be much help with the areas that Oracle just doesn't handle, e.g. SPs that return result sets. My current client started out wanting a conversion from Sybase to Oracle. In the mean time I fixed some rather large design issues with their Sybase implementation and in the process improved performance on a couple of key operations by a factor of 50. My client's customer was so happy they pulled the plug on the conversion project. When we rewrote their database system, we layered it so and compartmentalized everything in a database-specific "adapter" layer. The API we created is done in C++ and we code directly against the CTlib and OCI interfaces.
I don't know anything about your application and you really need to charactorize first:
-- What language is it written in?
-- How does it interface with the database?
-- Is it an interactive app or mostly batch/server?
-- How heavily does it rely on stored procedures?
-- Do the stored procedure use a lot of temp tables?
The bottom line is that, if the application is interactive, relies heavily on stored procedures, and particularly if those SPs return multiple result sets and/or make heavy use of temp tables, you have at least a major rewrite and perhaps a complete rearchitecture to undertake.
Bill
I don't know anything about your application and you really need to charactorize first:
-- What language is it written in?
-- How does it interface with the database?
-- Is it an interactive app or mostly batch/server?
-- How heavily does it rely on stored procedures?
-- Do the stored procedure use a lot of temp tables?
The bottom line is that, if the application is interactive, relies heavily on stored procedures, and particularly if those SPs return multiple result sets and/or make heavy use of temp tables, you have at least a major rewrite and perhaps a complete rearchitecture to undertake.
Bill
I would not worry about it in Oracle at all. There is no particular advantage to creating a CLUSTER in Oracle if you only have one table in it. CLUSTERs in ORACLE are for keeping related data physically close on the disk in order to improve performance.
The create table statement is pretty much the same for Oracle as it was for Sybase; just remove the "clustered" clause from the unique constraint.
Oh, BTW, you might not want to use an INT datatype in Oracle. There are really only a few Oracle-native data types which include VARCHAR2(), NUMBER, and DATE. (Not a complete list but you get the idea.) All the other data types are layered on top of those. CHAR(), INT, DECIMAL, BIT, etc all get mapped back into one of the handful of data types Oracle actually stores things in. There is a certtain amount overhead (Can another expert quantify it?) associated with using the non-native data types not to mention the fact that it kind of hides what is really going on from you.
If you are trying to port (or evaluating the feasibility of porting) an application from Sybase to Oracle, you should sit down and figure out what you are really getting into. It is not a trivial proposition. There are substantial differences in the stored procedure languages (TransactSQL and PL/SQL), temporary tables don't work the same in Oracle, data types are different, handling of NULLs, etc, etc, etc. You are going to find there are much bigger issues than clusters to reconcile.
Hope that helps.
Bill