Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Make sure that all automatic indices are covering indices. Otherwise, the table and index might be used together in a query but the table could get out of sync with the automatic index through out-of-band changes. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | experimental |
Files: | files | file ages | folders |
SHA1: |
2364313142668b3d0ad144447b486270 |
User & Date: | drh 2010-04-07 14:59:45.000 |
References
2011-06-23
| ||
16:17 | • New ticket [91e2e8ba6f] Automatic indices cause undesirable type conversions. (artifact: 43bb4571df user: drh) | |
Context
2010-04-07
| ||
16:54 | Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add the automatic_index PRAGMA to turn it on and off. (check-in: a811a47fbe user: drh tags: experimental) | |
14:59 | Make sure that all automatic indices are covering indices. Otherwise, the table and index might be used together in a query but the table could get out of sync with the automatic index through out-of-band changes. (check-in: 2364313142 user: drh tags: experimental) | |
14:33 | Enhance comments on the SrcList object definition to better explain the operation of the SrcList.a[].colUsed field. No changes to code. (check-in: c0f67ea131 user: drh tags: experimental) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 | pCost->plan.wsFlags = flags; pCost->plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } /* ** If the query plan for pSrc specified in pCost is a full table scan | > > > > > > > > > > > > > > > > > > > | | 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 | pCost->plan.wsFlags = flags; pCost->plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } /* ** Return TRUE if the WHERE clause term pTerm is of a form where it ** could be used with an index to access pSrc, assuming an appropriate ** index existed. */ static int termCanDriveIndex( WhereTerm *pTerm, /* WHERE clause term to check */ struct SrcList_item *pSrc, /* Table we are trying to access */ Bitmask notReady /* Tables in outer loops of the join */ ){ char aff; if( pTerm->leftCursor!=pSrc->iCursor ) return 0; if( pTerm->eOperator!=WO_EQ ) return 0; if( (pTerm->prereqRight & notReady)!=0 ) return 0; aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity; if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0; return 1; } /* ** If the query plan for pSrc specified in pCost is a full table scan ** and indexing is allows (if there is no NOT INDEXED clause) and it ** possible to construct a transient index that would perform better ** than a full table scan even when the cost of constructing the index ** is taken into account, then alter the query plan to use the ** transient index. */ static void bestTransientIndex( Parse *pParse, /* The parsing context */ |
︙ | ︙ | |||
1678 1679 1680 1681 1682 1683 1684 | return; } /* Search for any equality comparison term */ pTable = pSrc->pTab; pWCEnd = &pWC->a[pWC->nTerm]; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ | < | < < < < | 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 | return; } /* Search for any equality comparison term */ pTable = pSrc->pTab; pWCEnd = &pWC->a[pWC->nTerm]; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ WHERETRACE(("auto-index reduces cost from %.2f to %.2f\n", pCost->rCost, costTempIdx)); pCost->rCost = costTempIdx; pCost->nRow = logN + 1; pCost->plan.wsFlags = WHERE_TEMP_INDEX; pCost->used = pTerm->prereqRight; break; |
︙ | ︙ | |||
1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 | int regIsInit; /* Register set by initialization */ int addrInit; /* Address of the initialization bypass jump */ Table *pTable; /* The table being indexed */ KeyInfo *pKeyinfo; /* Key information for the index */ int addrTop; /* Top of the index fill loop */ int regRecord; /* Register holding an index record */ int n; /* Column counter */ CollSeq *pColl; /* Collating sequence to on a column */ /* Generate code to skip over the creation and initialization of the ** transient index on 2nd and subsequent iterations of the loop. */ v = pParse->pVdbe; assert( v!=0 ); regIsInit = ++pParse->nMem; addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit); sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit); | > > > > | > > < | < < | < > > > > > > > > > > > > > > > > > > | < | < < < < < | | < > > > > > > > > > > > > > > > > > | | 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 | int regIsInit; /* Register set by initialization */ int addrInit; /* Address of the initialization bypass jump */ Table *pTable; /* The table being indexed */ KeyInfo *pKeyinfo; /* Key information for the index */ int addrTop; /* Top of the index fill loop */ int regRecord; /* Register holding an index record */ int n; /* Column counter */ int i; /* Loop counter */ int mxBitCol; /* Maximum column in pSrc->colUsed */ CollSeq *pColl; /* Collating sequence to on a column */ Bitmask idxCols; /* Bitmap of columns used for indexing */ Bitmask extraCols; /* Bitmap of additional columns */ /* Generate code to skip over the creation and initialization of the ** transient index on 2nd and subsequent iterations of the loop. */ v = pParse->pVdbe; assert( v!=0 ); regIsInit = ++pParse->nMem; addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit); sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit); /* Count the number of columns that will be added to the index ** and used to match WHERE clause constraints */ nColumn = 0; pTable = pSrc->pTab; pWCEnd = &pWC->a[pWC->nTerm]; idxCols = 0; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ int iCol = pTerm->u.leftColumn; if( iCol<BMS && iCol>=0 ) idxCols |= 1<<iCol; nColumn++; } } assert( nColumn>0 ); pLevel->plan.nEq = nColumn; /* Count the number of additional columns needed to create a ** covering index. A "covering index" is an index that contains all ** columns that are needed by the query. With a covering index, the ** original table never needs to be accessed. Automatic indices must ** be a covering index because the index will not be updated if the ** original table changes and the index and table cannot both be used ** if they go out of sync. */ extraCols = pSrc->colUsed & ~idxCols; mxBitCol = (pTable->nCol >= BMS-1) ? BMS-1 : pTable->nCol; for(i=0; i<mxBitCol; i++){ if( extraCols & (1<<i) ) nColumn++; } if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){ nColumn += pTable->nCol - BMS + 1; } pLevel->plan.wsFlags |= WHERE_COLUMN_EQ | WHERE_IDX_ONLY | WO_EQ; /* Construct the Index object to describe this index */ nByte = sizeof(Index); nByte += nColumn*sizeof(int); /* Index.aiColumn */ nByte += nColumn*sizeof(char*); /* Index.azColl */ nByte += nColumn; /* Index.aSortOrder */ pIdx = sqlite3DbMallocZero(pParse->db, nByte); if( pIdx==0 ) return; pLevel->plan.u.pIdx = pIdx; pIdx->azColl = (char**)&pIdx[1]; pIdx->aiColumn = (int*)&pIdx->azColl[nColumn]; pIdx->aSortOrder = (u8*)&pIdx->aiColumn[nColumn]; pIdx->zName = "auto-index"; pIdx->nColumn = nColumn; pIdx->pTable = pTable; n = 0; for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){ if( termCanDriveIndex(pTerm, pSrc, notReady) ){ Expr *pX = pTerm->pExpr; pIdx->aiColumn[n] = pTerm->u.leftColumn; pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight); pIdx->azColl[n] = pColl->zName; n++; } } assert( n==pLevel->plan.nEq ); /* Add additional columns needed to make the index into a covering index */ for(i=0; i<mxBitCol; i++){ if( extraCols & (1<<i) ){ pIdx->aiColumn[n] = i; pIdx->azColl[n] = "BINARY"; n++; } } if( pSrc->colUsed & (((Bitmask)1)<<(BMS-1)) ){ for(i=BMS-1; i<pTable->nCol; i++){ pIdx->aiColumn[n] = i; pIdx->azColl[n] = "BINARY"; n++; } } assert( n==nColumn ); /* Create the transient index */ pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx); assert( pLevel->iIdxCur>=0 ); sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0, (char*)pKeyinfo, P4_KEYINFO_HANDOFF); VdbeComment((v, "for %s", pTable->zName)); |
︙ | ︙ | |||
2590 2591 2592 2593 2594 2595 2596 | }else{ bSort = 1; } } /* If currently calculating the cost of using an index (not the IPK ** index), determine if all required column data may be obtained without | | | 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 | }else{ bSort = 1; } } /* If currently calculating the cost of using an index (not the IPK ** index), determine if all required column data may be obtained without ** using the main table (i.e. if the index is a covering ** index for this query). If it is, set the WHERE_IDX_ONLY flag in ** wsFlags. Otherwise, set the bLookup variable to true. */ if( pIdx && wsFlags ){ Bitmask m = pSrc->colUsed; int j; for(j=0; j<pIdx->nColumn; j++){ int x = pIdx->aiColumn[j]; |
︙ | ︙ | |||
4260 4261 4262 4263 4264 4265 4266 | /* Close all of the cursors that were opened by sqlite3WhereBegin. */ assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc ); for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){ struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom]; Table *pTab = pTabItem->pTab; assert( pTab!=0 ); | | > | > | 4304 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 4321 | /* Close all of the cursors that were opened by sqlite3WhereBegin. */ assert( pWInfo->nLevel==1 || pWInfo->nLevel==pTabList->nSrc ); for(i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++){ struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom]; Table *pTab = pTabItem->pTab; assert( pTab!=0 ); if( (pTab->tabFlags & TF_Ephemeral)==0 && pTab->pSelect==0 && (pWInfo->wctrlFlags & WHERE_OMIT_CLOSE)==0 ){ int ws = pLevel->plan.wsFlags; if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){ sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor); } if( (ws & (WHERE_INDEXED|WHERE_TEMP_INDEX)) == WHERE_INDEXED ){ sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur); } |
︙ | ︙ |