[xwiki-devs] Oracle Database Index
This page describes how to create indexes for a MySQL 4 or 5 database http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration#... and I can create indexes on Oracle 10 with the exception of: create index xwl_value on xwikilargestrings (xwl_value(50)); create index xwd_parent on xwikidoc (xwd_parent(50)); create index xwd_class_xml on xwikidoc (xwd_class_xml(20)); which are clob and varchar columns. xwd_class_xml CLOB(4000) xwd_parent VARCHAR(1533) xwl_value CLOB(4000) Does anyone know how indexes should be created for these columns in Oracle? Thanks for any help Glenn Everitt -- View this message in context: http://www.nabble.com/Oracle-Database-Index-tp16945142p16945142.html Sent from the XWiki- Dev mailing list archive at Nabble.com.
On Apr 28, 2008, at 8:28 PM, Glenn Everitt wrote:
This page describes how to create indexes for a MySQL 4 or 5 database
http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration#...
and I can create indexes on Oracle 10 with the exception of:
create index xwl_value on xwikilargestrings (xwl_value(50)); create index xwd_parent on xwikidoc (xwd_parent(50)); create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
which are clob and varchar columns. xwd_class_xml CLOB(4000) xwd_parent VARCHAR(1533) xwl_value CLOB(4000)
Does anyone know how indexes should be created for these columns in Oracle?
I don't know the answer but it would be nice if you could update the page with Oracle information when you find out :) Thanks -Vincent
That's why I *very dislike Oracle* and I can't understand why people using it, they just don't stick with the standard. I'm not sure what it is in your case, but maybe it's because: - VARCHAR is limited and Oracle invented VARCHAR2 - CHAR and VARCHAR don't accept empty values if NOT NULL constraint is set (in that case the attribut has to be defined as NULL and has to be checked with the Oracle special function NVL() ) - Names of object is limited to 30 chars Other nice things invented by Oracle: - no BOOLEAN - no info tables - no limit => you have to write a procedure! - division of an integer 99'999'999 typ NUMERIC(10) with an integer 1'000 equals a rounded result 100'000 (!) - and more...Oracle is a typical lock-in application...but managers love it anyway.. ;-) Cheers, Squirrel On Mon, Apr 28, 2008 at 1:28 PM, Glenn Everitt <[email protected]> wrote:
This page describes how to create indexes for a MySQL 4 or 5 database
http://platform.xwiki.org/xwiki/bin/view/AdminGuide/Database+Administration#...
and I can create indexes on Oracle 10 with the exception of:
create index xwl_value on xwikilargestrings (xwl_value(50)); create index xwd_parent on xwikidoc (xwd_parent(50)); create index xwd_class_xml on xwikidoc (xwd_class_xml(20));
which are clob and varchar columns. xwd_class_xml CLOB(4000) xwd_parent VARCHAR(1533) xwl_value CLOB(4000)
Does anyone know how indexes should be created for these columns in Oracle?
Thanks for any help Glenn Everitt -- View this message in context: http://www.nabble.com/Oracle-Database-Index-tp16945142p16945142.html Sent from the XWiki- Dev mailing list archive at Nabble.com.
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
From the MySQL page http://dev.mysql.com/doc/refman/5.0/en/create-index.html it says:
BLOB and TEXT columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for non-binary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns. ### So I'm not sure if it is really useful to index these columns. Is there ever a lookup done on object contents? I also found this item: http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_2.shtml which show how to create a table with a LOB (large object either BLOB or CLOB) but it looks like you have to specify a TABLESPACE to hold the BLOB/CLOB index. Does anyone have a sense of whether this is worth the effort to index XWiki CLOB's ? CREATE TABLE test_lobtable ( id NUMBER , xml_file CLOB , image BLOB , log_file BFILE ) LOB (xml_file) STORE AS xml_file_lob_seg ( TABLESPACE lob_data CHUNK 4096 CACHE STORAGE (MINEXTENTS 2) INDEX xml_file_lob_idx ( TABLESPACE lob_index STORAGE (MAXEXTENTS UNLIMITED) ) ) ---snip--- Here is Hibernate reference to using Oracle CLOB : http://www.hibernate.org/56.html but it mentions nothing about indexes. -- View this message in context: http://www.nabble.com/Oracle-Database-Index-tp16945142p16974580.html Sent from the XWiki- Dev mailing list archive at Nabble.com.
participants (3)
-
Glenn Everitt -
Squirrel -
Vincent Massol