| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Mythical space savings of IOTs
Most documents on IOTs allude to space savings that can be realized by
eliminating the extra storage required by columns in both the table
and the PK. They hint at more savings that can be achieved by using
index compression. I have not been able to approach the space savings
I was hoping for and was wondering if anyone would care to comment on
their experiences with IOTs being 1 to 3X bigger than the object they
were meant to replace.
I've used IOTs in the past on small, PK-driven lookup tables, but never on really big tables.
Background:
In our industry, we have some entities that contain what amounts to
order line items, and order line item details . The details table
(called components) is in billions of rows (terabtyes) at our clients.
The components table is modeled in a "vertical" fashion. Each row is
composed of about 7 fields that make up the unique key, followed by a
text, numerical or date value, followed by a handful of optional,
nullable columns. This allows our application to be flexible as to the
number and type of components allowed for each line item, which in
turn allows us to resell the app within different markets with
different business rules without modification.
On a 1Million row CTAS sample of the components table, the table required about 59MB (from user_segments), and the composite UK required about 50MB (the UK is almost all the rows in the table). There's another global, single column index that takes up another 18MB. So 127MB in all.
Since our largest client used to have an in-house, specific-to-their-company, "flat" component structure that took up much less room (where each component is another column on a single line item row), they are demanding a 60-80% decrease in storage space required by our app.
We've looked at some other improvements that should net us about 27%
savings. But the big win I was hoping for was rebuilding the table as
an IOT so that 1M rows would only take up about 55MB, instead of 127MB
(the 18MB global index would be moot and dropped for the IOT).
After playing with everything I can under the ORGANIZATION INDEX syntax, the best I've been able to do is an IOT that says it is using 88MB. So I've hit 31% savings. Not quite what I was hoping. Doing ANALYZE INDEX...VALIDATE STRUCTURE was key for me in that it I wasn't able to get the IOT under 128MB until I used the OPT_CMPR_COUNT value in index_stats to lower the index key compression. That's when I finally dropped to 88MB. Nothing else I've tried, from PCTFREE, PCTTHRESHOLD, INCLUDING, using /*+ APPEND */ to load vs. not, and PCTFREE and PCTUSED on the empty overflow segment have yielded any further savings. I noticed in index_stats, that my IOT is only using 47% of the BTREE_SPACE allocated to the PK/IOT segment. Why? I'm not that familiar with block internals, so feel free to school me. If there were a way to make the index use most of each index block, I'd be down to 40MB, which is exactly where I want to be.
If it's helpful, here is an altered version of the DDL I'm using in my tests:
CREATE TABLE our_component_table
(
line_id NUMBER NOT NULL, component_id NUMBER NOT NULL, point_id NUMBER NOT NULL, counterpoint_id NUMBER, start_date DATE NOT NULL, end_date DATE NOT NULL, seldom_used_val VARCHAR2(100), num_val NUMBER, txt_val VARCHAR2(30), dt_val DATE, optional_col_1 NUMBER, -- NULL at most clients optional_col_2 VARCHAR2(100), -- NULL at most clients parent_point_id NUMBER, -- NULL at most clientsoptional_col_3 VARCHAR2(50), -- NULL at most clients optional_col_4 NUMBER, -- NULL at most clients CONSTRAINT nmsc_pk PRIMARY KEY (line_id, component_id, start_date, point_id, counterpoint_id, seldom_used_val, optional_col_1, optional_col_2, optional_col_3, optional_col_4) )
On the real table, it is partitioned by month on start_date, and for this test, I'm using a client-specific version of the large UK to eliminate the unused columns, which is why I can get 40MB of USED_SPACE instead of the expected 50-55MB.
My biggest complaint is that simply switching from a regular table to an IOT should have saved me at least the redundant space used by the table, minus a little overhead for logical ROWID, etc. Instead, with no key compression and no other indexes but the large PK, adding ORGANIZATION INDEX to the same DDL, yielded a segment that was over twice as large as the original heap table OR the original B-TREE UK alone. That's not what I call eliminating the redundant data storage problem.
Thoughts?
-- bill coulam bcoulam_at_gmail.com -- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 08 2005 - 14:59:01 CDT
 |
 |