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
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-devel
