Re: Need SQL Server Temp Table equivalent (challenge!)

Karsten,

My Business need is detailed in the 1st message of this thread. This was really a simplified requirement. The real requirement is having a DB that can adapt to the change of the business WITHOUT changing the DB design AND programs. What constitute a change of business? In our case, new concepts or attributes of the product or any information that we want to collect. I admit this is almost beyond what the current DB design norm is in which we generally are trying to solve a static view of any entity. Example: A product will have these attributes and relationships with what. Now we are trying to change it to: A product will have any attributes that will be defined by the users and the relationships among the attributes will be defined by the users. We have accomplished that beautifully. Unfortunately ALL other systems can't match our system's flexibility (that's another story). The current problem I am trying to solve is how to present these user defined attributes to the users in a more traditional manner. Programmatically in .NET, it's easy. Java Hard.

Having said that, in general having a temp table is very helpful in optimizing complex queries. Many times in Oracle, I saw a big different in performance by saving a portion of a complex query into a temp table and then joining that temp table with the rest of the query. In SQL Server they even optimized the temp table into a variable instead of actual temp table so it's even more efficient. Don't get me wrong, I like Oracle because of this incredible performance but there are lacking areas that are really annoying. SQL Server has its stupid area as well (such as Identity).

I will look into the memory "context" and see what it can do for me. I hope it is something similar to the "cheap" SQL Server's temp table feature and "expensive" IBM system's temp area (QTEMP for those who know) or even the old Novell's temp area for each user.

> I'm not offended. Perhaps if you explain what business problem you're
> trying to solve, someone can help you. You are absolutely right ...
> Oracle's implementation of temporary tables is "lacking" a lot of
> features that other RDBMS vendors provide. But I think that's because
> you usually don't need temporary tables to solve a particular business
> problem in Oracle. There are other ways.
>
> I can understand your desire to provide a technical solution that you've
> found useful in the past with other RDBMSs ... but there's probably a
> better (or at least, different) approach that's appropriate in Oracle.
>
> Perhaps you simply need to use Oracle's memory "context" area, which is
> very much like a temporary table that can be dynamically implemented on
> a per user basis, with different info being collected and cleaned up
> when no longer used (like the heap in C). But without knowing something
> about what BUSINESS need you're trying to satisfy, I can't say for sure.
> So far, you've only asked how to implement a particular TECHNICAL need
> using Oracle.
Received on Tue Jul 22 2003 - 20:15:42 CDT