mysql索引与数据分开存储_空间数据与索引分开存储管理

在我们使用ArcSDE对矢量数据和栅数据进行存储的时候，有必要将相关的表及索引分开存储。也就是说当用户访问一张表，首先会访问表的索引，然后根据索引来快速定位访问表的内容，那么表与表索引就会占据大量的磁盘I/O资源，从而导致访问表的效率下降。所以我们

在我们使用ArcSDE对矢量数据和栅格数据进行存储的时候，有必要将相关的表及索引分开存储。也就是说当用户访问一张表，首先会访问表的索引，然后根据索引来快速定位访问表的内容，那么表与表索引就会占据大量的磁盘I/O资源，从而导致访问表的效率下降。所以我们应该将表与索引分配在不同的表空间，将不同的表空间存储到不同的磁盘上，这样访问表时，就可以使用不同的磁盘I/O，提高访问效率

步骤一：创建存储矢量、栅格数据及对应的索引存储表空间

create tablespace GISDATA datafile 'D:\GISDATA.dbf' size 1000m autoallocate;

create tablespace GISINDEX datafile 'D:\GISINDEX.dbf' size 1000m autoallocate;

create tablespace RASTERDATA datafile 'D:\RASTERDATA.dbf' size 1000m autoallocate;

create tablespace RASTERINDEX datafile 'D:\RASTERINDEX.dbf' size 1000m autoallocate;

步骤二：设置自增长

-- autoextend

declare

cursor v_file is

select file_id from dba_data_files where autoextensible = 'NO' ;

begin

for v_i in v_file loop

execute immediate 'alter database datafile ' || v_i.file_id ||

' autoextend on next 1024M maxsize unlimited';

end loop;

end;

步骤三：创建sde服务

sdeservice -o create -p sde -d "ORACLE,NJ" -n -i esri_sde -H "C:\Program Files\ArcGIS\ArcSDE\ora11gexe"(具体操作见本博客专题介绍)

步骤四：修改dbtune.sde配置文件

该文件位于sde安装目录下(默认为Program Files\ArcGIS\ArcSDE\ora11gexe\etc下，修改前注意备份原文件)

新增下列参数配置

##DATABASEGIS

GEOMETRY_STORAGE "ST_GEOMETRY"

ATTRIBUTE_BINARY "BLOB"

RASTER_STORAGE "BLOB"

UI_TEXT "User Interface text description for DATABASEGIS keyword"

COMMENT "Any general comment for DATABASEGIS keyword"

S_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA"

S_INDEX_ALL "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX"

B_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA "

B_INDEX_ROWID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX"

B_INDEX_USER "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

B_INDEX_RASTER "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX "

B_INDEX_SHAPE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

B_INDEX_TO_DATE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX"

B_INDEX_XML "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

A_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA "

A_INDEX_ROWID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

A_INDEX_SHAPE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

A_INDEX_STATEID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

A_INDEX_USER "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

A_INDEX_XML "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

D_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA"

D_INDEX_STATE_ROWID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

D_INDEX_DELETED_AT "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

RAS_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE RASTERDATA"

RAS_INDEX_ID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX "

BND_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE RASTERDATA "

BND_INDEX_COMPOSITE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX "

BND_INDEX_ID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX"

AUX_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE RASTERDATA"

AUX_INDEX_COMPOSITE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX "

BLK_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE RASTERDATA"

BLK_INDEX_COMPOSITE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE RASTERINDEX "

XML_DOC_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA"

XML_DOC_INDEX "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX"

XML_DOC_LOB_STORAGE "NOCACHE NOLOGGING CHUNK 4K PCTVERSION 5 DISABLE STORAGE IN ROW"

XML_DOC_MODE "COMPRESSED"

XML_DOC_UNCOMPRESSED_TYPE "CLOB"

XML_DOC_VAL_LOB_STORAGE "NOCACHE NOLOGGING CHUNK 4K PCTVERSION 5 DISABLE STORAGE IN ROW"

XML_IDX_STORAGE "PCTFREE 10 PCTUSED 40 INITRANS 4 TABLESPACE GISDATA"

XML_IDX_INDEX_DOUBLE "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

XML_IDX_INDEX_ID "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

XML_IDX_INDEX_PK "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX"

XML_IDX_INDEX_STRING "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

XML_IDX_INDEX_TAG "PCTFREE 10 INITRANS 4 NOLOGGING TABLESPACE GISINDEX "

XML_IDX_INDEX_TEXT ""

XML_IDX_TEXT_TAG_STORAGE ""

XML_IDX_TEXT_UPDATE_MEMORY ""

XML_IDX_TEXT_UPDATE_METHOD "NONE"

END

修改完成后保存

步骤五：导入修改好的dbtune.sde配置文件

Sdedbtune -o import -f D:\666.txt -i esri_sde -u sde -p sde -N

步骤六：导入完成后，我们可以利用plsql查看导入结果

select * from DBTUNE t

Arccatalog连接数据库，将数据复制到数据库时，会看到config.keywZ喎?/kf/ware/vc/" target="_blank" class="keylink">vcmS1xCYjMjA1NDA7xKzIz86qztLDx9DC1Pa1xERBVEFCQVNFR0lTJiMyMDU0MDujqNXise3Kvs7Sw8e1xMr9vt29q7vhuPm+3cDg0M231rHwtOa0orW91eK49iYjMjA1NDA7z8K1xLj3uPa21NOmse2/1bzko6y+zcrHz8jHsL2ousO1xMvEuPax7b/VvOSjqTxicj4KPC9wPgo8cD48L3A+CjxpbWcgc3JjPQ=="file:///C:\Users\ningjun\AppData\Roaming\Tencent\Users\420741948\QQ\WinTemp\RichOle\)TD9%1_QUAU7VWSLA$_X284.jpg" alt="\">

导入完成后，可以通过plsql看这四个表空间数据是不是正常对应存储。

发布php中文网，转载请注明出处，感谢您的尊重！