a simple SQL question 
Author Message
 a simple SQL question

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



Wed, 24 Mar 2004 11:45:02 GMT
 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
=========================================


Quote:
> 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



Wed, 24 Mar 2004 13:13:10 GMT
 a simple SQL question


Quote:
> 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



Wed, 24 Mar 2004 13:45:02 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Simple SQL Question for the SQL Gurus

2. Simple SQL question

3. Simple SQL question

4. simple SQL question?

5. Simple SQL Question I think

6. Simple SQL question about WHERE

7. SIMPLE SQL QUESTION

8. Simple SQL Question (I hope!!!)

9. Simple sql question

10. Simple SQL question about WHERE

11. Another simple SQL question :-(

12. Simple SQL question...


 
Powered by phpBB® Forum Software