Clustered index in Sybase to Oracle

Posted on 2004-09-10
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 100 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
This tutorial will walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

831 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