如何“定长记录"?和“固定长度字段"提高数据库性能? [英] How do "Fixed-length records" and "Fixed-length fields" increases database performance?

问题描述

任何人都可以通过ORACLE_LOADER访问驱动程序解释以下两条语句来提高Oracle外部表的性能:

Could anyone please explain the below two statements w.r.t the Oracle external table performance improvement with the ORACLE_LOADER access driver:

1. 定长记录的处理速度比终止记录要快 一个字符串.
2. 定长字段的处理比定界字段的处理速度快.

1. Fixed-length records are processed faster than records terminated by a string.
2. Fixed-length fields are processed faster than delimited fields.

对代码的解释可能有助于我深入理解该概念.这是两种语法:

Explanation with code might help me to understand the concept in depth. here is the two syntax(s):

固定字段长度

Fixed field length

create table ext_table_fixed (
   field_1 char(4),
   field_2 char(30)
)
organization external (
   type       oracle_loader
   default directory ext_dir
   access parameters (
     records delimited by newline
     fields (
       field_1 position(1: 4) char( 4),
       field_2 position(5:30) char(30)
    )
  )
  location ('file')
)
reject limit unlimited;

逗号分隔

Comma delimited

create table ext_table_csv (
  i   Number,
  n   Varchar2(20),
  m   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('file.csv')
)
reject limit unlimited;

推荐答案

非特定于数据库的简化，概念性解释:

Simplified, conceptual, non-database-specific explanation:

当预先知道最大可能的记录长度时，可以在恒定时间内找到记录的结尾/下一个记录的开头.这是因为可以使用简单的加法来计算该位置，这非常类似于数组索引.想象一下，我正在使用int s作为指向记录的指针，并且记录大小是在某处定义的整数常量.然后，从当前记录位置移至下一个记录:

When the maximum possible record length is known in advance, the end of the record/the beginning of the next record can be found in constant time. This is because that location is computable using simple addition, very much analogous to array indexing. Imagine that I'm using ints as pointers to records, and that the record size is an integer constant defined somewhere. Then, to get from the current record location to the next:

int current_record = /* whatever */;
int next_record = current_record + FIXED_RECORD_SIZE;

就是这样！

或者，当使用以字符串终止(或以其他方式分隔)的记录和字段时，您可以想象通过线性时间扫描找到下一个字段/记录，该扫描必须查看每个字符，直到找到分隔符为止.和以前一样，

Alternatively, when using string-terminated (or otherwise delimited) records and fields, you could imagine that the next field/record is found by a linear-time scan, which has to look at every character until the delimiter is found. As before,

char DELIMITER = ','; // or whatever
int current_record = /* whatever */;
int next_record = current_record;
while(character_at_location(next_record) != DELIMITER) {
    next_record++;
}

这可能是现实世界实现的简化版本或天真的版本，但总体思路仍然存在:您不能轻易地在固定时间内进行相同的操作，即使是固定时间，也不太可能成为现实.与执行单个添加操作一样快.

This might be a simplified or naïve version of the real-world implementation, but the general idea still stands: you can't easily do the same operation in constant time, and even if it were constant time, it's unlikely to be as fast as performing a single add operation.

这篇关于如何“定长记录"?和“固定长度字段"提高数据库性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！