Home » RDBMS Server » Server Administration » BLOB Storage inline/out of line
BLOB Storage inline/out of line [message #64586] Thu, 10 February 2005 14:47 Go to next message
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 #64594 is a reply to message #64586] Fri, 11 February 2005 05:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>When we export/import the table and during import
>>have moved all the lobs out of line..
forgive my ignorance.
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?
Re: BLOB Storage inline/out of line [message #64598 is a reply to message #64594] Fri, 11 February 2005 14:41 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous message
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.
Previous Topic: PLS-00905: object DBADM.TABLE1@otherDB is invalid
Next Topic: LOG_BUFFER
Goto Forum:
  


Current Time: Fri Sep 27 04:27:27 CDT 2024