Improve company productivity with a Business Account.Sign Up


Clustered index in Sybase to Oracle

Posted on 2004-09-10
Medium Priority
Last Modified: 2013-11-15
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);

Question by:kpvn77
  • 3
  • 2
LVL 19

Expert Comment

ID: 12031663

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.

Author Comment

ID: 12032034
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,
LVL 19

Accepted Solution

grant300 earned 400 total points
ID: 12032522
1)  Yes, clustered indexes are entirely different in Oracle than Sybase.  Don't worry about it; just remove all "clustered" key words from the DDL.
2.1) The data types are trickier than you think.  You may well have problems with you binary(12) keys for instance.  Run some simple tests to make sure
       what you are planning will really work.
2.2) It's simple: you can make and use temp tables at will in Sybase with isolation from other users.  With Oracle you can't.  There is a sort-of temp table
       facility in newer versions of Oracle but there are significant drawbacks and limitations if you are coming from the Sybase world.  If your application
       and/or stored procedures make use of temporary tables, they will have to be substantially reengineered.
2.3) Null handling is just one of dozens of differences between the two databases.  FYI, Sybase allows and equality test with NULL, e.g. <fieldname> = NULL,
       while Oracle requires an explicit IS NULL test.  Queries and stored procedures that use the =NULL syntax will have to be rework.

3)  By far the biggest headache you will have going from Sybase to Oracle is with the stored procedures.  The most important difference is that Sybase allows
     you to return one or more result sets from a stored procedure.  Oracle allows you to return only OUTPUT parameters!!! This is a huge difference and if your
     application makes heavy use of stored procedures, you are in for a complete rewrite, procs and application.

     There are all kinds of other issues.  Sybase connections are handled in the engine with internal threads; Oracle connections like up a heavy weight
     process on the server for each one.  Sybase uses native machine datatypes, e.g. INT is a machine 32 bit integer; None of Oracles datatypes are
     machine native.  If you are using CTlib and coding C or C++ against it, you are in for a shock when you go to OCI.  Not that OCI won't do a bunch
     of good stuff, it is just very different and is a lot larger and more complicated library.

     There are differences in the SQL dialect.  For instance, Sybase allows aggregate functions in the select list without requiring a GROUP BY cluase.  Sybase
     triggers have no problem or limitation refering to or modifying rows in the table that fired the trigger.  This is completely forbidden in Oracle and is
     refered to as a "mutating trigger".  You start to get the idea.

I would undertake a complete impact analysis before I actually thought about touching any code.  You undoubtedly know the existing application pretty well
and understand its architecture.  You need to sit down with someone who knows both databases pretty well (an Oracle guy that doesn't know Sybase won't
be much help) and access all of the problem areas, develop a new application architecture addressing each of those areas, and figure out just how much of the application will need to be rewritten.  Then take the estimate to whomever has the money and give them a chance to bail before you start hacking at the code.

Bottom line, if this is something you or your boss are casually entertaining thinking it is no big deal, I can assure you, it is a big deal.

If this is a major must-have undertaking, then all I can add is that memorable line from Jaws when Roy Schider gets his first good look at the shark:
  Your going to need a bigger boat   ;-)


Author Comment

ID: 12039416
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/feedback with this migration workbench?
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.
LVL 19

Expert Comment

ID: 12040502
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.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question