Bill -
What do the archive logs have in them as far as the number of updates
to the LOB index? I believe the numbers will surprise you. You can see
this using logminer.
Check your CLOB column - whats the ratio of records with XML greater
than 4000 bytes?, If most records are less than that I would not store
the CLOB externally.
Still would look into an XML table rather than a CLOB - or even one or
more VARCHAR2 columns to store the complete record.
As an example - Look into how oracle stores the source for it's
functions and procedures, which stores alot of text data. The synonym
is USER_SOURCE, check out how the lines of text are stored as VARCHAR2
columns. This may give you some options.
Let me know how it goes.
Mike
Bill Ferguson wrote:
Alex,
Thanks for the link. I'm browsing through the presenation now,
and while it shows there's lots of other information written into the
redo, it really isn't all that much.
Mike,
The table itself is pretty simple:
CREATE TABLE "USGS"."SEARCH_TABLE"
( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
"XML_CLOB" CLOB NOT NULL ENABLE,
"UPDATE_DATE" DATE DEFAULT sysdate,
CONSTRAINT "SEARCH_TABLE_PK" PRIMARY KEY ("DEP_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE
STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USGS_LOB_DATA" ENABLE,
CONSTRAINT "SEARCH_TABLE_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "USGS"."DEPOSITS_BASE" ("DEP_ID") ON DELETE CASCADE
ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USGS_LOB_DATA"
LOB ("XML_CLOB") STORE AS (
TABLESPACE "USGS_LOB_DATA" DISABLE STORAGE IN ROW CHUNK 32768
PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Mar 18 2007 - 12:45:55 CDT
