1 2 Previous Next 22 Replies Latest reply: Oct 8, 2012 2:49 AM by Ravetd Go to original post RSS
      • 15. Re: Performance issue while inserting data
        Rahul_India
        >
        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
          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
            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
              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
                >
                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
                  >


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