36 Weeks Bowl Very Dark

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.