Hi,
If I understand correctly, H2 doesn't allow indexes on CLOB or BLOB
> columns.
>
Yes.
> If I still understand correctly, column selectivity is used solely to
> calculate which index to use.
>
Yes.
The auto-analyze that H2 performs calculates the selectivity for CLOB
> and BLOB columns. This seems of no use.
That's true.
> It seems to me that it can
> also cause a large H2 database to pause for a significant amount of
> time, causing unnecessary memory churn.
>
That would be bad. The auto-analyze feature should only have very little
impact (I would say at most 5 percent in the normal case).
> I've tested out this theory and concluded that auto-analyze is 10 to
> 20 times faster when running on a simple table containing two INT
> column and one CLOB field.
>
That's weird. According to my test case, excluding the LOB columns has
little effect (about 15% for 2 columns, see my patch and test case below).
Please note that calculating the selectivity doesn't (shouldn't) load the
whole LOB column in memory, as it uses the hash value to calculate the
selectivity.
Is this optimisation worth implementing?
>
I guess yes, even if it's only about 15%.
Patch:
### Eclipse Workspace Patch 1.0
#P h2
Index: src/main/org/h2/command/ddl/Analyze.java
===================================================================
--- src/main/org/h2/command/ddl/Analyze.java (revision 3980)
+++ src/main/org/h2/command/ddl/Analyze.java (working copy)
@@ -16,6 +16,7 @@
import org.h2.table.RegularTable;
import org.h2.table.Table;
import org.h2.util.StatementBuilder;
+import org.h2.value.Value;
/**
* This class represents the statement
@@ -82,7 +83,14 @@
Column[] columns = table.getColumns();
for (Column col : columns) {
buff.appendExceptFirst(", ");
- buff.append("SELECTIVITY(").append(col.getSQL()).append(')');
+ int type = col.getType();
+ if (type == Value.BLOB || type == Value.CLOB) {
+ // can not index LOB columns, so calculating
+ // the selectivity is not required
+ buff.append("100");
+ } else {
+
buff.append("SELECTIVITY(").append(col.getSQL()).append(')');
+ }
}
buff.append(" FROM ").append(table.getSQL());
if (sample > 0) {
Test case:
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
import org.h2.util.Profiler;
public class TestSimpleDb {
public static void main(String... args)
throws Exception {
Class.forName("org.h2.Driver");
DeleteDbFiles.execute("~/temp", null, true);
final String url = "jdbc:h2:~/temp/test";
Connection conn;
conn = DriverManager.getConnection(url);
Statement stat = conn.createStatement();
stat.execute(
"create table test(id int primary key, data clob) " +
"as select x, space(100000) " +
"from system_range(1, 100)");
Profiler prof = new Profiler();
prof.interval = 1;
// 3101 old
// 2688 LOB columns excluded
// prof.startCollecting();
long start = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
stat.execute("analyze");
long t = System.currentTimeMillis() - start;
if (t > 1000) {
System.out.println("i: " + i);
break;
}
}
// System.out.println(prof.getTop(5));
conn.close();
}
}
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
