DB2 vs Oracle
Bob Miles
We are doing a comparison of DB2 and Oracle to replace our Informix
database runing SAP. I have found some good white papers on why to
select Oracle vs DB2, but have not found some good reasons to select
DB2 rather than Oracle.
If anyone can point me to a good source of DB2 pluses over Oracle, I
would appreciate it.
Thanks,
Bob
Blair Adamache
their cooperation is sort of a shotgun marriage.
DB2 and SAP have lots of excellent integration. See:
http://www-3.ibm.com/software/data/partners/ae1partners/sap/
Specifically the benchmark data links off that page.
Larry
aixquery
Bob
we run v. large number of boxes with SAP/DB2. Its not as common as
Oracle/SAP of course , so one factor is the getting people with the
right skillset ( if you are xtraining its not too big a deal ).
I think the SAP DB2 support is good, even though the bias in SAP is
understandably to Oracle/SAP, there are things now such as the site
below
http://www.sap-db2.de/
which is non SAP official but is moderated by SAP DB2 people. It
should give you a flavour of some of the issues.
There are definitely more utilities delivered with SAP for Oracle than
for DB2 - there is no "sapdba" menu system to do maintenance tasks for
instance. Also some SAP products may still first be supported on
Oracle. But if your site is comfortable with Informix under SAP I
don't think you'll find DB2 under SAP any problem.
Db2 shouldn't lose on cost, though depending on the salespeople,
Oracle may match it.
HTHs
Erik Hendrix
We're using UDB with SAP and I have to say, no complaints. And within SAP
the monitoring for the database has been heavily improved for UDB compared
to Oracle or so. Especially from 4.6C hotpack 37 onwards.
You might also get better support to move from Informix to UDB since both
are now from IBM.
I can't say how the relationship between Oracle and SAP is now, but not too
long ago SAP and IBM put together some kind of partnership to go after small
businesses. I've also read somewhere that SAP will prefer UDB over Oracle.
Nothing official here offcourse, just some reading from the past.
"Bob Miles" <bobn...@attbi.com> wrote in message
news:3f57db0f...@netnews.comcast.net...
Mark A
>
> We're using UDB with SAP and I have to say, no complaints. And within SAP
> the monitoring for the database has been heavily improved for UDB compared
> to Oracle or so. Especially from 4.6C hotpack 37 onwards.
> You might also get better support to move from Informix to UDB since both
> are now from IBM.
>
> I can't say how the relationship between Oracle and SAP is now, but not
too
> long ago SAP and IBM put together some kind of partnership to go after
small
> businesses. I've also read somewhere that SAP will prefer UDB over Oracle.
> Nothing official here offcourse, just some reading from the past.
>
(Order Entry) systems. IBM has spent well over 100 million on SAP licenses,
and this has helped to cement the relationship between IBM and SAP. Just
like any other customer, these SAP software licenses have to be renewed
every 4 years or so, which will be additional revenue to SAP from IBM.
Obviously, the agreement was that IBM would use DB2 for its SAP
installations, and that SAP would make DB2 compatibility with SAP software a
high priority.
Daniel Morgan
Well if it helps you at all ... Microsoft runs SAP on Oracle.
But to be perfectly even-handed both DB2 and Oracle are just fine as the
database underlying SAP and the differences between them are not, in and
of themselves, sufficiently pursuasive to move anyone one way or the other.
When spending as much money as these systems cost ... the differences
are insignificant.
If performance and scalability were significantly different no-one would
be arguing these factors.
Concentrate on where the money for these systems is really spent ... on
personnel resources and training. Examine your enterprise for what skill
sets you have in-house, the availability of resources available for hire
or on contract, the availability of training, and the availability of
training materials.
You can save $20,000 or lose a few seconds of CPU and never really
notice. But find yourself unable to hire a decent staff of DBAs,
developers, or SysAdmins and you're in a world of hurt.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Database Guy
> Concentrate on where the money for these systems is really spent ... on
> personnel resources and training. Examine your enterprise for what skill
> sets you have in-house, the availability of resources available for hire
> or on contract, the availability of training, and the availability of
> training materials.
>
> You can save $20,000 or lose a few seconds of CPU and never really
> notice. But find yourself unable to hire a decent staff of DBAs,
> developers, or SysAdmins and you're in a world of hurt.
I claim no knowledge of SAP or the relative costs of staff, but surely
in the case of a package migration from Informix to another database
you are shouldn't need any developers - aren't we essentially just
talking DBMS installation and configuration, data export, data import,
go? Would have thought a knowledge of SAP might be the overriding
skill.
Therefore the fact that one DBMS is half the cost of another may be
quite significant.
Two or three DBA's can be trained in either DB2 or Oracle as needs be
- at many sites these days, DBA's already support multiple DBMS's.
DG
Daniel Morgan
Database Guy wrote:
Daniel Morgan <damo...@exxesolutions.com> wrote in message news:<1062811287.368062@yasure>...Concentrate on where the money for these systems is really spent ... on personnel resources and training. Examine your enterprise for what skill sets you have in-house, the availability of resources available for hire or on contract, the availability of training, and the availability of training materials. You can save $20,000 or lose a few seconds of CPU and never really notice. But find yourself unable to hire a decent staff of DBAs, developers, or SysAdmins and you're in a world of hurt.I claim no knowledge of SAP or the relative costs of staff, but surely in the case of a package migration from Informix to another database you are shouldn't need any developers - aren't we essentially just talking DBMS installation and configuration, data export, data import, go? Would have thought a knowledge of SAP might be the overriding skill.
Therefore the fact that one DBMS is half the cost of another may be quite significant.
In fact DB2 + Tivoli is pretty much a wash against Oracle when one is bean counting. And I can't imagine anyone wanting to run SAP without some pretty hefty security. So you'll not find your 50%: You'll be lucky to find 5% unless your CFO has some religious conviction against negotiating.
Two or three DBA's can be trained in either DB2 or Oracle as needs be - at many sites these days, DBA's already support multiple DBMS's. DG
Bob Miles
After reading the responses to my posting and talking to reps from IBM
and Oracle, I agree that Daniel Morgan is right that either database
system will work just fine with SAP R3. On the TPC web site I found a
TPC-C result using the same hardware for the database server (a 32
processor IBM p690) for both DB2 UDB V8.1 and Oracle 10g with less
than a 0.6% difference between the database systems.
For warehouse systems, which we will also be using, I would lean
toward Oracle for the range partitioning and bitmap indexes. The
range partitioning does add additional work for the DBA, but is faster
when dropping large chunks of data.
I do have a bias toward Oracle since I know it quite well since I have
worked with it for several years and I am a certified Oracle DBA.
What causes me to now to lean toward DB2 is that there seems to be
less probability of outsourcing of the administration function to
India.
Mark A
it was important, they would use it. I don't think that most DBA's really
understand the underlying architectures well enough to understand which is
better, especially since the indexes are implemented differently (even for
b-trees) in both products. The best way to evaluate performance is via a
benchmark.
As previously mentioned in another post, IBM uses SAP internally and has
already spent over a $100 million in SAP licenses (not including hundreds of
millions more for implementation), all implemented on various DB2 platforms.
SAP and IBM have a close alliance and commitment towards each other.
Obviously, Oracle will also work fine with SAP, even if Oracle's ERP
business (and aspirations) make them less than friendly some times.
Daniel Morgan
Mark A wrote:
"Bob Miles" <bobn...@attbi.com> wrote in message news:3f691111....@netnews.comcast.net...
<snipped>
I don't know why you think bitmap indexing is so important. If IBM thought it was important, they would use it.
they were important, they'd use them. Sorry for the derisive laughter but the same can be said of
every feature in every product on the market sold by any company for any purpose.
Surely you can just be honest and acknowledge that there are things that product A does that product
B doesn't do and that some customers might find them of value. If the purpose of these groups is
brain-dead weasel marketing have at it. But if the point of these groups is to be objective and fact-based
it seems to me that integrity dictates that sometimes we acknowledge that this product or that has a weakness.
I challenge anyone to name any product that doesn't have weaknesses. They all do. Get over it!
I don't think that most DBA's really understand the underlying architectures well enough to understand which is better, especially since the indexes are implemented differently (even for b-trees) in both products. The best way to evaluate performance is via a benchmark.
vast majority of DB2 DBAs I've worked with have been knowledgeable and professional. And the same
can be said for those working in Oracle, Informix, and Sybase (got to leave a little room to diss. MS again).
As previously mentioned in another post, IBM uses SAP internally and has already spent over a $100 million in SAP licenses (not including hundreds of millions more for implementation), all implemented on various DB2 platforms. SAP and IBM have a close alliance and commitment towards each other. Obviously, Oracle will also work fine with SAP, even if Oracle's ERP business (and aspirations) make them less than friendly some times.
Again sorry for the laughter but lets get real. Either product will suffice for SAP. If the in-house expertise is with
Oracle's architecture that is probably the best choice for that one particular customer. If he'd said DB2 I'd say
exactly the opposite. But if the fear, as stated, is a job being off-shored ... that's not likely to happen with
accounting records unless the OP's company is planning on going out of business soon.
Mark A
and ">>" into my response. Accepted etiquette for newsgroups is to use text
format. Thank you.
>>I don't know why you think bitmap indexing is so important.
>>If IBM thought
>>it was important, they would use it.
>>I don't know why you think stability, security, and scalability
>>are important. If Microsoft thought
>>they were important, they'd use them. Sorry for the derisive
>>laughter but the same can be said of
>>every feature in every product on the market sold by any
>>company for any purpose.
>Surely you can just be honest and acknowledge that
>there are things that product A does that product
>B doesn't do and that some customers might find them
>of value. If the purpose of these groups is
>brain-dead weasel marketing have at it. But if the point
>of these groups is to be objective and fact-based
>it seems to me that integrity dictates that sometimes we
>acknowledge that this product or that has a weakness.
>I challenge anyone to name any product that doesn't
>have weaknesses. They all do. Get over it!
DB2 like all products has its share of shortcomings. I do not agree that
lack of bit-map indexes is one of them. Check out the benchmark results and
tell me why it is so much better if the performance is virtually identical.
>>I don't think that most DBA's really
>>understand the underlying architectures well enough
>>to understand which is
>>better, especially since the indexes are implemented differently
>>(even for b-trees) in both products. The best way to evaluate
>>performance is via a benchmark.
>That is absolutely untrue. From my experience every professional
>and product has its slouches. But the
>vast majority of DB2 DBAs I've worked with have been
>knowledgeable and professional. And the same
>can be said for those working in Oracle, Informix, and Sybase
>(got to leave a little room to diss. MS again).
DBA's are not slouches just because they don't fully understand ALL of the
internal workings of the database. This is especially true of relational
database, where the theory of the relational database is to deliberately
isolate the access method details from the user. Just the fact that you
claim that having a bit-map index is necessarily better, proves to me that
you don't understand the internal workings of the databases. If had b-tree
indexes like Oracle, I would probably want bit-map indexes also. I can
absolutely categorically state that YOU don't understand all the internal
differences between Oracle and DB2 with respect to indexes.
One thing is for sure--you lack a fundamental understanding of relational
database theory. Any vendor can do things to make their databases run
faster, but when they do, they are sometimes moving away from the relational
model (where the access statement is completely independent of the access
path). IBM has databases like IMS fastpath that will blow Oracle out of the
water 10 times in a TPC-C benchmark, but it is not a relational database.
Oracle, which started out with a lower adherence to the relational model
than DB2 (according to the famous article published by the inventor of the
relational model, Dr. Ted Codd in Computerworld about 15 years ago), has
moved further away from relational over the years. It has tied applications
and database closer together, using non-relational concepts and proprietary
features that make Oracle less relational and less able to be ported to
another ANSI standard relational database.
If DB2 and Oracle have almost identical benchmark results, then I don't see
how you can argue that Oracle has some performance features that DB2 does
not have. But it would not surprise me if IBM added bit-map indexes, not to
improve performance, but to improve their marketing efforts to morons like
you.
>>As previously mentioned in another post, IBM
>>uses SAP internally and has already spent over a $100 million
>>in SAP licenses (not including hundreds of
>>millions more for implementation), all implemented on various
>>DB2 platforms. SAP and IBM have a close alliance and
>>commitment towards each other.
>>Obviously, Oracle will also work fine with SAP, even if
>>Oracle's ERP business (and aspirations) make them less
>>than friendly some times. And as mentioned previously Microsoft
>>usess SAP internally and has decided to use Oracle.
>Again sorry for the laughter but lets get real. Either product will
>suffice for SAP. If the in-house expertise is with Oracle's architecture
>that is probably the best choice for that one particular customer.
>If he'd said DB2 I'd say exactly the opposite. But if the fear, as
>stated, is a job being off-shored ... that's not likely to happen with
>accounting records unless the OP's company is planning on
>going out of business soon.
>Daniel Morgan
I agree (and explicitly said so) that either database will work fine with
SAP. Only possible difference is admin cost, not a difference in
performance. The reason that I mentioned the relationship of IBM and SAP was
to underscore the commitment of SAP to DB2. This was not always the case,
and most Oracle salesman will still claim that SAP does not support DB2 as
well as Oracle. I guess I thought you were smart enough to understand the
context of my remark.
Mark Townsend
> I don't know why you think bitmap indexing is so important. If IBM thought
> it was important, they would use it.
Unless it was protected by a patent, of course.
> has
> already spent over a $100 million in SAP licenses (not including hundreds of
> millions more for implementation)
Hmm - not a great endorsement. I wonder if the implementation would have
been cheaper if Oracle had been used - after all, I wonder how many SAP
BASIS consultants out there have DB2 skills vs Oracle ?
Lets check the SAP education website for training courses. That may give
(some of) us a clue
BASIS on Oracle -
http://www.sap.com/usa/education/curriculum/course.asp?rid=55&cid=60040368
- 3 dates in America in the next three months
Basis on DB2 LUW -
http://www.sap.com/usa/education/curriculum/course.asp?rid=55&cid=60050438
- this course is currently not scheduled
Informix -
http://www.sap.com/usa/education/curriculum/course.asp?rid=55&cid=60051016
- this course is currently not scheduled
Basis on DB2/390 -
http://www.sap.com/usa/education/curriculum/course.asp?rid=55&cid=60051017
- this course is currently not scheduled (and it's even taught by IBM)
A quick check of the UK schedule at
http://www.sap.com/uk/education/courses/edusched.pdf shows that they
don't even bother to include DB2 courses in the literature.
Mark A
news:lOaab.376417$cF.112913@rwcrnsc53...
>
> > I don't know why you think bitmap indexing is so important. If IBM
thought
> > it was important, they would use it.
>
> Unless it was protected by a patent, of course.
>
matching the AND & OR found in the search criteria of your query. But DBA's
really don't need to know this since it is completely transparent to them.
Other databases use hashing to directly address data on disk, including
IBM's IMS and CA's IDMS (both non-relational). DB2 always addresses data
from memory first (and puts data in memory if not already in memory), so
direct addressing of disk is not as important or useful. It also creates
very inefficient usage of storage, causing large holes of unused disk space.
This would make sequential prefetch much less effective.
Bottom line is that the underlying physical architectures of Oracle and DB2
have some differences that lead them to use different methods to access
data. In the end, the performance is almost identical, so it is not as
simple as why one database doesn't use a particular internal method to
achieve optimal performance.
> > has
> > already spent over a $100 million in SAP licenses (not including
hundreds of
> > millions more for implementation)
>
> Hmm - not a great endorsement. I wonder if the implementation would have
> been cheaper if Oracle had been used - after all, I wonder how many SAP
> BASIS consultants out there have DB2 skills vs Oracle ?
>
IBM didn't need any DB2 training. The cost was for a corporate unlimited-use
license all over the world. IBM is a much more decentralized company than
you might suspect, and there were many different implementations of SAP on
several different platforms.
The implementation cost was not unusual considering the number of separate
SAP installations at IBM, and the complexity of migrating from multiple
manufacturing and fulfillment legacy systems, and interfacing with the many
other IBM systems that were not replaced by SAP. No one has ever said that
implementing SAP is cheap.
David Cuffee
DB2. The only think you might have a problem with in DB2 is dropping a
column. Not very easy to do, you have to basically move the data to a new
table without the column you want to drop :).
David
"Bob Miles" <bobn...@attbi.com> wrote in message
news:3f691111....@netnews.comcast.net...
>
Daniel Morgan
>You might find that the difference in cost will also make you lean toward
>DB2. The only think you might have a problem with in DB2 is dropping a
>column. Not very easy to do, you have to basically move the data to a new
>table without the column you want to drop :).
>
>David
>
>
So you say. But every price comparison of apples with apples, rather
than apples with oranges, I've ever run has shown the prouducts to be
roughly equal in cost.
Take the price of Oracle standard edition licensed by CPU and compare
with DB2 + Tivoli + C compiler which is what is required as Oracle
supplies the complete security infrastrcture and C compiler in its
standard license.
Your mileage may vary. But I've negotiated agreements with both
companies and have yet to see a significant difference in price.
I think this "DB2 costs less" is just another bit of mythology that
needs to be put to bed. Can anyone here say they bought their last car
based on price and no other feature? How about your TV? Your shoes?
Certainly IBM has far more to offer a potential buyer than price. If not
time to move to MS Access and get that really big savings.
Daniel Morgan
>One thing is for sure--you lack a fundamental understanding of relational
>database theory.
>
One thing for sure Mark ... you didn't take the couple of seconds
required to follow the links under my name. I started in this industry
in 1969 and now teach relational databases, graduate level (all of my
students already have at lesat a computer science degree), for a major
research university. And I have been approached by a second name
university to help them further develop their program.
I'd suggest you take one of my classes ... except that doing so would
require the ability to both read and perform reserach.
Sorry for the dig but you really did beg for it with both hands out. In
the future try not to cross the line into personal insult and others may
not follow.
Ian
> So you say. But every price comparison of apples with apples, rather
> than apples with oranges, I've ever run has shown the prouducts to be
> roughly equal in cost.
>
> Take the price of Oracle standard edition licensed by CPU and compare
> with DB2 + Tivoli + C compiler which is what is required as Oracle
> supplies the complete security infrastrcture and C compiler in its
> standard license.
Why do you keep saying that you need Tivoli to use DB2?
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Mark A
"Daniel Morgan" <damo...@x.washington.edu> wrote in message
news:1063893259.743238@yasure...
> David Cuffee wrote:
>
> >You might find that the difference in cost will also make you lean toward
> >DB2. The only think you might have a problem with in DB2 is dropping a
> >column. Not very easy to do, you have to basically move the data to a new
> >table without the column you want to drop :).
> >
> >David
> >
> >
> So you say. But every price comparison of apples with apples, rather
> than apples with oranges, I've ever run has shown the prouducts to be
> roughly equal in cost.
>
> Take the price of Oracle standard edition licensed by CPU and compare
> with DB2 + Tivoli + C compiler which is what is required as Oracle
> supplies the complete security infrastrcture and C compiler in its
> standard license.
>
> Your mileage may vary. But I've negotiated agreements with both
> companies and have yet to see a significant difference in price.
> I think this "DB2 costs less" is just another bit of mythology that
> needs to be put to bed. Can anyone here say they bought their last car
> based on price and no other feature? How about your TV? Your shoes?
> Certainly IBM has far more to offer a potential buyer than price. If not
> time to move to MS Access and get that really big savings.
>
> --
> Daniel Morgan
Oracle prices have come down to match the prices of DB2 and MS SQL Server.
Mark A
> >database theory.
> >
> One thing for sure Mark ... you didn't take the couple of seconds
> required to follow the links under my name. I started in this industry
> in 1969 and now teach relational databases, graduate level (all of my
> students already have at lesat a computer science degree), for a major
> research university. And I have been approached by a second name
> university to help them further develop their program.
>
> I'd suggest you take one of my classes ... except that doing so would
> require the ability to both read and perform reserach.
>
> Sorry for the dig but you really did beg for it with both hands out. In
> the future try not to cross the line into personal insult and others may
> not follow.
>
> --
> Daniel Morgan
I looked at your links and am not impressed by "credentials." Instead, I
rely on the content of your posts. Based on that, you understanding of
relational database theory and Oracle/DB2 internals is severely lacking.
Personally, I hope you choose Oracle. You deserve each other.
P. Saint-Jacques
Bob Miles wrote:
> After reading the responses to my posting and talking to reps from IBM
> and Oracle, I agree that Daniel Morgan is right that either database
> system will work just fine with SAP R3. On the TPC web site I found a
> TPC-C result using the same hardware for the database server (a 32
> processor IBM p690) for both DB2 UDB V8.1 and Oracle 10g with less
> than a 0.6% difference between the database systems.
>
> For warehouse systems, which we will also be using, I would lean
> toward Oracle for the range partitioning and bitmap indexes. The
> range partitioning does add additional work for the DBA, but is faster
> when dropping large chunks of data.
######## Have looked enough at multi dimension cluster tables in DB2 V8
to "equate" (maybe, perhaps) to range partioning. This is independant
of wether you are using single or multinodes. My understanding is that
they provide the same functionality at far less "cost" to both optimizer
and DBA's.
Also, you might want to look at the way DB2 uses index ANDing and so on.
Here, i'm out of my depth as I don't know how Oracle bitmap indexes
work. Just a thought!
Regards, Pierre.
P. Saint-Jacques
opposed to a personal.
There polite ways to indicate one's disagreement with another one's
position and/or knowledge.
None of us pretend to know everything about everything and I don't think
they should.
If one has reservations and expresses them politely has Danile has done,
I don't see what slouches, morons,... has to do in this discussion.
This is not a personal debate group but one where one attempts to get
information and also learn a lot.
Thank you for your attention, Pierre.
Mark A
news:3F69DDA3...@nospam.attglobal.net...
> Can we please, PLEASE, keep this on DB2 group discussion level as
> opposed to a personal.
> There polite ways to indicate one's disagreement with another one's
> position and/or knowledge.
> None of us pretend to know everything about everything and I don't think
> they should.
> If one has reservations and expresses them politely has Danile has done,
> I don't see what slouches, morons,... has to do in this discussion.
> This is not a personal debate group but one where one attempts to get
> information and also learn a lot.
>
> Thank you for your attention, Pierre.
>
Here are some of the personal, condescending, and derisive remarks that
Daniel made about me, before my remarks that you object to:
"I don't know why you think stability, security, and scalability are
important. If Microsoft thought they were important, they'd use them. Sorry
for the derisive laughter..."
"Surely you can just be honest..."
"If the purpose of these groups is brain-dead weasel marketing have at it."
"every professional and product has its slouches..." not directed toward me,
but you are under the mistaken idea that I called anyone a slouch. Daniel
used the word, not me. I said that DBA's don't need to know all the
internals of database technology.
"And as mentioned previously Microsoft usess SAP internally and has decided
to use Oracle. Again sorry for the laughter but lets get real."
Given the above statements that Daniel made about me, I stand by comments
about him, and don't apologize for them.
Database Guy
> Daniel Morgan wrote:
>
> > So you say. But every price comparison of apples with apples, rather
> > than apples with oranges, I've ever run has shown the prouducts to be
> > roughly equal in cost.
> >
> > Take the price of Oracle standard edition licensed by CPU and compare
> > with DB2 + Tivoli + C compiler which is what is required as Oracle
> > supplies the complete security infrastrcture and C compiler in its
> > standard license.
>
> Why do you keep saying that you need Tivoli to use DB2?
Judging from his previous posts, because he feels that operating
system user authentication is insecure. It's a bogus argument to
support a view that he wishes was true. Needless to say, customers
don't rush out to buy Tivoli when they implement DB2.
The C compiler thing is basically true (for now, and assuming you want
to create SQL stored procedures but lack a supported compiler) but the
cost is trivial. On some platforms people often can and do use free
(but officially unsupported) compilers without any issues. Even an
"expensive" compiler like MS Visual C++ only costs about $60 for the
required licence, which is negligible compared with the extra cost of
an Oracle licence.
Despite his propaganda in non-Oracle newsgroups, when posting to
Oracle groups Daniel Morgan suggests Oracle is losing out by being
overpriced:
"But if rumors are true about Oracle changing its
pricing model ... the 10g license might be cheaper than
the 9i. This is one rumor I hope is true because it
won't just help all of us ... it will help Oracle too."
[Daniel Morgan, 16th September 2003]
Incidentally, Oracle may change its pricing model but it sure as hell
won't make it cheaper. Smoke and mirrors, bluster etc. If it did
actually halve its licences to match DB2, it would more-or-less halve
its revenues; Oracle is way too dependent on DBMS sales.
DG
Daniel Morgan
You don't need it except with respect to my comment that one must
compare apples with apples.
If you want security equal to that provided by Oracle you need Tivoli or
something like it. Just as if you want a C compiler
you have to purchase one of those too. This isn't rocket science here
... just making sure that one is comparing objects
that are roughly equal.
Lets face it, to make a vehicle purchase you wouldn't compare a Jaguar
with an 18 Wheeler ... you'd compare it with a BMW or Mercedes.
Daniel Morgan
I teach is the one the students want. If that offends your ego .... fine. But to me they are tools like hammers
and screw drivers. I don't get emotionally attached to them.
Daniel Morgan
Daniel Morgan
One thing though I'd suggest everyone do is pay attention to the fact that the software market is one of the most rapidly
changing commercial marketplaces. What was true last year may not be true today. To quote, on September 18th pricing
information about Software Brand X vs Software Brand Y based on an experience six to 12 months before is without
foundation.
And as I highly doubt anyone here has reached into his or her checkbook in the last year and purchased licenses for a commercial installation of an RDBMS so I doubt any of you actually know the real-world prices being offered by either company after everyone laughs at the list price and gets down to dealing. All of this X is cheaper than Y stuff is all mythology and heresay. Got written quotes for comparable systems from Oracle, DB2, Sybase, and Informix in your pocket? Post 'em!
And if price is your big concern ... run, don't walk, to MySQL.
Darin McBride
> You don't need it except with respect to my comment that one must
> compare apples with apples.
This sounds fine on the surface, but I think it misses part of the
point. Don't get me wrong - there are valid reasons to compare apples
to apples, but there are also valid reasons to compare apples to
oranges. Vitamin C content, for starters.
If I need more vitamin C, I'm going for the orange. If I like the
taste better, maybe I'll choose the apple. However, if I eat my apple
and take a Vitamin C pill, it may "cost" the same, but I'll get
something I like to eat ;-)
> If you want security equal to that provided by Oracle you need Tivoli or
> something like it. Just as if you want a C compiler
*IF*. I think that's the most important part of what you say. What if
you do NOT need that security level? Does Oracle give you a cheaper
quote if you don't need it? Or will they slap you with the fee and
force it on you?
> you have to purchase one of those too. This isn't rocket science here
Again, *IF* you need a C compiler. Perhaps you're using a pure-Java
environment <shudder>. Or everything is querying through VB <shudder
again>. Why pay for a compiler license if you don't need it?
> ... just making sure that one is comparing objects
> that are roughly equal.
Because that's to Oracle's advantage. Sure, you may be getting
everything and the kitchen sink, but what if you don't NEED that much
stuff?
> Lets face it, to make a vehicle purchase you wouldn't compare a Jaguar
> with an 18 Wheeler ... you'd compare it with a BMW or Mercedes.
No, I'd compare them all with my needs and my budget. And my wants, of
course. If I need to haul rolls of carpet, I'm going to not even
entertain the Jaguar, no matter how fast and nice the vehicle is.
Conversely, if I can avoid the 18 wheeler (I want to avoid it!), then I
will.
No matter which RDBMS you're looking at, you need to compare against
your list of priorities and requirements. Once you get a solution
proposed by each vendor, you can now compare costs. If you don't need
Tivoli's security, then that won't be part of DB2's quote. If you do,
then IBM better quote that to you, or their proposal won't meet your
needs, and will be irrelevant.
Mark A
sarcasm personally.
>--
>Daniel Morgan
Oh really. Then why don't document that.
My retort to you is that you don't understand the underlying architectures
of Oracle and DB2 well enough to know why they use different index schemes.
If you did understand the architectures, particularly the different way they
each store data physically on disk, then you would not have made your
ignorant comments.
You also have not explained how one database can be "lacking" in certain
performance features (such as its indexing scheme) when benchmarks prove
that the databases are virtually identical in TPC-C (transaction)
benchmarks, and when DB2 generally beats Oracle in TPC-H (Ad hoc query)
benchmarks.
Next, you have not addressed the claim that Oracle has moved further away
from the relational model in recent years (even though Dr. Codd rated it
lower than DB2 in relational compliance years ago), and that Oracle is more
and more tied at the user level to the way the data is physically stored.
Instead you wave around some credentials about how you teach classes in
relational technology. Well guess what buster, so do I.
Lastly, your claim that Oracle has all the features of Tivoli Storage
Manager is ridiculous, especially since Tivoli supports Oracle databases and
it is used with many Oracle installations. I guess you think all those
Tivoli customers who bought it for use with Oracle are idiots?
Fan Ruo Xin
Bob Miles wrote:
> For warehouse systems, which we will also be using, I would lean
> toward Oracle for the range partitioning and bitmap indexes. The
> range partitioning does add additional work for the DBA, but is faster
> when dropping large chunks of data.
>
===
That is pretty easy...
Bitmap index - how to think of this technology. Your applications care
more about a static bitmap index or they care more about STAR-JOIN,
INDEX-ANDING or ORING, ...
Range partitioning - I can say it is so easy to implement it using DB2
UDB, and there are a couple of solutions to do this. The thing is not you
can? do this; it is which way you should choose, which way is the better
choice for your scenario ?
I am wondering if you would like to spend just a little time to take a
full look on the presentation ?DB2 Internals for Administrators - which
was written by an IBM guru Matt Huras and presented on the IDUG. This will
not going to take you so long time if you are using INFORMIX server.
Regards,
Fan Ruoxin
Ian
> Ian wrote:
>
>> Daniel Morgan wrote:
>>>
>>> Take the price of Oracle standard edition licensed by CPU and compare
>>> with DB2 + Tivoli + C compiler which is what is required as Oracle
>>> supplies the complete security infrastrcture and C compiler in its
>>> standard license.
>>
>> Why do you keep saying that you need Tivoli to use DB2?
>
> You don't need it except with respect to my comment that one must
> compare apples with apples.
>
> If you want security equal to that provided by Oracle you need Tivoli or
> something like it.
I want you to list the actual security features that are present in
Oracle that you "need" Tivoli to reproduce.
P. Saint-Jacques
What a grand opportunity you had of bringing (or keeping) this back to a
decent polite level.
I hope you do't think that this forum is a "school yard" where "He did
it first ...." applies.
I also stand by my remarks and they apply to any or all who use the
forum like that.
Politeness and appropriate reserve never hurt anybody and it keeps
things so much on an even keel.
Regards, Pierre.
Mark A
> I apologize for missing his but the point is still missed....
> What a grand opportunity you had of bringing (or keeping) this back to a
> decent polite level.
> I hope you do't think that this forum is a "school yard" where "He did
> it first ...." applies.
> I also stand by my remarks and they apply to any or all who use the
> forum like that.
>
> Politeness and appropriate reserve never hurt anybody and it keeps
> things so much on an even keel.
> Regards, Pierre.
>
he really knows much about what he speaks, and is just repeating what Oracle
told him. I assume he did not know that what Oracle told him is not
accurate.
Mark Townsend
> That is pretty easy...
> Bitmap index - how to think of this technology. Your applications care
> more about a static bitmap index or they care more about STAR-JOIN,
> INDEX-ANDING or ORING, ...
None of these techniques are mutually exclusive in Oracle. In fact, the
whole point of a BMI is to make OR and AND operations really, really
fast. And it's when you start to add them to a star join (especially if
you use bit mapped join indexes) that truly Wonderful (TM) things start
to happen. And note that you can afford to build BM Join indexes because
on average they use 75% less space than a B-Tree.
Some people have been known to argue that the whole reason IBM purchased
Informix was to get hold of Red Brick, so that they could start to use
some of the equivalent technology in DB2 (that patent thingy again). I
have no idea if this is true, howver.
> Range partitioning - I can say it is so easy to implement it using DB2
> UDB, and there are a couple of solutions to do this. The thing is not you
> can? do this; it is which way you should choose, which way is the better
> choice for your scenario ?
Oracle has more than Range partitioning. Currently Range, Hash, List,
Range-Hash, List-Hash.
Built in support for partitioning in a DB is extremely useful - for
example, without support for a level one partition object, it's pretty
much impossible to have
1) Global indexes (indexes that span 1 or more partitions). Really,
really useful when you want to access a partition on something other
than the partition key.
2) Global Statistics.
3) Partitioned Materialized Views that rewrite to partitioned base
tables - these allow different partitions in an MV to become stale at
different times. Query rewrite aginst the base tables happens for the
stale partitions, but uses the relevant partitions of the MV's for up to
date ones (up and down the dimension hierachy). The same query can use a
combination of rewrites against the base tables and the MV's. Really,
really useful when you have a lot of data to load and then summarize,
with queries that hit different levels in the same dimension hierarchy
(i.e weekly, monthly, quarterly).
Mark Townsend
> Next, you have not addressed the claim that Oracle has moved further away
> from the relational model in recent years (even though Dr. Codd rated it
> lower than DB2 in relational compliance years ago), and that Oracle is more
> and more tied at the user level to the way the data is physically stored.
If you'd care to give an example I'd be more than happy to address it.
>
> Lastly, your claim that Oracle has all the features of Tivoli Storage
> Manager is ridiculous, especially since Tivoli supports Oracle databases and
> it is used with many Oracle installations. I guess you think all those
> Tivoli customers who bought it for use with Oracle are idiots?
Similar to the disdain you have for customers that have bought Oracle ?
Daniel Morgan
Darin McBride wrote:
Daniel Morgan wrote:You don't need it except with respect to my comment that one must compare apples with apples.This sounds fine on the surface, but I think it misses part of the point. Don't get me wrong - there are valid reasons to compare apples to apples, but there are also valid reasons to compare apples to oranges. Vitamin C content, for starters.
If I need more vitamin C, I'm going for the orange. If I like the taste better, maybe I'll choose the apple. However, if I eat my apple and take a Vitamin C pill, it may "cost" the same, but I'll get something I like to eat ;-)If you want security equal to that provided by Oracle you need Tivoli or something like it. Just as if you want a C compiler*IF*. I think that's the most important part of what you say. What if you do NOT need that security level? Does Oracle give you a cheaper quote if you don't need it? Or will they slap you with the fee and force it on you?
Heck if MySQL on Linux will do the you'd be stark raving made to buy the offerings of either IBM, or Oracle.
you have to purchase one of those too. This isn't rocket science hereAgain, *IF* you need a C compiler. Perhaps you're using a pure-Java environment <shudder>. Or everything is querying through VB <shudder again>. Why pay for a compiler license if you don't need it?
compare things of equal asset value. You wouldn't want to get into an argument with someone that had a copy of MS Access and
claimed that because it did everything he needed DB2 was overpriced. I sure wouldn't want to. The conversation would be just
plaint ridiculous. All I'm saying, again and again and again, is that if you want to compare the pricing of DB2 with Oracle, or DB2
with Sybase, or whatever you must compare equal implementations.
... just making sure that one is comparing objects that are roughly equal.Because that's to Oracle's advantage. Sure, you may be getting everything and the kitchen sink, but what if you don't NEED that much stuff?
Product is better meets my needs and it costs less too.
Lets face it, to make a vehicle purchase you wouldn't compare a Jaguar with an 18 Wheeler ... you'd compare it with a BMW or Mercedes.No, I'd compare them all with my needs and my budget. And my wants, of course. If I need to haul rolls of carpet, I'm going to not even entertain the Jaguar, no matter how fast and nice the vehicle is. Conversely, if I can avoid the 18 wheeler (I want to avoid it!), then I will.
where I have tried to be clearer.
No matter which RDBMS you're looking at, you need to compare against your list of priorities and requirements. Once you get a solution proposed by each vendor, you can now compare costs. If you don't need Tivoli's security, then that won't be part of DB2's quote. If you do, then IBM better quote that to you, or their proposal won't meet your needs, and will be irrelevant.
and the fact that Windows O/S security was insufficient to protect a production database therefore one would need to
consider Tivoli, etc. This was not and is not (at least from my standpoint) about OS/390. Please don't try to tell me, or
anyone else, that you would put your corporate books on SAP and DB2 on a WinXP box and feel you had something
that couldn't be trashed by a 24 year old. Because if you are willing to guarantee my students they won't be prosecuted
I will gladly prove to you that your faith was ill-founded. ;-)
Daniel Morgan
If you are looking for an argument you'll have to have it with someone else because I don't engage at that level and will henceforth not
respond to such postings. And I didn't say Oracle had all the features of Tivoli. I said DB2 needs Tivoli or something similar to have the
functionality in Oracle. If you wish to paraphrase me please be accurate. Thank you.
Daniel Morgan
Here's a good start:
http://www-3.ibm.com/software/swnews/swnews.nsf/n/twan59xr64?OpenDocument&Site=default
Mark A
news:4vuab.103100$mp.4...@rwcrnsc51.ops.asp.att.net...
> > Lastly, your claim that Oracle has all the features of Tivoli Storage
> > Manager is ridiculous, especially since Tivoli supports Oracle databases
and
> > it is used with many Oracle installations. I guess you think all those
> > Tivoli customers who bought it for use with Oracle are idiots?
>
> Similar to the disdain you have for customers that have bought Oracle ?
>
disdain for people who make up false accusations against other products that
perform virtually identical (or better) to Oracle in benchmark tests.
Mark A
> wrote in message news:1063945558.847771@yasure...
>Not one thing said in this entire thread .... and I reviewed all of its
>postings has ever referred to how any RDBMS stores anything on disk.
That's the problem. Oracle indexing schemes are different (in part) from DB2
because of the way they store data on disk. That's why you don't understand
the internals of either system (not that I think a DB2 needs to understands
all the internals). Your comments are irrelevant anyway since you admitted
that the performance of both databases in TPC-C is virtually the same. So
why are you complaining about the lack of certain features in DB2 to boost
performance?
>If you are looking for an argument you'll have to have it with
>someone else because I don't engage at that level and will henceforth not
>respond to such postings. And I didn't say Oracle had all the
> features of Tivoli. I said DB2 needs Tivoli or something similar to
>have the functionality in Oracle. If you wish to paraphrase me please
> be accurate. Thank you.
>Daniel Morgan
You already engaged in that level, in fact you started it.
DB2 does not need Tivoli--period. You arguments are straight out the Oracle
sales manual. But no one is buying it.
Daniel Morgan
Benchmarks are worth exactly what you pay for them and not a penny more unless they are run with your application,
on your hardware, with your O/S and your patch levels tuned by your SAs and DBAs. And no one every inserts,
updates, or deletes more than a few percent of the records.
Daniel Morgan
are stored on disk? Not part of the thread and not relevant to anything I've posted. Now "since you admitted that
the performance of both databases in TPC-C is virtually the same." when I've not once typed a sentence containing
the letters TPC in a row. I will no longer respond to your postings if they do not stick closely to the subject and
topic of this thread. I am not here for an argument and refuse to engage further at this level of discourse.
Mark A
> my AS/400 for a Wintel box?
AS/400 was good in its day, but that day is long gone. DB2/400 sucks.
>Benchmarks are worth exactly what you pay for them and not
>a penny more unless they are run with your application,
>on your hardware, with your O/S and your patch levels tuned
>by your SAs and DBAs. And no one every inserts,
>updates, or deletes more than a few percent of the records.
>Daniel Morgan
I don't know what kind of benchmarks you are talking about, but I am talking
about TPC benchmarks where each vendor does use whatever hardware and OS
they want, and the vendor does there own tuning. TPC benchmarks are
conducted by vendors, certified by independent consultants, and reviewed by
the TPC council.
Contrary to what you say, TPC benchmarks are quite comprehensive, using high
volumes (for both TPC-C and TPC-H).
Please stop posting in rich text, or I will have to start using the "M" word
again.
Daniel Morgan
Benchmarks are worth exactly what you pay for them and not a penny more unless they are run with your application, on your hardware, with your O/S and your patch levels tuned by your SAs and DBAs. And no one every inserts, updates, or deletes more than a few percent of the records. Daniel Morgan
I wasn't talking about any ... and since you seem heck-bent on changing the topic you are now talking to yourself.I don't know what kind of benchmarks you are talking about, <snipped>
William Rice
> performance features (such as its indexing scheme) when benchmarks prove
> that the databases are virtually identical in TPC-C (transaction)
> benchmarks, and when DB2 generally beats Oracle in TPC-H (Ad hoc query)
> benchmarks.
I feel I can give an example of a performance enhancing feature which
IBM UDB does not have which _definitely_ makes a difference on real
world systems.
Well I will say that range partitioning can give huge gains in
datawarehousing/reporting on large data sets where data is partitioned
by date range and the users are running reports on periods of time.
Case:
--1 Year of data partitioned by day
--User doing a report on 1 week of data.
This eliminates 98% of the data scanned in the fact table.
<SNIP>
Will Rice
P.S. My understanding is that DB/2 is implementing this feature in a
future release, but I do feel they are a bit behind in implenting such
a powerful feature.
Mark A
"William Rice" <ri...@operamail.com> wrote in message
news:1f1a539b.03091...@posting.google.com...
Range partitioning has been available on OS/390 for many years. Pseudo-range
partitioning can be accomplished on DB2 UDB (without paying for the
partitioning version of DB2) using multiple tables with a union all view. I
have tried this, and it works exceptionally well.
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0202zuzarte/0202zuzarte.pdf
No one would suggest that DB2 or Oracle (or any DBMS) cannot be improved,
and new features added. However, in most benchmark tests (on identical
hardware) the performance is almost identical. In cases where the
"performance" features are different, there are usually work-arounds to the
problem (as mentioned above) that achieve equivalent results. Therefore,
decisions about which database to use should probably not be made on the
basis of performance.
Larry Edelstein
>> I challenge anyone to name any product that doesn't have weaknesses. They all do. Get over it!
In that case Daniel, can you list ten weaknesses of Oracle's 9i database on this NG, and would you be willing to cross post them to the Oracle NG also?
Larry Edelstein
Comments interspersed.Mark A wrote:
"Bob Miles" <bobn...@attbi.com> wrote in message news:3f691111....@netnews.comcast.net...<snipped>I don't know why you think bitmap indexing is so important. If IBM thought it was important, they would use it.
I don't know why you think stability, security, and scalability are important. If Microsoft thought
they were important, they'd use them. Sorry for the derisive laughter but the same can be said of
every feature in every product on the market sold by any company for any purpose.Surely you can just be honest and acknowledge that there are things that product A does that product
B doesn't do and that some customers might find them of value. If the purpose of these groups is
brain-dead weasel marketing have at it. But if the point of these groups is to be objective and fact-based
it seems to me that integrity dictates that sometimes we acknowledge that this product or that has a weakness.
I challenge anyone to name any product that doesn't have weaknesses. They all do. Get over it!
That is absolutely untrue. From my experience every professional and product has its slouches. But theI don't think that most DBA's really understand the underlying architectures well enough to understand which is better, especially since the indexes are implemented differently (even for b-trees) in both products. The best way to evaluate performance is via a benchmark.
vast majority of DB2 DBAs I've worked with have been knowledgeable and professional. And the same
can be said for those working in Oracle, Informix, and Sybase (got to leave a little room to diss. MS again).As previously mentioned in another post, IBM uses SAP internally and has already spent over a $100 million in SAP licenses (not including hundreds of millions more for implementation), all implemented on various DB2 platforms. SAP and IBM have a close alliance and commitment towards each other. Obviously, Oracle will also work fine with SAP, even if Oracle's ERP business (and aspirations) make them less than friendly some times.
And as mentioned previously Microsoft usess SAP internally and has decided to use Oracle.
Again sorry for the laughter but lets get real. Either product will suffice for SAP. If the in-house expertise is with
Oracle's architecture that is probably the best choice for that one particular customer. If he'd said DB2 I'd say
exactly the opposite. But if the fear, as stated, is a job being off-shored ... that's not likely to happen with
accounting records unless the OP's company is planning on going out of business soon.
Blair Adamache
integration into DB2 on other platforms, but we came up with dynamic
bitmaps in 1997, so the DBA didn't have to choose which kind of index to
create.
I also thought that IBM and Oracle cross-licensed each other's database
patents? I could be wrong about that,
Mark Townsend wrote:
>
>> I don't know why you think bitmap indexing is so important. If IBM
>> thought
>> it was important, they would use it.
>
>
> Unless it was protected by a patent, of course.
>
Larry Edelstein
does it provide?
Larry
Daniel Morgan wrote:
> David Cuffee wrote:
>
> >You might find that the difference in cost will also make you lean toward
> >DB2. The only think you might have a problem with in DB2 is dropping a
> >column. Not very easy to do, you have to basically move the data to a new
> >table without the column you want to drop :).
> >
> >David
> >
> >
> So you say. But every price comparison of apples with apples, rather
> than apples with oranges, I've ever run has shown the prouducts to be
> roughly equal in cost.
>
> Take the price of Oracle standard edition licensed by CPU and compare
> with DB2 + Tivoli + C compiler which is what is required as Oracle
> supplies the complete security infrastrcture and C compiler in its
> standard license.
>
> Your mileage may vary. But I've negotiated agreements with both
> companies and have yet to see a significant difference in price.
> I think this "DB2 costs less" is just another bit of mythology that
> needs to be put to bed. Can anyone here say they bought their last car
> based on price and no other feature? How about your TV? Your shoes?
> Certainly IBM has far more to offer a potential buyer than price. If not
> time to move to MS Access and get that really big savings.
Haider Rizvi
the external world. Here it is again.
bobn...@attbi.com (Bob Miles) writes:
> For warehouse systems, which we will also be using, I would lean
> toward Oracle for the range partitioning and bitmap indexes. The
> range partitioning does add additional work for the DBA, but is faster
> when dropping large chunks of data.
Bob,
You may want to look at the multi-dimensional clustering capability of
DB2, available since v8.1. It is specifically targeted for data
warehouses, and in a way helps towards both the issues of range
partitioning and bitmap indexes.
Here are some articles on MDC that you may want to look at:
http://www7b.software.ibm.com/dmdd/library/techarticle/0207huras/0207huras.html
http://www-8.ibm.com/software/au/universe/download/db2/id112.pdf
http://www.db2mag.com/db_area/archives/2003/q2/bhattacharjee.shtml
http://db2mag.com/db_area/archives/2003/q2/welgan.shtml
Regards,
--
Haider
Haider Rizvi
Blair Adamache
2003-09-07-17:24:51 PST in the other flamethread ("Company thought DB2
will be better than Oracle") he mentioned Tivoli and Veritas.
In each case, if I read correctly, he referred to them as supporting
security.
This is confusing a lot of us, as most DB2 customers use Tivoli and
Veritas as backup managers - i.e. for database backup images (as well as
file systems that may or may not be used by DB2). I suspect that many
Oracle customers use Tivoli Storage Manager (if they use AIX), or Legato
or Veritas (on AIX, Solaris, Windows and HP-UX) for the same function,
as you say in your last paragraph.
If you search on Tivoli in the DB2 documentation
(http://publib.boulder.ibm.com/infocenter/db2help/index.jsp)
you'll see that Tivoli products in the DB2 context are used for storage
and monitoring. There is a Tivoli product called "Privacy Manager", but
I've never heard of any DB2 customer using it - whereas I hear about
Kerberos all the time.
Veritas Cluster server is also popular with DB2 customers for high
availability on Solaris (like HACMP on AIX), but I've never heard of a
DB2 customer using Veritas for security.
Larry Edelstein
function it provides, and what in Oracle provides that functionality. I'm still
waiting.
Larry Edelstein
Larry Edelstein
on your hardware, with your O/S and your patch levels tuned by your SAs and DBAs. And no one every inserts,
updates, or deletes more than a few percent of the records."
So I guess this means that you're against Oracle's use of TPC benchmarks in the press to make themselves look better than other databases, since they are not run with applications ... and you must be in favor of IBM's strategy of running benchmarks with ISV software like SAP?
Larry Edelstein
Larry Edelstein
Larry Edelstein
Larry Edelstein
Larry Edelstein
Darin McBride
If my goal and desire was to get from Toronto to Los Angeles, and I got
a quote for an 18 wheeler, for an SUV, and for a Jaguar, I probably
would say that the 18 wheeler was too expensive ;-) All three vehicles
would suffice for the goal and desire. None would be the cheapest
method, but all would work. They have features that I don't want or
need, and would have to pay for. To me, that's the bottom line in this
thread - who is the best bang for your buck ... given your goals and
needs.
> Heck if MySQL on Linux will do the you'd be stark raving made to buy the
> offerings of either IBM, or Oracle.
Very true. Again, there's that "if", but given that "if", you're quite
correct. Of course, both IBM and Oracle would propose that there are
features they have that make it a better choice for you, but if that's
just a pushy salesperson, then, yes, by all means, go with MySQL.
>>>you have to purchase one of those too. This isn't rocket science here
>>
>>Again, *IF* you need a C compiler. Perhaps you're using a pure-Java
>>environment <shudder>. Or everything is querying through VB <shudder
>>again>. Why pay for a compiler license if you don't need it?
>>
> I agree wholeheartedly. But lets not get hung up on this component or
> that. My only point was that to compare prices you must
> compare things of equal asset value. You wouldn't want to get into an
My point is that to compare prices, you must compare things that
fulfill your needs, whether the "asset value" was equal or not. Sure,
if you add all the bells and whistles, the prices may be equivalent.
But if you don't care about the bells and whistles, why compare them?
> argument with someone that had a copy of MS Access and
> claimed that because it did everything he needed DB2 was overpriced. I
No, because there wouldn't be an argument. If this person was correct
that MS Access did actually do everything they needed, then DB2 would
be overpriced for their needs. No argument whatsoever from me.
> sure wouldn't want to. The conversation would be just
> plaint ridiculous. All I'm saying, again and again and again, is that if
> you want to compare the pricing of DB2 with Oracle, or DB2
> with Sybase, or whatever you must compare equal implementations.
I know that's what you're saying. And I'm saying that, respectfully
(and without namecalling ;->), I disagree with this premise. I believe
you need to compare based on actual use requirements. Which, of
course, means that for some applications, MySQL will be the best bang
for your buck, but other applications will not be able to entertain
MySQL because it doesn't have the right features, performance,
platforms, or some other need or want. Does that make DB2 the
cheapest? I'm sure IBM wants to convince you that it is for YOUR
business. ;-)
>>>... just making sure that one is comparing objects
>>>that are roughly equal.
>>
>>Because that's to Oracle's advantage. Sure, you may be getting
>>everything and the kitchen sink, but what if you don't NEED that much
>>stuff?
>>
> Then don't buy it. But don't try to make a price comparison either and
> say Brand X is cheaper than Brand Y. Just say ...
> Product is better meets my needs and it costs less too.
Product X meets all my needs at the lowest price for doing so. That,
to me, is the best value. For you, product X may not meet your needs,
so its price is irrelevant.
>>>Lets face it, to make a vehicle purchase you wouldn't compare a Jaguar
>>>with an 18 Wheeler ... you'd compare it with a BMW or Mercedes.
>>
>>No, I'd compare them all with my needs and my budget. And my wants, of
>>course. If I need to haul rolls of carpet, I'm going to not even
>>entertain the Jaguar, no matter how fast and nice the vehicle is.
>>Conversely, if I can avoid the 18 wheeler (I want to avoid it!), then I
>>will.
>>
> I think you missed my point. Perhaps I should have been clearer. See my
> 18 wheeler example earlier in this thread
> where I have tried to be clearer.
I do not believe I missed your point, but I think our entire thread
will revolve around disagreements ;-)
>>No matter which RDBMS you're looking at, you need to compare against
>>your list of priorities and requirements. Once you get a solution
>>proposed by each vendor, you can now compare costs. If you don't need
>>Tivoli's security, then that won't be part of DB2's quote. If you do,
>>then IBM better quote that to you, or their proposal won't meet your
>>needs, and will be irrelevant.
>>
> Please correct me if I am wrong but way back when this thread started I
> commented specifically on DB2 on Windows
> and the fact that Windows O/S security was insufficient to protect a
> production database therefore one would need to
> consider Tivoli, etc. This was not and is not (at least from my
I think this, too, can depend on your needs. Perhaps for you, or
especially any university campus, you need that level of security.
Other companies may only have three employees which, if any of them go
sour, will ruin the company anyway, so OS security isn't as important
as physical security (preventing non-employees from accessing the
intranet). I think all of these requirements are relevant to the
actual customer in question.
> standpoint) about OS/390. Please don't try to tell me, or
> anyone else, that you would put your corporate books on SAP and DB2 on a
> WinXP box and feel you had something
Personally, I would never put anything of importance on Windows, but
that's because I'm somewhat biased ;-> (Heck, I wouldn't put anything,
important or not, on Windows if I could help it ;->)
> that couldn't be trashed by a 24 year old. Because if you are willing to
> guarantee my students they won't be prosecuted
> I will gladly prove to you that your faith was ill-founded. ;-)
I do believe I could set up a Windows machine running DB2 WSUE as the
backend to a web service with no bells or whistles that your students
couldn't hack into. Heck, I could tell them all my admin passwords,
just for fun, and it would still be impenetrable.
Of course, the web server would be running on Linux with the DB2
client, behind a firewall that only opened port 80. But I wouldn't
spend a (virtual) dime on Tivoli.
The only problem is physical security - which neither Tivoli nor Oracle
can solve anyway ;-)
Oh, and I also assume that I can trust my own family not to hack in on
your behalf - but then we get to the "employees going sour" bit again.
Serge Rielau
>If you'd care to give an example I'd be more than happy to address it.
AFAIK Microsoft actually challenged Oracle on that one
Reason being that the first row is being deleted that the DBMS gets it's
hands on.
Due to the physical definition of the underlying table it happens to be the
next in line for delivery.
Another example for non relational thinking is INSERT/UPDATE/DELETE WITH
RETURN
(and predictably it fell on it's face in the standard)
Us standard folks and DB2 SQL Language folks were also not amused when we
learned of the 10g MERGE enhancements.
Multitable Insert is a shortsighted frill (I hope for Oracle it was cheap to
do).
Oracle is very strong on PL/SQL, I also like some (not all) of the OR.
Core SQL however I'm thankful they don't lead the pack.
Cheers
Serge
DB2 SQL Compiler Development
Fan Ruo Xin
>the whole point of a BMI is to make OR and AND operations really,
>really fast. And it's when you start to add them to a star join
>(especially if you use bit mapped join indexes) that truly Wonderful
>(TM) things start to happen. And note that you can afford to build BM
>Join indexes because on average they use 75% less space than a B-Tree.
Yes, static bitmap technique and Index-ANDing, Index-ORing are not mutually
exclusive. If you create a static bitmap index, you still can do INDEX-ANDing or
ORing. Thanks to point this out.
My point is if this way gives the optimizer more choice? Does that means your
optimizer has to choose bitmap operation, if this way is the only choice for the
optimizer? Is that possible there is a better choice/access plan?
My answer is IT IS POSSIBLE.
Suppose I'd like to SELECT ... FROM fact_table WHERE region_id = 1 and
product_key = 12 ...
If my fact table happened to cluster based on the region_id. That means all the
rows with the same region_id will be stored together on the physical devices.
These rows can be read into the memory using more less I/O. Then db2 agent will
figure out the final qualified data from this temporary result sets. Please do
not forget, if these pages (those region_id=1) are not so big, the second step
will not need any more I/O, which can be done in the memory.
***Conclusion: Using dynamic bitmap technology give the optimizer more choice,
and give you more reasonable access plan.
>Some people have been known to argue that the whole reason IBM
>purchased Informix was to get hold of Red Brick, so that they could
>start to use some of the equivalent technology in DB2 (that patent
>thingy again). I have no idea if this is true, however.
=====
I don't know either. I wonder if Informix based on the same reason to purchase
RedBrick 4 or 5 years ago? Who knows if Oracle, or even MICROSOFT were never
thinking to hold it too? And Teradata never kept an eye on it?
I will be happy to know if IBM can plug some technology from RedBrick into DB2
UDB.
I don't know how much they need to pay for that. But I know this is a good thing
for the end user - only use one engine/code/install image to deal with any kind
of applications.
I will be happy to see DB2 UDB are going out of the shadow of ORACLE, they no
longer not only working hard on implement the features which Oracle provide, but
developing some new technology, which make Oracle will think if they need to
follow.
>Oracle has more than Range partitioning. Currently Range, Hash, List,
>Range-Hash, List-Hash.
If you need to choose a dbms between IBM DB2 UDB and ORACLE for your DW system,
which technology will you consider most? Partitioning (especially Range
partitioning)? How to compare?
Both DB2 UDB and ORACLE support PARALLEL processing technology.
If we could peer-to-peer compare the basic parallel operations - PARALLEL
SCAN/SORT/AGGREGATION - if we could use the same resource, same table size, same
access plan, ...
How is the performance result?
Both DB2 UDB and ORACLE support HASHING technology.
Why the hash join in one dbms is much faster than that in the other one ...
...
Even for the partitioning technology, Yes, Oracle provides all the partitioning
methods you list here. But which partitioning technology is used mostly in the
DW and DSS systems - HASHing
- Why do we need partitioning technology?
We want to decompose a very large table into a couple of smaller parts
(partitions) and improve I/O, CPU parallelism.
- Why do we use hashing partitioning in most time?
Because hashing partitioning is the best way to evenly distribute data on
multiple partitions - that is very, very important for the performance of DW and
DSS system.
Because hashing partitioning can provide the best performance when working
together with parallel technique.
Can DB2 UDB do the partitioning like Oracle's partitioning methods, let's see:
Range: MDC or UNION ALL view
List: MDC (not exactly the same)
Hash: (I don't need to say more ?
Composite Range-Hash: MDC + EEE
Composite Range-List: MDC
>Built in support for partitioning in a DB is extremely useful - for
>example, without support for a level one partition object, it's pretty
>much impossible to have
>1) Global indexes (indexes that span 1 or more partitions). Really,
>really useful when you want to access a partition on something other
>than the partition key.
====
Global indexes are very, very useful in the OLTP system. My personal opinion is
Local indexes is enough for the DW or DSS system.
>2) Global Statistics.
You can collect Global Statistics for the MDC tables, just like a regular table.
>3) Partitioned Materialized Views that rewrite to partitioned base tables -
these allow different
> partitions in an MV to become stale at different times. Query rewrite aginst
the base tables
> happens for the stale partitions, but uses the relevant partitions of the MV's
for up to date
> ones (up and down the dimension hierachy). The same query can >use a
combination of rewrites
> against the base tables and the MV's. Really, really useful when you have a
lot of
> data to load and then summarize, with queries that hit different levels in the
same
> dimension hierarchy (i.e weekly, monthly, quarterly).
You can create a MULTIPLE DIMENSIONAL CLUSTERING MQT. It is so much easy to
build and maintained, if compare with Oracle's partitioned MV.
Remember: Using MDC can give more benefits:
1). MDC will allow you clustering on multiple dimensions (columns), even on
function(column).
***NOTE*** the function can be either BUILD-IN functions or USER-DEFINED
functions.
AFAIK, Oracle's Range Partitioning key can't be based on the function.
2). Block index tree is far smaller than regular B-tree.
3). When you define a MDC, beside the dimension block index, db2 will also build
a composite block index (in all dimensions).
4). Easy to move the record(s) from one partition to another partition.
5). Easy to do ROLL IN / ROLL OUT of data.
6). The user don't need additional job to take care of ADD partition(s) or DROP
partition(s).
Regards,
FRX
Fan Ruo Xin
Sorry! please delete this one. I keep get problem to post the message.
Bob Miles
On Fri, 19 Sep 2003 21:58:26 GMT, Haider Rizvi
<hai...@nouce.ca.ibm.com> wrote:
>I had posted this response from another server but it never made it to
>the external world. Here it is again.
>
>bobn...@attbi.com (Bob Miles) writes:
>
>> For warehouse systems, which we will also be using, I would lean
>> toward Oracle for the range partitioning and bitmap indexes. The
>> range partitioning does add additional work for the DBA, but is faster
>> when dropping large chunks of data.
>
>Bob,
>
>You may want to look at the multi-dimensional clustering capability of
>DB2, available since v8.1. It is specifically targeted for data
>warehouses, and in a way helps towards both the issues of range
>partitioning and bitmap indexes.
I will look at these features.
The reason that I brought up bitmap indexes was my experience a couple
of years ago at a different company with a data warehouse system that
was test on an IBM ES9000 mainframe using DB2 and on a single IBM
RS6000 wide node on a SP2 using Oracle. Queries on the single RS6000
wide node ran faster than the mainframe using DB2 because of bitmap
indexes.
Before some people get upset that I am being biased because of one
result, let me say that bitmap indexes are just one tool for the DBA.
Woe be unto the poor DBA that uses bitmap indexes on a table that is
updated on the column of a bitmap index. With that said, I like to
have as many tools to select from as possible. Yes you can drive a
screw into a board with a hammer, but it is nice to have a
screwdriver.
Mark Townsend
Let me help. Some things in this doc sort of sound like stuff that
Oracle does.
> Tivoli access management solutions can reduce the need to code security into multiple applications.
We (Oracle) would claim that the virtual private database (VPD)
capabilities built into the database also significantly reduce the need
to code security into multiple applications. Hence this is one potential
security capability that Oracle has in the database that seems to be
also addressed by this Tivoli product (and no, dynamic views are NOT
equivalent)
> Identity management tools allow developers to create a common identity infrastructure across the enterprise that can reduce internal threats across applications,
Oracle supports LDAP based Enterprise User definitions in the database.
If you want to use strong authentication with these users (as opposed to
password authent), you would also need to use Oracle's ASO option (an
additional licence point to the DB). So this one may be a wash.
> while the IBM privacy tool helps developers comply with legal and organizational privacy policies when developing Web services applications.
Not sure what this actually means, but Oracle provides a fine grained
policy based audit capability in the database that can be used to both
comply with legal and organizational privacy requirements, and also
prove this compliance.
Mark Townsend
>>None of these techniques are mutually exclusive in Oracle. In fact,
>>the whole point of a BMI is to make OR and AND operations really,
>>really fast. And it's when you start to add them to a star join
>>(especially if you use bit mapped join indexes) that truly Wonderful
>>(TM) things start to happen. And note that you can afford to build BM
>>Join indexes because on average they use 75% less space than a B-Tree.
>
<snip discussion on MDC>
> ***Conclusion: Using dynamic bitmap technology give the optimizer more choice,
> and give you more reasonable access plan.
So I've read a little on MDC (:-)), and I really don't see how you can
equate it to what BMI does. There are fundamental differences between
the two (least of all being that BMI's (at least as they are implemented
in Oracle) deal much more elegantly with sparse data)
And sorry if I'm a little obtuse, but I don't see how the lack of an
indexing technology can give the optimizer more choice, and give you
more reasonable access plans. Seems to me the opposite would be true.
> But which partitioning technology is used mostly in the
> DW and DSS systems - HASHing
We would have to disagree on this one.
> - Why do we need partitioning technology?
> We want to decompose a very large table into a couple of smaller parts
> (partitions) and improve I/O, CPU parallelism.
> - Why do we use hashing partitioning in most time?
> Because hashing partitioning is the best way to evenly distribute data on
> multiple partitions - that is very, very important for the performance of DW and
> DSS system.
> Because hashing partitioning can provide the best performance when working
> together with parallel technique.
No. This is NOT why the majority of Oracle customers use partitioning.
The majority of Oracle customers use partitioning to manage rolling
window data loads.
> Global indexes are very, very useful in the OLTP system. My personal opinion is
> Local indexes is enough for the DW or DSS system.
Agree that Global indexes in an OLTP system rock. Disagree that local
indexes are enough for DW or DSS systems.
>>2) Global Statistics.
>
> You can collect Global Statistics for the MDC tables, just like a regular table.
Can you have one set for the table, and then another set of each MDC
'cluster'. Alternatively, with a UNION ALL approach, can you have one
overall set for all the tables ?
> You can create a MULTIPLE DIMENSIONAL CLUSTERING MQT. It is so much easy to
> build and maintained, if compare with Oracle's partitioned MV.
Why ?
>
> Remember: Using MDC can give more benefits:
> 1). MDC will allow you clustering on multiple dimensions (columns), even on
> function(column).
> ***NOTE*** the function can be either BUILD-IN functions or USER-DEFINED
> functions.
> AFAIK, Oracle's Range Partitioning key can't be based on the function.
If the column is stored it can be used as a partitioning key.
> 2). Block index tree is far smaller than regular B-tree.
But I seriously doubt that it's as small as a BMI
> 3). When you define a MDC, beside the dimension block index, db2 will also build
> a composite block index (in all dimensions).
> 4). Easy to move the record(s) from one partition to another partition.
Do you mean easier than with Oracle's approach ? How so ?
> 5). Easy to do ROLL IN / ROLL OUT of data.
Do you mean easier than with Oracle's approach ? How so ?
Mark Townsend
> Darn I can't stand by idle on that one....
Yeah, well, I wasn't talking to you :-P
>
>>If you'd care to give an example I'd be more than happy to address it.
>
> Hmm... take a look at the delete statement in TPC-C "order delivery".
> AFAIK Microsoft actually challenged Oracle on that one
> Reason being that the first row is being deleted that the DBMS gets it's
> hands on.
> Due to the physical definition of the underlying table it happens to be the
> next in line for delivery.
Is this what Mark A is talking about when he says Oracle is more tied to
the physical storage ? Does he get the same playbook as the rest of you
? :-)
>
> Another example for non relational thinking is INSERT/UPDATE/DELETE WITH
> RETURN
> (and predictably it fell on it's face in the standard)
Hmm - it made it's way into JDBC, didn't it ? - I actually think it's
very clever. And very, very fast.
> Us standard folks and DB2 SQL Language folks were also not amused when we
> learned of the 10g MERGE enhancements.
> Multitable Insert is a shortsighted frill (I hope for Oracle it was cheap to
> do).
OK, so this one I don't know what you are talking about - this will need
to be the next topic for the bar conversation when we are next co-located.
> Oracle is very strong on PL/SQL, I also like some (not all) of the OR.
> Core SQL however I'm thankful they don't lead the pack.
Well, I have a pet theory on why IBM actually NEEDS to standardize on
core SQL that has nothing to do with ideals or customer benefit at all
- but expounding on it would be churlish on my part.
Serge Rielau
> > RETURN
> > (and predictably it fell on it's face in the standard)
>
> Hmm - it made it's way into JDBC, didn't it ? - I actually think it's
> very clever. And very, very fast.
language.
Oracle implemented an interface between PL/SQL and SQL.
SELECT FROM INSERT is just as fast AND combines with SQL naturally because
it is relational
Come to my session at DBM Tech in Vegas and I tell you all about it: D37
"SQL Unleashed"
> > Us standard folks and DB2 SQL Language folks were also not amused when
we
> > learned of the 10g MERGE enhancements.
> > Multitable Insert is a shortsighted frill (I hope for Oracle it was
cheap to
> > do).
>
> OK, so this one I don't know what you are talking about - this will need
> to be the next topic for the bar conversation when we are next co-located.
I prefer my front porch. Bring a bottle of wine :-)
> > Oracle is very strong on PL/SQL, I also like some (not all) of the OR.
> > Core SQL however I'm thankful they don't lead the pack.
>
> Well, I have a pet theory on why IBM actually NEEDS to standardize on
> core SQL that has nothing to do with ideals or customer benefit at all
> - but expounding on it would be churlish on my part.
Uh.. new word, I'll sure learn to mis-spell that one :-)
Result of search for "churlish": churlish -- flegelhaft
If you happen to play on DB2's multiple codebases, don't be shy.
They force us (SQL Language Council) to sit back and not let the
implementation rule the semantics.
The result is that IBM's SQL Standard proposals are well thought through by
necessity.
I suppose it's the end result that counts:
Has Yukon implemented recursion using CONNECT BY or WITH?
Cheers
Serge
Mark A
> Is this what Mark A is talking about when he says Oracle is more tied to
> the physical storage ? Does he get the same playbook as the rest of you
> ? :-)
>
> Well, I have a pet theory on why IBM actually NEEDS to standardize on
> core SQL that has nothing to do with ideals or customer benefit at all
> - but expounding on it would be churlish on my part.
>
I know why Oracle doesn't follow standards. It wants to lock customers into
their product so that it will be cost prohibitive to ever switch DBMS's. The
have owned the market up until recently, and now are trying to hold on
against the competition.
I don't deny that most of the non-standard enhancements are useful, but they
defeat the purpose of having standards in the first place.
Database Guy
but this one takes nerve.
(1) Daniel Morgan wrote:
> Last time I looked MySQL beat DB2 on some benchmarks [etc]
So he's talking about benchmarks, right? Specifically, ones where
MySQL beat DB2 (obviously not TPC then).
(2) Mark A replied:
> I don't know what kind of benchmarks you are talking about [etc]
A fair enough question.
(3) Daniel Morgan then said:
> I wasn't talking about any ... and since you seem heck-bent
> on changing the topic you are now talking to yourself.
So when he was talking about benchmarks in (1), he in fact wasn't.
DG
Fan Ruo Xin
Mark Townsend wrote:
> I've read a little on MDC (:-)), and I really don't see how you can
> equate it to what BMI does. There are fundamental differences between
> the two
I don’t know why you think I equate MDC with BMI?
I mentioned the MDC here is because MDC can guarantee the clustering. You don’t need
to do reorganization on the MDC table.
Bitmap index is very useful for the equi- predicates. When using dynamic bitmap index,
the optimizer can decide if the bitmap operation is the best, then dynamic build
bitmap index; if not, you still benefit from where the regular index tree provide.
>No. This is NOT why the majority of Oracle customers use partitioning.
>The majority of Oracle customers use partitioning to manage rolling
>window data loads.
Not exactly. The introduction of partitioning technology in ORACLE begins with
addressing the big size of the user table or index. Oracle has to adjust partitioning
technology in order to deal with more and more ROLAP applications.
On the other way, DB2 UDB MDC is DESIGNED to address this kind of applications.
>> Global indexes are very, very useful in the OLTP system. My personal opinion is
>> Local indexes is enough for the DW or DSS system.
>Agree that Global indexes in an OLTP system rock. Disagree that local
>indexes are enough for DW or DSS systems.
>>>2) Global Statistics.
>> You can collect Global Statistics for the MDC tables, just like a regular table.
>Can you have one set for the table, and then another set of each MDC
>'cluster'. Alternatively, with a UNION ALL approach, can you have one
>overall set for all the tables ?
Range Partitioning in ORACLE and UNION ALL views in DB2 UDB are physical partitioning
approach.
Can you tell me how many tablespaces allowed to build in Oracle db?
Can you tell me how many partitions allowed to build for one user table?
But MDC in DB2 UDB is the logical partitioning approach. When using MDC in EE, you can
build either global or local indexes; you can collect statistics, either you think it
is as global or local.
>> You can create a MULTIPLE DIMENSIONAL CLUSTERING MQT. It is so much
>> easy to build and maintained, if compare with Oracle's partitioned MV.
> Why ?
Why why? Do you mean why I think it is easy to …?
Let me give you an example about the case you talk in the previous post, you will see
how easy it is.
/* Really, really useful when you have a lot of data to load and then summarize, with
queries that hit different levels in the same dimension hierarchy (i.e weekly,
monthly, quarterly). */
In db2 sample db, there is table – sales
$ db2 select * from sales
SALES_DATE SALES_PERSON REGION SALES
---------- --------------- --------------- -----------
12/31/1995 LUCCHESSI Ontario-South 1
12/31/1995 LEE Ontario-South 3
12/31/1995 LEE Quebec 1
12/31/1995 LEE Manitoba 2
12/31/1995 GOUNOT Quebec 1
03/29/1996 LUCCHESSI Ontario-South 3
03/29/1996 LUCCHESSI Quebec 1
….
create table mdc_mqt as (
select int(sales_date)/10000 as yy,
int(sales_date)/100 as yymm,
sum(sales) as total_sales
from sales
group by rollup(int(sales_date)/10000, int(sales_date)/100)
) data initially deferred refresh deferred
organize by (yy) /* partitioned by the year of sales_date */
refresh table mdc_mqt
select * from mdc_mqt
YY YYMM TOTAL_SALES
----------- ----------- -----------
- - 14
1995 - 1
1995 199512 1
1996 - 13
1996 199603 9
1996 199604 4
6 record(s) selected.
Please tell me how long it will take you to do the same thing I did here???
What do you think which way is easy to build??? Which way is easy to maintain????
>>
>> Remember: Using MDC can give more benefits:
>> 1). MDC will allow you clustering on multiple dimensions (columns), even on
>> function(column).
>> ***NOTE*** the function can be either BUILD-IN functions or USER-DEFINED
>> functions.
>> AFAIK, Oracle's Range Partitioning key can't be based on the function.
>If the column is stored it can be used as a partitioning key.
Does ORACLE give the user an approach to store the value based on the function?
>> 2). Block index tree is far smaller than regular B-tree.
>But I seriously doubt that it's as small as a BMI.
Maybe. Maybe not. --- I don’t know.
What I know is when you do Insert/Update/Delete on the based table, the cost of
block-index maintain is less than static bitmap index maintain.
>> 3). When you define a MDC, beside the dimension block index, db2 will also build
>> a composite block index (in all dimensions).
>> 4). Easy to move the record(s) from one partition to another partition.
>Do you mean easier than with Oracle's approach ? How so ?
>> 5). Easy to do ROLL IN / ROLL OUT of data.
>Do you mean easier than with Oracle's approach ? How so ?
>> 6). The user don't need additional job to take care of ADD partition(s) or DROP
>> partition(s).
For all the last three items, I would like to say it again, because MDC is logically
partitioning table. When you delete a bunch of data, and even drop the whole
partition, this is the same as you only delete one or a small size records. If db2
find out you delete the whole partition, db2 just mark the whole partition as free in
the block map. The DBA does not need to worry if the index was invalid ….
Regards,
FRX
Mark A
>
No, the indexes should be the same for each table, but they are tied to each
table and are not global. If data from multiple tables is required (and
hence multiple indexes need to be accessed) the access can be done in
parallel. The optimizer will choose to only access those tables (and
indexes) needed based on the where clause (or table constraints if you use
them instead of the where clause in the UNION ALL view).
I don't know how Oracle prices its partitioning/parallel offerings these
days (although I purchased it about 5 years ago), but the UNION ALL approach
in DB2 can be implemented without purchasing the more expensive partitioning
versions of DB2 (EEE or ESE).
http://www7b.boulder.ibm.com/dmdd/library/techarticle/0202zuzarte/0202zuzarte.pdf
I have used the UNION ALL technique with DB2 and did extensive analysis of
the access paths using the "explain." I was amazed how well it works.
Haider Rizvi
> The reason that I brought up bitmap indexes was my experience a couple
> of years ago at a different company with a data warehouse system that
> was test on an IBM ES9000 mainframe using DB2 and on a single IBM
> RS6000 wide node on a SP2 using Oracle. Queries on the single RS6000
> wide node ran faster than the mainframe using DB2 because of bitmap
> indexes.
Absolutely, if a particular workload fits bitmap indexing perfectly then
not much else can beat its performance. Question is how many times do
you do:
select count(*) from table where car.color = 'red'
We think MDC is a good approach towards data warehousing that doesn't
have the insert / update / delete penalty that bitmaps do. And this
approach seems to be catching customers' eyes as well.
Please post here if you have any technical questions on MDC.
Regards,
--
Haider
Daniel Morgan
Eh? I know Daniel Morgan has an Oracle-type relationship with truth, but this one takes nerve. (1) Daniel Morgan wrote:Last time I looked MySQL beat DB2 on some benchmarks [etc]So he's talking about benchmarks, right? Specifically, ones where MySQL beat DB2 (obviously not TPC then).
and read the entire article chain not just the first page.
Since you likely won't let me provide you with some quotes:
"Walking the talk, Oracle9i Database came out at the top of the heap with 629 pages per second."
"Almost nudging Oracle9i for top honors was the dark horse, MySQL, with 608 pages per second."
"BM's DB2 Universal Database managed 494 pages per second. What surprised us was that just after hitting its peak, the database inexplicably throttled back to around 200 pages per second."
(2) Mark A replied: I don't know what kind of benchmarks you are talking about [etc]A fair enough question. (3) Daniel Morgan then said:I wasn't talking about any ... and since you seem heck-bent on changing the topic you are now talking to yourself.So when he was talking about benchmarks in (1), he in fact wasn't. DG
Daniel Morgan
>Not exactly. The introduction of partitioning technology in ORACLE begins with
>addressing the big size of the user table or index. Oracle has to adjust partitioning
>technology in order to deal with more and more ROLAP applications.
>On the other way, DB2 UDB MDC is DESIGNED to address this kind of applications.
>
>
>
Sorry here but utter nonsense. Partitioning in Oracle is used for what
developers and DBAs decide to use it for. Not what Oracle
puts in an advertisement. And I've seen it used in applications from
AT&T, Boeing, Bank of America, etc. and not once for any
purpose of than to create an easy to maintain rolling window.
I'd be surprised if most of the people using it would do more than laugh
if told there was some relationship to ROLAP.
Mark A
> wrote in message news:1064113094.958019@yasure...
>Benchmark does not equal TCP. If you don't know that what>
>are you doing writing code in a relational database? ;-)
>Daniel Morgan
It's TPC, not TCP. TPC is the most respected database benchmarking
organization in the industry. All tests are conducted by vendors and
supervised by independent consultants (approved by the TPC council) who
monitor the benchmarking activities, and then submitted to the TPC council
for approval.
It's really amazing that someone who claims to teach relational database
theory, and who frequently talks about how one database performs better than
another, does not know about the TPC organization and benchmarks.
Larry Edelstein
You've apparently either missed ... or chosen not to mention ... the "clarification" that was printed in eWeek some months later:
See http://www.eweek.com/article2/0,4149,1184846,00.asp
"...The differences between our tests and the results IBM and Microsoft saw in their own labs using our code demonstrate how myriad environmental and coding factors can affect test results ..."
"... Using the code and configuration files we provided, IBM subsequently set up the benchmark test at its Toronto DB2 development lab to further explore the issue. IBM's testbed was somewhat different from ours擁t used a two-way database server with five disks rather than the four-way, 24-disk box we used. However, the basic architecture預 load balanced BEA Systems Inc. WebLogic Server application server tier, the same DB2 version and setup, the same amount of server memory, and the same size of database謡as carefully duplicated, all using eWEEK's data set and exact code and configuration files.
IBM did not see the drop-off we did, even after several rounds of testing with different configurations to try to force the drop-off to occur ( see DB2 performance chart )...."
Larry Edelstein
Daniel Morgan
"Daniel Morgan" <damo...@x.washington.edu wrote in message news:1064113094.958019@yasure...Benchmark does not equal TCP. If you don't know that what> are you doing writing code in a relational database? ;-) Daniel MorganIt's TPC, not TCP.
approaches to solving business problems, etc. Now we have the pleasure of being treated to spelling tests.
Just for fun I went to google.com and did a search of this group's postings for key words like "Newbie" and "Loop" and such to
see if you were attracting any new blood. I got more hits in c.d.ingress.
Reminding me of a favorite quotation:
Truth often suffers more by the heat of its defenders,
than from the arguments of it opposers.
-from Fruits of Solitude by William Penn
Mark A
>market-share hyperbole rather than SQL optimization, varying
>approaches to solving business problems, etc. Now we have the
>pleasure of being treated to spelling tests.
>Just for fun I went to google.com and did a search of this group's
>postings for key words like "Newbie" and "Loop" and such to
>see if you were attracting any new blood. I got more hits in c.d.ingress.
>Daniel Morgan
I have no idea what your diatribe above means. You may be interested to know
that Oracle is a member of TPC and Meikel Poess (Chairman TPC-H and TPC-R)
is Principal Software Developer with Oracle Corp.
Ian
> Correct. Try going to http://www.pcmag.com and search for "Clash of the
> Titans: SQL Databases" and read the entire article chain not just the
> first page.
>
> Since you likely won't let me provide you with some quotes:
>
> "Walking the talk, Oracle9/i/ Database came out at the top of the heap
> with 629 pages per second."
> "Almost nudging Oracle9/i/ for top honors was the dark horse, MySQL,
> with 608 pages per second."
> "BM's DB2 Universal Database managed 494 pages per second. What
> surprised us was that just after hitting its peak, the database
> inexplicably throttled back to around 200 pages per second."
Please don't tell me that you actually trust PC Magazine to complete
a good comparison of these products. PC Mag has always struck me as
just a shill for the vendors -- they don't criticize too much for fear
of losing advertising revenue.
In reading this article, it's pretty clear that the authors don't even
understand the architecture of the products, when they make a statement
like "Unlike DB2, Oracle9i, and Sybase ASE, SQL Server relies on Windows
for clustering." i.e. they don't understand the difference between
Clustering for availability (MSCS) and clustering for performance (EEE).
Plus, if they are making comments like, "DB2's administrative console,
Control Center, is among the best we tested." then you know that they
are not doing any kind of hard core product testing. I think we all
know that Control Center is not "the best."
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Database Guy
That's not what I said, and is anyway an attempt to change the
subject. The point I was quite obviously making is that when you
claimed not to be talking about benchmarks, you were clearly
contradicting yourself.
DG
Database Guy
> Dollars are dollars. And I didn't say DB2 wasn't a bit cheaper. I have
> found that if one compares apples with apples and negotiates, which is
> what everyone either does or should do, tha the price difference is
> small enough to be inconsequential.
DB2 is more than "a bit" cheaper - it's half the price for the same
level of functionality. And, more important, most people are smart
enough to realise that Oracle will bleed them dry once renewal time
comes up and they've locked you in to their (very) proprietry
database. So list prices do matter very much.
> And if price is your big concern ... run, don't walk, to MySQL.
So when you expressed your concerns about Oracle being overpriced in
an Oracle newsgroup...
"But if rumors are true about Oracle changing its
pricing model ... the 10g license might be cheaper than
the 9i. This is one rumor I hope is true because it
won't just help all of us ... it will help Oracle too."
[Daniel Morgan, 16th September 2003]
...you were really worrying that everyone is moving to mySQL, a
product that definitely isn't comparing apples with apples, and which
you can't possibly match on price? Pull the other one.
DG
Daniel Morgan
>
> Please don't tell me that you actually trust PC Magazine to complete
> a good comparison of these products. PC Mag has always struck me as
> just a shill for the vendors -- they don't criticize too much for fear
> of losing advertising revenue.
I trust them as much as I trust anyone else doing a benchmark while
taking money from vendors to support their organization's existance.
And that goes for the Transaction Processing Performance Council too. Go
to tpc.org. Click on 'Members' under 'Who We Are'.
Looks like the advertising page in any glossy publication to me.
Mark A
> > a good comparison of these products. PC Mag has always struck me as
> > just a shill for the vendors -- they don't criticize too much for fear
> > of losing advertising revenue.
>
> I trust them as much as I trust anyone else doing a benchmark while
> taking money from vendors to support their organization's existance.
>
> And that goes for the Transaction Processing Performance Council too. Go
> to tpc.org. Click on 'Members' under 'Who We Are'.
> Looks like the advertising page in any glossy publication to me.
>
> --
> Daniel Morgan
The TPC benchmarks are conducted by vendors (under the rules of each TPC
benchmark) and supervised by a independent consultant that is approved by
the TPC. This gives the vendor the opportunity to properly tune its own
product. The reason that all the vendors are members is so they can ensure
that the benchmark standards and procedures are fair and unbiased. These
standards have been developed and refined over many years. You can download
the test specifications from their site.
The TPC benchmarks are accepted as a fair test by all those who are members,
which includes IBM, Oracle, and MS. Not so with PC Magazine. I don't
necessarily think that PC magazine is always biased, but they surely are not
competent enough to run a fair test. BTW, no one spends more money on TPC
benchmarking than Oracle.
The fact that you were unaware of the TPC benchmarks until recently, casts
serious doubts about your knowledge of the relative database performance of
each product.
Fan Ruo Xin
Fan Ruo Xin
for this kind of applications. Does that mean this is the best way?
Daniel Morgan
find something of value the consumer should to. Obviously the vendors do ... but for the sole purpose of marketing
and advertising. I don't because I think marketing and advertising are concocted solely to sell things to people that
otherwise might not have had the intelligence to find buy them without being told what to buy.
Mark A
> wrote in message news:1064191467.611977@yasure...
>Even you don't believe a typo constitutes lack of knowledge.
>But what you might believe is that because the vendors
>find something of value the consumer should to. Obviously
>the vendors do ... but for the sole purpose of marketing
>and advertising. I don't because I think marketing and
>advertising are concocted solely to sell things to people that
>otherwise might not have had the intelligence to find buy
>them without being told what to buy.
--
>Daniel Morgan
It wasn't a typo. You repeated the mistake multiple times. Then you did a
goggle search on TPC, then after you found the web site you complained it
was too commercial because the members were vendors.
You knew nothing about TPC until I mentioned on this board. You are not
telling the truth. Have you no shame?
Further, by your comments above, you still don't understand the TPC. It is
the most widely accepted DBMS benchmark available today and is widely
respected by corporate America. The fact that the major DBMS vendors are all
members of the TPC, and they must approve the makeup of the benchmark tests
(with all the associated detail), ensures that no vendor can abuse the
benchmark.
Again you have demonstrated your total ignorance about DBMS performance and
benchmarking.
Pablo Sanchez
> Even you don't believe a typo constitutes lack of knowledge. But
> what you might believe is that because the vendors find something
> of value the consumer should to. Obviously the vendors do ... but
> for the sole purpose of marketing and advertising. I don't because
> I think marketing and advertising are concocted solely to sell
> things to people that otherwise might not have had the
> intelligence to find buy them without being told what to buy.
Yet again you are incorrect and to think, you "teach." *gulp*
The TPC's are valuable on several fronts - not that I expect you to
even begin to appreciate what's involved in an effort to product a
TPC number:
Hardware Vendor
---------------
* O/S scalability
* O/S tunes specific to the DBMS
* Hardware scalability
DBMS Vendor
-----------
* DB Tuning handbooks (which make their way to you, the "teacher")
* DB scalability
* DB kernel tuning
Customer
--------
* Benefits from the tuning done by the Hardware and DBMS vendor.
There is no code in DBMS kernels that says - well, not anymore!
<g>
#if TPC_C
<<highly_tuned SQL>>
#endif
* Capacity planning
--
Pablo Sanchez, Blueoak Database Engineering
http://www.blueoakdb.com
Daniel Morgan
>The DBAs and developers decide to use it because this is the best way Oracle can provide
>for this kind of applications. Does that mean this is the best way?
>
>
><snipped>
>
>>Sorry here but utter nonsense. Partitioning in Oracle is used for what
>>developers and DBAs decide to use it for. Not what Oracle
>>puts in an advertisement. And I've seen it used in applications from
>>AT&T, Boeing, Bank of America, etc. and not once for any
>>purpose of than to create an easy to maintain rolling window.
>>
>>I'd be surprised if most of the people using it would do more than laugh
>>if told there was some relationship to ROLAP.
>>
>>--
>>Daniel Morgan
>>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
>>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
>>damo...@x.washington.edu
>>(replace 'x' with a 'u' to reply)
>>
>>
If you are asking with respect to a rolling window yes. It certainly
helps improve DML performance. But it turns pruning into a
near instantaneous affair. And, I should add, also is used to simplify
backups. Once a partition no longer requires updating the
tablespace is converted to readonly and no further backups are required.
I may disagree with some of the people here on non-technical matters but
I don't wish to be rude. If you have any further Oracle
questions lets take them to c.d.o.server so as not to be off-topic. Thanks.
Fan Ruo Xin
Daniel Morgan wrote:
>but I don't wish to be rude.
I bet you don't ... if we can call it 'polite' --- when you jump here without understanding
what people are talking about.
/* Why do we need partitioning technology?
We want to decompose a very large table into a couple of smaller parts (partitions)... */
I am wondering I was denied at the first place when I said the root reason of partitioning is
because of the BIG TABLE SIZE or the LARGE VOLUME OF DATA. Do you suggest the Oracle DBA to
build and configure a couple of tablespaces, split a SMALL table into a couple of more
smaller parts in order to take the advantage of the feature - rolling window???
>If you are asking with respect to a rolling window yes. It certainly
>helps improve DML performance. But it turns pruning into a
>near instantaneous affair. And, I should add, also is used to simplify
>backups. Once a partition no longer requires updating the
>tablespace is converted to readonly and no further backups are required.
Please tell me for the DW applications, the No. 1 advantage of partitioning is not because of
improving QUERY PERFORMANCE (I/O, CPU parallelism improving) - but DML PERFORMANCE ???
Please tell me the main reason for rolling out the oldest data is because of SIMPLIFYING THE
BACKUP, not because of the storage can be REUSED by the more recently data.
…
Again, the following is from my previous posting message:
/* Why do we need partitioning technology?
We want to decompose a very large table into a couple of smaller parts (partitions) and
improve I/O, CPU parallelism. */
>I'd be surprised if most of the people using it would do more than
>laugh if told there was some relationship to ROLAP.
May I ask you - in which scenario rolling window is used mostly? That's not fact table? That
has nothing with ROLAP applications?
Again either rolling fact table or rolling MV is not because of the large volume data of the
fact tables or MVs?
>I may disagree with some of the people here on non-technical matters
You did impress me when you were talking the TECHNICAL issue.
Daniel Morgan
Ian
[trimmed range based partitioning discussion]
>>
> As this relates to Oracle rather than DB2 please repost your question in
> the appropriate usenet group.
Why? Many customers do the equivalent of Oracle range-based artitioning
in DB2 UDB with multiple tables and UNION ALL.
Therefore, the discussion of range-based partitioning is very relevant
in this newsgroup, regardless of the physical implementation.
So, can you answer FRX's questions? I'll restate them here since I
trimmed them off.
1) Why would you use Range Based Partitioning on small tables?
2) Do you roll out old data because you can, or because you have to?
Given infinite storage space, would you still roll out old data?
Daniel Morgan
I often use range partitioning on small tables. Here are the main reasons:
The convenience of being able to prune data without the danger of
implementing delete statements
with possibly incorrect WHERE clauses in a production database. Last
time I was at AT&T on a
consulting gig a DBA (and no it wasn't me) accidentally missed on part
of a WHERE clause and the
result was ... to put it gently ... unpleasant. We implemented
partitioning immediately.
The second reason is the ability to place a single table into multiple
tablespaces allowing for the ability
to make partitions read-only. This guaranteed data security and
eliminated the need to back up data
that will never be changed.
With the price of DASD as low as it is I can't imagine why I'd roll out
old data to save space on small
tables. Large ones: Absolutely but not small ones. But in many
situations, for example a client I have in
the retail business, they partition SKUs from year to year so they can
easily get rid of 2002 data when
2003 rolls around which improves data integrity. Someone can't
accidentally pick last-year's SKU or
last year's catalog price. Could it be handled in another way?
Absolutely. But code requires QA testing.
Pruning a partition does not. Which save a lot of time and money.
HTH
William Rice
> "William Rice" <ri...@operamail.com> wrote in message
> news:1f1a539b.03091...@posting.google.com...
> > <SNIP ALL SORTS OF FUN COMMENTS>
> > > You also have not explained how one database can be "lacking" in certain
> > > performance features (such as its indexing scheme) when benchmarks prove
> > > that the databases are virtually identical in TPC-C (transaction)
> > > benchmarks, and when DB2 generally beats Oracle in TPC-H (Ad hoc query)
> > > benchmarks.
> >
> > I feel I can give an example of a performance enhancing feature which
> > IBM UDB does not have which _definitely_ makes a difference on real
> > world systems.
> >
> > Well I will say that range partitioning can give huge gains in
> > datawarehousing/reporting on large data sets where data is partitioned
> > by date range and the users are running reports on periods of time.
> >
> > Case:
> > --1 Year of data partitioned by day
> > --User doing a report on 1 week of data.
> > This eliminates 98% of the data scanned in the fact table.
> >
> > <SNIP>
> >
> > Will Rice
> > P.S. My understanding is that DB/2 is implementing this feature in a
> > future release, but I do feel they are a bit behind in implenting such
> > a powerful feature.
>
> Range partitioning has been available on OS/390 for many years. Pseudo-range
> partitioning can be accomplished on DB2 UDB (without paying for the
> partitioning version of DB2) using multiple tables with a union all view. I
> have tried this, and it works exceptionally well.
> http://www7b.boulder.ibm.com/dmdd/library/techarticle/0202zuzarte/0202zuzarte.pdf
>
> No one would suggest that DB2 or Oracle (or any DBMS) cannot be improved,
> and new features added. However, in most benchmark tests (on identical
> hardware) the performance is almost identical. In cases where the
> "performance" features are different, there are usually work-arounds to the
> problem (as mentioned above) that achieve equivalent results. Therefore,
> decisions about which database to use should probably not be made on the
> basis of performance.
I will admit, I had not explored the union all option due to past
issues on other DBMS's comming up with good plans when views were
involved. I will have to do some testing of that in DB2.
While I think the work around listed in the link definitely gives DB2
more flexibility than I originally thought it had, the limitations
section does have some low numbers for the number of branches you can
have(36 branches for a reasonable compile time, 200 branches for a
query to be able to access it at all.)
Example given earlier
> > Case:
> > --1 Year of data partitioned by day
> > --User doing a report on 1 week of data.
> > This eliminates 98% of the data scanned in the fact table.
This would change to
-- 1 year of data partitioned by 2 week fragments(to stay under 36
branch limitation)
-- User doing report on 1 week of data
This eliminates 94-96% of the data scanned.
Still a huge gain in the test case given, though it will take at twice
as much i/o to scan the fact table, and won't scale as I add more
data.
I will due some testing on degredation with more than 36 branches if I
get a chance.
Thanks for the information,
Will
Mark A
I tried it and also ran visual explain to verify the parallel access paths
and the ability of DB2 to access only those partitions (tables) necessary
per the WHERE clause of the query. I did not try more 36 tables in the UNION
ALL.
Blair Adamache
I ran similar searches on newbie and loop in this newsgroup and in
c.d.ingres - like so: newbie group:comp.databases.ibm-db2
I got more hits for these terms on DB2, and goole doesn't even cover the
many db2 newsgroups hosted on news.software.ibm.com.
Oh well, ymmv
Matthew Emmerton
Since we used UNION ALL in our recent TPC-C benchmarks, we've made
performance improvements for queries involving UNION ALL views with multiple
branches, which will be shipped in DB2 UDB v8 FP4.
These changes reduce the section size with large numbers of branches, adds
the ability to JOIN multiple UNION ALL views effectively, as well as various
compile-time and run-time improvements when executing queries against UNION
ALL views.
--
Matt Emmerton
IBM DB2 OLTP Performance
Mark Townsend
> Yes ... I would also like to understand specifically why DB2 needs Tivoli, what
> function it provides, and what in Oracle provides that functionality. I'm still
> waiting.
>
> Larry Edelstein
Perhaps you can ask the other Larry Edelstein. He seems to know all
about it - to the question
> Hello everybody,
>
> we are new with db2 (using oracle for many years) and we wonder about
> some things concerning monitoring the db's.
>
> For example there seems to be no tool looking automatically for error
> in the diaglog. I searched here in the newsgroups and found out that
> most of you use grep or egrep. Okay, we do so either, and surely it
> will funktion but i am still looking for some tools watching my dbs
> like the oracle enterprise manager (if anybody know this).
>
> The tool should sent email in case of entrys in the errorlogs or when
> the db is gone away or things like that.
>
> Is there any? On any Platform?
>
> Many thanks,
>
> Lara
He replied ...
> Specifically for monitoring of the db2diag.log, Tivoli is your best bet.
> Using the "Tivoli Monitoring For Databases" product, you can develop what
> is called a custom logfile adapter which can parse any type of log file
> for specific msgs and take actions.
I presume you are not related ?
>
> Blair Adamache wrote:
>
>
>>At 2003-09-12-17:01:21 PST Daniel mentioned Tivoli and Veritas, and at
>>2003-09-07-17:24:51 PST in the other flamethread ("Company thought DB2
>>will be better than Oracle") he mentioned Tivoli and Veritas.
>>
>>In each case, if I read correctly, he referred to them as supporting
>>security.
>>
>>This is confusing a lot of us, as most DB2 customers use Tivoli and
>>Veritas as backup managers - i.e. for database backup images (as well as
>>file systems that may or may not be used by DB2). I suspect that many
>>Oracle customers use Tivoli Storage Manager (if they use AIX), or Legato
>>or Veritas (on AIX, Solaris, Windows and HP-UX) for the same function,
>>as you say in your last paragraph.
>>
>>If you search on Tivoli in the DB2 documentation
>>(http://publib.boulder.ibm.com/infocenter/db2help/index.jsp)
>>you'll see that Tivoli products in the DB2 context are used for storage
>>and monitoring. There is a Tivoli product called "Privacy Manager", but
>>I've never heard of any DB2 customer using it - whereas I hear about
>>Kerberos all the time.
>>
>>Veritas Cluster server is also popular with DB2 customers for high
>>availability on Solaris (like HACMP on AIX), but I've never heard of a
>>DB2 customer using Veritas for security.
>>
>>Mark A wrote:
>>
>>
>>>>He hit me first is the retort of a three year old. Sorry if you took the
>>>
>>>sarcasm personally.
>>>
>>>
>>>>--
>>>>Daniel Morgan
>>>
>>>
>>>Oh really. Then why don't document that.
>>>
>>>My retort to you is that you don't understand the underlying architectures
>>>of Oracle and DB2 well enough to know why they use different index schemes.
>>>If you did understand the architectures, particularly the different way they
>>>each store data physically on disk, then you would not have made your
>>>ignorant comments.
>>>
>>>You also have not explained how one database can be "lacking" in certain
>>>performance features (such as its indexing scheme) when benchmarks prove
>>>that the databases are virtually identical in TPC-C (transaction)
>>>benchmarks, and when DB2 generally beats Oracle in TPC-H (Ad hoc query)
>>>benchmarks.
>>>
>>>Next, you have not addressed the claim that Oracle has moved further away
>>>from the relational model in recent years (even though Dr. Codd rated it
>>>lower than DB2 in relational compliance years ago), and that Oracle is more
>>>and more tied at the user level to the way the data is physically stored.
>>>Instead you wave around some credentials about how you teach classes in
>>>relational technology. Well guess what buster, so do I.
>>>
>>>Lastly, your claim that Oracle has all the features of Tivoli Storage
>>>Manager is ridiculous, especially since Tivoli supports Oracle databases and
>>>it is used with many Oracle installations. I guess you think all those
>>>Tivoli customers who bought it for use with Oracle are idiots?
>>>
>>>
>
>
Mark Townsend
> bobn...@attbi.com (Bob Miles) writes:
>
>
>>The reason that I brought up bitmap indexes was my experience a couple
>>of years ago at a different company with a data warehouse system that
>>was test on an IBM ES9000 mainframe using DB2 and on a single IBM
>>RS6000 wide node on a SP2 using Oracle. Queries on the single RS6000
>>wide node ran faster than the mainframe using DB2 because of bitmap
>>indexes.
>
>
> Absolutely, if a particular workload fits bitmap indexing perfectly then
> not much else can beat its performance. Question is how many times do
> you do:
> select count(*) from table where car.color = 'red'
Hmm - missed this one earlier, so apologies for the late follow up.
This is the Oracle8 usage of BMI - designed for census type queries.
Since then, BMI has been improved, and can be used in many,many more
situations. One is of the latest is bit mapped Join Index - the bitmap
is created on dimension values, but references rows in the fact. You can
afford to do this, because BMI's are SMALL ! Note that you can then AND
or OR across one or more dimensions predicates in a query and
immediately qualify the fact rows and go get them. So, how many times DO
you do a query on the fact table filtering by one or more dimensional
values ?
>
> We think MDC is a good approach towards data warehousing that doesn't
> have the insert / update / delete penalty that bitmaps do.
So back to partitioning again, huh ? This is one of the reasons that you
have partitions - to reduce the cost of index maintenance on data load.
> And this
> approach seems to be catching customers' eyes as well. >
> Please post here if you have any technical questions on MDC.
>
> Regards,
Mark Townsend
>
> Mark Townsend wrote:
>
>>I've read a little on MDC (:-)), and I really don't see how you can
>>equate it to what BMI does. There are fundamental differences between
>>the two
>
> I don’t know why you think I equate MDC with BMI?
Sorry, I probably misread your post. I also wasn't sure either how MDC
equated with BMI. Glad you agree
> I mentioned the MDC here is because MDC can guarantee the clustering. You don’t need
> to do reorganization on the MDC table.
> Bitmap index is very useful for the equi- predicates. When using dynamic bitmap index,
> the optimizer can decide if the bitmap operation is the best, then dynamic build
> bitmap index; if not, you still benefit from where the regular index tree provide.
OK, so now I think you are being absurd. Why repeatedly build something,
then throw it away. Why not build it once, and store it. After all, you
could also do dynamic b-trees and have the optimizer gather stats every
time it needs to evaluate an execution plan, if you wanted to.
>
>>No. This is NOT why the majority of Oracle customers use partitioning.
>>The majority of Oracle customers use partitioning to manage rolling
>>window data loads.
>
>
> Not exactly. The introduction of partitioning technology in ORACLE begins with
> addressing the big size of the user table or index. Oracle has to adjust partitioning
> technology in order to deal with more and more ROLAP applications.
> On the other way, DB2 UDB MDC is DESIGNED to address this kind of applications.
>
Actually, exactly. We already had UNION ALL predicate push down and
probe in Oracle7.3, similar to what IBM has today, and then (obviously
stupidly) went and spent a few 100's of millions of dollars more and
specifically developed and designed partitioning to meet and solve
customer rolling window requirements.
>
>
>>>Global indexes are very, very useful in the OLTP system. My personal opinion is
>>>Local indexes is enough for the DW or DSS system.
>>
>>Agree that Global indexes in an OLTP system rock. Disagree that local
>>indexes are enough for DW or DSS systems.
>>
>>>>2) Global Statistics.
>>>
>>>You can collect Global Statistics for the MDC tables, just like a regular table.
>>
>>Can you have one set for the table, and then another set of each MDC
>>'cluster'. Alternatively, with a UNION ALL approach, can you have one
>>overall set for all the tables ?
>
>
> Range Partitioning in ORACLE and UNION ALL views in DB2 UDB are physical partitioning
> approach.
> Can you tell me how many tablespaces allowed to build in Oracle db?
I don't believe there is a limit on these. The real question is how many
datafiles can be opened at any 1 time ? This is OS dependant, and is
many, many 100's of thousands.
> Can you tell me how many partitions allowed to build for one user table?
64 Thousand, minus 1
I think we differ on syntax and meaning. To me, hash partitioning is
physical, because I use it to manage how data is laid on disk. Range and
List partitioning are logical, because I use it to logically manage
collections of related data.
I think you will also find that it's IBM that uses it's current UNION
ALL pseudo-partitioning to overcome table/index size limits.
>
> But MDC in DB2 UDB is the logical partitioning approach. When using MDC in EE, you can
> build either global or local indexes; you can collect statistics, either you think it
> is as global or local.
>
>
>
>>>You can create a MULTIPLE DIMENSIONAL CLUSTERING MQT. It is so much
>>>easy to build and maintained, if compare with Oracle's partitioned MV.
>>
>>Why ?
>
> Why why? Do you mean why I think it is easy to …?
> Let me give you an example about the case you talk in the previous post, you will see
> how easy it is.
> /* Really, really useful when you have a lot of data to load and then summarize, with
> queries that hit different levels in the same dimension hierarchy (i.e weekly,
> monthly, quarterly). */
>
> In db2 sample db, there is table – sales
> $ db2 select * from sales
> SALES_DATE SALES_PERSON REGION SALES
> ---------- --------------- --------------- -----------
> 12/31/1995 LUCCHESSI Ontario-South 1
> 12/31/1995 LEE Ontario-South 3
> 12/31/1995 LEE Quebec 1
> 12/31/1995 LEE Manitoba 2
> 12/31/1995 GOUNOT Quebec 1
> 03/29/1996 LUCCHESSI Ontario-South 3
> 03/29/1996 LUCCHESSI Quebec 1
> ….
>
> create table mdc_mqt as (
> select int(sales_date)/10000 as yy,
> int(sales_date)/100 as yymm,
> sum(sales) as total_sales
> from sales
> group by rollup(int(sales_date)/10000, int(sales_date)/100)
> ) data initially deferred refresh deferred
> organize by (yy) /* partitioned by the year of sales_date */
>
> refresh table mdc_mqt
>
> select * from mdc_mqt
> YY YYMM TOTAL_SALES
> ----------- ----------- -----------
> - - 14
> 1995 - 1
> 1995 199512 1
> 1996 - 13
> 1996 199603 9
> 1996 199604 4
> 6 record(s) selected.
>
> Please tell me how long it will take you to do the same thing I did here???
OK - so the query part is the same in Oracle. The 'data initially
deferred refresh deferred' is just setting up a manual refresh, right ?
On Oracle, for an MV, thats REFRESH ON DEMAND. The concept doesn't apply
to a table.
So I guess the part you are talking about is 'organize by (yy)' - right ?
Personally, I think MDC is actually different from Oracle's
Partitioning. But for the sake of argument, and to match your example,
I'll assume that you actually meant a partitioned materialized view,
which can be refreshed
So in Oracle the syntax would be (untested)
create materialized view mdc_mqt
build deferred
refresh on demand
as select int(sales_date)/10000 as yy,
int(sales_date)/100 as yymm,
sum(sales) as total_sales
from sales
group by rollup(int(sales_date)/10000, int(sales_date)/100)
partition by range (yy)
Note that you could also parititon by list, hash, composite etc
depending on what you want to do.
A table is even simpler - change materialized view to table, and delete
line 2 and 3.
Apologies, but I think I'm missing your point on this one.
> What do you think which way is easy to build??? Which way is easy to maintain????
>
>
>>>Remember: Using MDC can give more benefits:
>>>1). MDC will allow you clustering on multiple dimensions (columns), even on
>>>function(column).
You can partition on multiple columns as well.
>>>***NOTE*** the function can be either BUILD-IN functions or USER-DEFINED
>>>functions.
>>> AFAIK, Oracle's Range Partitioning key can't be based on the function.
>>
>>If the column is stored it can be used as a partitioning key.
>
>
> Does ORACLE give the user an approach to store the value based on the function?
You can index on a function, but not partition on it.You can of course
materialize the column result and partition on that. No biggy - if we
ever get some real customer requests from function based partitioning,
we can implement it very quickly.
>>>2). Block index tree is far smaller than regular B-tree.
>>
>>But I seriously doubt that it's as small as a BMI.
>
>
> Maybe. Maybe not. --- I don’t know.
> What I know is when you do Insert/Update/Delete on the based table, the cost of
> block-index maintain is less than static bitmap index maintain.
It may well be. Bit mapped indexes do not cope well with DML, especially
if the DML introduces a new value for the index key. We are working on
this however :-)
Note that this then takes us back partitioning. BMIs ARE expensive to
maintain (and they are just as expensive to build dynamically, which is
why I don't understand why you think not storing them after you have
built them is a good thing), so thats why you use range partitioning and
local BMIs so that you only ever have to re-index the data you loaded.
>
>
>>>3). When you define a MDC, beside the dimension block index, db2 will also build
>>>a composite block index (in all dimensions).
>>>4). Easy to move the record(s) from one partition to another partition.
>>
>>Do you mean easier than with Oracle's approach ? How so ?
>>
>>>5). Easy to do ROLL IN / ROLL OUT of data.
>>
>>Do you mean easier than with Oracle's approach ? How so ?
>>
>>>6). The user don't need additional job to take care of ADD partition(s) or DROP
>>>partition(s).
>
> For all the last three items, I would like to say it again, because MDC is logically
> partitioning table. When you delete a bunch of data, and even drop the whole
> partition, this is the same as you only delete one or a small size records. If db2
> find out you delete the whole partition, db2 just mark the whole partition as free in
> the block map. The DBA does not need to worry if the index was invalid ….
OK, so this is closer to the internals of DB2 than I understand. Not
sure that you can assume that moving/adding/deleting/truncating a
partition is any harder in Oracle, however. It was designed to be very easy.
>
> Regards,
> FRX
>