SQLite

Changes On Branch materialized-view-reuse
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch materialized-view-reuse Excluding Merge-Ins

This is equivalent to a diff from 4e1df76e to c64fe3a1

2017-05-02
17:54
Reuse the same materialization of a view when that view appears in a query more than once, such as in a self-join. (check-in: 9e35c89d user: drh tags: trunk)
2017-05-01
18:12
Clear the BTCF_ValidNKey flag set if a cursor is moved by sqlite3BtreeInsert(). Fix for [f68dc596c4]. (check-in: a6727eef user: dan tags: trunk)
17:04
Futher bug fixes to the function that determines when a materialized view can be reused. (Closed-Leaf check-in: c64fe3a1 user: drh tags: materialized-view-reuse)
16:37
Minor bug fixes and performance enhancement. (check-in: b2aae559 user: drh tags: materialized-view-reuse)
15:15
Initial implementation of an optimization that attempts to reuse the same materialization of a view on a self-join of the view. (check-in: 478c34b9 user: drh tags: materialized-view-reuse)
14:25
Update this branch with latest trunk changes. (check-in: 11f4761c user: dan tags: schemalint)
2017-04-29
19:29
Add a single testcase() macro to the subquery processing logic. (check-in: 4e1df76e user: drh tags: trunk)
18:02
Improvements to opcode documentation in the bytecode engine. No changes to code. (check-in: e54c9f8d user: drh tags: trunk)

Changes to src/select.c.

4874
4875
4876
4877
4878
4879
4880


























4881
4882
4883
4884
4885
4886
4887
        pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
    );
  }
}
#else
# define explainSimpleCount(a,b,c)
#endif



























/*
** Generate code for the SELECT statement given in the p argument.  
**
** The results are returned according to the SelectDest structure.
** See comments in sqliteInt.h for further information.
**







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







4874
4875
4876
4877
4878
4879
4880
4881
4882
4883
4884
4885
4886
4887
4888
4889
4890
4891
4892
4893
4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904
4905
4906
4907
4908
4909
4910
4911
4912
4913
        pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
    );
  }
}
#else
# define explainSimpleCount(a,b,c)
#endif

/*
** Check to see if the pThis entry of pTabList is a self-join of a prior view.
** If it is, then return the SrcList_item for the prior view.  If it is not,
** then return 0.
*/
static struct SrcList_item *isSelfJoinView(
  SrcList *pTabList,           /* Search for self-joins in this FROM clause */
  struct SrcList_item *pThis   /* Search for prior reference to this subquery */
){
  struct SrcList_item *pItem;
  for(pItem = pTabList->a; pItem<pThis; pItem++){
    if( pItem->pSelect==0 ) continue;
    if( pItem->fg.viaCoroutine ) continue;
    if( pItem->zName==0 ) continue;
    if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue;
    if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
    if( sqlite3ExprCompare(pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) ){
      /* The view was modified by some other optimization such as
      ** pushDownWhereTerms() */
      continue;
    }
    return pItem;
  }
  return 0;
}

/*
** Generate code for the SELECT statement given in the p argument.  
**
** The results are returned according to the SelectDest structure.
** See comments in sqliteInt.h for further information.
**
5109
5110
5111
5112
5113
5114
5115


5116
5117
5118
5119
5120
5121
5122
5123
5124
5125
5126
5127
5128




5129
5130
5131

5132
5133
5134
5135
5136
5137
5138
      ** the content of this subquery.  pItem->addrFillSub will point
      ** to the address of the generated subroutine.  pItem->regReturn
      ** is a register allocated to hold the subroutine return address
      */
      int topAddr;
      int onceAddr = 0;
      int retAddr;


      assert( pItem->addrFillSub==0 );
      pItem->regReturn = ++pParse->nMem;
      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
      pItem->addrFillSub = topAddr+1;
      if( pItem->fg.isCorrelated==0 ){
        /* If the subquery is not correlated and if we are not inside of
        ** a trigger, then we only need to compute the value of the subquery
        ** once. */
        onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
        VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }else{
        VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }




      sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
      explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
      sqlite3Select(pParse, pSub, &dest);

      pItem->pTab->nRowLogEst = pSub->nSelectRow;
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
      VdbeComment((v, "end %s", pItem->pTab->zName));
      sqlite3VdbeChangeP1(v, topAddr, retAddr);
      sqlite3ClearTempRegCache(pParse);
    }







>
>













>
>
>
>
|
|
|
>







5135
5136
5137
5138
5139
5140
5141
5142
5143
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
5157
5158
5159
5160
5161
5162
5163
5164
5165
5166
5167
5168
5169
5170
5171
      ** the content of this subquery.  pItem->addrFillSub will point
      ** to the address of the generated subroutine.  pItem->regReturn
      ** is a register allocated to hold the subroutine return address
      */
      int topAddr;
      int onceAddr = 0;
      int retAddr;
      struct SrcList_item *pPrior;

      assert( pItem->addrFillSub==0 );
      pItem->regReturn = ++pParse->nMem;
      topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
      pItem->addrFillSub = topAddr+1;
      if( pItem->fg.isCorrelated==0 ){
        /* If the subquery is not correlated and if we are not inside of
        ** a trigger, then we only need to compute the value of the subquery
        ** once. */
        onceAddr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
        VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }else{
        VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
      }
      pPrior = isSelfJoinView(pTabList, pItem);
      if( pPrior ){
        sqlite3VdbeAddOp2(v, OP_OpenDup, pItem->iCursor, pPrior->iCursor);
      }else{
        sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
        explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
        sqlite3Select(pParse, pSub, &dest);
      }
      pItem->pTab->nRowLogEst = pSub->nSelectRow;
      if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
      retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
      VdbeComment((v, "end %s", pItem->pTab->zName));
      sqlite3VdbeChangeP1(v, topAddr, retAddr);
      sqlite3ClearTempRegCache(pParse);
    }

Changes to src/vdbe.c.

3535
3536
3537
3538
3539
3540
3541




























3542
3543
3544
3545
3546
3547
3548
  testcase( pOp->p2 & OPFLAG_SEEKEQ );
#endif
  sqlite3BtreeCursorHintFlags(pCur->uc.pCursor,
                               (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
  if( rc ) goto abort_due_to_error;
  break;
}





























/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.
** The cursor is always opened read/write even if 
** the main database is read-only.  The ephemeral







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







3535
3536
3537
3538
3539
3540
3541
3542
3543
3544
3545
3546
3547
3548
3549
3550
3551
3552
3553
3554
3555
3556
3557
3558
3559
3560
3561
3562
3563
3564
3565
3566
3567
3568
3569
3570
3571
3572
3573
3574
3575
3576
  testcase( pOp->p2 & OPFLAG_SEEKEQ );
#endif
  sqlite3BtreeCursorHintFlags(pCur->uc.pCursor,
                               (pOp->p5 & (OPFLAG_BULKCSR|OPFLAG_SEEKEQ)));
  if( rc ) goto abort_due_to_error;
  break;
}

/* Opcode: OpenDup P1 P2 * * *
**
** Open a new cursor P1 that points to the same ephemeral table as
** cursor P2.  The P2 cursor must have been opened by a prior OP_OpenEphemeral
** opcode.  Only ephemeral cursors may be duplicated.
**
** Duplicate ephemeral cursors are used for self-joins of materialized views.
*/
case OP_OpenDup: {
  VdbeCursor *pOrig;    /* The original cursor to be duplicated */
  VdbeCursor *pCx;      /* The new cursor */

  pOrig = p->apCsr[pOp->p2];
  assert( pOrig->pBtx!=0 );  /* Only ephemeral cursors can be duplicated */

  pCx = allocateCursor(p, pOp->p1, pOrig->nField, -1, CURTYPE_BTREE);
  if( pCx==0 ) goto no_mem;
  pCx->nullRow = 1;
  pCx->isEphemeral = 1;
  pCx->pKeyInfo = pOrig->pKeyInfo;
  pCx->isTable = pOrig->isTable;
  rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR,
                          pCx->pKeyInfo, pCx->uc.pCursor);
  if( rc ) goto abort_due_to_error;
  break;
}


/* Opcode: OpenEphemeral P1 P2 * P4 P5
** Synopsis: nColumn=P2
**
** Open a new cursor P1 to a transient table.
** The cursor is always opened read/write even if 
** the main database is read-only.  The ephemeral

Changes to src/vdbeaux.c.

2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
  assert( pCx->pBtx==0 || pCx->eCurType==CURTYPE_BTREE );
  switch( pCx->eCurType ){
    case CURTYPE_SORTER: {
      sqlite3VdbeSorterClose(p->db, pCx);
      break;
    }
    case CURTYPE_BTREE: {
      if( pCx->pBtx ){
        sqlite3BtreeClose(pCx->pBtx);
        /* The pCx->pCursor will be close automatically, if it exists, by
        ** the call above. */
      }else{
        assert( pCx->uc.pCursor!=0 );
        sqlite3BtreeCloseCursor(pCx->uc.pCursor);
      }
      break;







|
|







2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
  assert( pCx->pBtx==0 || pCx->eCurType==CURTYPE_BTREE );
  switch( pCx->eCurType ){
    case CURTYPE_SORTER: {
      sqlite3VdbeSorterClose(p->db, pCx);
      break;
    }
    case CURTYPE_BTREE: {
      if( pCx->isEphemeral ){
        if( pCx->pBtx ) sqlite3BtreeClose(pCx->pBtx);
        /* The pCx->pCursor will be close automatically, if it exists, by
        ** the call above. */
      }else{
        assert( pCx->uc.pCursor!=0 );
        sqlite3BtreeCloseCursor(pCx->uc.pCursor);
      }
      break;