This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 8, 2012 12:49 AM by Ravetd Go to original post RSS
  • 15. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    >
    you can use the definitions in your sql*loader file to create the external table. ExTables actually use SQL*Loader as the mechanics that make ET function. Then just insert /* append */ into tableX (select a,b,c,...z from ExtTable);
    >

    how can i create the external table using sql *loader definition.
    I have never used external table .As far as i know to use external tables the data must be in fixed length format.What if my data is not in fixed length format?Then i am forced to use the sql loader.Isnt it?

    @damorgan
    honestly i dont know what is exadata ?
  • 16. Re: Performance issue while inserting data
    Stew Ashton Expert
    Currently Being Moderated
    Dan didn't ask you what exadata was. He asked you to help us figure out why your load is slow. Stop asking questions about solutions and help us diagnose the problem.

    When you are sick, what do you do?

    1) go to the doctor, tell him your symptoms and ask him what disease you have.

    or

    2) go right to the pharmacist, pick some medicine at random and ask how many pills you should take a day?

    So far you are asking us to be pharmacists, but you need a doctor first.
  • 17. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    CREATE TABLE "SYS_SQLLDR_X_EXT_FUNCDEMO" 
    (
      "LAST_NAME" VARCHAR2(20),
      "FIRST_NAME" VARCHAR2(20)
    )
    ORGANIZATION external 
    (
      TYPE oracle_loader
      DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
      ACCESS PARAMETERS 
      (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
        BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'bad_file.bad'
        DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'discard_file.dsc'
        LOGFILE 'ctl_01.log_xt'
        READSIZE 1048576
        SKIP 17
        FIELDS TERMINATED BY "," LDRTRIM 
        REJECT ROWS WITH ALL NULL FIELDS 
        (
          "LAST_NAME" CHAR(255)
            TERMINATED BY ",",
          "FIRST_NAME" CHAR(255)
            TERMINATED BY ","
        )
      )
      location 
      (
        'ctl_01.txt'
      )
    )REJECT LIMIT UNLIMITED
    wheni execute above code which are generate in sql ldr log file
    i am getting an error
    Error at Command Line:9 Column:21
    Error report:
    SQL Error: ORA-06564: object SYS_SQLLDR_XT_TMPDIR_00000 does not exist
    06564. 00000 -  "object %s does not exist"
    *Cause:    The named object could not be found.  Either it does not exist
               or you do not have permission to access it.
    *Action:   Create the object or get permission to access it.
  • 18. Re: Performance issue while inserting data
    Ravetd Newbie
    Currently Being Moderated
    Hi,

    you have to create a databse directory and set permission to it :
    CREATE OR REPLACE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000  AS '<directory>';
    
    GRANT READ ON DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000   TO <user>;
    GRANT WRITE ON DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000   TO <user>; 
    replace <user> and <directory> by your environment value

    Regards
  • 19. Re: Performance issue while inserting data
    rp0428 Guru
    Currently Being Moderated
    >
    how can i create the external table using sql *loader definition.
    I have never used external table .As far as i know to use external tables the data must be in fixed length format.What if my data is not in fixed length format?Then i am forced to use the sql loader.Isnt it?
    >
    Now you have asked a good question!

    Here is the link to the Oracle doc which has examples.
    http://docs.oracle.com/cd/B28359_01/server.111/b28319/et_concepts.htm

    And here is a link to an Oracle-Base article that shows EXACTLY how to do it and has an example almost exactly like yours.
    http://www.oracle-base.com/articles/9i/external-tables-9i.php

    Start with the Oracle-Base article and see if you can perform the example. If you run into any issues ask questions about it.
  • 20. Re: Performance issue while inserting data
    Paulie Pro
    Currently Being Moderated
    >


    Hi Rahul,

    wheni execute above code which are generate in sql ldr log file
    Check out my post in [url https://forums.oracle.com/forums/thread.jspa?threadID=2447694&tstart=0]this thread.

    HTH,


    Paul...
  • 21. Re: Performance issue while inserting data
    Rahul_India Journeyer
    Currently Being Moderated
    Hi All,

    Today i inserted 500000 rows in about 4-5 mins.I have been able to reduce time to about 90%.
    I want time to be below 1 min
  • 22. Re: Performance issue while inserting data
    Ravetd Newbie
    Currently Being Moderated
    Hi,

    Try this :
    1) split your file
    2) Use these files with PARALLEL in your External table (or several SQL*Loader in Direct mode to a Hash partitionned table)
    3) Insert /*+ APPEND*/ with alter session enable parallel DML to a Hash partitionned table (only if external table is used)

    Regards
1 2 Previous Next

Legend

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