Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Austin
    Posts
    62

    Question Unanswered: Options to load millions of records per minute?

    I'm working a problem requiring very fast data load speeds. I need to load something on the order of 650M records into a table in 2hrs. This comes to about 5M records per minute or 90,000+ records per second.

    Is this doable with Oracle not using something like datapump? Part of the issue is the data to be loaded may be an update rather than a straight insert. What are my options here?

    Can this be done via PL SQL with some sort of parallelism. We'll be running 10g. The data will be sourced from either a flat file or some other delimited type file.

    Any thoughts are welcome.

    Regards,

    Mike

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    It depends on a lot of factors i.e. what is your record size?, are you loading into a staging table (i.e. it doesnt matter if you lose the table), do you need indexes on the table, what is the file format (i.e. is it an Oracle export or csv etc) etc.

    Best place to start would be to test different loading strategies on your existing hardware, try parallelism etc. and then try to extrapolate from there as to what you will actually need in terms of hardware, software or wether it is even feasible.

    Alan

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Saying that there'll be an UPDATE instead of a pure INSERT, perhaps you could consider use of external tables - doing so, this operating system file will be reachable as if it was an ordinary Oracle table (meaning: you'll be able to apply any function, statement, etc.) to it.

    I guess it will not be faster than (Data Pump) Import (I didn't measure so I can't tell for sure), but - if you use Import - you won't be able to do anything but.

    Of course, this is just a suggestion - the truth can be found in Alan's words: try, measure, compare, decide.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    If you are doing inserts only SQL*Loader might be an option. If your load qualifies for direct load (read the manual for the restrictions) you could very well achieve the 5M records per minute (depending how large one record is!). But you'll have to test it yourself.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    my suggestion would be to copy your table, make it nologging, do direct
    insert, create any helpful indexes, do your updates, then swap table-names
    with your old production table and create any constraints, indexes, etc after
    the fact (then switch to logging).

    any constraints or indexes will slow down your inserts so you will want to
    make sure all of those are either disabled or don't exist.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    external tables an option ?

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is reading previously posted answers an option?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •