Home » SQL & PL/SQL » SQL & PL/SQL » Performace of CLOBs in table.
Performace of CLOBs in table. [message #268138] Mon, 17 September 2007 14:05 Go to next message
callsatya801
Messages: 3
Registered: November 2005
Location: India
Junior Member

Hi All,

We need to define about 30 CLOB objects in a table.

What is the impact on performance ?
Is it a good idea to have all the clob objects in one table or to divide these clob objects into multiple tables ?


Pl. advice.

Thanks
Satya
Re: Performace of CLOBs in table. [message #268148 is a reply to message #268138] Mon, 17 September 2007 14:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We need to define about 30 CLOB objects in a table.

You THINK you need them.

Quote:
What is the impact on performance ?

Not very good.

Quote:
Is it a good idea to have all the clob objects in one table or to divide these clob objects into multiple tables ?

Explain us your business need and all appropriate information.

Regards
Michel
Re: Performace of CLOBs in table. [message #268155 is a reply to message #268148] Mon, 17 September 2007 16:33 Go to previous messageGo to next message
callsatya801
Messages: 3
Registered: November 2005
Location: India
Junior Member

Our Business requirement is some thing like this:

We Need to explain the status of the project Every month.
Need to explain the deviation in of each parameter on the project eg: deviation in expenditure, deviation in profitability and so on.
We have about 30 parameters where we need the explanation.

Each explanation can be as big as 5-6 Word Document pages.

So we would like to capture these information in CLOB objects.
Re: Performace of CLOBs in table. [message #268206 is a reply to message #268138] Tue, 18 September 2007 00:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I can say that distribute tables in different tablespaces.
Re: Performace of CLOBs in table. [message #268240 is a reply to message #268155] Tue, 18 September 2007 00:54 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
callsatya801 wrote on Mon, 17 September 2007 16:33
Our Business requirement is some thing like this:

We Need to explain the status of the project Every month.
Need to explain the deviation in of each parameter on the project eg: deviation in expenditure, deviation in profitability and so on.
We have about 30 parameters where we need the explanation.



You can normalized your tables as per some criteria(s) and then use separate tablespaces for each tables.
Re: Performace of CLOBs in table. [message #268249 is a reply to message #268155] Tue, 18 September 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should say, use only one CLOB and add a column for parameter type.

Regards
Michel
Re: Performace of CLOBs in table. [message #268424 is a reply to message #268249] Tue, 18 September 2007 09:47 Go to previous messageGo to next message
callsatya801
Messages: 3
Registered: November 2005
Location: India
Junior Member

For ever Page to show I need to query for multiple records rather than one record.

Can you pl. expalain me how is it different from having multiple clobs in a row. Whatz the gain I get if I have clob in multiple rows?

Thanks,
Satya

Re: Performace of CLOBs in table. [message #268433 is a reply to message #268424] Tue, 18 September 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your application and what you will do.
In any case, it will be easier to manage.

Regards
Michel
Re: Performace of CLOBs in table. [message #268451 is a reply to message #268433] Tue, 18 September 2007 12:55 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
If you have very clearly defined requirements were only a fixed number of CLOB columns will be used and usually you will populate all/most of them, then having "reapeating" columns is acceptable (example address_line1, address_line2, or days-of-week is always 7 etc). The advantage of having repeating columns is that you can easily enforce rules like the number of address lines that can be entered. If you normalize the structure and have a child table rather than repeating columns, it's difficult to ensure that the right number of child records per parent is maintained.

CLOBS are usually stored out-of-line, so they usually aren't stored in the same segment anyway. The reason is for performance. If you only want to search through the non-clob columns in the table, Oracle doesn't need to unnecessarily retrieve the CLOBS just because they are part of the same record.
Previous Topic: Format date?
Next Topic: Help w/CLOB error
Goto Forum:
  


Current Time: Sun Dec 04 10:56:02 CST 2016

Total time taken to generate the page: 0.20116 seconds