SecureFiles - A small digression into the lowlands of the Oracle database Recently I was asked by a customer, what recommendations exist with regard to the DB Block Size
for storing raster data as
Large Objects (LOBs) are.
In order to answer this question, the knowledge of the core functionality of the Oracle database is essential. Therefore, I would like to share some thoughts on this with other interested parties.
Since the release of version 11.1, there is a new data type for storing large objects in the database,
SecureFiles called.
a very good introduction to the use of
SecureFiles are
this German-Tip in the Oracle DBA community. Moreover, I would like
also refer to this article
in
Database Journal . If you have worked through the first two chapters, there are some references to include parameters relevant DB tables or properties.
is important to know first of all that up to version 11 for
LOBs not so much the
DB Block Size is important - here you really need, just take care that they do not Moeche greater as the
LOB itself - but the size of the so-called
CHUNK (German: Brocken, lumps, large pieces). Since
11.1 and
SecureFiles has
DB Block Size minimal impact on performance and the
CHUNK size must be specified explicitly. The DB will undertake to determine to the best of "knowledge and belief," the optimal
CHUNK size. Which varies between the
DB Block Size (minimum) and 64 MB (maximum). Our DB developers call the "Super Chunk".
If you want to still take effect, there remains the possibility of the minimum by setting a corresponding
DB Block Size per tablespace regulate.
That has also been the cue for another Note: In any case, I recommend a separate tablespace
for LOBs provide
, possibly several for different types of LOBs
or different data sets of this.
Then there when creating tablespaces
the important concept of
extents, which are physically contiguous memory areas in which the database objects are stored. The closer the data in contiguous disk sectors, the lower course, the access times when reading.
a recommendation at this point is therefore to specify the size of the extents
thus to minimize the number of these during a charging process must be created.
whom it generally depends on speed of loading, should consider to load the data with
NOLOGGING to as logging
ReDo reduce information.
After loading can be restored in
LOGGING and move ahead and apply even an online backup.
ALTER TABLE my_lob_tab NOLOGGING; ALTER TABLE MODIFY my_lob_tab (lob_col CLOB) LOB (lob_col) STORE AS SECURE FILE (NOCACHE NOLOGGING - NOCACHE must be assembled with NOLOGGING); ... ALTER TABLE MODIFY LOB my_lob_tab (lob_col) (CACHE); ALTER TABLE my_lob_tab LOGGING;
Further information can be found in the online documentation
SecureFiles and Large Objects Developer's Guide .
What else is important?
database objects are stored as so-called segments. There are several different thereof, eg, tables, index or LOB segments.
latter are generated by default if the size of the LOBs
> 4000 bytes. This is called
Outline storage. The opposite
inline stores the entire row including
LOB in a table segment and is the default for LOBs \u0026lt;4000 bytes. Will you only
Outline store, for example, LOBs, and the other (relational) data in different segments (possibly due to different tablespaces
) to share, so you can specify in the
Storage clause of the table definition
DISABLE STORAGE IN ROW .
But beware: This one has at the time of applying the table already do, because it does not change nachträlglich.
And what's all this now to do with actually SDO_GEOMETRY , SDO_GEORASTER , do the Oracle object types for vector and raster data? Both this and also SDO_PC (for Point Clouds ) can use SecureFiles . In a raster data table that looks like this, eg:
CREATE TABLE city_images_rdt OF SDO_RASTER (PRIMARY KEY (rasterID, pyramid level, tape block number, rowBlockNumber, column block number)) lobseg TABLESPACE raster_im_tbs LOB (grid block) STORE AS SECURE FILE (NOCACHE NOLOGGING) /
When vector data both bases (SDO_ORDINATE_ARRAY) and SDO_ELEM_INFO in VARRAY stored. And they can be specified with the STORE AS clause Storage SECURE FILE. The syntax is an example like this: CREATE TABLE
my_geom_tab (Feature_name VARCHAR2 (4000), geometry SDO_GEOMETRY) geometry.SDO_ORDINATES VARRAY STORE AS SECURE FILE lob geom_ord geometry.SDO_ELEM_INFO VARRAY STORE AS SECURE FILE lob geom_elem /
Before this blog entry is now too long and no one has more fun to him until the end of to read I'm keeping the issue of compressing the stored as
SecureFiles bases on a new entry.
