Oracle9i has several shortcomings compared to Sybase ASE12.5
Thousands of dreams 2004-7-24
Oracle9i and ASE12.5 are the world's mainstream database manufacturers Oracle, Sybase's main products, with considerable market share. This article throws out between the two, the difference in the architecture, the technical route, is purely from the user's point of view, and several deficiencies compared to Sybase Adaptive Server Enterprise 12.5 (ASE12.5), as for ASE12. 5 Insufficient compared to Oracle9i is not discussed herein.
1 Do not support regular expressions: familiar with Unix / Linux and ASE12.0 / 12.5 know that the regular expression is flexible and powerful. Demand like "Find all table names containing numbers" is extremely simple in ASE12.5:
Select Name from sysobjects where type = "u" And name limited "%" (here the sysobjects are equivalent to all_Objects in Oracle9i), and Oracle9i's implementation is quite more than one fold, in general, many people It's all written a function, the following is a function that implements this function when you just learn Oracle:
Create or Replace function is_number (p_str in varchar2)
Return Number
AS
W_CHAR CHAR (1);
I Number: = 1;
Begin
While I <= Length (p_str) loop
W_CHAR: = SUBSTR (p_str, i, 1);
IF w_char> = '0' and w_char <= '9' THEN
Return 1;
END IF;
i: = i 1;
End loop;
Return 0;
END;
/
In Oracle9i calls a custom PL / SQL function overhead, it will be large; it is far from being implemented internal implementation of ASE12.5.
2 UPDATE statement "蹩", in the application of the database, two tables associated Update are very common, if there are two tables: T_A (ID, Name, Point, ...), T_B (ID, Point, ...) Both of the IDs or Unique INDEX, now implement the POINT of the T_B table to the T_A table Point on the corresponding relationship according to the ID. Let's see the implementation of Oracle9i and ASE12.5:
/ ** for oracle9i ** /
Update t_a a
Set a.point = a.point (Select B.Point from T_B where B.ID = A.ID)
WHERE EXISTS (SELECT 1 from from t_ b where b.id = a.id)
/
/ ** for ASE12.0 / 12.5 ** /
Update t_a
SET POINT = a.Point B.Point
From t_a a, t_b b
WHERE A.ID = B.ID
Go
Oracle9i's statement seems to be clear, easy to misunderstood, and view the execution plan to discover the T_B table or index scanned 2 times! ASE12.5 scanned only 1. Although the second time is logical reading, it always feels unwilling;
Sometimes if the T_B table is small, there is no index on the ID, I would rather adopt Cursor mode, many trials show
It is much more than Create Index associated Update. There is also the OLAP characteristics of Oracle9i, complete with merge statements.
3 Comparison of temporary table technology:
Item storage location DDL Data Lifecycle Oracle9i Any Table Space User Maintenance, Table Name is Global (Database User) Unique Session / Transaction ASE12.5 Tempdb Library User Create; User DROP or System DROP, Table Name Session Level Unique Session Although Each has a director, but I think the realization of ASE12.5 seems to be more "temporary" essence - "Calls" during each session, don't worry with others, # 11 # 22 #aa #bb Create, "Go", the end of the session does not have to be explicitly deleted, from the system, do not worry about the existence of the garbage table.
4 COUNT Question or Distinct Question? In the SELECT statement, use the Distinct keyword to return to the only rowset, which is especially important in statistical analysis, excluding duplicate data, but sometimes I only want to count a total? I first wrote this first time:
Select Count (Distinct A, B, C) from my_table result syntax is wrong, there is only to modify:
SELECT Count (*) from (Select Distinct A, B, C from my_table) is passed, and thinking is that this must be a disaster provoking, but then Distinct is obedient, because even:
Select Count (A, B, C) from my_table is not. Can only be written:
SELECT Count (*) from (SELECT A, B, C from my_TABLE)
But in ASE12.5, whether it is Select Count (Distinct A, B, C) from my_table or SELECT COUNT (A, B, c) from my_table can be obtained.
5 Comparison of Data Import Export Tools: EXP / IMP is used in backup and recovery, and is subject to version (high and low versions, 32bit / 64bit), the language has a large influence, and SQLLDR can only count the import tool, strictly Oracle9i has no text-level export tool for table data! Instead, ASE12.5's BCP is very flexible in table data import export, and the format is also very simple, it is easy to do the input of the application. There are also views, stored procedures, export tools for triggers DefNCopy is also very easy; as for Oracle9i, I encountered the following situation: N 1 time:
Q: How do I get the code of a stored procedure?
A: Is there any Client?
Q: Pooled!
A: Open Enterprise Manager Console, after logging in, find it below.
......
Q: How can I trouble?
A: Then you have to install toad or PL / Development Ah, these tools are good
Q: Nothing!
A: L
or
Q: How do I get the code of a stored procedure?
A: Is there any Client?
Q: Installed, but I am on the host!
A: Then you use Sqlplus to watch,
Set long 300
Select text from all_source where name = 'Yourname'
Go
Q: Wow, how to trouble?
A: L
If at ASE12.0
Q: How do I get the code of a stored procedure?
A: Use DefNCopy
Q: How is it?
A: I rely on, so simple! You still use it! J6 Online Backup: Although Oracle9i provides cold and hot backup technology, it seems to be inferior to the ASE12.5 Open Server Technology On the Internet Real-time Backup Server seems to be inferior, the backup media will directly get another ASE12.5 LOAD, soon you can get a very powerful "mirror" database environment. And I think EXP / IMP is trivial, and there are more restrictions.
The command line tools included with Oracle9i, such as SQLPLUS EXP / IMP SQLLDR, etc., there is no exception to the output time, version, copyright information, give people like me, I like to use the shell processing results, it is a lot of trouble, compared to ASE12.5 is QL BCP Defncopy's cleanliness is Oracle9i's boring and "narring" or the domineering of Eric Ellison.
