Re: a simple SQL question

"Dino Hsu" <dino1.nospam_at_ms1.hinet.net> wrote in message news:a1vsrtc3d4tv0qo5qjk559orcraf3djv1m_at_4ax.com...
> Dear all,
>
> SQL> desc tb_dealers
> Name Null? Type
> ----------------------------- -------- --------------------
> OP_YEAR NOT NULL NUMBER(4)
> OP_CAMP NOT NULL NUMBER(2)
> REP_NO NOT NULL VARCHAR2(8)
> ZONE VARCHAR2(3)
> ZIP_CODE VARCHAR2(3)
> DEALER_TYPE CHAR(4)
> OCCUPATION_CODE NUMBER(2)
> BIRTHDAY DATE
> PROCESS_DATE NOT NULL DATE
>
> SQL> select count(1) from tb_dealers;
>
> COUNT(1)
> ----------
> 10220791
>
> SQL> select count(1) from tb_dealers where op_year=2001 and
> op_camp=13;
>
> COUNT(1)
> ----------
> 191187
>
> I want to copy the data from op_year=2001 and op_camp=13 to
> op_year=2001 and op_camp=14, so I try this:
>
> 1.create table tb_temp as select * from tb_dealers where op_year=2001
> and op_camp=13;
> 2.update tb_temp set op_camp=14;
> 3.insert into tb_dealers select * from tb_temp;
> 4.commits;
> 5.drop tb_temp;
>
> All steps takes a couple of seconds, except 3, which takes several
> minutes. I guess there are many checks going on during the insert
> process. The primary key is the first 3 columns. Because this is a
> contingency or ad-hoc procedure, we need it to be quick. How do you
> speed step 3 up or any better ideas? Thanks in advance.
>
> Dino
>

Just use
insert into tb_dealer
select op_year, 14, <etc>
from tb_dealer
where op_year = 2001
and op_camp = 13
No temporary tables necessary.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Sat Oct 06 2001 - 00:45:02 CDT