| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need SQL Server Temp Table equivalent (challenge!)
Comments embedded...
Hi Kin Ng, thanks for writing this:
> 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.
I am probably missing your real point here and oversimplifying it in my mind (with a simple mind like mine, I have to do that <g>). We handle code customization by populating a table (Member_Custom_Fields) defined like this:
institution_id
seqno
field_label field_type field_optional field_mask
can_view can_update can_delete
Our java code has to have "switch" statements that lookup specific field labels for a given institution to find the "custom" stuff.
Will this type of table work with your products?
> 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).
It's very rare (with Oracle) that you can create a temp table on the fly, populate it, and use it in a join ... and beat the performance of a permanent table. If you say you saw a "big" difference, then I'll take your word for it (though I remain skeptical <g>).
> 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.
You need to search for Virtual Private Database (or it's older name, Fine Grained Access Control ... or it's even older name, Row Level Security). One annoying thing about Oracle is that the keep renaming things ... and don't always keep their doc set up-to-date with the name changes (eg, you still find references to WebDB, even though it's been renamed Portal for a long time).
> > 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.
>
-- [:%s/Karsten Farrell/Oracle DBA/g]Received on Wed Jul 23 2003 - 13:03:33 CDT
 |
 |