Solved

Clustered index in Sybase to Oracle

Posted on 2004-09-10
5
1,163 Views
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);

Thanks,
kpvn  
0
Comment
Question by:kpvn77
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:grant300
Comment Utility
No.

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
0
 

Author Comment

by:kpvn77
Comment Utility
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
0
 
LVL 19

Accepted Solution

by:
grant300 earned 100 total points
Comment Utility
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   ;-)

Bill
0
 

Author Comment

by:kpvn77
Comment Utility
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.
kpvn
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
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
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to fix error ""Failed to validate the vCentre certificate. Either install or verify the certificate by using the vSphere Data Protection Configuration utility" when you are trying to connect to VDP instance from Vcenter.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now