Oracle PL / SQL: CLOB Data Type Performance in PL / SQL

If I use many CLOB variables in a PL / SQL stored procedure to store a lot of long string, are there any performance issues? Is CLOB length also a variable? Are there any known limitations / disadvantages for CLOBs instead of using varchar2 and long?

+3
source share
3 answers

CLOB variables in length, yes. The upper limit varies depending on which version of Oracle is included and the database block size. For 11G, the limit is the “4G * DB_BLOCK_SIZE parameter value” (from 11G PL / SQL Language Reference ). VARCHAR2 values ​​are limited to 32767 bytes in PL / SQL.

CLOB VARCHAR2 PL/SQL, Google . , VARCHAR2 , CLOB ( , ), . , , VARCHAR2 CLOB, 1000 . Tom Kyte Runstats - Oracle.

: 32K, CLOB; , CLOB, VARCHAR2.

NB: CLOB , VARCHAR2 , Oracle, , desrecate VARCHAR2, , .

+5

CLOB () , VARCHAR2. CLOB VARCHAR2, , .

, , .

1), 4000 , VARCHAR2, CLOB.

2), 32k PLSQL, VARCHAR2, CLOB.

, . VARCHAR2, CLOB, VARCHAR2.

, , LOB LOB ( ), PERMANENT ( ). CLOB PLSQL CLOB JAVA , CLOB Oracle. , , CLOB, CLOB, . , . , , ( ). , . , . , (, java) .

.

+2

CLOB , . CLOB. :

  • Relying on implicit CLOB transforms, CLOB ↔ VARCHAR2 tends to be slow.
  • Work with the package is dbms_lobquite effective. Just do it in a loop:
    • Get a piece of data from your CLOB to the buffer
    • Use data from your buffer. Maybe turn it into another piece of data.
    • Positively write the converted fragment of data to the target destination CLOB.
0
source

Source: https://habr.com/ru/post/1741040/


All Articles