not a future discussion post
optimizer
up the right plan for all different query flavors? (so many
customers so many different dataset so many possibilities so
many different ways of writing the sql)
how many open/active optimizer related issues/cases Sybase
have?
Is Oracle optimizer better than Sybase?
Carl Kayser
<optimizer> wrote in message news:4af5f71e.627...@sybase.com...
> is it really possible to create an optimizer which will pick
> up the right plan for all different query flavors? (so many
> customers so many different dataset so many possibilities so
> many different ways of writing the sql)
I think you have answered the question yourself.
>
> how many open/active optimizer related issues/cases Sybase
> have?
>
> Is Oracle optimizer better than Sybase?
Well, I believe that both of the current optimizers are volcano based and
Oracles has been around longer ... so you would think that it has benefited
from more experience and would be better. But we have one query that runs
in 10 minutes on Oracle 10 and 2+ hours on Oracle 11. I would be reluctant
to believe any claim that one is better than the other.
John McVicker
O6 - they had a rule-based optimizer, then added a cost-based optimizer in
1993 with O7. It "worked" but needed attention through the future of
Oracle. Sybase had issues with some optimization thru the years too. When
I worked at Sybase in the 90s, sometimes we called it a "pessimizer" when it
chose wrong query plans.
The new optimizer is looking better but is maturing like anything else. It
can help queries but only if it knows enough about the data and index
structures. It's also up to the customers to do enough update index
statistics to inform the optimizer more about the data spread.
There are bugs, sure, and as they get addressed in any RDBMS product -
things get better. Our grandkids will have excellent RDBMS systems to work
with.
Example:
- SQL Server tends to perform merge joins a little too much in larger
queries - a few customers I've seen have to force LOOP JOIN hints.
- Oracle has too many hints to count and has lots of ways to use them
- Sybase 15 is new (even though it's a couple years old) that it is evolving
with every new customer who upgrades and reports issues. it is new for many
customers now as Sybase is forcing upgrades this year rather than people
taking their time in the last few years. More ASE 15 performance "issues"
are cropping up lately. Many solveable with some proper indexing on tables
which "now run slower in 15". The optimizer cannot entirely guess at what
you intended.
Bottom line, though - make sure you design *good* indexes on tables. A lot
of customers will design 1-column indexes on many tables and complain about
performance problems. You need to use as wide an index as you can to help
out many situations. Don't lead all indexes with the same column, either.
Highly skilled data modellers and ERD planners are hard to find for
companies looking at going cheap. They don't want to pay $100K+ for a guy
to design SQL Server tables on a server that they bought for $20K.
- John