SQLite

Check-in [e4d220a381]
Login

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

Overview
Comment:The one-pass optimization is now working for DELETE on WITHOUT ROWID tables.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | optimize-delete
Files: files | file ages | folders
SHA1: e4d220a381388f900a95d1b656a82f14c837f92e
User & Date: drh 2013-11-16 20:45:01.087
Context
2013-11-16
22:48
Combine the rowid and WITHOUT ROWID paths for DELETE into a single path. (check-in: c4734b881a user: drh tags: optimize-delete)
20:45
The one-pass optimization is now working for DELETE on WITHOUT ROWID tables. (check-in: e4d220a381 user: drh tags: optimize-delete)
20:13
Enhance the DELETE logic so that it can make use of WHERE_ONEPASS_DESIRED for rowid tables. (check-in: 8f479a7275 user: drh tags: optimize-delete)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/delete.c.
︙︙
382
383
384
385
386
387
388
389
390
391

392

393
394
395
396
397
398
399
400
401














402




403
404
405
406
407
408
409

410
411

412
413
414
415
416
417

418
419

420
421
422
423
424
425
426

    assert( pPk!=0 );
    nPk = pPk->nKeyCol;
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    iKey = ++pParse->nMem;
    iEph = pParse->nTab++;

    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 0, 0);

    if( pWInfo==0 ) goto delete_from_cleanup;

    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pPk->aiColumn[i],iPk+i);
    }
    sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
                      sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
    sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, iKey);
    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
    }














    sqlite3WhereEnd(pWInfo);





    /* Open cursors for all indices of the table.
    */
    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
                               &iDataCur, &iIdxCur);

    /* Loop over the primary keys to be deleted. */

    addr = sqlite3VdbeAddOp1(v, OP_Rewind, iEph);
    sqlite3VdbeAddOp2(v, OP_RowKey, iEph, iPk);


    /* Delete the row */
    sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
                             iPk, 0, 1, OE_Default, 0);

    /* End of the delete loop */

    sqlite3VdbeAddOp2(v, OP_Next, iEph, addr+1);
    sqlite3VdbeJumpHere(v, addr);


    /* Close the cursors open on the table and its indexes. */
    assert( iDataCur>=iIdxCur );
    for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      sqlite3VdbeAddOp1(v, OP_Close, iIdxCur+i);
    }
  }else{








|

|
>

>



<
<
<



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

>
>
>
>







>
|
|
>



|


>
|
|
>








382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397



398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447

    assert( pPk!=0 );
    nPk = pPk->nKeyCol;
    iPk = pParse->nMem+1;
    pParse->nMem += nPk;
    iKey = ++pParse->nMem;
    iEph = pParse->nTab++;

    addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, iEph, nPk);
    sqlite3VdbeSetP4KeyInfo(pParse, pPk);
    pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, 0, 0, 
                               WHERE_ONEPASS_DESIRED, iTabCur+1);
    if( pWInfo==0 ) goto delete_from_cleanup;
    okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
    for(i=0; i<nPk; i++){
      sqlite3ExprCodeGetColumnOfTable(v, pTab, iTabCur, pPk->aiColumn[i],iPk+i);
    }



    if( db->flags & SQLITE_CountRows ){
      sqlite3VdbeAddOp2(v, OP_AddImm, memCnt, 1);
    }
    if( okOnePass ){
      aToOpen = sqlite3DbMallocRaw(db, nIdx+2);
      if( aToOpen==0 ) goto delete_from_cleanup;
      memset(aToOpen, 1, nIdx+1);
      aToOpen[nIdx+1] = 0;
      if( aiCurOnePass[0]>=0 ) aToOpen[aiCurOnePass[0]-iTabCur] = 0;
      if( aiCurOnePass[1]>=0 ) aToOpen[aiCurOnePass[1]-iTabCur] = 0;
      sqlite3VdbeChangeToNoop(v, addr);
      addr = sqlite3VdbeAddOp0(v, OP_Goto);
    }else{
      sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk, nPk, iKey,
                        sqlite3IndexAffinityStr(v, pPk), P4_TRANSIENT);
      sqlite3VdbeAddOp2(v, OP_IdxInsert, iEph, iKey);
    }
    sqlite3WhereEnd(pWInfo);
    if( okOnePass ){
      sqlite3VdbeAddOp0(v, OP_Halt);
      sqlite3VdbeJumpHere(v, addr);
    }

    /* Open cursors for all indices of the table.
    */
    sqlite3OpenTableAndIndices(pParse, pTab, OP_OpenWrite, iTabCur, aToOpen,
                               &iDataCur, &iIdxCur);

    /* Loop over the primary keys to be deleted. */
    if( !okOnePass ){
      addr = sqlite3VdbeAddOp1(v, OP_Rewind, iEph);
      sqlite3VdbeAddOp2(v, OP_RowKey, iEph, iPk);
    }

    /* Delete the row */
    sqlite3GenerateRowDelete(pParse, pTab, pTrigger, iDataCur, iIdxCur,
                             iPk, nPk*okOnePass, 1, OE_Default, okOnePass);

    /* End of the delete loop */
    if( !okOnePass ){
      sqlite3VdbeAddOp2(v, OP_Next, iEph, addr+1);
      sqlite3VdbeJumpHere(v, addr);
    }

    /* Close the cursors open on the table and its indexes. */
    assert( iDataCur>=iIdxCur );
    for(i=0, pIdx=pTab->pIndex; pIdx; i++, pIdx=pIdx->pNext){
      sqlite3VdbeAddOp1(v, OP_Close, iIdxCur+i);
    }
  }else{
︙︙