学习oracle的域索引1

oracle中的域索引

oracle中的索引主要包括以下几种索引：
1.B-Tree index
2.Bitmap index
3.Function Based Index
4.Domain index
等等.

域索引实际为用户自定义索引，域索引主要对存储在数据库中的媒体，图像数据进行索引，这些数据在oracle中基本上
以BLOB类型存储，不同的应用存储格式也不同，oracle不可能提供某一种现成的算法对这些数据进行索引，为了能够对
这些类型数据快速访问，oracle提供了现成的接口函数，用户可以针对自己的数据格式实现这些接口函数，以达到对这些
数据的快速访问。

看一个建立在符合类型字段上的一个域索引：

SQL> select * from (select index_name from dba_indexes where wner='SDE' and ind
ex_type='DOMAIN') where rownum<=1;

INDEX_NAME
------------------------------
A445_IX1

然后看看A445_IX1的定义


SQL> set long 10000
SQL> select dbms_metadata.get_ddl('INDEX','A445_IX1','SDE') from dual;

DBMS_METADATA.GET_DDL('INDEX','A445_IX1','SDE')
--------------------------------------------------------------------------------


  CREATE INDEX "SDE"."A445_IX1" ON "SDE"."SUBSTATION" ("SHAPE")
   INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=360.000000000000,

0.000000000000,0.000000000000 ST_SRID=2 ST_COMMIT_ROWS=10000 PCTFREE 0 INITRANS
4')

从定义上可以看出该索引是定义在SDE.SUBSTATION表上的SHAPE字段的，看看shape字段是如何定义


SQL> desc sde.substation;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OBJECTID                                  NOT NULL NUMBER(38)
 NAME                                               NVARCHAR2(64)
 IS_USER                                            NVARCHAR2(4)
 VOLTAGE_LEVEL                                      NVARCHAR2(32)
 SYS_ID                                    NOT NULL NUMBER(10)
 STATUS                                             NVARCHAR2(32)
 VINDICATOR_ID                                      NVARCHAR2(20)
 VINDICATOR                                         NVARCHAR2(30)
 CREATOR_ID                                         NVARCHAR2(20)
 CREATOR                                            NVARCHAR2(30)
 CREATE_DATE                                        DATE
 ZONE_ID                                            NVARCHAR2(50)
 PWMIS_CODE                                         NUMBER(10)
 DESCRIPTION                                        NVARCHAR2(100)
 SHAPE                                              SDE.ST_GEOMETRY

SHAPE字段为SDE.ST_GEOMETRY类型，该类型为符合类型，然后可以看看ST_GEOMETRY是如何定义的

SQL> desc sde.st_geometry;
 sde.st_geometry is NOT FINAL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ENTITY                                             NUMBER(38)
 NUMPTS                                             NUMBER(38)
 MINX                                               FLOAT(64)
 MINY                                               FLOAT(64)
 MAXX                                               FLOAT(64)
 MAXY                                               FLOAT(64)
 MINZ                                               FLOAT(64)
 MAXZ                                               FLOAT(64)
 MINM                                               FLOAT(64)
 MAXM                                               FLOAT(64)
 AREA                                               FLOAT(64)
 LEN                                                FLOAT(64)
 SRID                                               NUMBER(38)
 POINTS                                             BLOB

METHOD
------
 FINAL CONSTRUCTOR FUNCTION ST_GEOMETRY RETURNS SELF AS RESULT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 GEOM_STR                       CLOB                    IN
 SRID                           NUMBER                  IN

METHOD
------
 MEMBER FUNCTION ST_AREA RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_LEN RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_LENGTH RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_ENTITY RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_NUMPTS RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MINX RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MAXX RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MINY RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MAXY RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MINM RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MAXM RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MINZ RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_MAXZ RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_SRID RETURNS NUMBER

METHOD
------
 STATIC FUNCTION GET_RELEASE RETURNS NUMBER

索引的类型为INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" ，查看一下ST_SPATIAL_INDEX的类型

SQL> select owner,object_type from dba_objects where object_name='ST_SPATIAL_IND
EX';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SDE                            INDEXTYPE

类型为INDEXTYPE,具体INDEXTYPE是个什么类型的东东，查看一下其定义

SQL> select dbms_metadata.get_ddl('INDEXTYPE','ST_SPATIAL_INDEX','SDE') from dua
l;

DBMS_METADATA.GET_DDL('INDEXTYPE','ST_SPATIAL_INDEX','SDE')
--------------------------------------------------------------------------------


  CREATE OR REPLACE INDEXTYPE "SDE"."ST_SPATIAL_INDEX" FOR
         "SDE"."ST_CONTAINS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_CROSSES" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_ENVINTERSECTS" ("SDE"."ST_GEOMETRY", NUMBER, NUMBER, NUMBER,
NUMBER)
,
         "SDE"."ST_ENVINTERSECTS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_EQUALS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_INTERSECTS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_ORDERINGEQUALS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_OVERLAPS" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),

DBMS_METADATA.GET_DDL('INDEXTYPE','ST_SPATIAL_INDEX','SDE')
--------------------------------------------------------------------------------

         "SDE"."ST_RELATE" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY", VARCHAR2),

         "SDE"."ST_TOUCHES" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY"),
         "SDE"."ST_WITHIN" ("SDE"."ST_GEOMETRY", "SDE"."ST_GEOMETRY")
         USING "SDE"."ST_DOMAIN_METHODS"
         WITH LOCAL RANGE PARTITION
对ST_SPATIAL_INDEX的具体的含义，下次了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8052309/viewspace-625296/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8052309/viewspace-625296/