Re: best way to "parameterize" a tablename?
Date: Fri, 22 Aug 2008 02:20:33 -0700 (PDT)
Message-ID: <e2335b39-8460-4ce3-924f-dc3233411659@e53g2000hsa.googlegroups.com>
On Aug 22, 1:13 am, m..._at_pixar.com wrote:
> gym dot scuba dot kennedy at gmail <kenned..._at_verizon.net> wrote:
>
> > <m..._at_pixar.com> wrote in message
>
> > > if tablename = 'FOO'
> > > insert into foo ...
> > > else if tablename = 'BAR'
> > > insert into bar ... (exact same stuff)
> > > etc...
>
> > Use bind
> > variables.)
>
> I guess that's what I'm asking... how can I use bind variables or
> equivalent for the table name?
>
> > You have an odd schema is each table has the same structure but a different
> > name.
>
> There's a reason for it, we basically don't want to intermingle
> one set of data with another. Think of the case where there's
> a million records of FOO and 100 records of BAR. An index
> won't be very useful, so you end up doing a lot of full
> table scans to extract the data, and we don't want to pay
> the big cost of FOO for the tiny data from BAR. The real
> case is a bit more complicated, but that's what it boils
> down to.
>
> Thanks!
>
> --
> Mark Harrison
> Pixar Animation Studios
You could give multitable insert a shot:
SQL> var tablename varchar2(30) SQL> var somevalue varchar2(20) SQL> exec :tablename := 'wr_t1'; :somevalue := 'Banana'
PL/SQL procedure successfully completed.
SQL> insert first
2 when :tablename = 'wr_t1' then into wr_t1 values (:somevalue) 3 when :tablename = 'wr_t2' then into wr_t2 values (:somevalue)4 select * from dual;
1 row created.
SQL> select * from wr_t1;
COL
Banana
1 row selected.
SQL> select * from wr_t2;
no rows selected
Alternatively partitioning would probably handle this type of thing. Received on Fri Aug 22 2008 - 04:20:33 CDT