DA Morgan wrote:
What is a proper vehicle? A plane or a submarine?

FWIW, Mark and I agree.

Count to 10, ok?
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Now why would anyone want to go and do that? It would be akin to trading in
a perfectly good Porche 911 Turbo on a Toyota toy car.

Seriously.

Under Oracle the temp table isn't temporary. As an object, the definition of
the temp table is persistant thus forcing each "user" er schema on the
instance, to reserve that name for a specific meaning.

And it means that unless you index your column up front, any schema which is
currently using the table, will prohibit you from adding an index to the
temp table. Not a good idea an not a good design.

The dynamic ability to create a temp table on the fly is a very good idea
and its kind of implied by the name temp and the std sql syntax.

Is this to say that Oracle is "wrong" and IDS is "right". Hardly. Its a
design philosophy and you can see that Oracle carries a lot of baggage with
its design.

This isn't to say that IDS is perfect, but its design and flexibility lends
itself to easier adoption of advancements in technology than either DB2 and
Oracle. But lets also be real. Both DB2 and Oracle trace their roots to the
mainframe. Different beast.

As a developer who supports multiple databases, I can appreciate the best
and hate the worst of all of them. On the issue of temp tables and how
Oracle defines a "user", Oracle blows and there isn't any thing that they
can do to fix that in the short term.

I will say one thing... Oracle has definitely done a good job of
brainwashing their dbas.

__________________________________________________ _______________
Capture the missing critters!** Play Search Queries and earn great prizes.
http://club.live.com/search_queries....ltextlink1_oct

Obnoxio The Clown wrote:
Scotch helps.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

I can't believe that OTC is afraid of a place like B'ham.
After all, it can't be worse than Jo Berg....

__________________________________________________ _______________
More photos; more messages; more storage—get 5GB with Windows Live Hotmail.
http://imagine-windowslive.com/hotma...M_mini_2G_0507

Ian Michael Gumby said:
I've never been the same since I went to Alderley Edge ... (

--
Bye now,
Obnoxio

"I'm astonished anyone pays real money for this crap."
-- Cosmo

"Cluster in my trousers"
-- Guy Bowerman

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

Serge Rielau wrote:
Speaking about limitations / differences...

Are inserts / deletes / updates on Oracle temp tables still logged? I
remember "older versions" and I believe this was so, but I'm not sure as
to whether it was or still is . . . .

JWC

John Carlson wrote:
Global temporary tables have several major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table you either
rollback, or delete it. But in a GTT, you can truncate explicitly,
without affecting anyone else (or allow the implicit "truncate on commit
/ exit" effect to do the same thing).

3. Decreased redo generation as, by definition, they are non-logging.

No decreased ... not zero.

create table reg_tab (
testcol VARCHAR2(100));

CREATE GLOBAL TEMPORARY TABLE gtt_ocd (
testcol VARCHAR2(100))
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_ocp (
testcol VARCHAR2(100))
ON COMMIT PRESERVE ROWS;

col value format 999999999999

-- get baseline redo value
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a heap table
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO reg_tab
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT DELETE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocd
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT PRESERVE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocp
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

Description Value Redo Generated
Baseline 254269080 -
Regular Table Run 254605916 336836
On Commit Delete 254742528 136612
On Commit Preserve 254879140 136612

Be sure this is part of the compatibility feature set. <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

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.

Now when I say "WRONG", I'm talking from a purely design perspective. (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. )

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.

The example I've used is that you can not create an index on a temp table if
the table has data anywhere. 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.

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.

The "right" solution allows the developer a lot of freedom and still
conforms to the spec. Hence the preference for IDS.

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.

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.

Again kudos to IDS's developers who thought things out before
implementations. ER/HDR anyone?

That is the point. Its a better designed system.

And DA, you keep citing statistics about open rec's for FTEs.

Here's an example I think you might be able to grasp.

Porsche doesn't have a "green" car in its line up. The company defended
itself by saying that if you took all the Porsche vehicles off the road,
you'd have a less than 1% impact on CO2 emmissions from automobiles.

So when you ask a customer, that for the same price, would you rather be
driving an econobox or a Porsche, what do you think they'll say? ;-)

__________________________________________________ _______________
You keep typing, we keep giving. Download Messenger and join the i’m
Initiative now. http://im.live.com/messenger/im/home/?source=TAGHM

Ian Michael Gumby wrote:
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.

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.

Closer to the facts but a waffle given the above rant.

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.

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.

Nor should it be. Do you think creating and dropping objects has no
cost?

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.

So far you've not given a single example of this but I doubt that will
stop you ... you're on a roll.

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.

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.

You are presiding over a funeral so it is understandable that you would
praise the departed.

And curses to Informix and IBM management: antipathy in action.

Just like WordStar, just like Lotus 123, just like Borland Pascal.

Amazing how some people actually think getting paid for what they do
has value: Go figure.

Why? Do you think your readers too stupid too see a lack of jobs as
relevant?

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.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

DA Morgan wrote:
Back on that soapbox, Daniel? I was kinda wondering how long it would
take . . . .

Some more noteworthy quotes . . .

More value-added blather for c.d.i.

JWC