by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

LOB 발표









LOB 기본



A. 종류




1. 저장 위치 별 분류
  • Internal - CLOB/NCLOB/BLOB/SECUREFILE
  • External - BFILES


2. 유형 별 분류
  • CLOB
       XML이나 일반 문장과 같은 문자 정보를 대량 저장
       DB 케릭터셋 변환 적용 - 검색 시 DB 케릭터셋 <-> Client 캐릭터셋 변환
  • NCLOB
       CLOB 과 유사하나 DB 의 네셔널 케릭터 셋으로 저장
  • BLOB
       이미지, 워드 문서 등의 정보를 대량 저장
       케릭터셋 변환 적용되지 않음
  • BFILES
       운영체제 파일에 대한 포인터
       파일의 내용에 대해 읽기 모드로만 접근 가능
       데이터 저장 위해 Oracle Directory Object 사용
       읽기 일관성 보장 되지 않음
       세션 별 Open 할 수 있는 파일은 session_max_open_files 에 따름(Default 10)
  • Securefiles
       기존 LOB 을 대체하기 위한 11G 이상 버전에서 지원되는 객체
       중복제거/압축/암호화/저널링 등 지원


B. 구조



  • LOB Table 생성 시 Table Segment 와 무관하게 Index/Segment 생성
  • 일반적으로 Table row 에 저장되는 것은 LOB Pointer
  • LOB Segment 는 Chunk 단위로 저장


1. 동작 방식
  • 테이블의 LOB 은 실제로 LOB Index 만 지정
  • LOB Index 는 LOB 조각 모두 지정
  • Table LOB Locator 에서 포인터 참조 후 인덱스 경유하여 Random Access


2. 읽기 일관성 구현
  • 일관성 이미지를 LOB Segment 자체에 저장
  • Transaction 이 LOB 을 변경하면 과거 데이터를 남겨두고 새 데이터 저장 위해 Chunk 할당
  • 롤백 시 LOB Index Pointer 만 과거 이미지로 변경
  • Undo Tablespace 의 이미지는 Lob Locator 와 Lob Index 를 위해서만 사용



LOB 옵션





A. 종류




B. 상세


1. STORE AS



... lob (COL_NAME) store as basicfile | securefile "SEG_NAME" (...);


  • SECUREFILE 인지 BASICFILE (구형 LOB) 인지 결정
  • Default: BASICFILE


2. TABLESPACE


  • 테이블 실제 저장 가능한 Tablespace 와 분리하여 관리 가능
       일반 Tablespace 와 다른 Uniform extent 저장
       백업/복구 및 효율적 공간 관리
  • LOB Segment, LOB Index 는 분리 저장 불가(8i R3 이상부터)


3. IN/OUT Row


... lob (COL_NAME) store as basicfile "SEG_NAME" (enable | disable storage in row);


  • IN Row
       LOB 데이터를 Table Row 에 저장
       실제 읽기 일관성 및 Caching 등의 방식은 Table 속성에 따름
  • OUT Row
       LOB 데이터를 LOB Segment 에 저장
       Table row 에는 20 byte 의 Locator 저장
       Row 별 최소 사이즈는 1 block (1 Chunk 의 최소 사이즈)
  • 4000 byte 이상의 데이터는?
       IN/OUT Row 옵션과 관계 없이 LOB Segment 에 저장
       In Row 에는 36byte 정도의 Control data 가 저장 --> 실제 입력 가능 사이즈는 3964byte
       Multibyte 케릭터셋(ex UTF8) 은 3964 / 2 인 1982 byte까지만 저장 가능
  • 관리 포인트
       IN ROW 저장 시 별도 Access / Disk 탐색 과정 없으므로 성능 향상 가능하나
       1 ROW 사이즈가 길어지면 Block chaining 이 발생 가능
  • Default: ENABLE STORAGE IN ROW (생성 이후 변경 불가)


4. CHUNK


... lob (COL_NAME) store as basicfile "SEG_NAME" (chunk 8192);


  • Bytes 단위 명시 가능하나, DB Block Size 의 배수 중 Chunk 지정 사이즈 이상의 최소값으로 지정
       Block size 8192 이고 Chunk 를 20000 으로 지정 했을 시 실제 Chunk 사이즈는 24576
       Min 은 db_block_size / Max 는 32767
  • 각 LOB 의 값은 실제 사이즈와 무관하게 최소 하나의 Chunk 사용 함
  • Chunk Size 가 작을 때
       LOB Segment 할당 단위 세분화로 공간 효율적 사용 가능
       LOB Index 가 복잡해짐
  • Chunk Size 가 Block Size 보다 클 때
       Multiblock Read I/O 가능
  • Default: db_block_size


5. RETENTION / PCTVERSION


... lob (COL_NAME) store as basicfile "SEG_NAME" (retention | pctversion 50);


  • LOB Segment 의 UNDO 이미지 관리
  • 두 가지 방법 중 택 1 가능
       RETENTION
       일관성 읽기 이미지 관리 정책이 undo_retention 정책에 따르도록 함
       PCTVISION
       일관성 읽기를 위한 별도의 LOB Segment 공간을 전체 공간의 n% 까지 확장(를 위해 노력)
  • 너무 작을 경우 ORA-01555 / ORA-22924 에러 발생 가능
22924, 00000, "snapshot too old"
//  *Cause:  The version of the LOB value needed for the consistent read was
//           already overwritten by another writer.
//  *Action: Use a larger version pool.



5-2. Securefile Retention


... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention auto) ; 
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention min 900) ;
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention none) ;
... lob (COL_NAME) store as securefile "SEG_NAME" ( ... retention max (storage maxsize 400M)) ;


  • MAX / MIN / AUTO / NONE 4가지 타입
       MAX: storage 옵션의 max 사이즈에 도달 할 때 까지 old 이미지를 유지
       MIN: 최소 보존 기간을 undo_retention 처럼 지정할 수 있음
       AUTO: DB 가 알아서 관리
       NONE: retention 보존 기간이 없으므로 필요 시 마다 재 사용 가능함




6. CACHE


... lob (COL_NAME) store as basicfile "SEG_NAME" (cache | nocache | cache reads);


  • LOB Segment 에 I/O 발생 시 Buffer Cache 를 경유할지 결정
  • CACHE READ 옵션은 Read 시에만 캐실
  • 대기 이벤트
       CACHE : db file sequential read/write, latch: cache buffer chains ..
       NOCACHE: direct path read/write(lob)
    Cache 설정 시 유의
  • Table Block 과는 다르게 Buffer 의 most-recently-used end 에 캐싱
  • CACHE_SIZE_THRESHOLD 에도 영향을 받지 않음(8i 이하 parameter)
       --> 다른 버퍼 블록을 Age out 시킬 수 있으므로 사용에 주의 필요
  • Redo 생성에도 영향
       노캐시에서 블록은 direct path 모드로 I/O 되므로 전체 이미지(chunk) 가 redo 에 기록
       캐시 모드에서는 블록 변경사항만 redo 에 기록
       DISABLE STORAGE IN ROW + NOCACHE + CHUNK 32767 일 경우
       LOB 실제 변경 사이즈에 무관하게 32KB 전체를 redo 에 기록
  • Defaule: NOCACHE


7. LOGGING | NOLOGGING


  • 다들 알고 있는 그것



LOB 성능 및 활용





A.성능비교



1. Update 시 성능비교
  • In line Row --> In line Row
  • In line Row --> Out line Row
  • OUT line Row --> OUT line Row
  • OUT line Row --> In line Row
SQL> create table lob_update_test (a clob);


-- Table segment, Lob segment Extent 확장
insert into lob_update_test select substr(a, 1, 1981) from lob_raw;
insert into lob_update_test select substr(a, 1, 1983) from lob_raw;
delete lob_update_test;

-- 테스트 데이터 삽입
insert into lob_update_test select substr(a, 1, 1981) from lob_raw;

-- 1981 -> 1982자: in row update
SQL> update lob_update_test set a=a||'a';

-- 1982 -> 1983자: in -> out row update
SQL> update lob_update_test set a=a||'a';

-- 1983 -> 1984자: out -> out row update
SQL> update lob_update_test set a=a||'a';

-- 1984 -> 1981자: out -> in row update
SQL> update lob_update_test set a=substr(a, 1, 1981); -- out --> in row update


-- 1981 -> 1982자: in row update
SQL> update lob_update_test set a=a||'a';

8192 rows updated.

Elapsed: 00:00:12.58


-- 1982 -> 1983자: in -> out row update
SQL> update lob_update_test set a=a||'a';

8192 rows updated.

Elapsed: 00:00:20.37


-- 1983 -> 1984자: out -> out row update
SQL> update lob_update_test set a=a||'a';

8192 rows updated.

Elapsed: 00:00:24.15


-- 1984 -> 1981자: out -> in row update
SQL> update lob_update_test set a=substr(a, 1, 1981);

8192 rows updated.

Elapsed: 00:00:13.21

  • In -> Out / Out -> In Row 변경 시 차이는 없음
  • 실제 기록하는 Segment 가 어디인지에 의해 더 큰 차이 발생



B. 트레이스 분석



  • LOB Segment 에 대한 Direct path read block 은 어디에?
  • CR block 은 왜 증가할까?


1. LOB Segment 에 대한 Direct path read block 은 어디에?




SQL> create table lob_direct_test (a clob) lob(a) store as basicfile (disable storage in row);

Table created.

SQL> insert into lob_direct_test select a from lob_raw_small;

3000 rows created.

-- Buffer 에 캐싱
SQL> select * from lob_direct_test;

...
A
--------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

...

3000 rows selected.

SQL> alter session set events '10046 trace name context forever, level 12' ;

Session altered.

SQL> select * from lob_direct_test;

...
A
--------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

...

3000 rows selected.


-- 10046 trace --> tkprof 변환

********************************************************************************

SQL ID: gu15dbd8v74zk Plan Hash: 4217871914

select *
from
 lob_direct_test


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         17          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3001      0.02       0.07          0       3005          0        3000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3003      0.02       0.07          0       3022          0        3000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
      3000       3000       3000  TABLE ACCESS FULL LOB_DIRECT_TEST (cr=3005 pr=0 pw=0 time=12426 us cost=5 size=6006000 card=3000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    3002        0.00          0.00
  SQL*Net message from client                  3002        0.74          0.84
********************************************************************************



  • Default NOCACHE + DISABLE STORAGE IN ROW 에서 LOB 은 Direct path read/write 를 한다고 알려져 있으나 10046 Trace 에서는 Disk read 확인 불가
  • Overall 통계 확인

    
    ********************************************************************************
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0         17          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch     3001      0.02       0.07          0       3005          0        3000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3003      0.02       0.07          0       3022          0        3000
    
    Misses in library cache during parse: 1
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                    6003        0.00          0.00
      SQL*Net message from client                  6003       15.44         16.43
      Disk file operations I/O                        2        0.00          0.00
      direct path read                             6000        0.00          0.05
      log file sync                                   1        0.12          0.12
    
    
    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          1          2           1
    Fetch        1      0.00       0.00          0         16          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        5      0.00       0.00          0         17          2           2
    
    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    
        2  user  SQL statements in session.
        1  internal SQL statements in session.
        3  SQL statements in session.
    ********************************************************************************
    Trace file: lob_direct_test.trc
    Trace file compatibility: 11.1.0.7
    Sort options: default
    
           1  session in tracefile.
           2  user  SQL statements in trace file.
           1  internal SQL statements in trace file.
           3  SQL statements in trace file.
           3  unique SQL statements in trace file.
       24195  lines in trace file.
           1  elapsed seconds in trace file.
    
    



  • direct path read 는 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 에만 확인 가능
  • 10046 trace 원본 확인

    
    =====================
    PARSING IN CURSOR #140502644297648 len=29 dep=0 uid=84 oct=3 lid=84 tim=1384572185091261 hv=1370723314 ad='783d7a60' sqlid='gu15dbd8v74zk'
    select * from lob_direct_test
    END OF STMT
    PARSE #140502644297648:c=3000,e=3710,p=0,cr=17,cu=0,mis=1,r=0,dep=0,og=1,plh=4217871914,tim=1384572185091260
    EXEC #140502644297648:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4217871914,tim=1384572185091308
    WAIT #140502644297648: nam='SQL*Net message to client' ela= 7 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091360
    WAIT #140502644297648: nam='SQL*Net message from client' ela= 130 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091496
    WAIT #140502644297648: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091554
    FETCH #140502644297648:c=0,e=52,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=4217871914,tim=1384572185091570
    WAIT #140502644297648: nam='SQL*Net message from client' ela= 61 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1384572185091647
    WAIT #0: nam='Disk file operations I/O' ela= 52 FileOperation=2 fileno=5 filetype=2 obj#=79315 tim=1384572185091921
    WAIT #0: nam='direct path read' ela= 19 file number=5 first dba=435500 block cnt=1 obj#=79315 tim=1384572185091970
    WAIT #0: nam='direct path read' ela= 5 file number=5 first dba=435500 block cnt=1 obj#=79315 tim=1384572185092022
    
    .
    .
    .
    
    WAIT #0: nam='SQL*Net message from client' ela= 39 driver id=1650815232 #bytes=1 p3=0 obj#=79315 tim=1384572186011006
    FETCH #140502644297648:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4217871914,tim=1384572186011028
    STAT #140502644297648 id=1 cnt=3000 pid=0 pos=1 obj=79314 op='TABLE ACCESS FULL LOB_DIRECT_TEST (cr=3005 pr=0 pw=0 time=12426 us cost=5 size=6006000 card=3000)'
    WAIT #140502644297648: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=79315 tim=1384572186011095
    
    
    



  • SQL 에 대한 커서는 140502644297648 이나 (PARSING IN CURSOR #140502644297648)
  • Direct path read 는 커서 0 이 대기 (WAIT #0: nam='direct path read' ela= 19 file number=5 first dba=435500 block cnt=1 )
  • LOB Segment 에 대한 오퍼레이션은 Internal cursor 가 수행 한다고 함 참조(http://ukja.tistory.com/256)




1. Query block 은 왜 증가?



SQL> select segment_name, blocks from dba_segments where segment_name in (
  2  'LOB_DIRECT_TEST','SYS_LOB0000079317C00001$$','SYS_IL0000079317C00001$$'
  3  );

SEGMENT_NAME                                                                          BLOCKS
--------------------------------------------------------------------------------- ----------
SYS_LOB0000079317C00001$$                                                               3072
SYS_IL0000079317C00001$$                                                                  32
LOB_DIRECT_TEST                                                                           16



  • LOB Segment (Direct path read) 를 제외 한 block 의 총 합은 50 미만이나
  • 이 전 trace 에서 3022 개 블록 query

    
    -- Test Table 생성
    
    SQL> create table lob_qb_test2 (id number, a clob) lob(a) store as QBSEG2 (disable storage in row);
    
    SQL> create table lob_qb_test3 (
         val1 varchar(4000), 
         val2 varchar(4000), 
         val3 varchar(4000), 
         val4 varchar(4000), 
         val5 varchar(4000), 
         val6 varchar(4000), 
         val7 varchar(4000), 
         val8 varchar(4000), 
         a clob
    ) lob(a) store as QBSEG3 (disable storage in row);
    
    
    -- Test 데이터 입력
    
    SQL> insert into lob_qb_test2 select rownum id, a from lob_raw_small;
    
    SQL> insert into lob_qb_test3 select a, a, a, a, a, a, a, a, a from lob_raw_small;
    
    SQL> commit;
    
    
    - Size 확인
    
    SEGMENT_NAME                        BLOCKS
    ------------------------------- ----------
    LOB_QB_TEST2                            16  -- Table2
    QBSEG2                                3072  -- Lob Segment2
    SYS_IL0000079299C00002$$                32  -- Lob Index2
    
    LOB_QB_TEST3                          6144  -- Table3
    QBSEG3                                3072  -- Lob Segment3
    SYS_IL0000079305C00009$$                32  -- Lob Index3
    
    
    SQL> alter session set events '10046 trace name context forever, level 12' ;
    SQL> select /*+ full(t) noparallel(t) */ id from lob_qb_test2 t;
    SQL> select /*+ full(t) noparallel(t) */ id, a from lob_qb_test2 t;
    SQL> select /*+ full(t) noparallel(t) */ * from lob_qb_test3 t;
    
    



  • TKPROF trace 확인

    
    ********************************************************************************
    
    SQL ID: ct1cryakrby3n Plan Hash: 3454962022
    
    select /*+ full(t) noparallel(t) */ id
    from
     lob_qb_test2 t
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0         17          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch      201      0.00       0.00          0        216          0        3000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      203      0.00       0.00          0        233          0        3000
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 84
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
          3000       3000       3000  TABLE ACCESS FULL LOB_QB_TEST2 (cr=216 pr=0 pw=0 time=1434 us cost=5 size=39000 card=3000)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                     201        0.00          0.00
      SQL*Net message from client                   201        2.60          2.63
    
    
    
    
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0         17          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch      201      0.00       0.00          0        216          0        3000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total      203      0.00       0.00          0        233          0        3000
    
    Misses in library cache during parse: 1
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                     202        0.00          0.00
      SQL*Net message from client                   202       12.28         14.92
      log file sync                                   1        0.03          0.03
    
    
    
    
    ********************************************************************************
    
    SQL ID: 538uyahv081gc Plan Hash: 3454962022
    
    select /*+ full(t) noparallel(t) */ id, a
    from
     lob_qb_test2 t
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.05         14         17          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch     3001      0.02       0.07          0       3003          0        3000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3003      0.03       0.13         14       3020          0        3000
    
    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 84
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
          3000       3000       3000  TABLE ACCESS FULL LOB_QB_TEST2 (cr=3003 pr=0 pw=0 time=14151 us cost=5 size=603000 card=3000)
    
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      Disk file operations I/O                        1        0.00          0.00
      db file sequential read                         1        0.04          0.04
      SQL*Net message to client                    3002        0.00          0.00
      SQL*Net message from client                  3002        1.98          2.08
    
    
    
    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.05         14         17          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch     3001      0.02       0.07          0       3003          0        3000
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total     3003      0.03       0.13         14       3020          0        3000
    
    Misses in library cache during parse: 1
    
    Elapsed times include waiting on following events:
      Event waited on                             Times   Max. Wait  Total Waited
      ----------------------------------------   Waited  ----------  ------------
      SQL*Net message to client                    6003        0.00          0.00
      SQL*Net message from client                  6003       23.53         25.78
      Disk file operations I/O                        1        0.00          0.00
      db file sequential read                        22        0.04          0.05
      direct path read                             6000        0.01          0.08
      log file sync                                   1        0.04          0.04
    
    
    ********************************************************************************
    
    
    



  • 답은?

C. 기타































  • col+1, round, ceil, floor: 문자--> 숫자 묵시적 형 변환 불가
    SQL> select ceil(c)+1 from lob_test_f;
    
    ,ceil(c)+1
          *
    ERROR at line 4:
    ORA-00932: inconsistent datatypes: expected NUMBER got CLOB
    



  • reverse
    SQL> select reverse(a) from lob_test1;
    select reverse(a) from lob_test1
                   *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected CHAR got CLOB
    



  • vsize
    SQL> select vsize(a) from lob_test2;
    select vsize(a) from lob_test2
                 *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got CLOB
    
    



  • initcap, translate : 캐릭터셋과 무관하게 4000글자까지만 지원
    SQL> select initcap(a) from lob_test1;
    select initcap(a) from lob_test1
                   *
    ERROR at line 1:
    ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 12000, maximum: 4000)
    
    SQL> select translate(a,'a','b') from lob_test1;
    select translate(a,'a','b') from lob_test1
                     *
    ERROR at line 1:
    ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 12000, maximum: 4000)
    






문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.