Re: a simple SQL question

You could go for a direct load insert instead of a conventional one. That also gives you the opportunity to do it without generating redo:

insert /*+APPEND*/ into tb_dealers
nologging
select * from tb_temp;

Depending on your box, you can also parallelize that operation which would speed things up a bit.

Regards
HJR

--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================


"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

>