Re: What is the best way to do this?

You are preaching to the choir here!
However, ultimately this is about making my company more profitable. There are probably over a million lines of code (plsql java, hibernate,struts,php,etc etc) that depend on this ... ahem, how shall I say it ... sub-optimal database structure. The cost of making deep architectural changes would be astronomical. Would it be worth it in the long run? But that's the wrong question. The right question is, How would an expensive re-archetecting project affect next quarter's EBIDTA and ultimately our stock price?

My original thinking on asking this question is that maybe there was some feature of Oracle of which I was not aware. For example, maybe there is a hint that allows 210 varchar2(4000) columns to be context-switched directly into an array more quickly. Probably not, but I figured it couldn't hurt to ask.

Regards,
Mike

On Sun, Apr 3, 2011 at 8:32 AM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:

> Mike,
>
> No need to apologize, I didn't spend hours on it. But the problem is
> precisely the scope of what you are allowed to do, and that is a problem
> that is very common.
>
> <rant>
> I have seen umpteen cases of queries (or processes) that, when seen
> under a magnifier, are absolutely untunable - OK, let's say you can gain 10%
> after hours of testing of obscure parameters or features. But when you see
> that the query that takes the most time on a machine is some kind of "select
> attribute from mega_table where sequence_generated_pk=:1" you know that
> something is seriously wrong with the program and that no amount of "SQL
> tuning" can save you. Many people on this list will tell you the same thing.
> I am flabbergasted by the number of developers who before writing a join
> need to search the web for "SQL tips", and the number of managers who seem
> to believe that "SQL tuning" is something analogous to copy editing. It
> isn't.
> Why not as blatant as the join-in-a-loop, your case is in my eyes very
> similar and made worse by a totally twisted data repository (I cannot
> decently call THAT a database). But if you walk up the stack of function
> calls, perhaps one level up, perhaps two level ups, perhaps even more, you
> probably have a big loop (you mentioned a batch) that returns customer ids.
> This is where you need to hit, and hit hard, rethinking what you are given
> as input and what you need to produce in the end. This looks like a case
> where you can have performance gains of one order of magnitude.
> Needless to say, what you'd really need to have is a properly designed
> database, because the SQL that is required to make any sense of what you
> have to work with is probably slightly above the means of a developer with a
> couple of years of experience.
> </rant>
>
> If I were you I'd renegotiate the scope.
>
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com>
> Konagora <http://www.konagora.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
> On 04/03/2011 04:52 PM, Michael Moore wrote:
>
> Response to Stephane's post which starts:
> " Hmm, looks like one of the hidden reasons ..."
>
> Stephane,
> I feel a bit guilty that you made such a huge effort. While you solution is
> very clever, it addresses the problem at a level that is beyond the scope of
> what I am allowed to do. Specifically, I am not trying to resolve the
> virtual column names in a given 'where clause'. Instead, my task is to
> receive a single operand (virtual_column_name) and resolve it and then
> return it's value which is stored in the ABC table. I think my April 2nd
> post should make this clear. My apologies for not being more clear from, the
> beginning.
>
> Regards,
> Mike
>
>
>

--
http://www.freelists.org/webpage/oracle-l