This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Nov 22, 2012 4:27 AM by PeterMarcoen Go to original post RSS
  • 45. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Paul Horth wrote:
    So, you don't have a business key that naturally links the orders to the customer on your source system? Where is this data coming from?
    Just to be clear Paul, I use the customers model as an example to explain my case.
    The orders and customers in my example are linked through the customer_id. An order belongs to only one customer, one customer can place many orders.
  • 46. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    I wouldn't. There is another application though that also needs customer ids for something similar. This other application needs to insert about 1000 customers each run, while this application inserts only about 100 customers each run. Therefore I would have liked some sort of a solution where application A requests 1000 ids, which it can use to fill it's file which will then be inserted using sqlldr; while application B requests only 100 ids.
    I'm sorry, this part is unclear to me.

    The customer_id is in the files right ?
    Then where will the sequence values go to ?
    (in others words, considering your example what would the customer and order target tables will look like ?)
  • 47. Re: Sequence with multiple increments
    KeithJamieson Expert
    Currently Being Moderated
    What prevents you from using the existing 'customer_id' and 'order_id' when writing out the data files?
  • 48. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Nicosa wrote:
    Peter Marcoen wrote:
    I wouldn't. There is another application though that also needs customer ids for something similar. This other application needs to insert about 1000 customers each run, while this application inserts only about 100 customers each run. Therefore I would have liked some sort of a solution where application A requests 1000 ids, which it can use to fill it's file which will then be inserted using sqlldr; while application B requests only 100 ids.
    I'm sorry, this part is unclear to me.

    The customer_id is in the files right ?
    Then where will the sequence values go to ?
    (in others words, considering your example what would the customer and order target tables will look like ?)
    customers

    customer_id,
    name,
    email

    orders

    customer_id,
    item,
    amount

    The application (A) would request the nextval from customer_sequence, thereby increasing the sequence by 1000. Then, as a stream of data comes in, this application would fill 2 files.
    Let's say in the incoming stream of data there was the following information: "Eddy Griffin", "eddy.griffin@hbo.com", "champagne", 1000

    File 1 would be filled with the customer_id (a number between the one he got from the sequence and that number + 1000), "Eddy Griffin", "eddy.griffin@hbo.com".
    File 2 would be filled with the customer_id (same number as in file 1), "champagne", 1000.

    Sqlldr would then be used to insert the contents of these 2 files to the tables customers and orders.

    Application B would do something similar but will process a lot more customers (about 1000 every run)
    This will work perfectly. The only thing I 'regret' in this solution is that I will not actually use 1000 ids in application A, I would only use about 100.
  • 49. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    The application (A) would request the nextval from customer_sequence, thereby increasing the sequence by 1000. Then, as a stream of data comes in, this application would fill 2 files.
    Let's say in the incoming stream of data there was the following information: "Eddy Griffin", "eddy.griffin@hbo.com", "champagne", 1000

    File 1 would be filled with the customer_id (a number between the one he got from the sequence and that number + 1000), "Eddy Griffin", "eddy.griffin@hbo.com".
    File 2 would be filled with the customer_id (same number as in file 1), "champagne", 1000.

    Sqlldr would then be used to insert the contents of these 2 files to the tables customers and orders.

    Application B would do something similar but will process a lot more customers (about 1000 every run)
    This will work perfectly. The only thing I 'regret' in this solution is that I will not actually use 1000 ids in application A, I would only use about 100.
    (o_0)
    So, the "stream of data" does not have customer_id information, right ?
    What is exactly this "stream of data" ?

    I can't believe your asking the database to receive that streams and "split" it to 2 flat files using a sequence value to create the relation between customer and its orders, then expect to use sqlldr to load the flat file into the database....

    You're making the data come inside the database, then back to files outside the database, than back inside tables... or I'm missing something ?

    Explain what is this "stream of data". If the database can read it, it can most certainly use multi-table insert from it to populate both customer and order tables at once.
  • 50. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Nicosa wrote:
    (o_0)
    So, the "stream of data" does not have customer_id information, right ?
    What is exactly this "stream of data" ?

    I can't believe your asking the database to receive that streams and "split" it to 2 flat files using a sequence value to create the relation between customer and its orders, then expect to use sqlldr to load the flat file into the database....

    You're making the data come inside the database, then back to files outside the database, than back inside tables... or I'm missing something ?

    Explain what is this "stream of data". If the database can read it, it can most certainly use multi-table insert from it to populate both customer and order tables at once.
    No, I'm sorry, you misunderstand. This "stream of data" I talk about is just information the application reads from a file.
    The application reads data from this file, converts it, and then writes to the 2 files I mention, the files that need to be uploaded using sqlldr.

    Again, I already have a possible solution, just use an increment of 1000. The thing is that only application B will use 1000 ids every run. Application A actually only needs about 100 ids.
  • 51. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    No, I'm sorry, you misunderstand. This "stream of data" I talk about is just information the application reads from a file.
    The application reads data from this file, converts it, and then writes to the 2 files I mention, the files that need to be uploaded using sqlldr.
    Ok, let's use the following vocabulary to minimize misunderstanding :
    - Step A : There is a single file (this kind of row : "Eddy Griffin", "eddy.griffin@hbo.com", "champagne", 1000 )
    - Move #1 : The application splits it into 2 files : customers and orders
    - Step B : There are now 2 files, whose lines are related by customer_id
    - Move #2 : The database reads some files to insert them into target tables customers and orders without any data modification.

    My question is about Step B : where did "the application" get this customer_id ? Is it from the database sequence ? How exactly ?
    (I feel like it is in that step were you want to get those 1000 nextvals)
  • 52. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Nicosa wrote:
    Ok, let's use the following vocabulary to minimize misunderstanding :
    - Step A : There is a single file (this kind of row : "Eddy Griffin", "eddy.griffin@hbo.com", "champagne", 1000 )
    - Move #1 : The application splits it into 2 files : customers and orders
    - Step B : There are now 2 files, whose lines are related by customer_id
    - Move #2 : The database reads some files to insert them into target tables customers and orders without any data modification.

    My question is about Step B : where did "the application" get this customer_id ? Is it from the database sequence ? How exactly ?
    (I feel like it is in that step were you want to get those 1000 nextvals)
    Correct, except, the way I do it now, in Move #2, Sqlldr "uploads" these files to the database.

    The application gets its customer_id from the sequence in the database. It calls on a pl/sql function that does a seq.nextval() and returns this number to the application.
    I have now set up the sequence so that it increments by 1000. The application can then assign customer_ids to the records it finds in the single file.
    Now this application actually only needs 100 of those ids, another application needs 1000 of those ids.

    Thanks for sticking around by the way ;)

    Edited by: Peter Marcoen on 21-nov-2012 8:59
  • 53. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Why not let the application calls the PL/SQL function as much as different customer you get ?
    Having the application makes 1000 calls to the function should not be very long +(unless the application disconnect from the database between each call)+
  • 54. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Ok, this is how I would do it.

    First the files I'll be using :
    data.csv (your original file) :
    [oracle@Nicosa-oel test_external_tables]$ cat data.csv 
    "Scott", "Scott@mail.com", "champagne", 1000
    "Scott", "Scott@mail.com", "Caviar", 500
    "John", "John@deere.com", "Engine", 1
    The external table declaration for that file :
    [oracle@Nicosa-oel test_external_tables]$ cat createExternalTable.sql 
    CREATE TABLE raw_data
                       (cust_name VARCHAR2(30),
                        cust_mail VARCHAR2(60),
                        item_name VARCHAR2(30), 
                        item_qty NUMBER(4)
                       ) 
         ORGANIZATION EXTERNAL 
         ( 
           TYPE ORACLE_LOADER 
           DEFAULT DIRECTORY test_external_tables
           ACCESS PARAMETERS 
           ( 
             records delimited by newline 
             badfile test_external_tables:'empxt%a_%p.bad' 
             logfile test_external_tables:'empxt%a_%p.log' 
             fields terminated by ',' optionally enclosed by '"'
             missing field values are null 
             ( cust_name, cust_mail, item_name, item_qty )
           ) 
           LOCATION ('data.csv')
         ) 
         PARALLEL 
         REJECT LIMIT UNLIMITED; {code}
    The real target tables (and sequence) :{code}
    [oracle@Nicosa-oel test_external_tables]$ cat createTables.sql 
    create sequence myseq;
    
    create table customers(
    cust_id integer,
    cust_name varchar2(30),
    cust_mail varchar2(30),
    constraint customers_pk primary key (cust_id),
    constraint cust_name_uq unique (cust_name)
    );
    
    create table orders(
    cust_id integer,
    item_name varchar2(30),
    item_qty integer,
    constraint orders_fk foreign key (cust_id) references customers(cust_id)
    );{code}
    The loading process :{code}
    [oracle@Nicosa-oel test_external_tables]$ cat load.sql 
    insert into customers
    select myseq.nextval, cust_name, cust_mail
    from (
         select distinct
              cust_name
              , cust_mail
         from raw_data
    ) r
    where not exists (
         select null
         from customers c
         where c.cust_name=r.cust_name
    );
    
    
    insert into orders
    select (
              select cust_id
              from customers c
              where c.cust_name=r.cust_name
         )
         , r.item_name
         , r.item_qty
    from raw_data r;
    
    commit;
    {code}
    The display of tables :{code}[oracle@Nicosa-oel test_external_tables]$ cat showTables.sql 
    select * from customers;
    select * from orders;
    {code}
    
    Now the test case :{code}[11.2] Scott @ My11g > @createTables
    
    Sequence created.
    
    Elapsed: 00:00:00.04
    
    Table created.
    
    Elapsed: 00:00:00.05
    
    Table created.
    
    Elapsed: 00:00:00.01
    [11.2] Scott @ My11g > @createExternalTable
    
    Table created.
    
    Elapsed: 00:00:00.06
    [11.2] Scott @ My11g > @showTables
    
    no rows selected
    
    Elapsed: 00:00:00.02
    
    no rows selected
    
    Elapsed: 00:00:00.00
    [11.2] Scott @ My11g > @load
    
    2 rows created.
    
    Elapsed: 00:00:01.80
    
    3 rows created.
    
    Elapsed: 00:00:00.25
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    [11.2] Scott @ My11g > @showTables
    
       CUST_ID CUST_NAME                      CUST_MAIL
    ---------- ------------------------------ ------------------------------
             2 John                           John@deere.com
             3 Scott                          Scott@mail.com
    
    Elapsed: 00:00:00.02
    
       CUST_ID ITEM_NAME                        ITEM_QTY
    ---------- ------------------------------ ----------
             3 champagne                            1000
             3 Caviar                                500
             2 Engine                                  1
    
    Elapsed: 00:00:00.00
    {code}
    Now let's modify data.csv as if it was another load :{code}[oracle@Nicosa-oel test_external_tables]$ cat data.csv 
    "John", "John@deere.com", "Oil", 2
    "Albert", "Al@relativity.com", "Chalk", 314
    {code}
    
    And run the load process again :{code}[11.2] Scott @ My11g > @load
    
    1 row created.
    
    Elapsed: 00:00:00.19
    
    2 rows created.
    
    Elapsed: 00:00:00.05
    
    Commit complete.
    
    Elapsed: 00:00:00.00
    [11.2] Scott @ My11g > @showTables
    
       CUST_ID CUST_NAME                      CUST_MAIL
    ---------- ------------------------------ ------------------------------
             2 John                           John@deere.com
             3 Scott                          Scott@mail.com
             4 Albert                         Al@relativity.com
    
    Elapsed: 00:00:00.01
    
       CUST_ID ITEM_NAME                        ITEM_QTY
    ---------- ------------------------------ ----------
             3 champagne                            1000
             3 Caviar                                500
             2 Engine                                  1
             2 Oil                                     2
             4 Chalk                                 314
    
    Elapsed: 00:00:00.02
    {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 55. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    The load.sql could have been written like that which might be better performance wise:
    merge into customers tgt
    using (select distinct cust_name, cust_mail from raw_data) src
    on (src.cust_name=tgt.cust_name)
    when not matched then insert values (myseq.nextval, src.cust_name, src.cust_mail);
    
    insert into orders
    select c.cust_id, r.item_name, r.item_qty
    from raw_data r
    join customers c
    on (c.cust_name=r.cust_name);
    
    commit;
    Nota : untested as I've no database to play with on my home laptop. I'll give it a try tomorrow morning.

    Edited by: Nicosa on Nov 22, 2012 10:10 AM
    Corrections after testing :
    - missing VALUES keyword in MERGE query
    - DISTINCT added in MERGE query (src) to avoid pk violation in customers
  • 56. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    That is actually a very nice solution Nicosa !
    I will definitely give this a try, my real life example is a bit more complex, but I think I could make it work.

    Thank you very much for all your time and effort, I learned a lot from the examples you gave me.
  • 57. Re: Sequence with multiple increments
    Nicosa Expert
    Currently Being Moderated
    Peter Marcoen wrote:
    That is actually a very nice solution Nicosa !
    I will definitely give this a try, my real life example is a bit more complex, but I think I could make it work.

    Thank you very much for all your time and effort, I learned a lot from the examples you gave me.
    You're welcome.

    Notice that this solution doesn't need "trick" on the sequence. The other applications can use it in a standard fashion.
  • 58. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    Nicosa wrote:
    Notice that this solution doesn't need "trick" on the sequence. The other applications can use it in a standard fashion.
    Yes indeed, this is a much cleaner solution
  • 59. Re: Sequence with multiple increments
    PeterMarcoen Newbie
    Currently Being Moderated
    This part of the Oracle documentation verifies your choice for external tables:

    Choosing External Tables Versus SQLLoader

    The record parsing of external tables and SQL*Loader is very similar, so normally there is not a major performance difference for the same record format. However, due to the different architecture of external tables and SQL*Loader, there are situations in which one method is more appropriate than the other.

    In the following situations, use external tables for the best load performance:

    - You want to transform the data as it is being loaded into the database

    - You want to use transparent parallel processing without having to split the external data first

    However, in the following situations, use SQL*Loader for the best load performance:

    - You want to load data remotely

    - Transformations are not required on the data, and the data does not need to be loaded in parallel

    Source: http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_concepts.htm#i1004917

    Edited by: Peter Marcoen on 22-nov-2012 4:26

    Edited by: Peter Marcoen on 22-nov-2012 4:27
1 2 3 4 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points