Log in

No account? Create an account
current entries friends' entries archives about me Previous Previous Next Next
Technical Life - cellophane — LiveJournal
the story of an invisible girl
Technical Life
read 16 comments | talk to me!
pstscrpt From: pstscrpt Date: April 1st, 2008 01:09 pm (UTC) (Link)
I'd really want serious cost justification before I even considered it.
I figured you were changing because you couldn't find Sybase people, anymore. Maybe not.

if you "talk to it wrong" some things are much slower
That's probably true of any of them, but I have particularly noticed that the Oracle optimizer really doesn't seem to be very bright. I'm mostly comparing Oracle 9i to SQL Server 2000, though, and that might not be entirely fair.

Hard and fast rules are dumb
Hard and fast rules in general are dumb, as every problem is different. Hard and fast rules within one system, though, tell you where to look when you have trouble.
kyril From: kyril Date: April 2nd, 2008 06:41 am (UTC) (Link)
The optimization is somewhat different: in Oracle, you want to prepare statements or use stored procedures wherever possible, using BEGIN/END; makes parsing slow beyond all sanity and even "soft parses" (all the literature implies if it's not a "hard parse" it's not too bad) take 90+% of the time a hard parse takes.

The empty string turns into NULL, and it's not true in Oracle that NULL = NULL, nor NULL != NULL, though NULL IS NULL is true. So any time you might have empty strings or NULL values, you have to add a separate "is NULL" case. (In Sybase, '' = ' ' and '' is not null; null = null, and not (null <> null) is also true.)

Usually it doesn't pick stupid query plans if your statistics are vaguely up to date. Usually. But there's subtle things that make it stupid. And even if you have two tables ordered by indexes on the key you join them on, sometimes it still likes to hash the tables (equivalent of Sybase REFORMATTING if quicker) rather than doing nested loops.

Oracle Index Organized Tables seem very much like Sybase Clustered Indexes, except Oracle proponents say they're totally different. And they say Oracle's "clusters" are a third totally different thing...but they don't really look it, from my perspective.

Sybase and Oracle like to miss an index and convert the indexed columns to your literal's type rather than convert your literal to the type supported by the index. But at least in Sybase you can specify a BINARY literal.

Oracle 10G parallelizes (esp. the select part of multi-table insert/select) better than Sybase 12.5 but maybe not better than the current Sybase 15.

The list goes on...

...and no we didn't run out of Sybase people, we thought we had more Oracle support than what little we got. And a premium was placed on using Company Standard Products, plus Oracle goes on someone else's budget but Sybase was on ours. They won't even let us use SQL Server unless the application is internal and puny.
pstscrpt From: pstscrpt Date: April 2nd, 2008 01:18 pm (UTC) (Link)
stored procedures wherever possible
75% of my work since June has been PL/SQL programming, so I've got that part covered. Would the hard parse be where it does the hash comparison to see if it already has that SQL compiled?

it's not true in Oracle that NULL = NULL
SQL Server is descended from Sybase, but they corrected the "Null = Null" thing in SQL Server 7 (the version before 2000), and it returns Null, like it's supposed to. I was just getting started in databases then, and was surprised to learn that, because I didn't know you could successfully compare Null to Null. Anyway, I'm kinda surprised Sybase hasn't changed that, yet.

People have been complaining about Oracle treating empty strings as Null for many years. I hear rumors they're going to fix it, but nothing concrete.

But there's subtle things that make it stupid.
Yup. One thing I've learned is that, even though a WHERE EXISTS may seem like it's saying exactly what I want and ought to be more efficient, it's likely to have 300 times the execution cost of a derived table equivalent. I'm not really sure, though, if the optimizer really does just have more shortcomings, or it's that I had eight years of experience learning what SQL Server likes.

Sybase and Oracle like to miss an index and convert the indexed columns to your literal's type rather than convert your literal to the type supported by the index.
SQL Server does that, too. You can have a VarChar column and do a select "Where MyColumn = 3", and it will work until somebody puts in a value that can't be interpreted as a number.

And a premium was placed on using Company Standard Products
That's how we wound up using Oracle's Java tools at Talk America (after they bought out LDMI, where I used to work). The idea was that we could just call up Oracle for help, no matter where in the stack a problem occurred. I think that ended when Talk America was bought by Cavelier, though.
read 16 comments | talk to me!