Re: BLOB inline or out-of-line storage

From: Ryan January <rjanuary_at_gmail.com>
Date: Thu, 18 Feb 2016 16:08:45 -0600
Message-Id: <7AE5679D-D800-40CD-BA33-69C5F22D0535_at_gmail.com>

Do you store large blobs? Do you ever have unindexed table access requiring full scans? How often does this occur and how quickly do you need a result?

Depending on your hardware and access patterns you may be very disappointed by performance of inline storage.

> On Feb 18, 2016, at 4:03 PM, Chen Zhou <oracle.unknowns_at_gmail.com> wrote:
>
> Hi, Everyone,
> When I was testing moving BLOBs from BasicFile to SecureFile, I found out currently the BLOBs were stored out of line (DISABLE STORAGE IN ROW). It seems to take up large space.
>
> "SELECT SUM(BYTES) FROM DBA_extents WHERE SEGMENT_NAME ='<BLOB NAME>' " returns 353GB.
>
> "select sum(dbms_lob.getlength('<BLOB COLUMN NAME>')) from <TABLE NAME>" returns less than 2G!!!
>
> Now the people who designed these tables are long gone, I really don't have anyone to turn to ask for the thoughts behind the storage choice. I am considering using dbms_redefinition to convert the BLOBs to SecureFile, as well as enable storage in row.
>
> Do you see any reason that I might regret the conversion to inline storage someday?
>
> Thank you,
> Chen

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 18 2016 - 23:08:45 CET