John Carlson wrote:
> DA Morgan wrote:
>> Ian Michael Gumby wrote:
>>>
>>>
>>>
>>>> From: DA Morgan <damorgan@psoug.org>
>>> [SNIP]
>>>
>>> Sigh.
>>>
>>> This is a design issue that shows that while there are general
>>> concepts which are the same across different platforms, there some
>>> subtle differences which will impact performance.
>>>
>>> The implementation of Temp tables in Oracle is wrong. Ugly,
>>> inefficient and barely supports the idea of the "INTO TEMP" clause
>>> that I believe is part of the SQL standard.
>>
>> This is the most preposterous statement I've heard in quite awhile not
>> because, quite simply, there is no basis in fact. Allow me to prove it.
>>
>> 1. On what version of Oracle did you test Oracle's temp tables?
>> 2. Which temp table types did you test?
>> 3. With what tool did you gather the metrics?
>> 4. Post the test design, the DDL, the DML, and the results.
>>
>> Your statement has as much basis in fact as saying swordfish is better
>> than salmon.
>>
>> You think what Informix does, is better. Put up the test case.
>>
>>> Now when I say "WRONG", I'm talking from a purely design perspective.
>>
>> And purely from the perspective of someone who doesn't work with undo
>> segments and undo tablespaces and doesn't understand the architecture
>> underlying MVRC and has no actual basis for the opinion other than that
>> he likes blue more than red.
>>
>>> (In truth there is no right or wrong, its a question about how to
>>> interpret the requirements and does the solution meet the stated
>>> goals. )
>>
>> Closer to the facts but a waffle given the above rant.
>>
>>> However, as a developer if I want to KISS (that's an engineering
>>> term), I want to have my temp tables defined dynamically and be
>>> unique in that I don't incur overhead or issues from other
>>> implementations.
>>
>> Which means you don't want to write code the creates and drops them
>> on-the-fly. Far better to create them, index them, constrain them, and
>> let them take care of themselves forever. But that would conflict with
>> your overriding prejudice against anything non-Informix.
>>
>>> The example I've used is that you can not create an index on a temp
>>> table if the table has data anywhere.
>>
>> Of course not. Buiding indexes on-the-fly in a production database is
>> not just silly it is counterproductive adding unnecessary overhead.
>>
>> If you design systems rather than just throw them over the cubicle wall
>> then you design your table, you design your indexes, you build them
>> during schema creation, and you leave them alone for all to use and for
>> the life of the application.
>>
>> Complaining that an implementation doesn't let you create objects that
>> the optimizer might want to know about any time you feel like it is
>> the very core of bad practice.
>>
>>> That is even if I trunc my data, a different user could still have
>>> data in the temp table. So I can't create an index. While this may
>>> seem like a small nit, its not. When you're doing some computations
>>> on a subset, or need to create a functional index on the subset. So
>>> creating and dropping indexes on subsets is not possible.
>>
>> Nor should it be. Do you think creating and dropping objects has no
>> cost?
>>
>>> An example? Suppose I have a field where the value is a bitmask and I
>>> only want to select a certain portion for processing. I can't easily
>>> do this with Oracle's temp tables. Hence the issue.
>>
>> Sure you can. Of course provided you know how. If you have a problem
>> why don't you tell us about it in the Oracle usenet group and we will
>> help you solve it.
>>
>>> The "right" solution allows the developer a lot of freedom and still
>>> conforms to the spec. Hence the preference for IDS.
>>
>> So far you've not given a single example of this but I doubt that will
>> stop you ... you're on a roll.
>>
>>> On a completely different topic, is the extensibility issue. (Don't
>>> get me started on Oracle's "extensibility....". And to keep this
>>> issue simple, lets talk about Sybase's adaptive server.
>>
>> Why not other than, it would seem, the fact that you know nothing about
>> it with respect to any currently supported version of the product.
>>
>>> Its extensible, however, they didn't fence in the user/developer's
>>> code so that if there is ineffcient code, it will kill the
>>> performance of the entire database. Note that even if the code looks
>>> clean, it can still be inefficient.
>>
>> Explaining, it would seem, why it is that Sybase is currently outselling
>> Informix by a wide margin. And why Sybase shops are looking for
>> employees for real-work while Informix shops are not:
>>
>> dice.com monster.com hotjobs.com
>> Sybase 2,146 304 548
>> Informix 343 43 173
>>
>> jobs available as of 5 November, 2007.
>
> Back on that soapbox, Daniel? I was kinda wondering how long it would
> take . . . .
>
> Some more noteworthy quotes . . .
>>
>> You are presiding over a funeral so it is understandable that you would
>> praise the departed.
>>
>> Just like WordStar, just like Lotus 123, just like Borland Pascal.
>>
>> And this somehow trumps the fact that not a single college or university
>> on the planet offers a single class for Informix. The next generation of
>> developers and DBAs is coming from where? Apparently the same place new
>> sales are coming from? The tooth fairy.
>
> More value-added blather for c.d.i.
>
> JWC

If you choose to discuss Oracle here ... then I will post. If you don't
I won't. Funny thing no one in the DB2, Oracle, SQL Server, and Sybase
forums ever brings up Informix. I guess people with real products to
work with don't feel compelled to bash products they don't know.

What is interesting here is that my posts are almost always with
respect to clearing up FUD about Oracle posted by people who haven't
used a currently supported version. If you folks had real work to do
you wouldn't be spending so much time focusing on the make-believe
faults of a product that has the lion's share of the marketplace.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org