a simple SQL question

From: Dino Hsu <dino1.nospam_at_ms1.hinet.net>
Date: Sat, 06 Oct 2001 11:45:02 +0800
Message-ID: <a1vsrtc3d4tv0qo5qjk559orcraf3djv1m@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 Received on Fri Oct 05 2001 - 22:45:02 CDT