Skip to Main Content
  • Questions

  • Sizing DB Block in relations to XMLType & CLOBs

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Yves.

Asked: May 03, 2002 - 8:14 am UTC

Last updated: July 31, 2004 - 7:35 pm UTC

Version: 9.0.1

Viewed 1000+ times

You Asked

Hi Tom

The requirement for my database (9.0.1) is that it be able to store XML fragments as well as relational data derived from XML data as well. I am planning to
use the XMLType for my XML fragments. Here is my strategy: I have various entity types in my database; for each entity type there is an associated XML
fragment as well as relational data for that entity. I have created a table of what I call templates (XML Fragments) and every entity that needs to store XML
fragments stores it in my entity table. Of course I have a foreign key from each entity table to the template table.

My main concern is about economy of storage and database block size. Some of the XML fragments will have 5 lines while others will have 100 or 1000 lines.
My understanding from reading the literature is that regardless of the size of the XML, Oracle must use a minimum of space equal to the database block size.
For me even if I size my database block down to 1k, I'd still be wasting a lot of space. On the other hand, some of the relational tables in my database can have
a row size of 8k. Using the 1k example an 8k record will be spread over 8 database block and querying will be much slower given this.

Any thoughts, advise, corrections, or suggestion to my quandary?

Thanks,

Yves

and Tom said...

Using CLOBS (in the xmltype) -- they will be stored INLINE upto 4000 bytes. It is only when they are stored out of line that they will each take a CHUNKSIZE set of storage (blocksize being the smallest chunk).

Hence -- I do not see any problems here. The clob for the XML will be stored inline when it is small and out of line when it is big.



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Yves, May 03, 2002 - 6:35 pm UTC

What do you mean by "inline up to 4000 bytes"? Is 4k the upper limit for XMLType? I thought XMLTType was really a metatype for CLOBs. If the latter is true, the smallest chunk size for a clob is a DB block. Please clarify your response.

Tom Kyte
May 03, 2002 - 7:33 pm UTC

No, I mean CLOBS are stored (by default) "inline" -- in the table itself -- not in the clob segment -- when they are 4000 or less characters.

They are moved "out of line" when they exceed that (from 4000 bytes to 4 gigabytes).

XMLType is built on a clob.
A clob will be "in line" -- stored in the table, not the clob segment -- upto 4000 bytes.
A clob will move out of line taking up one or more chunksize when it exceeds that.

So, your clobs that are small -- they will NOT consume a chunk. The will just be in the table (like a varchar2 would).

This is totally transparent to you.

clobs and table spaces

A reader, July 31, 2004 - 5:40 pm UTC

Does it make sense to store clob data in a separate
tablespace? (e.g. the tablespace could have a bigger
block size?) what are the pros and cons for this?

thanx!

Tom Kyte
July 31, 2004 - 6:08 pm UTC

I would not be looking at multiple block sizes for this no.

you'd do it because it was easier to admin for whatever reason, because it made you feel better.

you would definitely do it if it were nologging.

if it is nocache -- you might do it just to be able to control where the IO's are going.

In general, people do it -- for administrative reasons. You can manage the space in a more control fashion (you have a "blob" tablespace). Lobs do their "undo" mgmt in their tablespaces so they tend to have different "growth" patterns than tables do.

thanx!

A reader, July 31, 2004 - 6:51 pm UTC

"if it is nocache -- you might do it just to be able to control where the IO's
are going."
I suppose you mean that you can control I/O because
you can ultimately map a tablespace to datafiles to disks,
correct? And may be you could put these data files on
a separate disk for example?

"you would definitely do it if it were nologging."
Not sure what you mean here...why do we need to do it
in this case? Perhaps so that you can take it offline
separately due to some reason?

Many thanx!

Tom Kyte
July 31, 2004 - 7:02 pm UTC

correct.

it would make sense to have your nologging stuff separate -- you'll be backing it up differently and backups predominantly happen at the tablespace level so...

thanx!!!!!

A reader, July 31, 2004 - 7:35 pm UTC


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here