Archive for the ‘xml’ Category
Data Modeling Thoughts about XML Databases
Over the last couple of weeks I had the privilege of reviewing the progression of a presentation made by Michael Bowers at the MarkLogic 2008 User Conference. The presentation makes a compelling case that XML databases may have a much larger market share in the near future.
If you’re interested in my reflection on data modeling and a quick example of highly normalized or structured XML, it’s here …
XQuery books, tools, and pending new features
While working through the semantics of XQuery, the lack of an update feature was annoying. Though there’s now a candidate XQuery Update Facility as of last Friday. Also, Priscilla Walmsley’s XQuery book alerted me to some differences in the handling of style sheets between XPath 2.0 (used in XQuery) and XPath 1.0 (used in XSLT). By the way, I’d recommend her book if you’re writing XQuery and also oXygen as an XML editor. You can also find some great XQuery examples she’s posted here.
Cleaning up wasted LOB space
After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.
You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:
TABLE COLUMN Segment Segment Name Name Segment Name TYPE Bytes ----- --------- ------------------------- ---------- --------- ITEM ITEM_BLOB SYS_IL0000074435C00007$$ LOBINDEX 65536 ITEM ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT 2097152 ITEM ITEM_DESC SYS_IL0000074435C00006$$ LOBINDEX 393216 ITEM ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416 |
You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.
ALTER TABLE item ADD (item_temp CLOB); UPDATE item SET item_temp = item_desc; |
When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.
TABLE COLUMN Segment Segment Name Name Segment Name TYPE Bytes ----- --------- ------------------------- ---------- --------- ITEM ITEM_BLOB SYS_IL0000074435C00007$$ LOBINDEX 65536 ITEM ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT 2097152 ITEM ITEM_DESC SYS_IL0000074435C00006$$ LOBINDEX 393216 ITEM ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416 ITEM ITEM_TEMP SYS_IL0000074435C00016$$ LOBINDEX 131072 ITEM ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712 |
You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.
ALTER TABLE item DROP COLUMN item_desc; ALTER TABLE item ADD (item_desc CLOB); UPDATE item SET item_desc = item_temp; |
You can now requery the table and find that you’ve eliminated extraneous space.
TABLE COLUMN Segment Segment Name Name Segment Name TYPE Bytes ----- --------- ------------------------- ---------- --------- ITEM ITEM_BLOB SYS_IL0000074435C00006$$ LOBINDEX 65536 ITEM ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT 2097152 ITEM ITEM_DESC SYS_IL0000074435C00016$$ LOBINDEX 131072 ITEM ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712 ITEM ITEM_TEMP SYS_IL0000074435C00016$$ LOBINDEX 131072 ITEM ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712 |
You drop the temporary column after making the change.
ALTER TABLE item DROP COLUMN item_temp; |
You can now requery the table and find that you’ve eliminated extraneous space.
TABLE COLUMN Segment Segment Name Name Segment Name TYPE Bytes ----- --------- ------------------------- ---------- --------- ITEM ITEM_BLOB SYS_IL0000074435C00006$$ LOBINDEX 65536 ITEM ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT 2097152 ITEM ITEM_DESC SYS_IL0000074435C00016$$ LOBINDEX 131072 ITEM ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT 65011712 |
Subscribe via RSS