Indexes can become invalid
For Oracle users, this is normal - yes that is correct. Simply loading a table with SQL-Loader can make an index unusable, and invalid. Indexes need to be rebuilt on a regular basis, if sql-loader is used to load data. We'll post common ORA-XXXX errors soon, to illustrate.
With SQL Server and Sybase, this never happens. An index will not become invalid or unusable by simply loading data into a table.
Drop table: does a delete in the background.
This is fixed in Oracle 10g. In previous versions of Oracle, a "delete from" was performed when a DROP TABLE was issues. Crazy, but true. Now, in 10g, the data is moved to the "recycle bin" which brings up a whole new set of problems!
Each connection takes too much memory (2-4 MB)
Yes, that is correct. Each database connection takes 2-4 MB of memory on the host machine.
For Sybase and SQL Server, the amount of memory is less than 1/10th that!
Stored procedures don't return a result set.
You have to use a pointer to an open cursor in your client application.
Oracle developers don't know what you are talking about, when you say: "Stored procedures need to return a result set."
For Sybase and SQL Server developers, this is the core advantage of stored procedure: they can simply replace SQL statements if needed.
This is impossible in Oracle:
create proc P_get_employee as
begin
select emp_id, emp_name from employee
end
Oracle does not support update within a join.
You can't do an update, like this:
update company_data
set t1.msci_gics_sector = t2.msci_id
from company_data t1, msci_map t2
where t2.company_id = t1.company_id
You need to use a convoluted sub-select format, in order to achieve the same result.
Note that Oracle developers consider this to be 'normal' .. so beware.
No real numeric data types
This is fixed in 10g - at last, Oracle has real numeric data types.
The official announcement was made in Oracle Magazine in 2007.
Schema/user methodology is dated, and difficult to work with.
Try setting up a schema with read_only users, dbo users, and read/write users. You'll need to create separate schemas, and then maintain synonyms (at least now there are public synonyms to make this less painful). Note that most enterprise Oracle DBAs cringe when they hear "public synonyms" ...
Error messages are useless most of the time
- try coding a stored proc, and figuring out what the errors are
- typical error statement: Cannot allocate segment in tablespace 10
( yet there is no way to find which tablespace is #10 ?!?!? )
No built-in export utility; for export to delimited files.
Oracle has SQL Loader for getting data in ... but Larry Ellison does not want you to get your data out!
You need to code a hack spool program, using SQL-Plus to export to a flat file.
No database dump utility, tablespace backups require you to offline the file.
There is no "block dump" style backup program in Oracle, export is a p-coded series of SQL statements, transportable tablespaces are basically copies of different segments of the databases. Loading one Oracle instance from another is not a simple task!
PL-SQL is inconsistent with Oracle SQL-Plus implementation
Some functions work in SQL Plus, but not in a PL-SQL procedure ( like to_lob ).
PL-SQL is inconsistent
e.g. When declaring a parm, varchar must be used ... varchar2(10) does not compile!
SQL Plus procs wait until they are completed, before displaying printed terminal output.
This really makes debugging difficult. The output from the PRINT function (DBMS OUTPUT) does not appear to the console until the stored procedure has completed executing.
Space reclamation is poor, table grows after deletes. Need to drop/rebuild tables periodically.
Even after deleting rows ... a query might still scan those empty blocks, which are part of the tables extent list.
Support, software is too expensive, poor value.
Oracle is the true leader when it comes to sucking up IT budgets.
Performance is lacking; this is inherent in the architecture.
- Raw devices are discouraged, Oracle tablespaces are made up of files in filesystems. With 10g, Oracle invented its own filesystem type ... but still, there are files in the filesystem, visible at the OS prompt.
- Oracle keeps 'before image' available, allowing dirty reads. Nice to have that dirty read available, but the overhead with the redo log really puts the damper on update performance.
GUI tools included are poorly designed, rareley implementd at client sites. ( Enterprise Mgr, Oracle Forms, etc )
The toolset which ships with the database is rarely used by DBAs. Hence the popularity of TOAD and DB Artisan.
Also note: Oracle has poor replication tools. Other vendors (like
Informatica) are capitializing on this weakness by releasing their own tools.
|
|
