Affects Version/s: 2.2.0, 2.2.1, 2.2.2, 2.2.3, 2.3.0, 2.3.1, 2.3.2, 2.4.0, 2.4.1, 2.5.0
Fix Version/s: None
Component/s: Database service (Pre-K1/2.6)
This relates to
REQ-103, where we can't use the Site browser tool due to performance problems, and other performance issues. In particular, it is very difficult to search and do anything with the properties currently buried in xml fileds in certain tables. A conversion to use the clob type would help, as well as moving some fields to their own column (e.g., the term field, which is used very frequently).
On Thu, Aug 10, 2006 at 02:49:25PM -0400, Drew Zhu wrote:
> As I suggested before, we should switch from LONG to CLOB for the
> columns in the databases because Oracle started this suggestion from 8i
> and a lot of Oracle features do not support LONG. As far as I know, even
> MySql supports CLOB datatype. Two most recent events confirm me we
> should convert from LONG to CLOB.
> 1. There some block fragmentations in production database and I want to
> use Oracle 10g's new feature, shrink space to reclaim space and do
> defragmentation because the fragmentations cause IOs and waits, and
> therefore, slower the database performance. I had some successes.
> However, when a table contains LONG column, I cannot do it. The
> following is an example of the error (we have more than 10 errors like
> SQL> alter table "CTOOLS_USER"."CONTENT_RESOURCE" shrink space
> ERROR at line 1:
> ORA-10662: Segment has long columns
> When checking Oracle support for related documentation, I found the
> "it is a documented restriction that you cannot shrink tables (segments)
> that contain the obsolete long data type. You will have to resort to
> use of the old exp/truncate/imp with ignore=y option."
> That would be huge task which can be done easily by using Oracle 10g's
> "shrink space" method. When we need do that maintenance often, it would
> make a big difference.
> 2. John asked me to delete some unwanted assignments from
> ASSIGNMENT_ASSIGNMENT, which has a LONG datatype, in CTOOLS database.
> That would be a piece of cake if there are no LONG columns. However,
> with the LONG datatype, we cannot search any strings unless we use
> Oracle text, which we currently are not using. Even with Oracle text
> search, we need to a lot of extra work, such as enabling the Oracle text
> feature, build the TEXT indexes, and building search conditions. I
> eventually came with a "workaround" by building a temporary table and
> converting the LONG to CLOB in that temporary table and then do the
> search in the temp table and then compare the two tables and then do the
> deleting. If we have a CLOB, the query would be much easier to
> construct and the task would be much easier.
> Converting LONG to CLOB is not a big task and I have done that before.
> All we need is a key Oracle function, TO_LOB, which is already there
> (yes, it will take some time to write the conversion scripts but we can
> to it one by one). To developers, the change is transparent and they
> do not need to make any changes to their codes. All the applications
> would be the same with LONG substituted by CLOB.
> There more reasons to make the switch. Here I just list what I faced by
> examples so far.
> The above are for your reference.