Skip to Main Content
  • Questions
  • sdo_geometry varrays and basic to securefile migration

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Tom.

Asked: April 07, 2021 - 7:06 am UTC

Last updated: April 14, 2021 - 3:57 am UTC

Version: 19.9.0

Viewed 100+ times

You Asked

Hi Tom,

we're making plans to migrate from basicfile to securefile lob storage (Oracle Database 19c).
The database also contains some spatial layers, in partitioned tables with a local spatial index. These tables/partitions were defined in Oracle 11 without explicitly specifying a storage clause for the sdo_geometry varray elements (sdo_elem_info and sdo_ordinates). As I understand from the Oracle docs ( https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/introduction-to-large-objects.html#GUID-9B66BC56-A427-4C1B-B185-8E004CF4B2F7 ), these varrays are only stored as basicfile LOB's in case their size exceeds approximately 4kB. The varray elements of smaller geometries are stored as raw values inline.

As we want to migrate to securefile lobs (using table redefinition), we have two options for the definition of the sdo_geometry varrays
1. WITH an explicit storage clause:
   VARRAY "SPATIAL_COLUM"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB (CACHE ENABLE STORAGE IN ROW)
   VARRAY "PERC_GEOMETRIE_PERCEEL"."SDO_ORDINATES" STORE AS SECUREFILE LOB (CACHE ENABLE STORAGE IN ROW)
   ENABLE ROW MOVEMENT
   

2. WITHOUT an explicit storoge clause. In this case only the larger geometry varrays will be stored as LOB's using securefiles. The other varrays will be stored inline as
raw values.

My question is what option is advisable in term of spatial query performance?

Thank you.

best regards

and we said...

I don't think it is going make any difference. Notice from the doc link you mentioned:

"If the VARRAY storage clause is not specified, and the declared size of varray data is more than 4000 bytes"


The varrays in spatial are typically declared as "VARRAY(1048576)" which means they can hold more than 4000 bytes, so they'll be stored as LOBs regardless, eg

SQL> select * from user_lobs
  2  where table_name in ('T','T1')
  3  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : SD
SEGMENT_NAME                  : SYS_LOB0000156852C00001$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000156852C00001$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :
==============================
TABLE_NAME                    : T1
COLUMN_NAME                   : SD
SEGMENT_NAME                  : SYS_LOB0000156856C00001$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000156856C00001$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :

PL/SQL procedure successfully completed.



I don't think you'll see any raw values - they'll all be stored as LOBS

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.