Re: LOBS or not ?

quote
> you have no choice but to use LOBs.

unquote

It is quite clear that Markus has a choice. He can choose to continue with his current strategy (with or without minor enhancements), or he can consider using LOBs.

There isn't really enough information available to determine whether LOBs would be a good idea or a very bad idea. But let's pretend Markus is using a 4k block size because most of his articles are less than 4000 bytes.

Using LOBS -

    Minimum lob chunk size is 4K.
    So Oracle breaks up larger lobs in chunks of 4K     stores them in a separate segment, and indexes     them, introducing a large LOCATOR into the     row that nominally holds the LOB. If the LOBs     are stored out of line every LOB, no matter     how small uses the full 4K minimum in the     LOB segment. Then you have to play around     with pl/sql packages and worry about locators     and SCN-related issues to manage the LOB.

Using current technology

    Markus breaks up larger texts into chunks of     about 4K (hmm just like the LOB solution) He     has an index which allows him to collect the     chunks he needs (sounds familiar). The index     can be based on a short numeric column rather     than a global id. He can rebuild the index if he     wants to, and he can locate it where he wishes.     He can fit several small texts into a single block     (could be an advantage). He can address the     data with simple SQL.

Bottom Line:
Markus hasn't told us enough about how his system is currently structured, or the volume and distribution of his text data, the state of his application, any target time-scales or the programmer resources available. LOBs may be a marketing feature with strategic appeal, but that doesn't make them an automatic benefit.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Daniel A. Morgan wrote in message <3AFE35E9.5C604710_at_exesolutions.com>...


>Markus Fischer wrote:

>

>> Hi,

>>

>> For some Webapplication articels are stored in an Oracle DB. They

 sometimes


>> can exceed the normal varchar column size of 4000 chars. Up today they're

>> just stored in multiple 4000 char-sized columns. So, in PHP, its quiet

 easy


>> to select an article by doing 'SELECT * from Article WHERE id = 100' and

>> then concatenating the columns in the right order.

>>

>> Is there any advantage of using LOB's here ? Using LOB's from a

>> programmatically view needs some overhead to get dealt with.

>>

>> thanks,

>>

>> - Markus

>

>I don't think it is a question of advantages. It is rather a question of

 the


>fact that you have no choice but to use LOBs. There is nothing else larger

 than


>4K bytes that will be in the next version of Oracle AFAIK.

>

>Daniel A. Morgan

>