Pohkalopokh Pohkalopokh - 3 months ago 10
SQL Question

SQL, ORACLE: Update the value of a column with different values

I searched a lot and could not find answer to my question yet.


So, I have to generate a lots of data (millions of rows) into different tables, and this script should be fast.
Now let's speak about 3 specific tables, in these there are id rows:


  • in table Owners the id starts from 10000, incremented by 1 (with sequence)

  • in table Cars the id starts from 10000000, incremented by 1 (with sequence).




Into the third table, called Ownership, I have to "merge" these IDs with some specific rates:


  • 50% of the owners have 1 car

  • 20% of the rest owners have 2 cars

  • 10-10-10% of the rest will have 3, 4 and 5 cars




Important things:


  • note for Owners and Cars tables: rates: for 100 unit owners, 210 unit cars will be generated, and in the Ownership table the cars will be unique, so 210 rows will be in this table too

  • first the Owners, then the Cars rows will be generated

  • then I insert into the Ownership table some values "from" Cars






Notes: Here is how I generated rows into Cars (Owners is similar)
(It will generate v_custom_unit pieces rows, this unit is calculated from the rates which I give a few lines ago (for 100 unit owners, 210 unit cars will be generated and 210 unit Ownerships too), and then I used a for loop to multiply the rows):

insert /*+ APPEND */ into Cars(
carId
, carType
, ...
)
select /*+ PARALLEL */
seq_carid.nextval as carId
, REGEXP_SUBSTR( 'Suziki,Toyota,Subaru,Saab,Hyundai,Opel,Volkswagen', '([^,]+)', 1, ROUND(DBMS_RANDOM.VALUE(1,7)) ) as carType
, ...
from dual
connect by level <= v_custom_unit;


Multiplying is like this:

FOR i in 1..v_forSteps LOOP

EXECUTE IMMEDIATE '
insert /*+ APPEND */ into Cars (
carId,
, carType,
, ...
)
SELECT /*+ PARALLEL */
seq_carid.nextval as carId,
, carType
, ...
FROM Cars
WHERE ROWNUM <= ' || v_custom_unit;

COMMIT;

END LOOP;


The next step is to generate Ownership rows:

insert /*+ APPEND */ into Ownership (
ownerId
, carId
, date_bought
)
select /*+ PARALLEL */
1
, c.carId
, some_random_date as date_bought
from Cars c;


Here comes my problem: Every car is in the Ownership with ownerId=1.


My question is: how can I update the Ownership table with different owner values in a single update (and maybe to maintain the rates (50%-20%-10%-10%-10%))?

Answer

One of the ways to do it:

insert into ownership (ownerid, carid, bought)
  with t(oid, cid, cnt) as ( 
    select 1, 1, 1 from dual
    union all 
    select case when oid <= 5 
                     or oid <=  7 and cnt >= 2 
                     or oid <=  8 and cnt >= 3 
                     or oid <=  9 and cnt >= 4 
                     or oid <= 10 and cnt >= 5 then oid + 1
                else oid 
           end, 
           cid + 1, 
           case when oid <=  5 
                     or oid <=  7 and cnt >= 2 
                     or oid <=  8 and cnt >= 3 
                     or oid <=  9 and cnt >= 4 
                     or oid <= 10 and cnt >= 5 then 1
                else cnt + 1 
           end
    from t where cid < 21) 
  select oid, cid, trunc(sysdate) - round(dbms_random.value * 1000) from t

This is a demo for 10 owners and 21 cars. Not sure about efficiency, but this is only one recursive query. Oracle 11g required.

Test table and output:

create table ownership (carid number(6), ownerid number(6), bought date);

  CARID OWNERID BOUGHT
------- ------- -----------
      1       1 2013-12-29 -- one car
      2       2 2015-12-16
      3       3 2014-04-04
      4       4 2013-12-17
      5       5 2013-11-20
      6       6 2014-04-04 -- two cars
      7       6 2015-09-05
      8       7 2013-12-19
      9       7 2016-01-02
     10       8 2015-08-22 -- three 
     11       8 2014-03-05
     12       8 2016-07-14
     13       9 2015-09-02 -- four 
     14       9 2015-08-28
     15       9 2015-06-04
     16       9 2014-04-20
     17      10 2016-08-07 -- five 
     18      10 2015-07-16
     19      10 2014-12-08
     20      10 2016-04-26
     21      10 2014-05-30
Comments