You are viewing a plain text version of this content. The canonical link for it is here.
Posted to slide-user@jakarta.apache.org by Gao Jun <ga...@yahoo.com> on 2004/11/19 08:25:05 UTC
using CLOB for properties table's property_value column
Hi,
Has anybody tried to using Oracle's CLOB for properties table's property_value column,
instead of the original VARCHAR2(255)? We are trying to do this right now. Although
it can pass the basic test cases, we are still not sure whether this is a reliable solution.
Could anyone share your experience with us? Thanks.
regards,
Jun
---------------------------------
Do you Yahoo!?
Meet the all-new My Yahoo! � Try it today!
RE: using CLOB for properties table's property_value column
Posted by Nick Longinow <ni...@vanhooseassociates.com>.
I'm using the CLOB datatype and have had no problems.
Nick
-----Original Message-----
From: Roman Novak [mailto:r.novak@iqsoft.at]
Sent: Friday, November 19, 2004 7:08 AM
To: Slide Users Mailing List
Subject: Re: using CLOB for properties table's property_value column
Yep, it is in the bug description (
http://www.mail-archive.com/slide-dev@jakarta.apache.org/msg11754.html )
"SQL-Query:
select max(length(property_value)) from properties t
--> 368
Ann: File was versioned 8 times. "
The general role "users" grows probably faster. I have 19 users assigned to
that role and the field content is already 980 chars. (if you use e.g.
Projector for role and user management, users get the role "users" assigned
upon creation).
19 users isn't that many.
Roman
Am Fr 19.11.2004 12:46, Luke Noel-Storr <lu...@dsl.pipex.com> schrieb:
> Roman Novak wrote:
>
> >Hi Jun,
> >
> >- The db-schema scripts don't define an index on that column.
> >- Possibly a change from varchar2(255) to varchar2(4000) will be
sufficient for you
> >
> >
> >
> I think the main problem with this field is with the version history,
> even with it set to varchar2(4000) I'd be worried about it 'running out'
> of space too soon. Though personally I feel this is more a design
> problem wth the version history being stored in this field rather, and
> any change to the database column type id just a work around.
>
> Is there any work under way to change the way version history is stored?
>
>
> Cheers
>
> Luke.
> -----
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
Re: using CLOB for properties table's property_value column
Posted by Roman Novak <r....@iqsoft.at>.
Yep, it is in the bug description ( http://www.mail-archive.com/slide-dev@jakarta.apache.org/msg11754.html )
"SQL-Query:
select max(length(property_value)) from properties t
--> 368
Ann: File was versioned 8 times. "
The general role "users" grows probably faster. I have 19 users assigned to that role and the field content is already 980 chars. (if you use e.g. Projector for role and user management, users get the role "users" assigned upon creation).
19 users isn't that many.
Roman
Am Fr 19.11.2004 12:46, Luke Noel-Storr <lu...@dsl.pipex.com> schrieb:
> Roman Novak wrote:
>
> >Hi Jun,
> >
> >- The db-schema scripts don't define an index on that column.
> >- Possibly a change from varchar2(255) to varchar2(4000) will be sufficient for you
> >
> >
> >
> I think the main problem with this field is with the version history,
> even with it set to varchar2(4000) I'd be worried about it 'running out'
> of space too soon. Though personally I feel this is more a design
> problem wth the version history being stored in this field rather, and
> any change to the database column type id just a work around.
>
> Is there any work under way to change the way version history is stored?
>
>
> Cheers
>
> Luke.
> -----
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
Re: using CLOB for properties table's property_value column
Posted by Luke Noel-Storr <lu...@dsl.pipex.com>.
Roman Novak wrote:
>Hi Jun,
>
>- The db-schema scripts don't define an index on that column.
>- Possibly a change from varchar2(255) to varchar2(4000) will be sufficient for you
>
>
>
I think the main problem with this field is with the version history,
even with it set to varchar2(4000) I'd be worried about it 'running out'
of space too soon. Though personally I feel this is more a design
problem wth the version history being stored in this field rather, and
any change to the database column type id just a work around.
Is there any work under way to change the way version history is stored?
Cheers
Luke.
-----
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
Re: using CLOB for properties table's property_value column
Posted by Roman Novak <r....@iqsoft.at>.
Hi Jun,
- The db-schema scripts don't define an index on that column.
- Possibly a change from varchar2(255) to varchar2(4000) will be sufficient for you
(see latest changes also here -> http://www.mail-archive.com/slide-dev@jakarta.apache.org/msg12926.html)
- Versioning and other things (possibly many users assigned to a role) create large entries in the column property_value of the table PROPERTIES (another URL: http://www.mail-archive.com/slide-dev@jakarta.apache.org/msg11754.html)
If i remember right there were discussions about storing group-sets values in a more "relational" manner. So i am using the varchar2(4000) setting atm and not CLOB. The mentioned (theoretical) indexing issues was the only thing that came to mind immediately. I didn't make thorough tests running with a CLOB, so i can share experiences there.
I am curious myself, if using a CLOB for that column would be the final solution for this issue.
Roman
Am Fr 19.11.2004 10:02, Gao Jun <ga...@yahoo.com> schrieb:
> Hi Roman,
>
> Thanks for your answer. I'd like to know is index on this column necessary?
> If I don't need to worry search for now, is there any other known issues related to
> the difference of Oracle's varchar2 and CLOB type?
>
> regards,
>
> Jun
>
> Roman Novak <r....@iqsoft.at> wrote:
> Hi,
>
> I thought about that too somewhen. Indexing the CLOB will be an issue. Formally you need the "Context Option", which basically will mean that it will be a "Domain Index" suitable for fulltext search.
>
> If you try to index that type of column you get an ORA-02327 (cannot create index ...).
>
> The workaround was to alter the table and extend the column to varchar2(4000) (this apparently didn't find the way into beta 2. I remember a hotfix a while ago).
>
> Good luck
>
> Am Fr 19.11.2004 08:25, Gao Jun schrieb:
>
> > Hi,
> >
> > Has anybody tried to using Oracle's CLOB for properties table's property_value column,
> > instead of the original VARCHAR2(255)? We are trying to do this right now. Although
> > it can pass the basic test cases, we are still not sure whether this is a reliable solution.
> > Could anyone share your experience with us? Thanks.
> >
> > regards,
> >
> > Jun
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Meet the all-new My Yahoo! ?Try it today!
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: slide-user-help@jakarta.apache.org
>
>
>
> ---------------------------------
> Do you Yahoo!?
> The all-new My Yahoo! Get yours free!
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
Re: using CLOB for properties table's property_value column
Posted by Gao Jun <ga...@yahoo.com>.
Hi Roman,
Thanks for your answer. I'd like to know is index on this column necessary?
If I don't need to worry search for now, is there any other known issues related to
the difference of Oracle's varchar2 and CLOB type?
regards,
Jun
Roman Novak <r....@iqsoft.at> wrote:
Hi,
I thought about that too somewhen. Indexing the CLOB will be an issue. Formally you need the "Context Option", which basically will mean that it will be a "Domain Index" suitable for fulltext search.
If you try to index that type of column you get an ORA-02327 (cannot create index ...).
The workaround was to alter the table and extend the column to varchar2(4000) (this apparently didn't find the way into beta 2. I remember a hotfix a while ago).
Good luck
Am Fr 19.11.2004 08:25, Gao Jun schrieb:
> Hi,
>
> Has anybody tried to using Oracle's CLOB for properties table's property_value column,
> instead of the original VARCHAR2(255)? We are trying to do this right now. Although
> it can pass the basic test cases, we are still not sure whether this is a reliable solution.
> Could anyone share your experience with us? Thanks.
>
> regards,
>
> Jun
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Meet the all-new My Yahoo! ?Try it today!
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org
---------------------------------
Do you Yahoo!?
The all-new My Yahoo! � Get yours free!
Re: using CLOB for properties table's property_value column
Posted by Roman Novak <r....@iqsoft.at>.
Hi,
I thought about that too somewhen. Indexing the CLOB will be an issue. Formally you need the "Context Option", which basically will mean that it will be a "Domain Index" suitable for fulltext search.
If you try to index that type of column you get an ORA-02327 (cannot create index ...).
The workaround was to alter the table and extend the column to varchar2(4000) (this apparently didn't find the way into beta 2. I remember a hotfix a while ago).
Good luck
Am Fr 19.11.2004 08:25, Gao Jun <ga...@yahoo.com> schrieb:
> Hi,
>
> Has anybody tried to using Oracle's CLOB for properties table's property_value column,
> instead of the original VARCHAR2(255)? We are trying to do this right now. Although
> it can pass the basic test cases, we are still not sure whether this is a reliable solution.
> Could anyone share your experience with us? Thanks.
>
> regards,
>
> Jun
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Meet the all-new My Yahoo! Try it today!
---------------------------------------------------------------------
To unsubscribe, e-mail: slide-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-user-help@jakarta.apache.org