Ah, I forgot one important issue about LOB performance -> if you select a varchar or long, the data in those is returned to client instantly, but if you select a LOB, then only a pointer (locator) is returned and it's up to client whether it sends a reading request to server to get actual contents of LOB. This means additional sqlnet roundtrips for each LOB item.
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 24, 2003 5:04 AM > Hi! > > > Basically making a column type as Clob - instead of varchar2(4000) - how > > much extra overhead (as far as bytes/size) will it add to the table size? > > When creating table with clob columns, you can specify whether you can allow > storing of small lobs inline with the actual data row. The clob data maximum > inline limit is 3964 bytes (4000 bytes - 20 bytes for lob locator -16 bytes > for lob inode structure), anything larger will go to external lob segment. > Note that for varying-width characterset clobs, Oracle converts data to > 2-byte fixed character set, so with UTF-8, a letter 'X' will be stored as > hex value 00 58 or 58 00 to the datablock, depending on which platform you > are. If you store your 'X' into normal varchar field, it'll be stored just > as hex value 58, thus in one byte. > So, with variable-width charsets you can actually store only 1982 characters > inline with lob column. Note that nclobs are a bit different, they can have > even longer "byte-length". Also they always seem to be stored in little > endian format, even on Intel platform (that means 00 58 for 'X' not 58 00 > like with regular clob). > > Note that there's a difference between "enable storage in row" lob which is > forced to store its lob item out-of-line (because it's size >3964 bytes) and > regular "disable storage in row" lob. In first case, more space is used in a > row because inode structure, which points directly to physical lob location. > But native out-of-line lobs created with "disable storage in row" do have > only lob locator inline, thus needing additional sequential IOs for scanning > lob index which directs them to lob item physical location. In other words, > inline lobs don't normally need lob index scanning. > > > > > I have a table with 6000 rows, and about 500 of them have 1 column with > more > > that 4000 chars, so they need to go to a clob column. now I just want to > > know if I make a clob column and put the bigger column into clob for all > the > > 6000 rows, how much disk space would I waste by doing this, or would > Oracle > > use the space efficient enough on the clobs so that I shouldn't worry > about > > With so small table you don't actually have to worry about size at all. > Anyway, depending on your data and character set, your table might grow > because lob locator&inode structures are bigger than varchar2 control > structure (only 1 or 3 bytes stating it's length) and if using > variable-width charsets, your inline data will probably grow. On the other > hand, large lobs will be stored in lob segment. But: space in lob segments > is allocated in chunks, which are multiplies of Oracle blocks. Thus if you > have 8kB chunk size for lobs, then every out-of-line lob item will take at > least 8k. If lobs size is 9000 bytes, then it takes 16k and so on (situation > changes a bit with big lobs). > > So, overall size of your data will grow. > > > size ? Other question is it true that Clobs are generally slower even the > > data in them are small ? > > Querying inline clobs shouldn't be much slower, if you are using indexed > access to your table. But since you have very big rows, your buffer cache > might get saturated, since in normal envrionment you'd have tens to hundreds > of rows in a block, now you got only few. Full table scans and analyzing > will of course be slower, since table is much bigger. > > Sorry for answering a simple question with too long answer, I got carried > away... > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
