BLOB Storage inline/out of line [message #64586] |
Thu, 10 February 2005 14:47 |
Tod
Messages: 6 Registered: June 2002
|
Junior Member |
|
|
we have a 10 Mill rows table with a BLOB column in it
the size of the lob varies: from 1K up ward to a few megabytes, but most are in the 2K-3K range.
So currently, we have ENABLE STORAGE IN ROW.
and want to do DISABLE STORAGE IN ROW b/c
we are starting to do a lot of range scan on the table.
When we export/import the table and during import
have moved all the lobs out of line.. the total space
used during the import bloated 5 times from
a 2GIG tablespace into a 10GIG tablespace??? Why?
The database block size is 8K, running 9.2.0.6 with
auto sgement management in the tablespace
CREATE TABLESPACE "BLOB_DATA" LOGGING
DATAFILE 'D:ORACLEORADATATESTDBBLOB_DATA01.ora' SIZE 2048M
REUSE AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M
SEGMENT SPACE MANAGEMENT AUTO
|
|
|
|
Re: BLOB Storage inline/out of line [message #64598 is a reply to message #64594] |
Fri, 11 February 2005 14:41 |
Tod
Messages: 6 Registered: June 2002
|
Junior Member |
|
|
>>I dont understand the above statement. how did you do >> that?
>>extract ddl from import run it again ( recreate the
>> >> object) and then reload only the data?
1. export data using exp cmd
2. drop all tables
3. create a new LOB tablespace
4. re-create all the tables with the LOB Storage clause
create table FOO (
col1 NUMBER
,col2 BLOB
)
tablespace DATA_TBLSPCE
LOB ( col2 ) STORE AS col2_blob
(
tablespace BLOB_TBLSPCE disable storage in row
chunk 8192 pctversion 10 cache
storage (initial 64K next 64K
minextents 1 maxextents unlimited
pctincrease 0
)
5. import data with ignore=y
|
|
|
Re: BLOB Storage inline/out of line [message #64602 is a reply to message #64598] |
Sat, 12 February 2005 03:12 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
Please try this for one table.
if it works, write a simple script to move all tables.
-- did you try to moving lob columns directly, with a reduced pctversion to the SAME TABLESPACE
-- Move the lob segment to the same tablespace.
-- sample table, with default options.
1 create table FOO (
2 col1 NUMBER,
3* col2 BLOB)
mag@mutation_mutation > /
Table created.
--
-- lets check the ddl
-- the tablespace for data and for lobs is USERS
mag@mutation_mutation > @ddl.ddl
CREATE TABLE "MAG"."FOO"
( "COL1" NUMBER,
"COL2" BLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("COL2") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
;
mag@mutation_mutation > insert into foo values(1,rawtohex('somejunk'));
1 row created.
mag@mutation_mutation > commit;
-- in this example i am moving lobdata to TOOLS tablespace.
-- You can move it to the EXISTING tablespace as before
-- i.e, move to the same tablespace!
-- also do specify your storage changes ( reduced pctversion and disble storage in row)
1* alter table foo move lob (col2) store as foolob ( tablespace tools disable storage in row pctversion 0)
mag@mutation_mutation > /
Table altered.
--
-- lets check it!.
--
mag@mutation_mutation > @ddl.ddl
CREATE TABLE "MAG"."FOO"
( "COL1" NUMBER,
"COL2" BLOB
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("COL2") STORE AS "FOOLOB"(
TABLESPACE "TOOLS" DISABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 0
NOCACHE
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
;
--
-- little thingy to get the ddl.
--
mag@mutation_mutation > get ddl.ddl
1 set long 500000
2 set linesize 1000
3 SET HEAD off
4* SELECT upper(DBMS_METADATA.GET_DDL('TABLE',D.TABLE_NAME))||';' FROM user_tables D where table_name='FOO';
mag@mutation_mutation >
|
|
|
Re: BLOB Storage inline/out of line [message #64603 is a reply to message #64602] |
Sat, 12 February 2005 08:09 |
Tod
Messages: 6 Registered: June 2002
|
Junior Member |
|
|
Mahesh
Thanks for attempting to explain but I believe moving
the lob using an alter stateme works similar to the export/import technique. We used exp/imp to also compact
and reorg the data
As far as using lower pctversion, i've even tried pctversion 0 (b/c our LOBs are write once read many and never updated. No changes here.
I think i finally figured out and any experts out tehre please comment:
when u are storing 10 byte BLOB inline, it is stored as roughly (10 bytes + some overhead) in the row.
But if you move it out of the row, lob are stored as chunk, even 10 bytes will require 1 chunk. and each chunk is equal to your BLOCK_SIZE. On my win2K machine, the db block_size was created with 8K size
which would explain why
a dtabase with mostly 2-3K BLOB bloat 4-5 times when
it's moved out of row.
|
|
|