Sybase ASE & Oracle 장단점

<> Sybase ASE가 Oracle보다 좋은점이라면...

- 더 싸다(?).

- 하나의 DBMS에 여러개의 DB를 생성할 수 있다.

- 리소스(메모리 등)를 공유하므로 사용자가 많아지더라도 오라클에 비해 시스템 확장이 덜하다.

- 쓰레딩 방식으로 되어 있어서 사용자의 연결이 잦더라도 프로세스를 fork하거나 리소스를 재 할당해줄 필요가 없으므로 신속한 처리를 해준다. 오라클은 멀티프로세싱 방식이죠. 사용자 연결시 서버 프로세스가 fork됩니다. 당연히 메모리를 추가로 할당하고 연결종료시 메모리를 해제하고... 그래서 사용자가 많으면 TP-monitor 등의 미들웨어를 도입하라고 합니다.

- 마이크로소프트사가 MS-SQL을 Sybase 기술로 만들었습니다. 지금은 자체적인 기능을 많이 추가하였지만요. 왜 MS사가 오라클을 모체로 하지 않았을까요?

- 다양한 Data type을 제공합니다. 숫자의 경우 tinyint, smallint, int, double, decimal(), numeric(), money, smallmoney 등. 오라클은 only NUMERIC()만을 지원합니다. 왜그럴까요?

- 쿼리옵티마이저가 쿼리플랜을 판단하여 처리비용이 적은 방법을 취합니다. cost-based라고 하지요. 이게 자동차로 말하면 오토방식이라고 비유할 수 있습니다. 반드시 오토가 좋은 것은 아니지만 대부분 유효합니다. sybase는 오라클에 비해 SQL 튜닝이 쉽습니다.

- T-SQL문장을 알면 sybase을 쉽게 구사할 수 있습니다. DDL, DML등 제약이 없습니다. SQL문으로 batch 처리할 때 프로그래밍이 아주 쉽습니다. C 프로그램을 몰라도 SQL만으로 데이터를 조회/입력/수정/삭제가 아주 용이합니다. Oracle은 리포팅이나 데이터 batch 프로그램을 작성할 때 PRO*C를 쓰는 경우가 많습니다. SQL을 C와 엮어서 사용할 이유가 꼭 있을까요?

<> 그래도 오라클을 선호하는 이유는?

우리나라는 남이 하는데로 따라 하는 경우가 많습니다.

오라클을 제안하면 OK인데, 다른 DBMS를 제안하면 다들 한마디씩은 하니까요... 대학교수들도 DBMS는 오라클이라는 공식이 박혀있습니다. 오라클이 영업을 잘해서일까요? sybase가 영업을 못해서 일까요?

오라클은 튜닝과정이 더 복잡하고 전문가의 도움을 받게되는 경우가 많은데 Sybase에서는 그러한 추가적인 노력과 비용이 훨씬 덜합니다.

그러나, SI 업체는 오라클을 제안합니다. 왜냐하면, 비싸기 때문에(더 많은 이익을 기대할 수 있다? 유지보수 비용도 높다). 그리고 오라클을 지원하는 벤더들이 많고 다양한 3rd-party 제품들이 훈수를 두어 sybase보다는 떡고물이 더 많습니다.

오라클을 제안하면 미들웨어는 덤으로 끼워팔 가능성이 높습니다.

오라클과 관련하여 제공하는 제품들이 무수히 많습니다.

이익의 폭이 훨씬 클 수 있습니다.

오라클 튜닝만으로 먹고사는 회사가 많습니다.

오라클은 사용자 function을 자유롭게 사용할 수 있습니다.(sybase는 Java를 이용한 function으로 지원함)

in-line view가 지원이 됩니다. (sybase도 v12.5.2부터 지원함).

rownum을 지원합니다.

DECODE()함수를 지원함(sybase는 case문으로 모두 해결).

<> 파워빌더를 개발툴로 쓰면서 왜 턱시도 등 미들웨어를 사용해야 할까요?

- 오라클은 다중사용자 환경에서 연결/끊기가 자주 반복될수록 서버의 성능에 문제가 생깁니다. 그래서 connection을 어떻게든 줄여야 하는데 이를 위해 미들웨어를 권장합니다. 미들웨어를 사용하면 서버프로그램을 C와 SQL을 조합하여 개발하게 되는데 이게 보통 힘든일이 아니죠. (브랑카 - 뭡니까 이게... XXX나빠요)

- 파워빌더는 데이터윈도우 기능이 탁월합니다. DB와 연동하여 쉬운 데이터 처리를 하도록 하지요. 그러나 미들웨어와 통신해야 하니 데이터윈도우와 같은 DB처리 모듈을 사용할 수 없게 되는 것입니다. 좋은 툴을 구입해서는 껍데기만 사용하게 되는 것이지요.

- Sybase를 사용하는 경우 미들웨어가 없이도 2~3000명까지는 거뜬히 감당해 냅니다. Sybase를 사용할 때는 미들웨어 필요없습니다.

<> 결론적으로

Sybase ASE와 Oracle은 RDBMS입니다. 하는 일이 비슷합니다. 그러나 내부구조는 완전히 다릅니다. 오라클은 덩치가 큽니다. 그러나 그 힘을 다 활용하려면 개발자가 더 많은 노력을 기울여야 합니다. Sybase는 많은 부분이 지능화 되어 있어서 초기에 개발자가 가이드를 조금 받으면 쉽게 활용할 수 있습니다. 오라클 DBA 2~3명이 할 일을 Sybase는 DBA 혼자서 여유롭게 할 수 있습니다.

장형운(좋은구름)님이 2005-02-15 00:19:22에 작성한 댓글입니다.

오라클 메니아는 아니지만..오라클과 사이베이스 다 써보았는데.. 조금 이상한 것이 있습니다.

몇가지 생각해 봐야 할것이 있습니다.

- 더 싸다(?).

- 하나의 DBMS에 여러개의 DB를 생성할 수 있다.

>> 장점인지 잘 모르겠슴.

- 리소스(메모리 등)를 공유하므로 사용자가 많아지더라도 오라클에 비해 시스템 확장이 덜하다.

>> ASIQ의 경우 시스템과 사용자가 리소스를 공유하는데. 사용자의 실수가 심각한 시스템에러를 유발.. ASE는 어떤지

- 왜 MS사가 오라클을 모체로 하지 않았을까요

>> 초창기 MS-SQL 6이 사이베이스를 모체로 했는데, 알다시피 당시 오라클은 매우 영향력이 컸고, 다른 DBMS가 나오는걸 원치 않았슴.

지금도 그렇지만. 기술이전에는 매우인색한 편임. 아마 Micrsoft가 기술만을  비교했다면..infomix나 mysql을 채택했을지도 모름.

- 다양한 Data type을 제공합니다

>> 실제 프로그램시. 그렇게 많은 type을 쓰는 경우는 별로 없슴. 반대로 말하면. numer로 decimal등을 할수 있슴. 오라클도 integer는 있슴.

오라클에는 varchar2가 있는데. 사이베이스는 왜 없는가 라고 묻는다면.. ?  이게 장점인지는 모르겠슴.

- 자동차로 말하면 오토방식이라고 비유할 수 있습니다

>> 사이베이스 튜닝하기는 매우 힘듦. 정말 힘듦. 한마디로.. 오토자동차와 스틱자동차와 비교할수 있지 않을까? 참 오라클은 옵션에 따라 cost-base로 할수 있슴.

-T-SQL문장을 알면 sybase을 쉽게 구사할 수 있습니다. DDL, DML등 제약이 없습니다. SQL문으로 batch 처리할 때 프로그래밍이 아주 쉽습니다. C 프로그램을 몰라도

>> 이말은 뭔가 이상하다. 사이베이스는 T-SQL은 프로시져처럼 사용할수 있다. T-SQL을 알면(단서가 붙어 있군..-.-). 이건. PL-SQL과 거의 유사한 것이다.. 어짜피..DBMS만 쓰는 경우는 거의 없다.. 다른 host언어 C,java, jsp, asp등에서 호출해 써야 한다. 근데 왜. T-SQL을 알면 C언어를 몰라도 상관 없다는 식으로 말을 하는지.. DBMS만 사용한다면 이게 맞을 지도 모르지만.. 이런 경우라도 오라클은 프로시져나 함수로 똑같이 쓴다.

--그래도 오라클을 선호하는 이유는

>> 오라클보다 사이베이스가 부족한 부분은 뭘까? 3가지가 있다.

첫째. 오라클은 프로그램을 개발할때 편하게 사용하도록 만들어졌다.

많은(이거 간과할수 없는 장점) 함수가 지원된다. 하지만 사이베이스는 기본적인 것 밖에 없다고 할수 있다.(롤업이니.. 큐브니 이런 사소한것 얘기하지 맙시다)

둘째. 관련 레퍼런스가 부족하다. 관련서적이나, 참조 사이트 찾기 힘들다. 실제로 별로 없기 때문이다. 사이베이스 홈피에 가면.. 메뉴얼을 꽤있다. 그러나.. 기술질의는 거의...오라클 OTN을 들어가보면.. 방대한 자료에 놀랍다.

세째. Third party 프로그램이 별로 없다는 것이다. 이걸별로 중요하게 생각안할 수는 있지만.. 현실을 보라.. 많은 개발자들이.. 쿼리하기 조차 불편하여 파워빌더를 깔아 쓰고 있는현실을...

사이베이스도 많이 좋아졌다.

in-line view가 지원이 됩니다. (sybase도 v12.5.2부터 지원함).

>> 진짜 최신버젼부텀 되는군

-- rownum을 지원합니다.

>> 이말을 진짜로 믿는가? 내가 써본 건 ASE 12.5까지 였는데.. 이것도 일단 임시테이블을 만들어야만.. 가능했다. ASIQ에서는 number(*)라는 것으로 지원되기는 했다... rownum이 지원된다는 것이 무슨 획기적인 일이었나?

-- DECODE()함수를 지원함(sybase는 case문으로 모두 해결

>> 미안하지만.. 같은 논리로 최신버젼의 오라클은 case도 지원된다.

옛날 816에서는 프로시져에서 case지원되고 있었다.

--<> 파워빌더를 개발툴로 쓰면서 왜 턱시도 등 미들웨어를 사용해야 할까요

>> 이건 심히 우려되는 언급이다.

이내용을 읽어 보면. 오라클은 미들웨어를 사용해야 좋구, 사이베이스는 파워빌더에 바로쓴다...

이말 자체는 맞는것 같기도 하다. 그러나.. 내용을 쏙 빼놧다. C-S방식과 3-tier방식의 개발변화를 말할필요조차 없다. 이 문장은 과연 몰라서 쓴것인지.. 심히 의심되는 문장이다.

<> 결론적으로

Sybase ASE와 Oracle은 RDBMS입니다. 하는 일이 비슷합니다. 그러나 내부구조는 완전히 다릅니다. 오라클은 덩치가 큽니다. 그러나 그 힘을 다 활용하려면 개발자가 더 많은 노력을 기울여야 합니다. Sybase는 많은 부분이 지능화 되어 있어서 초기에 개발자가 가이드를 조금 받으면 쉽게 활용할 수 있습니다. 오라클 DBA 2~3명이 할 일을 Sybase는 DBA 혼자서 여유롭게 할 수 있습니다.

>> 결론은 .. 맞는말 같다.... 오라클은 개발자위주의 dB라면..사이베이스는 관리자 혹은 정책결정자(가격, 지원)위주의 DB라고 할수있다.

오라클은 매우 안정적이며(이게 강점이다.) 비싸고.. 많이 안다면.. 훌륭한 DB로 사용할 수 있다...

그러나..mysql로 진행중이었던..프로젝트라면.. 굳이 오라클을 할 필요는 없다.. 왜냐면..mysql을 사용한다면.. DB구조가 사이베이스하고 비슷하고 고급함수를 쓰지 않았기 때문이다.

놀놀이님이 2005-02-15 08:35:45에 작성한 댓글입니다. Edit Ｘ

놀놀이(이름이 재밌네요)님의 고견에 감사드립니다.

생각나는 데로 적은 글에 좋은 평을 많이 하셨군요 ^^

다른 사람의 글을 조목조목 따져가면서 잘 파해치셨습니다. 본명을 알려주셨으면 더 좋았을텐데..^^

Sybase나 Oracle 다 외국산 소프트웨어죠.

우리 나라에서도 좋은 RDBMS를 개발하면 좋을텐데...하는 생각을 해 봅니다.

오해가 있는 부분이 있네요. rownum은 오라클에서만 지원되는거 맞아요. 흥분하지 말고 잘 보세요.

그 단원의 제목이 '그래도 오라클을 선호하는 이유는?'이라고 되어있죠?

그리고 C/S 환경을 아주 잘 아시는거 같아요. 이 부분에서는 완전히 깔아뭉게지는군요.하하^^

사실 Sybase DBMS를 사용하면서 3Tier 환경으로 구축하는 사이트를 몇 봐 왔습니다. 그러나 2-Tier로 다시 옮겨가는 추세입니다. 서버의 성능이 좋아지고, 또한 개발에 충분한 시간적 여유를 주지 않고 신속하게 개발/유지보수 해야하는 IT환경의 변화라고나 할까요.

진부한 C/S 환경의 논쟁이라.... 쯥!

놀놀이님은 오라클을 잘 아시는 것 같아요.

다음은 sybase 와 오라클에 대한 비교 내용을 발췌해서 그대로 올립니다. 참고가 되시길.

-------------------------------------------------------------------

원본 : http://www.bossconsulting.com/oracle_dba/over.oracle

Sybase v Oracle.  Why go Sybase?

Before reading this...you MUST read Brian Ceccarelli's discussion of
Oracle limitations.  You'll be amazed at the detail and the points
he brings up.  Surf to http://www.talusmusic.com/BrainTools/ and click on
the "Oracle Facts 1 & 2" documents.

---

Sybase...
- 11.9.2 is beating Oracle's database in Tpc tests.  See the TPC-C
tests at www.tpc.org and sort by Hardware Vendor.  A recent TPC-C 
benchmark of Sybase ASE 12.0..0.2 on Sun E-10000 beat Oracle on 
the same platform by 36%.  (Cost per transaction  (156,873 tpmC) was 
$48.81  vs. Oracle's (115,395 tpmC) of $105.63).  This is the fastest
TPC benchmark ever recorded (as of 12/2000) for a SMP environment.

- Sybase is far less Expensive than Oracle.  Arbitrarily, Oracle charges per
megahertz on the CPU, a Universal Power Unit.  UPU=number of processors
multiplied by processor speed, multiplied by $100 (the current price
per UPU).  This has problems two ways; a PC chip works at far higher
megahertz speeds than a Sun Ultrachip, meaning a far more powerful
server costs far less than a PC-based server.  Secondly, users are charged
for capacity over an entire server, even if Oracle is not the only software
running.  Additional features are always additional cost in Oracle;
Sybase builds in all features to its entine.

- Oracle's Tech support is inferior compared to Sybase's.  Sybase has
Online case management and updating, instant reponse times.

- Sybase is cheaper to administer, from a DBA standpoint.  Mgrs report
that one Sybase DBA can do what 2-3 Oracle DBA's do.  

- Sybase's Customization and Tuning is simple (one text file contains
hundreds of database server options) compared to tuning Oracle.  It is 
far easier to administer, install, operate Sybase.  Very easy to create
databases versus instances.  

- Sybase Requires fewer system resources; Sybase's code runs within one 
operating system process, not dozens like Oracle.  Sybase manages memory 
better.  Oracle: one instance, one database.  One client, one
process.  One sql*plus client can only take one batch of sql at a time.
Everything in Oracle is single-threaded, not multi-threaded like Sybase.

- more efficient use of tempdb.  Oracle has a temporary workspace 
but does not clean itself up afterward like Sybase's tempdb.

- has far better documentation.  Sybooks is nicely organized and
searchable.  Oracle's is a nightmare.

- Ease of data migrations both in and out of Sybase.  Oracle has no
tools to export data out of its Databases (except in native format).

- Reliability/Stability; Banks, Financial institutions, NYSE, Nasdaq,
Amex all run Sybase.  Sybase owns 90% of the financial industry.  Oracle
has a directory (/admin/cdumps) specifically made to collect core files!

- Use of Transaction logs: Sybase has rollback and recovery transaction
log features built into the same construct; each transaction only needs to
be written to one place.  Oracle must write transactions twice (once to
the Rollback segment, once to the Redo log) because the recovery feature
was never built into the main engine.  Configuration is thus more difficult.
I/O increases. 

- Needless Index maintenance: Oracle logs index creations (why??) and 
never deletes from Indexes...neccesitating periodic manual Index recreation
to reclaim space.

- Disaster Recovery; with inline backup utilities, Sybase can back itself
up on the fly w/o taking tablespaces offline.  Replication server
creates a warm standby for 100% uptime.  Oracle can do neither.

- Backup capabilities; Sybase has builtin backup tools; Oracle depends
on file system/3rd party backup solutions (a very difficult endeavor
when your data devices are on raw disks; dependency on dd commands to
do backups).  In Sybase its one command; in Oracle its a three-day class.
Hot Backup capability requires additional I/O overhead.  Oracle backups
work best doing "cold backups" (i.e., shutting down the server...how
feasable is that in today's computing environment??).

- SQL limitations: You cannot mix DDL inline with an if statement in
Oracle.  You cannot truncate a table in Oracle w/o removing its constraints.
Data types not being IEEE compliant (causing cpu overhead as regular
numbers are passed in and converted on the fly constantly).  Every
select statement is am implicit cursor in Oracle.  Oracle can't handle
result sets!!  All sql must return exactly one result.  PL/SQL considered
verbose and Cobol-esque.

- Similarity to Microsoft SQL Server: relatively easy to transfer data
from one to another (on account of their past shared code base).  Also
relatively straightforward to re-train MS Sql server admins for Sybase
and vice versa.

-----
"Features" in Oracle not in Sybase and rebuttals

- Automatic database segment growth; I would argue that this is incredibly
dangerous to allow to occur.  Its easy to extend databases, not so much
to fail them back.  Now many Unix SA's would like an automatic disk growth
feature for their users?

- "Minus" clause to detect table differences.  Re-writable with a simple
"not exists" clause of a select statement.  

- Decode statement; previously emulated with complicated abs functions,
but now (11.5) emulated with case and coalesce statements.

- larger varchar() capabilities (beyond 255 characters): varchar2 in Oracle
is not Ansi standard ... but it is flexible.  Later versions of Sybase
(12.0+ and above) increase the 255 character limit in varchar datatypes.

- Sequences: The Sybase identity was a weaker emulation of Oracle's sequence,
until 12.0, when the Identity can be more or less configured to be
monotonically increasing.  Prior to 12.0 sequences were easily emulated
with a stored proc/key storage table solution.

- Synonyms: these are actually Oracle constructs designed to get around
Oracle architecture limitations.

- Time datatype: Oracle seperates Time from Date.  Sybase's datetime
datatype has both included in one, plus a whole suite of display styles
that can show the date/time in a multitude of formats.  However, Oracle
is weak interpreting date/time variables being imported, and Oracle's time
variable is only precise to the second.  If you need more precision, you
must design your own datatype.

- Some nice Inhouse tools: Oracle Forms and Reports.  However these 
tools are very expensive and challenging to get upto speed on, and Sybase
has totally comparable 3rd party Reporting tools that can connect using
Sybase's native client libraries.

- Connect by: some sort of "tree-walking" clause?

- Before and After triggers: definite advantage over Sybase's "after"
only triggers.

- Cascade Deletes: ?

장형운(좋은구름)님이 2005-02-15 10:24:05에 작성한 댓글입니다.
이 댓글은 2005-02-15 10:44:35에 마지막으로 수정되었습니다.

간단하게 설명된 Oracle과 Sybase 비교 자료입니다.

http://www.edbarlow.com/document/EdsOracleCribSheet.doc

장형운(좋은구름)님이 2005-02-15 11:05:23에 작성한 댓글입니다.

ORACLE FACTS!!

"I had been a Sybase DBA for 8 years before I crossed paths with Oracle. It only took one hour for Oracle to sow seeds of despair. I hoped that I was just ignorant about Oracle and that the more I learned Oracle, the better I'd like it. Well, that turned out to be a false hope. It is now 3 years later and Oracle hasn't gotten any better. "Here is a list of my grievances. I hope you find them interesting and useful. I have approached company Oracle gurus and Oracle tech support as I discover each problem. The response I get from the gurus is, "Well, that's true . . . but Oracle has market share." From Oracle tech support, I get, "No, that is not a problem. That is a feature." And so my conclusion is this: I don't think there is single company out there who has purchased Oracle based on Oracle's technical merit. For every company and every Oracle DBA whom I met, in the end confesses that their use of Oracle is solely based on "Oracle's market share. "By the way, these problems are in all versions of Oracle. Regard the problems as genetic. The architectural flaws of Oracle just pass from release down to the next release. If anything, the more current the release, the more problems because mutations and inbreeding only produce more beastly manifestations."
Buying Oracle is like buying a car "as is". It comes with 3 flat tires, a burnt-out headlight, 5% brakes left, and an engine that will blow a rod within 3 weeks. The engine looks like something out of the '70s. There is no room under the hood, and 90% of what you see are smog control devices. Upon closer inspection, half the sparks plugs and the carburetor are missing. A look at the driver's instruments is similarly interesting. Here you'll find not one, but many stick shifts each with 200 gears labelled in hexadecimal which somehow imply functionality. You will see a steering wheel the size of a barge's, as well as a built-in 8-track tape player. The CD player, an after-thought, is dangling from two wires under the glove box. The ignition switch is nowhere to be found.
If you have any comments, corrections or additions, please send an e-mail to Tech Support. My purpose is not to defame Oracle, but rather to point out the truth while venting my frustrations. Since I am not a DBA zealot, you can correct me if I am wrong. I promise I will not get upset. I will only be pleased to find out that I am wrong, because I too have to wrestle the Oracle devil every day.
1 Oracle has no provision to store numbers in the computer's own native format, namely IEEE. All float and integer numbers are stored as integers with an extra byte for sign and a specification for decimal place. As for float numbers, Oracle does not support exponents. The scientific community, therefore, must look elsewhere. 2 Computers carry out their arithmetic with IEEE formatted numbers only. Because Oracle does not store any of its numbers in IEEE, every single computation involving an Oracle number must be converted from its internal format to IEEE. When you store it back to Oracle, Oracle must convert it back to their own format. Better buy a faster CPU. 3 Oracle does not support bit operators. "update table set bitmask = bitmask | 66536" is a syntax error. The reason why Oracle can not do this most basic of functionality is because of the way Oracle incorrectly stores numbers in the first place. There is no meaning to "bit" in its own internal format. The '|' sign which means 'bit-wise or' in standard programming, means nothing in Oracle. If your application makes use of bit flags or masks, which most applications do or should, Oracle will stop you dead in your tracks. I have written a set of Oracle PL/SQL bit operator substitution functions as a tourniquet to this Oracle pitfall. They are in BriansTools under the ./dbaccess/oracle_scripts directory. However, because they are functions, the Oracle optimizer will be far less efficient with them as Sybase would be to standard bit operations. 4 Oracle has at least three different languages:  SQL, SQL*Plus and PL/SQL. The three languages deceitfully imply flexibility and functionality. But really, the presence of three languages exists because of a lack of forethought. Whereas Sybase has one language with a grammar which handles everything, not any one of Oracle's 3 SQL languages can speak the language of its database. Because of the inadequacy of any of the languages, Oracle then introduces interfaces between them. The clumsy interfaces are an addtional level of complexity which you have to learn. For example, getting results back to SQL*Plus from PL/SQL includes a wide assortment of BIND variables. This is unecessary in Sybase Several Oracle people have e-mailed me on this point saying that "There is only one SQL language in Oracle. They are all the same." But I tell you the truth, the grammar rules change drastically between Server SQL, SQL*Plus and PL/SQL. The next several points illustrate this. 5 PL/SQL blocks within a SQL*Plus ignore SQL*Plus's 'set autocommit on'. Committing transactions within PL/SQL is independent from the SQL*Plus in which it is embedded. The commit syntax is also different. 6 You cannot write any DDL with PL/SQL. 'create table' is invalid with PL/SQL. To the Sybase user, that is like saying that you cannot create a table within a stored procedure. If you want to create tables in an Oracle stored procedure, you have to use dynamic SQL. The table defintion may be static, but you have to use dynamic SQL because PL/SQL cannot create tables to begin with. This is an up-front misuse of dynamic. Oracle dynamic SQL has a different and obtuse syntax. You will have to learn it. Also, any future DML statements in your PL/SQL stored procedure that manage that table will also have to be in Oracle dynamic syntax. After all, you cannot write insert statements into a table the compiler doesn't think exists. 7 You must have a special permission in Oracle to create a table in a PL/SQL dynamic statement. You must have CREATE ANY TABLE permission. You may already have the ability to "CREATE TABLE X", but if you use dynamic SQL to do the exact same thing, you don't have the permission. 8 There is no if statment in SQL*Plus. "if ..., then drop table ...." is impossible with Oracle. It is a standard in Oracle for a programmer to issue SQL*Plus DDL commands knowing that they will fail. You just let SQL*Plus generate an error. That is normal for Oracle. The problem is that "error" means error. One's attention is drawn to it. Each one requires examination. DBAs hate this stuff. Customers hate this stuff. Tech support hates this stuff. The typical tech support's response is "Yeah, that error is okay but that is normal for Oracle." To attempt to handle the error, you can introduce the WHENEVER SQLERROR clause in SQL*Plus, but that is only a Band-Aid. That does not prevent the error to begin with. You can also spool out a SQL command generated from a prior select from USER_TABLES, and then execute this spooled file later. The latter method is the best choice to prevent errors. But the method is just another kludge. It introduces a timing problem for the transaction, and you will also find yourself executing empty commands . . . but that is normal for Oracle. 9 Even though you can do a "select col1, col2 . . . from table" in Server SQL, you can not do a standard select statement in PL/SQL. This tells you that Oracle does not support result sets. 10 You cannot print anything to your screen from within a PL/SQL block in real time. If you want to print out the current iteration of a cursor, just forget it. If you want to print the rows of a cursor as they are being selected, just forget it. Oracle is incapable of doing it. Oracle buffers all the output from a PL/SQL block for printing until after the PL/SQL block completes. Even at that, Oracle limits you to 1 MB of buffer after which SQL*Plus blows up. So, when you have a long running loop, there is no way to tell your user about the progress of your loop. To the user, it looks like your script doesn't work. But that is normal for Oracle. 11 In Oracle, you can only execute one SQL command at a time. Oracle has no concept of a batch. This is true in SQL*Plus as well as through ODBC. So, I assume this is an OCI limitation. In Sybase, you can send 100s of commands in a batch-- an operation which takes only a single network I/O. In Oracle, you must execute 100s of commands to accomplish the same thing. In Sybase, you load up 100 commands in your car and go to the shop and have them all serviced at one time. In Oracle, you must load you car 100 times, go to the shop 100 times, and have each command serviced one at a time. 12 Oracle does not support client-side timeouts. In Sybase, you can program your client to timeout if the server doesn't respond to a login request or to a query in a settable time. This mechanism allows your client to recover gracefully when the server or network has a problem. Oracle offers no such mechanism. In Oracle, you have to kill -9 your client. If you are a capable programmer and have an extra three man-months to spare, you can program threads into your own client which handle timeouts in lieu of Oracle's inability to do so for itself. 13 When creating a stored procedure using SQL*Plus, you may have errors in your procedure. The best error message Oracle can give you is "Procedure created with warnings or errors." One would think Oracle would be more specific. You actually have to go out of your way to request more information. You must additionally type SHOW ERRORS. Note that the error message implies that Oracle will create a procedure with errors in it. 14 Ever try to change your line width in SQL*Plus on Solaris? Do it and then select from the dictionary. Watch SQL*Plus core dump. SQL*Plus has been core dumping for years. 15 Oracle is inconsistent with the quoted identifier feature. Your company may literally waste man months of work over each instance of this problem. As you know, in Oracle you can use a reserved word for a table name if you put quotes around it. The problem is, is that Oracle will let you get by with a reserved word without quotes in one instance but blow up months later when you try to use it another. For example: create table CLUSTERS. This command works even though CLUSTERS is a reserved word. You can also select * from CLUSTERS. But try: . . .where CLUSTID not in (select CLUSTID from CLUSTERS) and watch Oracle blow up. While I can create a CLUSTERS table and select from it, I cannot refer to CLUSTERS in a subquery. The grammar rules of Oracle SQL change within Oracle SQL. A true linguistic nightmare. 16 The Oracle data dictionary stores all its object names in upper case. Oracle allows you to access the object in upper case, lower case and mixed case. That is fine. But that concept doesn't work for object names where you have used the quoted identifier. Given create table TEST, you can select * from test, but you cannot select * from "test". Oracle regards this as a feature. Any object-name within quotes will be copied exactly to the data dictionary; yet that is not so for normal unquoted objects. This is another major inconsistency. Because of the reserved word problem, it is tempting to just put quotes around everything. If you do that, however, then all the object names in your SQL scripts have to match exactly those in the data dictionary Sybase is refreshing after this particular Oracle nightmare. Sybase is simply consistent. What you create is what you get. 17 A blank line within a SQL command in SQL*Plus is a syntax error. Actually, it is a feature. A blank line tells SQL*Plus to erase your last command. Any leftover SQL clause following your blank line therefore blows up. To the Oracle user this is normal; to the Sybase user this is stupid. Sybase ignores blank lines and reset is the command to erase your last command. The problem is this. You have to make sure all your SQL scripts do not have mid-command blank lines, becuase SQL*Plus will treat each blank line as an error. Some third party code generation programs are lax with Oracle's wonderful feature, and so those code generation programs will not work with Oracle. In Sybase this problem never happens because Sybase has an architecture. 18 Oracle is incapable of dropping a table with only outgoing referential integrity constraints. In other words, Oracle cannot drop the childmost table. This problem is just irritating. I wish that Oracle was logical. The work-around is easy. Just add "cascade constraints" to the drop statement. Nonetheless, Oracle should be able to drop the childmost table anyway since its RI constrainsts will never interfere with its disappearance. This is an 8i feature. 19 Oracle is incapable of truncating a table that has referential integrity constraints. It doesn't matter if the table is the childmost table. It doesn't matter even if the table is empty. You have to disable its RI constraints no matter if the table is empty or the table is the childmost table. The extra coding to disable and then reenable RI constraints before and after a truncation, is substantial and a total waste of time. The operation is logically unnecessary. Another 8i feature. 20 The system manager (Sybase equivalent of "sa") is not allowed to grant permissions on a user's objects to other users. In other words, the system manager does not have the permissions to manage the system. In Oracle, only the user himself can grant others permissions to his objects. 21 You cannot create a read-only view in your own schema which reads from tables in another user's schema. You may have select permissions on the other user's tables, but that doesn't matter to Oracle. 22 Avoid creating views based on large tables. Avoid creating views based on views. Oracle's optimizer is extremely lousy at finding the fastest way to the data. Whereas Sybase can return results of a third generation view based on a 20 million row table in 45 minutes, Oracle can never figure it out, even after days of processing, for a table that is 1/20th the size. This Oracle behavior will force you to create work tables, which is what you tried to avoid in the first place. 23 Oracle has screwed up the definition of the null set. NULL has a specific meaning in mathematics, yet Oracle gets it wrong. In math, the null set is the set containing nothing--the empty set. In Oracle, the null set also includes the set containing 0-length strings. Oracle's definition is an oxymoron. The ramifications are severe: 1) An application can no longer discern if a varchar2 column has been touched or not. (An empty string usually means that a user entered data even though the data has no length. A NULL means that the user never touched the column in the first place) and 2) You can longer use a varchar2 as one of the columns in a primary key if it is possible that the varchar2 value is "". The latter limitation will force the DB designer to use a heap table instead of the more desireable index-organized table. That will increase the size of the table significantly and thus will slow down access to it. All this slowness and excess bulk because Oracle has not grasped the fundamentals of math. 24 Oracle is single-threaded. Look at your process list. Every connection to Oracle has its own process. The listener is its own process. The writer has its own process. The monitor has its own process. Each connection has its own process. In Sybase, all connections and listeners are threads inside the dataserver. 25 Oracle has actually planned a core dump directory for itself. That should tell you something. Note that is it full all the time. 26 Oracle only supports one database per server. Sybase supports 32,767 databases per server. 27 Oracle uses the temporary tablespace to build the indexes of create index commands. If you are creating a large index, your temporary tablespace better be huge too. Hint:  make sure your init.ora variable SORT_AREA_SIZE is about 20 MB and that you have turned off logging in the temporary tablespace. If you don't do these two things, Oracle may never complete your create index command. 28 Oracle will use rollback segments to create the indexes. The problem is, is that whether the index gets created or not, there is no need to log any of the rows of an index to the rollback segment or to the redo logs. Either you can create the index or not. So there is no reason to log individual rows of the index. Nonetheless, Oracle will consume gobs of time and resources to log them. 29 Oracle will use the redo logs to create an index. 30 Oracle will step over its own shared memory bounds set in the init.ora file when creating a large enough index. Once it steps over its bounds, no one can log in anymore because Oracle not only had overstepped its bounds, but also it had leaked all the shared memory. 31 To the person who knows only Oracle, core dump directories, index logging, single-threaded and one-database servers do not sound strange. To the Sybase user, this is Jurassic Park. Sybase does not log the rows of an index. Sybase does not use the temporary database to form permanent indexes. Sybase does not have a rollback segment to also log temporary transactions, as if you would want to do that in the first place. And so, the Sybase user is surprised when it takes Oracle 16 hours to build a unique index on a large table whereas to took 40 minutes to build the same index on the same table in Sybase. I became aware that all this was happening when I tried to build an index and saw Oracle run out of temporary tablespace, run out of rollback segment space (2.5 GB), while busily writing to the redo logs. There is a way to ease the pain of this logging. Shut off logging in the temporary tablespaces and in the rollback segments tablespaces. 32 Oracle will log every row in the index to its rollback segments. Oracle uses rollback segments for everything without exception, and even for objects in the temporary tablespace. An index will be logged twice--one for the rows being generated in the permanent tablespace and again for rows being temporary written to in the temporary tablespace. 33 SQL*Plus will not inform you if you have run out of rollback segment while creating an index. It will just hang forever. You have to suspect something is up. You have to continuously view the alert log. tail -f alert.log . . . is normal for Oracle. 34 Oracle has no ability to index columns in dictionary order. If you want to search on a column in dictionary order, you must have another column in the row with the same data but in upper case. This is a tremendous waste of space and I./O time as well as another feature which causes heart attacks in a Sybase user. 35 The network configuration assistant program (netca) on Solaris will erase your entire tnsnames.ora and listener.ora files when you tell it to "Cancel and discard your changes." To the Sybase user, that is like quitting sybsetup or asecfg and having it erase the entire interfaces file. 36 The create database command does not load all the necessary SQL scripts to make the database a database. In Sybase, the equivalent is issuing a create database command and finding that the created database does not have system tables. 37 In Oracle, a database is a server. Oracle only supports one database per server. For each database you want in Oracle, you must run the equivalent of asecfg; that is, dbassist. So, a Sybase DBA who is used to managing 5000 databases on a single server is going to have to run 5000 iterations of dbassist to create his 5000 databases. Now dbassist is a trip in itself. Note that when dbassist generates a script to create the database, the script does not do any error checking. You can literally get thousands of errors and Oracle will say, "Database successfully installed." Remember that DDL errors are normal for Oracle. If you program Oracle, you do not even try to make your program error free because true DDL error handling is not part of Oracle's "architecture." Was the database installed correctly? Of course not. 38 Note that dbassist is incompatible with itself . After you ask dbassist to clean up after an unsuccessful generated script run, dbassist not only removes the database, but also removes the directory structure that your script initially requires. The script you just generated after spending an hour answering dbassist questions, no longer works. You have no choice but to answer all the questions again, even if you answer them the same way. 39 You cannot tell Oracle to use a specific rollback segment for an import. If you are importing a large table with a large index, you must first take all the small rollback segments