Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Only eliminate inner loops of a JOIN if they are the RHS of a LEFT JOIN and if they give no more than a single result. This appears to give correct answers in all cases. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | omit-join-table-opt |
| Files: | files | file ages | folders |
| SHA1: | d7a25cc79794817504ca1a4262008a68 |
| User & Date: | drh 2013-06-21 02:15:48 |
Context
|
2013-06-22
| ||
| 15:44 | Add the ability to disable the omit-join-table optimization for testing purposes. Closed-Leaf check-in: d929df9b user: drh tags: omit-join-table-opt | |
|
2013-06-21
| ||
| 02:15 | Only eliminate inner loops of a JOIN if they are the RHS of a LEFT JOIN and if they give no more than a single result. This appears to give correct answers in all cases. check-in: d7a25cc7 user: drh tags: omit-join-table-opt | |
| 02:05 | Attempt to disable inner loops of a join that do not generate output. This does not work, since the inner loops might run zero times and thus inhibit all output. Needs to be enhanced to work only for LEFT JOINs or when we know that the inner loop will always run at least once. check-in: ca839723 user: drh tags: omit-join-table-opt | |
Changes
Changes to src/where.c.
5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 5811 5812 5813 |
#endif
/* Attempt to omit tables from the join that do not effect the result */
if( pResultSet!=0 && pWInfo->nLevel>=2 ){
Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
while( pWInfo->nLevel>=2 ){
pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
if( ((wctrlFlags & WHERE_WANT_DISTINCT)!=0
|| (pLoop->wsFlags & WHERE_ONEROW)!=0)
&& (tabUsed & pLoop->maskSelf)==0
){
WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
pWInfo->nLevel--;
nTabList--;
}else{
break;
}
}
}
WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
/* If the caller is an UPDATE or DELETE statement that is requesting
** to use a one-pass algorithm, determine if this is appropriate.
|
> | | < > > > | | | < < < |
5790 5791 5792 5793 5794 5795 5796 5797 5798 5799 5800 5801 5802 5803 5804 5805 5806 5807 5808 5809 5810 5811 5812 5813 |
#endif
/* Attempt to omit tables from the join that do not effect the result */
if( pResultSet!=0 && pWInfo->nLevel>=2 ){
Bitmask tabUsed = exprListTableUsage(pMaskSet, pResultSet);
if( pOrderBy ) tabUsed |= exprListTableUsage(pMaskSet, pOrderBy);
while( pWInfo->nLevel>=2 ){
pLoop = pWInfo->a[pWInfo->nLevel-1].pWLoop;
if( (pWInfo->pTabList->a[pLoop->iTab].jointype & JT_LEFT)==0 ) break;
if( (wctrlFlags & WHERE_WANT_DISTINCT)==0
&& (pLoop->wsFlags & WHERE_ONEROW)==0
){
break;
}
if( (tabUsed & pLoop->maskSelf)!=0 ) break;
WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
pWInfo->nLevel--;
nTabList--;
}
}
WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
/* If the caller is an UPDATE or DELETE statement that is requesting
** to use a one-pass algorithm, determine if this is appropriate.
|