Understanding ArcSDE indexes by Oracle Indexes

It is a little strange to understand the ArcSDE index by using Oracle indexes. This article uses Oracle indexes as an example to rebuild a spatial index when it comes to the performance of ArcSDE, compared with the spatial index of ArcSDE for Oracle, if you can understand the oracle index example, you should be very familiar with the index problem of ArcSDE. In the database system, indexing is a very important object. Especially for large data tables, indexing can greatly improve the data retrieval speed. This section describes the principles and usage of indexes. The most typical example of www.2cto.com Indexing in the real world is dictionary retrieval. When using a dictionary, you can use two methods: one is page-by-page lookup to obtain the target, and the other is to retrieve the number of pages of the Target Based on the dictionary's retrieval directory, then, you can directly find the target on this page. There is no doubt that using a search directory (INDEX) to retrieve a target is a more efficient way.
Existing mainstream databases provide the index concept, and Oracle is no exception. Once an index is created on a column in the data table, Oracle creates a new space to store the correspondence between all values in the column and the recorded rowid. When you try to use the index column as the search condition, Oracle uses the index to obtain the corresponding rowid and capture the record. [Python] select * from people where name = 'David '; When Oracle processes this query statement, it performs a full table scan. When 6th records are found, the record meets the search criteria and is included in the result set. However, the search will not stop, because Oracle does not know whether there are still qualified records in the subsequent records. Oracle returns the final result set only when a complete table is searched. However, if an index is created in the name column of the people table in advance, the search order will be completely different. The syntax for creating an index is as follows: [python] create index idx_people_name on people (name) once an index is created, all data in the table is processed in alphabetical order. For example, use every five records as a data block (of course, the actual data block will be much larger ). Shows the data structure after partitioning.
Note: The Oracle index in this article is an index created by a string field, so the string field is classified in alphabetical order. Of course, numbers and dates can also be used, the same applies to ArcSDE for Oracle. The spatial index is a grid. Compared with this example, it is nothing more than sorting by letter, five or five, arcGIS contains related elements in the grid range. When you execute the search statement select * from people where name = 'David ';, Oracle only searches for the first data block because the database knows that, the second database is post-M data. After David is searched, if the value of the name column in the next record is not David, Oracle will stop searching because all values in the column are sorted alphabetically, all the "David" locations must be adjacent. Once "David" is found, Oracle immediately obtains its corresponding rowid and quickly locates the Record Based on the rowid. For the following SQL statements, the effect of indexes is clearer. [Python] select * from people where name = 'Michael '; because the condition to be searched is that the value of the name column is "Michael, the second data block is directly redirected to the second data block for search, because the second data block starts with M. In addition, when Michael's next record is "Richard", the search will be stopped and a set of results will be returned. Note: To query ArcSDE data in oracle, perform the following steps:
1) First, compare the grid and query range to find all the grids within the query range. 2) Find all the elements in these grids. Www.2cto.com 3) Compare the outer envelope rectangle of these elements with the query range to find all the elements that are within the query range and that are at the intersection of the query range. It is basically analogous. When an index is applied to a data table with a large amount of data, the query speed is greatly improved. To demonstrate this situation, first create a large data table. View dba_objects to obtain basic information about all objects in the database [python] createtable test_objects as select * from dba_objects; view the execution plan of PL/SQLDeveloper: [python] select * from test_objects where object_name = 'people' to create an index: [python] createindex idx_test_object on test_objects (object_name); view the execution plan again. Note that indexes can be used only when an index column is included in a search condition. [Python] select * from test_objects where owner = 'Scott '1. the impact of adding data on indexes take the table people as an example. Once an index is created on it and data is inserted into it, the database reorganizes the index. Insert into people (ID, NAME, STATUS) values (11, 'zoe', 'ac'); in the preceding SQL statement, insert user information named "Zoey" to the table people. In addition to the normal insert operation overhead, the database also needs to add index items for the record, and the existing data blocks of the index cannot meet the storage requirements. Therefore, the database will allocate a new storage space for it ,:
2. The impact of data modification on the index shows that "James" is added with the delete tag, and "Winne" is added to the last data block ". Oracle adopts this policy to minimize the overhead of database operations. But this also results in a waste of space, because the index item "James" still occupies the index space. The new index cannot be inserted into the first data block. 3. The impact of data deletion on the index the impact of data deletion on the index is relatively simple, because after the data is deleted, the corresponding index item is marked with a delete mark, indicating that the index item is unavailable. For example, you can use the following SQL statement to delete a record. Delete from people where name = 'Robert 'the index item is changed:

The index structure at this time shows that the delete operation is the smallest index overhead in the DML operation. However, it is inevitable that the terrain will waste storage space. Note: The Impact of the Oracle addition, deletion, and modification operations on the index is similar to that of the ArcSDE addition, deletion, and modification operations on the index. Therefore, frequent editing operations are not only performed on tables, but also related index information. As shown above, we can see that the greatest benefit of the index is for read-only data (not absolute), so we can get the following conclusion: 1. A table with a small amount of data should not use a table with an index of a small amount of data. First, you must create an index and search for data blocks before you perform actual data search. Therefore, for a table with a small amount of data, full table search is usually faster than indexing. 2. indexes are not suitable for data tables that frequently use DML operations such as insert, modify, and delete. Frequent insert, modify, and delete operations on tables can reduce database performance. Each action may trigger two or more times the cost to complete the index operation. Note: The same applies to ArcSDE. If your data is frequently edited, you can delete the spatial index, so that the edited data will not consume resources to maintain the index object, this will also improve the efficiency. However, the above statements are about flexible indexing and re-implementation. This is why esri engineers will simply say that the index can be re-built every time they feel slow. In fact, this sentence has gone through so many complicated processes, but isn't the software like this? Despite the complexity of the principle, formula, and so on, it's a simple operation to expose it to users! Understanding this knowledge helps users who are interested or who are more focused on performance optimization! You can create a spatial index using the following method: for grid indexes, you can click Add on the "Index" tab in the element class attribute dialog box of ArcCatalog. See set spatial indexes. Use sdelayer to manage command utilities. See ArcSDE management command reference. Use SQL. See create a spatial index for a table that contains the ST_Geometry column. Use ArcSDE C or Java application programming interface (API ). See the geographical database resource center. Use the load_only_io and normal_io operations of the sdelayer command to switch the element class from the load-only I/O mode to the normal I/O mode. Every time the element class is switched from LOAD_ONLY_IO mode to NORMAL_IO mode, ArcSDE automatically deletes and re-creates all spatial indexes created by ArcSDE. Www.2cto.com website-other information about the request does not mean that index creation can speed up the query, so that all fields can be indexed. Creating an index also has a certain price, the following is only for the creation of attribute indexes: it takes time to create and maintain indexes. This time increases with the increase of data volume. Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger. When adding, deleting, and modifying data in a table, indexes must be maintained dynamically, which reduces the Data Maintenance speed. Similarly, www.2cto.com should not create indexes for some columns. In general, these columns that should not be indexed have the following characteristics: For those columns that are rarely used or referenced in queries, they should not be indexed. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements. Indexes should not be added for columns with few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed. Indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small. When the modification performance is much higher than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index. Source http://blog.csdn.net/linghe301/article/details/7987715