Unique index metadata scan triggers expensive operations on Oracle
------------------------------------------------------------------
Key: GEOT-2758
URL:
http://jira.codehaus.org/browse/GEOT-2758 Project: GeoTools
Issue Type: Bug
Components: data jdbc-ng
Affects Versions: 2.5.7
Reporter: Andrea Aime
Assignee: Andrea Aime
Fix For: 2.5.8, 2.6-RC1
The patch that added the scan of the unique indexes in the JDBCDataStore (as a further option from pk) triggers a full equivalent of the postgres "analyze" on the table being scanned.
If the dataset is big the operation lasts for various minutes (3-5 in the FOSS4G benchmarking case).
The thing is due to the last parameter in the metadata request, asking for accurage page information about the index. Something we actually don't need, it should just be turned off (the parameter is actually 'true' -> use approximative index page informations).
The following patch adds a bit of extra logging should some other metadata operation cause long waits and allows the approximate page informations to be used:
{code}
Index: src/main/java/org/geotools/jdbc/JDBCDataStore.java
===================================================================
--- src/main/java/org/geotools/jdbc/JDBCDataStore.java (revisione 34060)
+++ src/main/java/org/geotools/jdbc/JDBCDataStore.java (copia locale)
@@ -30,10 +30,12 @@
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
@@ -84,6 +86,7 @@
import org.opengis.filter.sort.SortBy;
import org.opengis.filter.sort.SortOrder;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
@@ -760,6 +763,8 @@
try {
String tableName = entry.getName().getLocalPart();
DatabaseMetaData metaData = cx.getMetaData();
+ if(LOGGER.isLoggable(Level.FINE))
+ LOGGER.fine("Getting information about primary keys of " + tableName);
ResultSet primaryKey = metaData.getPrimaryKeys(null, databaseSchema,
tableName);
@@ -781,7 +786,9 @@
// causes problems with Oracle, so we skip it
if(!isView(metaData, databaseSchema, tableName)) {
//no primary key, check for a unique index
- ResultSet uniqueIndex = metaData.getIndexInfo(null, databaseSchema, tableName, true, false);
+ if(LOGGER.isLoggable(Level.FINE))
+ LOGGER.fine("Getting information about unique indexes of " + tableName);
+ ResultSet uniqueIndex = metaData.getIndexInfo(null, databaseSchema, tableName, true, true);
try {
pkey = createPrimaryKey(uniqueIndex, metaData, tableName, cx);
}
{code]
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.codehaus.org/secure/Administrators.jspa-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf_______________________________________________
Geotools-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
