Several deficiencies in oracle9i compared with Sybase ASE12.5

Oracle




Several deficiencies in oracle9i compared with Sybase ASE12.5





Thousands of Dreams 2004-7-24





Oracle9i and ASE12.5, respectively, are the world's leading database manufacturers Oracle, Sybase Company's flagship products, has a considerable market share. This paper sets aside the differences between the two in the system structure, technical route, from the user's point of view, sorted out the oracle9i and Sybase adaptive Server Enterprise 12.5 (referred to as ASE12.5) several deficiencies, The lack of ASE12.5 and oracle9i is not covered in this article.

1 does not support regular expressions: people who are familiar with Unix/linux and ase12.0/12.5 know that regular expressions are flexible and powerful. A requirement like "Find all table names that contain numbers" is extremely simple in ASE12.5:

Select name from sysobjects where type= ' U ' and name like '%[0-9]% ' (here sysobjects is equivalent to oracle9i in all_objects), and the implementation of Oracle9i is quite a lot of trouble, under normal circumstances, many people are writing a function implementation, the following is just learned Oracle wrote a function to achieve this function:

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

/

The cost of calling a custom Pl/sql function in Oracle9i is much larger than the internal implementation of ASE12.5.

2 UPDATE statement "Lame", in the database application, two table association update is very common, if there are two tables: T_a (Id,name,point,...), T_b (Id,point,...) ID are either the primary key or unique index, Now to implement the corresponding relationship based on the ID of the T_b table to add point to the T_a table. Let's look at the implementation of Oracle9i and ASE12.5:

/** for Oracle9i **/

Update T_a A

Set a.point=a.point+ (select B.point from T_b b where b.id=a.id)

where exists (select 1 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 disregard clear, easy to misunderstand, and view the execution plan found T_b table or index scanned 2 times! The ASE12.5 was only scanned 1 times. Although the 2nd time is logical reading, but always feel unwilling;

Sometimes if the T_b table is smaller and there is no index on the ID, I would prefer to use the cursor method, many experiments show

It is much faster than the Create Index+ Association update. There is the use of oracle9i OLAP features, with the merge statement to complete.

3 Comparison of the temporary table techniques:



ITEM

Storage location

Ddl

Data life cycle

Oracle9i

Any table space

The user maintains the table name is the global (database user) unique

Session/transaction

ASE12.5

Tempdb Library

User create; User drop or System drop, table name session level unique

Session











Although both have their own strengths, but I think the realization of ASE12.5 seems to be more "temporary" the essence-in each session, "Beck", do not worry about the same name as others, #11 #22 #aa #bb随个人喜好创建, "lingering", the end of the session also need not explicitly delete, by the system to do, There is no need to worry about garbage tables.

4 count question or distinct question? Using the DISTINCT keyword decoration in a SELECT statement to return a unique rowset is especially important in statistical analysis and culling of duplicate data, but sometimes only to count a total? The first time I wrote this:

The Select count (distinct a,b,c) from my_table result syntax is not correct and has to be modified to:

Select COUNT (*) from (the select distinct a,b,c from my_table) to pass, thinking that this must be distinct trouble, but then found distinct was wronged, because even:

Select count (a,b,c) from my_table is also not possible. Can only be written as:

Select COUNT (*) from (select A,b,c from my_table)

However, the correct results can be found on ASE12.5 either select COUNT (distinct a,b,c) from my_table or select count (a,b,c) from my_table.

5 Data Import Export tool comparison: Exp/imp in the backup and restore aspects of the more, and by version (High-Low version, 32bit/64bit), language impact is large, and Sqlldr can only count as import tool, strictly oracle9i no table data text-level export tool! In contrast, ASE12.5 's bcp is flexible in the import and export of table data, and is easily formatted for application input. Also has the view, the stored procedure, the trigger's export tool defncopy also very easy to use; As for oracle9i, I encountered the following situation n+1 times:

Q: How do I get the code for a stored procedure?

A: Is there a client?

Q: It's loaded!

A: Open the Enterprise Manager Console, and after you log in, find it under the scheme.

......

Q: What's the trouble?

A: Do you have a toad or pl/develop, these tools are easy to use.

Q: No!

Answer: L

Or

Q: How do I get the code for a stored procedure?

A: Is there a client?

Q: installed, but I am on the host Ah!

A: Then you use Sqlplus to see,

Set Long 300

Select text from All_source where name= ' YourName '

Go

Q: Wow, what's the trouble?

Answer: L

If you're in ASE12.0

Q: How do I get the code for a stored procedure?

Answer: Use Defncopy.

Q: How to use AH

A: Holy cow, that's so simple! You still need to ask Ah! J

6 Online Backup: Although oracle9i provides cold, hot backup technology, it appears to be less than the ASE12.5 online real-time backup server based on open server technology, To get the backup media directly to another ASE12.5 for load, it's very easy to get a highly time-sensitive "mirror" database environment. And I think exp/imp more trivial, and more restrictions.

Oracle9i with command-line tools such as Sqlplus exp/imp Sqlldr, when executed without exception, will output the time, version, copyright information, for people like me who like to use the shell to deal with the results of a lot of trouble, compared to ASE12.5 isql bcp Defncopy's neat, is Oracle9i's boredom and "narcissism", or Eriksson's domineering?