7 Replies Latest reply: Jul 14, 2013 12:04 AM by Barbara Boehmer RSS

    sqlldr bindsize parameter

    652398
      Using sqlldr to import data:
      sqlldr ${user_id} control=${ctl_file} rows=10000 bindsize=8192 readsize=8192 errors=999999 log=${log_file}/${today_DATE}.log

      When executing:
      Commit point reached - logical record count 2
      Commit point reached - logical record count 4
      Commit point reached - logical record count 6
      Commit point reached - logical record count 8
      Commit point reached - logical record count 10
      Commit point reached - logical record count 12
      ...

      In data file, there is only 80 characters(that is 80 bytes) in every row. However, in log file:
      Space allocated for bind array: 6714 bytes(2 rows)

      Why 6714 bytes is here? Except the 160 bytes for two rows, what's the other sizes?

      Thanks a lot for your kindly reply.
        • 1. Re: sqlldr bindsize parameter
          DK2010
          What is the OS/DB version you are using
          • 2. Re: sqlldr bindsize parameter
            Richard Harrison .
            Hi,
            This doesn't explain everything but its a good start point

            http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch05.htm#1005908

            It seems that the bind array is based on the max size of he datatype specified for each column basically - it doesn't take into account what it actually finds in the data.

            You might have a char column specified in your control file but if you don't specify char(1) it allocates 255 bytes which is the max -sqlldr doesn't know that he data is much smaller than that you have to tell it the smallest size it could be.

            Cheers,
            Harry
            • 3. Re: sqlldr bindsize parameter
              652398
              Oracle 9.2.0.7
              • 4. Re: sqlldr bindsize parameter
                652398
                Table has 14 fields:
                create table TBL
                (
                A VARCHAR2(3) not null,
                B VARCHAR2(10) not null,
                C VARCHAR2(5) not null,
                D VARCHAR2(1) not null,
                E VARCHAR2(4) not null,
                F VARCHAR2(4) not null,
                G VARCHAR2(6) not null,
                H VARCHAR2(1) not null,
                I VARCHAR2(1) not null,
                J VARCHAR2(5) not null,
                K VARCHAR2(6) not null,
                L VARCHAR2(1) not null,
                M TIMESTAMP(6) not null,
                N VARCHAR2(2) not null
                )
                The size of all columns is about 55 bytes.

                Binsize is 8192, it doesn't contains these actual fields size obviously.

                As you said, "You might have a char column specified in your control file but if you don't specify char(1) it allocates 255 bytes", if so, 13*255+size of timestamp is probably approachable to 8192 bytes in control file. However, how to specify a char column in control file as you said? If I don't specify that, the load operation of sqlldr is always not correct according to size of column?

                Thanks a lot.
                • 5. Re: sqlldr bindsize parameter
                  652398

                  Thanks.

                   

                  Table has 14 fields:

                  create table TBL

                  (

                  A VARCHAR2(3) not null,

                  B VARCHAR2(10) not null,

                  C VARCHAR2(5) not null,

                  D VARCHAR2(1) not null,

                  E VARCHAR2(4) not null,

                  F VARCHAR2(4) not null,

                  G VARCHAR2(6) not null,

                  H VARCHAR2(1) not null,

                  I VARCHAR2(1) not null,

                  J VARCHAR2(5) not null,

                  K VARCHAR2(6) not null,

                  L VARCHAR2(1) not null,

                  M TIMESTAMP(6) not null,

                  N VARCHAR2(2) not null

                  )


                  The size of all columns is about 55 bytes. Binsize is 8192 in control file, However, two lines import once a time when executing. Obviously, it is not "55" for actually. Control file is like:


                  TRUNCATE

                  INTO TABLE TBL

                  FIELDS TERMINATED BY X'09'

                  TRAILING NULLCOLS

                  (A                       "upper(trim(:A))"

                  ,B                       "upper(trim(:B))"

                  ,C                       "upper(trim(:C))"

                  ,D                       "upper(trim(:D))"

                  ,E                       "upper(trim(:E))"

                  ,F                       "upper(trim(:F))"

                  ,G                       "upper(trim(:G))"

                  ,H                       "upper(trim(:H))"

                  ,I                       "upper(trim(:I))"

                  ,J                       "upper(trim(:J))"

                  ,K                       "upper(trim(:K))"

                  ,L                       "upper(trim(:L))"

                  ,M                       "upper(trim(:M))"

                  ,N                       "upper(trim(:N))"

                  )

                   

                  It is not "14*255" as you said.

                   

                  Could you give me some advice? Thanks a lot.

                  • 6. Re: sqlldr bindsize parameter
                    652398

                    Thanks.

                     

                    Table has 14 fields:

                    create table TBL

                    (

                    A VARCHAR2(3) not null,

                    B VARCHAR2(10) not null,

                    C VARCHAR2(5) not null,

                    D VARCHAR2(1) not null,

                    E VARCHAR2(4) not null,

                    F VARCHAR2(4) not null,

                    G VARCHAR2(6) not null,

                    H VARCHAR2(1) not null,

                    I VARCHAR2(1) not null,

                    J VARCHAR2(5) not null,

                    K VARCHAR2(6) not null,

                    L VARCHAR2(1) not null,

                    M TIMESTAMP(6) not null,

                    N VARCHAR2(2) not null

                    )


                    The size of all columns is about 55 bytes. Binsize is 8192 in control file, However, two lines import once a time when executing. Obviously, it is not "55" for actually. Control file is like:


                    TRUNCATE

                    INTO TABLE TBL

                    FIELDS TERMINATED BY X'09'

                    TRAILING NULLCOLS

                    (A                       "upper(trim(:A))"

                    ,B                       "upper(trim(:B))"

                    ,C                       "upper(trim(:C))"

                    ,D                       "upper(trim(:D))"

                    ,E                       "upper(trim(:E))"

                    ,F                       "upper(trim(:F))"

                    ,G                       "upper(trim(:G))"

                    ,H                       "upper(trim(:H))"

                    ,I                       "upper(trim(:I))"

                    ,J                       "upper(trim(:J))"

                    ,K                       "upper(trim(:K))"

                    ,L                       "upper(trim(:L))"

                    ,M                       "upper(trim(:M))"

                    ,N                       "upper(trim(:N))"

                    )

                     

                    It is not "14*255" as you said.

                     

                    Could you give me some advice? Thanks a lot.

                    • 7. Re: sqlldr bindsize parameter
                      Barbara Boehmer

                      As previously stated, if you do not specify a size in your control file, like:

                       

                      D "upper(trim(:D))"

                       

                      then it defaults to 255, as if you had done:

                       

                      D char(255) "upper(trim(:D))"

                       

                      so if you want to limit the size, then you need to do something like:

                       

                      D char(1) "upper(trim(:D))"