Closed dynamobi-build closed 12 years ago
[author="jvs", created="Fri, 20 Oct 2006 01:47:37 -0500 (GMT-05:00)"] Running this on latest lu/dev code, the EXPLAIN PLAN above only takes 0.5 seconds. Attaching output.
[author="jvs", created="Fri, 20 Oct 2006 01:49:54 -0500 (GMT-05:00)"]
However, I have noticed that for really huge plans (much bigger than this one), sqlline takes a long long time to print them because of some buffering bug in its code (not Farrago). They come out really slowly, line by line.
[author="rchen", created="Fri, 20 Oct 2006 11:02:02 -0500 (GMT-05:00)"]
My fault, the original SQL has 20 constants so they are turned into a valuesRel.
The following with 19 constants takes a long time to optimize.
explain plan for
select * from emps1
where empno not in (20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20);
[author="jvs", created="Fri, 20 Oct 2006 12:13:02 -0500 (GMT-05:00)"]
You were right; it's my code for sarg evaluation. I hit Control-\ to dump the threads and below is what it was doing. When I ported the Broadbase code, I was lazy and made it find the complement in a very inefficient fashion. The Broadbase code was efficient (it called a virtual method on the base class to request the complement).
at java.io.PrintWriter.
at java.io.PrintWriter.
at org.eigenbase.rex.RexLiteral.toJavaString(RexLiteral.java:243)
at org.eigenbase.rex.RexLiteral.
at org.eigenbase.rex.RexBuilder.makeLiteral(RexBuilder.java:406)
at org.eigenbase.rex.RexBuilder.makeExactLiteral(RexBuilder.java:449)
at org.eigenbase.sarg.SargEndpoint.convertNumber(SargEndpoint.java:327)
at org.eigenbase.sarg.SargEndpoint.convertToTargetType(SargEndpoint.java:178)
at org.eigenbase.sarg.SargEndpoint.setFinite(SargEndpoint.java:156)
at org.eigenbase.sarg.SargMutableEndpoint.setFinite(SargMutableEndpoint.java:66)
at org.eigenbase.sarg.SargIntervalBase.setLower(SargIntervalBase.java:156)
at org.eigenbase.sarg.SargSetExpr.evaluateIntervalComplement(SargSetExpr.java:473)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:391)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:248)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at org.eigenbase.sarg.SargSetExpr.evaluateIntersection(SargSetExpr.java:245)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:126)
at org.eigenbase.sarg.SargSetExpr.evaluateComplement(SargSetExpr.java:418)
at org.eigenbase.sarg.SargSetExpr.evaluate(SargSetExpr.java:128)
at net.sf.farrago.query.FennelRelUtil.evaluateSargExpr(FennelRelUtil.java:524)
at com.lucidera.lcs.LcsIndexAccessRule.buildColumnFilters(LcsIndexAccessRule.java:303)
at com.lucidera.lcs.LcsIndexAccessRule.considerIndex(LcsIndexAccessRule.java:236)
at com.lucidera.lcs.LcsIndexAccessRule.onMatch(LcsIndexAccessRule.java:101)
at org.eigenbase.relopt.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:247)
at org.eigenbase.relopt.hep.HepPlanner.applyRule(HepPlanner.java:449)
at org.eigenbase.relopt.hep.HepPlanner.applyRules(HepPlanner.java:340)
at org.eigenbase.relopt.hep.HepPlanner.executeInstruction(HepPlanner.java:242)
at org.eigenbase.relopt.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:87)
at org.eigenbase.relopt.hep.HepPlanner.executeProgram(HepPlanner.java:158)
at org.eigenbase.relopt.hep.HepPlanner.findBestExp(HepPlanner.java:144)
at org.eigenbase.oj.stmt.OJPreparingStmt.optimize(OJPreparingStmt.java:341)
at net.sf.farrago.query.FarragoPreparingStmt.optimize(FarragoPreparingStmt.java:743)
at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:293)
at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:283)
at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:754)
at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:705)
at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:939)
at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:866)- locked <0xaa21b1b8> (a java.lang.Integer)
at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)
at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)
at sqlline.SqlLine$Commands.execute(Unknown Source)
at sqlline.SqlLine$Commands.sql(Unknown Source)
at sqlline.SqlLine.dispatch(Unknown Source)
at sqlline.SqlLine.begin(Unknown Source)
at sqlline.SqlLine.mainWithInputRedirection(Unknown Source)
at sqlline.SqlLine.main(Unknown Source)
[author="rchen", created="Tue, 24 Oct 2006 10:47:24 -0500 (GMT-05:00)"] The null value issue is fixed in eigenbase change 7982. Leave the bug open for the performance issue of sarg analysis.
[author="jvs", created="Tue, 24 Oct 2006 11:18:29 -0500 (GMT-05:00)"]
I'll fix the sarg analysis.
[author="jvs", created="Sat, 11 Nov 2006 11:07:27 -0500 (GMT-05:00)"]
Sarg analysis performance fixed in eigenchange 8131.
[reporter="rchen", created="Thu, 19 Oct 2006 15:34:05 -0500 (GMT-05:00)"] set schema 'sales';
alter system set "calcVirtualMachine" = 'CALCVM_JAVA';
alter session implementation set jar sys_boot.sys_boot.luciddb_plugin;
create table emps1 (empno int);
insert into emps1 values (10);
select empno from emps where empno not in (10, null);
will not return correct result due to the three value logic required is not possible if IN/NOT IN is translated to logical operations directly. Using Join will work. Though there is a bug when creating ValueRel from a list containing null values.
Another related problem is the time(>25 sec) to optimize something like this:
explain plan for
select * from emps1
where empno not in (20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20);