Re: create type with index
From: ddf <oratune_at_msn.com>
Date: Tue, 17 Mar 2009 12:17:04 -0700 (PDT)
Message-ID: <85361ae9-525e-43f5-ad47-609558d509d5_at_v39g2000yqm.googlegroups.com>
On Mar 16, 10:06 am, NOVA <nova1..._at_gmail.com> wrote:
> On Mar 16, 3:42 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Mar 16, 2:36 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > On Mar 15, 10:04 pm, ddf <orat..._at_msn.com> wrote:
>
> > > > Comments embedded.
>
> > > > On Mar 15, 2:00 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > > > Dear all,
>
> > > > > I face a problem with this package
>
> > > > > what is the target?
>
> > > > Who can say? You're wasting effort trying to use this type when it's
> > > > completely unnecessary.
>
> > > > > I have table "xch_t" (exchange rate table) to convert the currency to
> > > > > 'SAR'.
>
> > > > > for example:
> > > > > 1 USD = 3.75 SAR
> > > > > 1 EUR = 4.84859 SAR
>
> > > > > so the table xch shown below:
> > > > > CUCD1 CVRA
> > > > > USD 3.75
> > > > > JPY 0.0381255
> > > > > EUR 4.84859
>
> > > > > I have a lot of procedures use the exchange, so I want to make it an
> > > > > array by create type "xch_type" but the index will by CUCD1.
>
> > > > Why???
>
> > > > > let us start togather. execute all lines below.
> > > > > note: this is an example to be easier to understand.
>
> > > > > CREATE TABLE XCH_T -- line 1
> > > > > (
> > > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > > CVRA NUMBER(15,7) NOT NULL
> > > > > );
> > > > > insert into XCH_T values ('USD ', 3.75);
> > > > > insert into XCH_T values ('JPY ', 0.0381255);
> > > > > insert into XCH_T values ('EUR ',4.84859);
>
> > > > > CREATE TABLE aa
> > > > > (
> > > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > > CVRA NUMBER(15,7) NOT NULL,
> > > > > conv_result NUMBER(15,7) NOT NULL
> > > > > );
>
> > > > > create or replace package package_name as
>
> > > > The type and the variable are unnecessary. I would stop trying to use
> > > > them.
>
> > > > > type xch_type is table of
> > > > > number(15,7)
> > > > > index by varchar2(4);
> > > > > xch xch_type;
> > > > > procedure proc_t;
> > > > > end package_name;
> > > > > /
>
> > > > The package specification compiles successfully; your body does not.
>
> > > > > create or replace package body package_name as -- line 25
> > > > > procedure proc_t as
> > > > > begin
>
> > > > xch is NOT a function, it's a type, and it isn't part of the XCH_T
> > > > table, so why are you trying to select it?
>
> > > > > insert into aa
> > > > > select cucd1, CVRA, xch(cucd1) from xch_t; -- line 29
> > > > > end proc_t;
>
> > > > This is not how to load such a table.
>
> > > > > begin
> > > > > select cvra into xch(cucd1) from xch_t; -- line 33
> > > > > end package_name;
> > > > > /
>
> > > > > it show my error in line29 and 33.
>
> > > > > Can you help me.
>
> > > > Your body, if you REALLY want to use this type, needs to be written
> > > > this way:
>
> > > > create or replace package body package_name as -- line 25
>
> > > > procedure proc_t as
>
> > > > cursor get_xch_data is
> > > > select cucd1, cvra from xch_t;
>
> > > > begin
> > > > for xrec in get_xch_data loop
> > > > -- Populate the table
> > > > xch(xrec.cucd1):= xrec.cvra;
> > > > -- Use the table to populate another
> > > > table (busy work, in my opinion)
> > > > insert into aa
> > > > values (xrec.cucd1, xrec.cvra, xch(xrec.cucd1));
> > > > end loop;
>
> > > > end proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > The above code, in its entirety, can be replaced with:
>
> > > > create or replace package package_name as
>
> > > > procedure proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > create or replace package body package_name as -- line 25
>
> > > > procedure proc_t as
>
> > > > cursor get_xch_data is
> > > > select cucd1, cvra from xch_t;
>
> > > > begin
> > > > for xrec in get_xch_data loop
>
> > > > insert into aa
> > > > values (xrec.cucd1, xrec.cvra, xrec.cvra);
>
> > > > end loop;
>
> > > > end proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > Your array, as I see it, is useless here and it's only causing you
> > > > problems.
>
> > > > David Fitzjarrell- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I understand what do you mean.
>
> > > I try to use array as a function.
>
> > > look at last change after your advice
>
> > > create or replace package package_name as
> > > type xch_type is table of
> > > number(15,7)
> > > index by varchar2(4);
> > > xch xch_type;
> > > procedure proc_t;
> > > end package_name;
> > > /
>
> > > create or replace package body package_name as
> > > cursor get_xch_data is
> > > select cucd1, cvra from xch_t;
> > > procedure proc_t as
> > > begin
> > > --insert into aa select cucd1, CVRA, xch(cucd1) from xch_t; --
> > > line 6
> > > insert into aa select cucd1, CVRA, xch('USD') from xch_t; --
> > > line 7
> > > end proc_t;
> > > begin
> > > for xrec in get_xch_data loop
> > > xch(xrec.cucd1):= xrec.cvra;
> > > end loop;
> > > end package_name;
> > > /
>
> > > I still want to use an array. if you look at line 6 there is error
> > > here "xch(cucd1)"
> > > and in line 7 is successfull but the different is line 6 is dynamic
> > > and line 7 is static.
>
> > > How can I make line t successfully.
>
> > > thx alot.- Hide quoted text -
>
> > > - Show quoted text -
>
> > You cannot get 'line 6' to work as you have it written. Please
> > understand this. You would need to write a function that uses that
> > array and passes it a value from a select statement to get something
> > like line 6 to work. I'll leave it to you to write the function.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> you right,
>
> I add function as below
>
> function xch_f(cucd1 char, amont number) return number as
> begin
> return xch(cucd1);
> end xch_f;
>
> and change lin 6 to
> insert into aa select cucd1, CVRA, xch_f(cucd1) from xch_t;
>
> It work successfully.
>
> by this way I increase the performance to load into a table.
>
> let me explain:
>
> I have big query inserted to table and there is function read from
> xch_t (this table is very large because it is history data, that means
> there is column for date and I always take sysdate) by pass currency
> code to return number.
> but there are a lot of exhange rate so it need to use function many of
> times. on other hand one query inside the function executed many
> times.
>
> so I find solution by use with statment that is query like function
> query except no filter on currency code. and inside big query I make
> select statment from "WITH" by add filter for current code.
>
> what is the different:
> - By function: it take 28:10 min becuase xch_t executed many
> times, same query
> - By With statment: 4:30 min becuase xch_t executed one time,
> and filterd inside the big query
> - By array (after your help): 7:20 min becuase xch_t executed one
> time, and pass the currency code to function then to array.
>
> What do you think?
>
> I still try to find more performance at least like (by with statment).
>
> Using with statment has one disadvantage that it make the big query
> more bigger and difficult to read when I want to make change.
>
> I waiting for your advice.
>
> thx- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 17 Mar 2009 12:17:04 -0700 (PDT)
Message-ID: <85361ae9-525e-43f5-ad47-609558d509d5_at_v39g2000yqm.googlegroups.com>
On Mar 16, 10:06 am, NOVA <nova1..._at_gmail.com> wrote:
> On Mar 16, 3:42 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > On Mar 16, 2:36 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > On Mar 15, 10:04 pm, ddf <orat..._at_msn.com> wrote:
>
> > > > Comments embedded.
>
> > > > On Mar 15, 2:00 am, NOVA <nova1..._at_gmail.com> wrote:
>
> > > > > Dear all,
>
> > > > > I face a problem with this package
>
> > > > > what is the target?
>
> > > > Who can say? You're wasting effort trying to use this type when it's
> > > > completely unnecessary.
>
> > > > > I have table "xch_t" (exchange rate table) to convert the currency to
> > > > > 'SAR'.
>
> > > > > for example:
> > > > > 1 USD = 3.75 SAR
> > > > > 1 EUR = 4.84859 SAR
>
> > > > > so the table xch shown below:
> > > > > CUCD1 CVRA
> > > > > USD 3.75
> > > > > JPY 0.0381255
> > > > > EUR 4.84859
>
> > > > > I have a lot of procedures use the exchange, so I want to make it an
> > > > > array by create type "xch_type" but the index will by CUCD1.
>
> > > > Why???
>
> > > > > let us start togather. execute all lines below.
> > > > > note: this is an example to be easier to understand.
>
> > > > > CREATE TABLE XCH_T -- line 1
> > > > > (
> > > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > > CVRA NUMBER(15,7) NOT NULL
> > > > > );
> > > > > insert into XCH_T values ('USD ', 3.75);
> > > > > insert into XCH_T values ('JPY ', 0.0381255);
> > > > > insert into XCH_T values ('EUR ',4.84859);
>
> > > > > CREATE TABLE aa
> > > > > (
> > > > > CUCD1 CHAR(4 BYTE) NOT NULL,
> > > > > CVRA NUMBER(15,7) NOT NULL,
> > > > > conv_result NUMBER(15,7) NOT NULL
> > > > > );
>
> > > > > create or replace package package_name as
>
> > > > The type and the variable are unnecessary. I would stop trying to use
> > > > them.
>
> > > > > type xch_type is table of
> > > > > number(15,7)
> > > > > index by varchar2(4);
> > > > > xch xch_type;
> > > > > procedure proc_t;
> > > > > end package_name;
> > > > > /
>
> > > > The package specification compiles successfully; your body does not.
>
> > > > > create or replace package body package_name as -- line 25
> > > > > procedure proc_t as
> > > > > begin
>
> > > > xch is NOT a function, it's a type, and it isn't part of the XCH_T
> > > > table, so why are you trying to select it?
>
> > > > > insert into aa
> > > > > select cucd1, CVRA, xch(cucd1) from xch_t; -- line 29
> > > > > end proc_t;
>
> > > > This is not how to load such a table.
>
> > > > > begin
> > > > > select cvra into xch(cucd1) from xch_t; -- line 33
> > > > > end package_name;
> > > > > /
>
> > > > > it show my error in line29 and 33.
>
> > > > > Can you help me.
>
> > > > Your body, if you REALLY want to use this type, needs to be written
> > > > this way:
>
> > > > create or replace package body package_name as -- line 25
>
> > > > procedure proc_t as
>
> > > > cursor get_xch_data is
> > > > select cucd1, cvra from xch_t;
>
> > > > begin
> > > > for xrec in get_xch_data loop
> > > > -- Populate the table
> > > > xch(xrec.cucd1):= xrec.cvra;
> > > > -- Use the table to populate another
> > > > table (busy work, in my opinion)
> > > > insert into aa
> > > > values (xrec.cucd1, xrec.cvra, xch(xrec.cucd1));
> > > > end loop;
>
> > > > end proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > The above code, in its entirety, can be replaced with:
>
> > > > create or replace package package_name as
>
> > > > procedure proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > create or replace package body package_name as -- line 25
>
> > > > procedure proc_t as
>
> > > > cursor get_xch_data is
> > > > select cucd1, cvra from xch_t;
>
> > > > begin
> > > > for xrec in get_xch_data loop
>
> > > > insert into aa
> > > > values (xrec.cucd1, xrec.cvra, xrec.cvra);
>
> > > > end loop;
>
> > > > end proc_t;
>
> > > > end package_name;
> > > > /
>
> > > > Your array, as I see it, is useless here and it's only causing you
> > > > problems.
>
> > > > David Fitzjarrell- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I understand what do you mean.
>
> > > I try to use array as a function.
>
> > > look at last change after your advice
>
> > > create or replace package package_name as
> > > type xch_type is table of
> > > number(15,7)
> > > index by varchar2(4);
> > > xch xch_type;
> > > procedure proc_t;
> > > end package_name;
> > > /
>
> > > create or replace package body package_name as
> > > cursor get_xch_data is
> > > select cucd1, cvra from xch_t;
> > > procedure proc_t as
> > > begin
> > > --insert into aa select cucd1, CVRA, xch(cucd1) from xch_t; --
> > > line 6
> > > insert into aa select cucd1, CVRA, xch('USD') from xch_t; --
> > > line 7
> > > end proc_t;
> > > begin
> > > for xrec in get_xch_data loop
> > > xch(xrec.cucd1):= xrec.cvra;
> > > end loop;
> > > end package_name;
> > > /
>
> > > I still want to use an array. if you look at line 6 there is error
> > > here "xch(cucd1)"
> > > and in line 7 is successfull but the different is line 6 is dynamic
> > > and line 7 is static.
>
> > > How can I make line t successfully.
>
> > > thx alot.- Hide quoted text -
>
> > > - Show quoted text -
>
> > You cannot get 'line 6' to work as you have it written. Please
> > understand this. You would need to write a function that uses that
> > array and passes it a value from a select statement to get something
> > like line 6 to work. I'll leave it to you to write the function.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> you right,
>
> I add function as below
>
> function xch_f(cucd1 char, amont number) return number as
> begin
> return xch(cucd1);
> end xch_f;
>
> and change lin 6 to
> insert into aa select cucd1, CVRA, xch_f(cucd1) from xch_t;
>
> It work successfully.
>
> by this way I increase the performance to load into a table.
>
> let me explain:
>
> I have big query inserted to table and there is function read from
> xch_t (this table is very large because it is history data, that means
> there is column for date and I always take sysdate) by pass currency
> code to return number.
> but there are a lot of exhange rate so it need to use function many of
> times. on other hand one query inside the function executed many
> times.
>
> so I find solution by use with statment that is query like function
> query except no filter on currency code. and inside big query I make
> select statment from "WITH" by add filter for current code.
>
> what is the different:
> - By function: it take 28:10 min becuase xch_t executed many
> times, same query
> - By With statment: 4:30 min becuase xch_t executed one time,
> and filterd inside the big query
> - By array (after your help): 7:20 min becuase xch_t executed one
> time, and pass the currency code to function then to array.
>
> What do you think?
>
> I still try to find more performance at least like (by with statment).
>
> Using with statment has one disadvantage that it make the big query
> more bigger and difficult to read when I want to make change.
>
> I waiting for your advice.
>
> thx- Hide quoted text -
>
> - Show quoted text -
If it were me doing this, I'd take the subquery factoring version (using WITH) of the three choices offered and be done with it. You've taken a process which consumed 28+ minutes of time and reduced it to 4+ minutes with subquery factoring, then you tried to improve on that and almost doubled the improved time and created (in my opinion) an unnecessary type and table.
I fail to see your 'logic' in this.
David Fitzjarrell. Received on Tue Mar 17 2009 - 14:17:04 CDT