[ Pobierz całość w formacie PDF ]
.If the expected delimiter is absent and no maximum length has beenspecified, then the end of record terminates the field.If TRAILINGNULLCOLS is specified, remaining fields are null.If either the delimiter orthe end of record produce a field that is longer than the specified maximum,SQL*Loader generates an error.Date Field MasksThe length of a date field depends on the mask, if a mask is specified.Themask provides a format pattern, telling SQL*Loader how to interpret the datain the record.For example, if the mask is specified as: Month dd, yyyythen  May 3, 1991 would occupy 11 character positions in the record, while January 31, 1992 would occupy 16.If starting and ending positions are specified, however, then the lengthcalculated from the position specification overrides a length derived from themask.A specified length such as  DATE (12) overrides either of those.If thedate field is also specified with terminating or enclosing delimiters, then thelength specified in the control file is interpreted as a maximum length for thefield.Loading Data Across Different Operating SystemsWhen a datafile is created on one operating system that is to be loaded undera different operating system, the data must be written in a form that the targetsystem can read.For example, if the source system has a native, floating-pointrepresentation that uses 16 bytes, and the target system s floating-pointnumbers are 12 bytes; then there is no way for the target system to directlyread data generated on the source system.One solution is to load data acrossa SQL*Net link, taking advantage of the automatic conversion of datatypes.This is the recommended approach, whenever feasible.5-68 Oracle8 Server Utilities In general, the problems of inter-operating system loads occur with the nativedatatypes.Sometimes, it is possible to get around them by padding a fieldwith zeros to lengthen it, or reading only part of the field to shorten it.(Forexample, when an 8-byte integer is to be read on a system that uses 6-byteintegers, or vice versa.) Frequently, however, problems of incompatible byte-ordering, or incompatible implementations of the datatypes, make even thisapproach unworkable.Without a SQL*Net link, it is a good idea to use only the CHAR, DATE, andNUMERIC EXTERNAL datatypes.Datafiles written in this manner are longerthan those written with native datatypes.They take more time to load, butthey transport most readily across operating systems.However, whereincompatible byte-ordering is an issue, special filters may still be required toreorder the data.Determining the Size of the Bind ArrayThe determination of bind array size pertains to SQL*Loader s conventionalpath option.It does not apply to the direct path load method.Because a directpath load formats database blocks directly, rather than using Oracle s SQLinterface, it does not use a bind array.SQL*Loader uses the SQL array-interface option to transfer data to theRDBMS.Multiple rows are read at one time and stored in the bind array.WhenSQL*Loader sends Oracle an INSERT command, the entire array is inserted atone time.After the rows in the bind array are inserted, a COMMIT is issued.Minimum RequirementsThe bind array has to be large enough to contain a single row.If the maximumrow length exceeds the size of the bind array, as specified by the BINDSIZEparameter, SQL*Loader generates an error.Otherwise, the bind array containsas many rows as can fit within it, up to the limit set by the value of the ROWSparameter.The BINDSIZE and ROWS parameters are described in Command-Line Keywords on page 6-3.Although the entire bind array need not be in contiguous memory, the bufferfor each field in the bind array must occupy contiguous memory.If theoperating system cannot supply enough contiguous memory to store a field,SQL*Loader generates an error.SQL*Loader Control File Reference 5-69 Performance ImplicationsTo minimize the number of calls to Oracle and maximize performance, largebind arrays are preferable.In general, you gain large improvements inperformance with each increase in the bind array size up to 100 rows.Increasing the bind array size above 100 rows generally delivers more modestimprovements in performance.So the size (in bytes) of 100 rows is typically agood value to use.The remainder of this section details the method fordetermining that size.In general, any reasonably large size will permit SQL*Loader to operateeffectively.It is not usually necessary to perform the detailed calculationsdescribed in this section.This section should be read when maximumperformance is desired, or when an explanation of memory usage is needed.Specifying Number of Rows vs.Size of Bind ArrayWhen you specify a bind array size using the command-line parameterBINDSIZE (see page 6-4) or the OPTIONS clause in the control file (seepage 5-12), you impose an upper limit on the bind array.The bind array neverexceeds that maximum.As part of its initialization, SQL*Loader determines the space required to loada single row.If that size is too large to fit within the specified maximum, theload terminates with an error.SQL*Loader then multiplies that size by the number of rows for the load,whether that value was specified with the command-line parameter ROWS(see page 6-6) or the OPTIONS clause in the control file (see page 5-12).If thatsize fits within the bind array maximum, the load continues SQL*Loaderdoes not try to expand the number of rows to reach the maximum bind arraysize.If the number of rows and the maximum bind array size are both specified,SQL*Loader always uses the smaller value for the bind array.If the maximum bind array size is too small to accommodate the initialnumber of rows, SQL*Loader uses a smaller number of rows that fits withinthe maximum.5-70 Oracle8 Server Utilities CalculationsThe bind array s size is equivalent to the number of rows it contains times themaximum length of each row [ Pobierz całość w formacie PDF ]

  • zanotowane.pl
  • doc.pisz.pl
  • pdf.pisz.pl
  • funlifepok.htw.pl

    •