Re: Dynamic SQL
From: joel garry <joel-garry_at_home.com>
Date: Mon, 5 Jan 2009 14:06:45 -0800 (PST)
Message-ID: <7036d5f2-00ad-49bc-bf3c-3558549bf6f8_at_u18g2000pro.googlegroups.com>
On Jan 5, 9:58 am, mrdjmag..._at_aol.com wrote:
> On Jan 5, 11:43 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 5, 11:31 am, mrdjmag..._at_aol.com wrote:
>
> > > Hi,
>
> > > We're rewriting a lot of code in order to increase our hit count in
> > > the library cache. One thing we are running into is where dynamic
> > > table names are used. Here is a quick sample of what someone wrote::
>
> > > LOOKUP TABLE
> > > ------------------------
> > > ID NUM
> > > COLUMN NAME
> > > TABLE NAME
>
> > > PROCEDURE x (p_id_num NUMBER, p_code VARCHAR2) IS
> > > BEGIN
> > > SELECT column_name, table_name INTO v_column, v_table WHERE id_num =
> > > p_id_num;
>
> > > v_select := 'BEGIN
> > > SELECT count(*) INTO :v_num_row FROM ' || v_table
> > > || ' WHERE ' || v_column || ' = :p_code;
> > > END';
>
> > > EXECUTE IMMEDIATE v_select USING OUT p_id_num, p_code;
> > > .
> > > .
> > > END;
>
> > > Now, that is a piece of junk, but is what it is. Can that statement
> > > be re-written to be dynamic SQL so it can be reused and kept in the
> > > cache? The problem being that the FROM table may not be the same. I
> > > figured with bind variables this would be possible.....
>
> > Depending on what purpose the routine serves it may not be worth
> > worrying about such as if the routine was part of a DBA job to record
> > all tables and the number of rows in each then since it would run once
> > per day or less. In such a case it can go to the bottom of the list.
>
> > On the hand if the application makes heavy use of code like this then
> > you can add a using clause to get some reuse when the same table is
> > used but you probably need to revisit the design behind requiring such
> > a routine.
>
> > HTH -- Mark D Powell --
>
> I had read this post, but I do not believe him. I'm sure there is a
> way, a bit of trial and trial and trial with errors will eventually
> give the answer:
>
> http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/...
Date: Mon, 5 Jan 2009 14:06:45 -0800 (PST)
Message-ID: <7036d5f2-00ad-49bc-bf3c-3558549bf6f8_at_u18g2000pro.googlegroups.com>
On Jan 5, 9:58 am, mrdjmag..._at_aol.com wrote:
> On Jan 5, 11:43 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Jan 5, 11:31 am, mrdjmag..._at_aol.com wrote:
>
> > > Hi,
>
> > > We're rewriting a lot of code in order to increase our hit count in
> > > the library cache. One thing we are running into is where dynamic
> > > table names are used. Here is a quick sample of what someone wrote::
>
> > > LOOKUP TABLE
> > > ------------------------
> > > ID NUM
> > > COLUMN NAME
> > > TABLE NAME
>
> > > PROCEDURE x (p_id_num NUMBER, p_code VARCHAR2) IS
> > > BEGIN
> > > SELECT column_name, table_name INTO v_column, v_table WHERE id_num =
> > > p_id_num;
>
> > > v_select := 'BEGIN
> > > SELECT count(*) INTO :v_num_row FROM ' || v_table
> > > || ' WHERE ' || v_column || ' = :p_code;
> > > END';
>
> > > EXECUTE IMMEDIATE v_select USING OUT p_id_num, p_code;
> > > .
> > > .
> > > END;
>
> > > Now, that is a piece of junk, but is what it is. Can that statement
> > > be re-written to be dynamic SQL so it can be reused and kept in the
> > > cache? The problem being that the FROM table may not be the same. I
> > > figured with bind variables this would be possible.....
>
> > Depending on what purpose the routine serves it may not be worth
> > worrying about such as if the routine was part of a DBA job to record
> > all tables and the number of rows in each then since it would run once
> > per day or less. In such a case it can go to the bottom of the list.
>
> > On the hand if the application makes heavy use of code like this then
> > you can add a using clause to get some reuse when the same table is
> > used but you probably need to revisit the design behind requiring such
> > a routine.
>
> > HTH -- Mark D Powell --
>
> I had read this post, but I do not believe him. I'm sure there is a
> way, a bit of trial and trial and trial with errors will eventually
> give the answer:
>
> http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/...
It depends on the proportion of unique v_table. If most every one is "EMPLOYEES" then you'll get a lot of reuse. If everyone is different (or it is rarely used) you will get a lot of hard parsing. Isn't it really that simple?
jg
-- _at_home.com is bogus. http://chris.pirillo.com/2009/01/03/phishing-scam-spreading-on-twitter/Received on Mon Jan 05 2009 - 16:06:45 CST