Work table

[jaffar]

why worktable has no-index?

 

[RealHeavyDude]

Because they were designed that way. Work tables are remnants from the end of the '80s of the last century which are still in the product for compatibility reasons. But that does not mean you should use them - I stopped using them when Temp-Tables were introduced, which are indexed, somewhere at the beginning of the '90s.

There are other limitations to work tables which make Temp-Table superior - most prominently that work tables are not designed to hold much data as they solely remain in memory and you will blow your stack if you put lots of data into it. The contents of Temp-Tables are written to disk when they don't fit. Furthermore you can create them dynamically on the fly as you need them and build dynamic queries against them.

Heavy Regards, RealHeavyDude.

 

[Marian EDU]

Work tables are remnants from the end of the '80s of the last century which are still in the product for compatibility reasons.

so there are lists, vectors and hash tables but this does not mean those aren't used anymore

But that does not mean you should use them - I stopped using them when Temp-Tables were introduced, which are indexed, somewhere at the beginning of the '90s.

I'll prefer 'use them with care', for a hand-full of records one simply does not need an index...

 

[tamhas]

In fact, they can actually be preferable for small amounts of data because an empty temp-table actually has a fairly heavy footprint. E.g., in this presentation http://cintegrity.com/content/Object-Object-Relations I talk about using them to hold small collections of objects. Since a collection is accessed serially, no index is needed or appropriate. Moreover, sequence doesn't generally matter as long as one accesses each member of the collection in sequence.

BTW, in the performance tests in that talk I found a bug in 11.0 which has been found and a fix is slated for release.