Hi
We are in the process of porting sqlite 3.2.5 onto an Arm7
processor based embedded application with no FloatingPoint support.
We are switching OFF the double variables.( We are doing a typedef of
double to int). We are now facing some problems while
running our ported sqlite. We doubt the function bestIndex where we have
changed the double to int. I am attaching the bestIndex modified for
integers. Please help us whether we have done the right modifications for
switching OFF double. (double typecasted to int).
Please advise.
static double bestIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors that are not available */
ExprList *pOrderBy, /* The order by clause */
Index **ppIndex, /* Make *ppIndex point to the best index */
int *pFlags, /* Put flags describing this choice in
*pFlags */
int *pnEq /* Put the number of == or IN constraints
here */
){
WhereTerm *pTerm;
Index *bestIdx = 0; /* Index that gives the lowest cost */
double lowestCost = 0; /* The cost of using bestIdx */
int bestFlags = 0; /* Flags associated with bestIdx */
int bestNEq = 0; /* Best value for nEq */
int iCur = pSrc->iCursor; /* The cursor of the table to be accessed */
Index *pProbe; /* An index we are evaluating */
int rev; /* True to scan in reverse order */
int flags; /* Flags associated with pProbe */
int nEq; /* Number of == or IN constraints */
double cost; /* Cost of using pProbe */
TRACE(("bestIndex: tbl=%s notReady=%x\n", pSrc->pTab->zName, notReady));
/* Check for a rowid=EXPR or rowid IN (...) constraints
*/
pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
if( pTerm ){
Expr *pExpr;
*ppIndex = 0;
bestFlags = WHERE_ROWID_EQ;
if( pTerm->operator & WO_EQ ){
/* Rowid== is always the best pick. Look no further. Because only
** a single row is generated, output is always in sorted order */
*pFlags = WHERE_ROWID_EQ | WHERE_UNIQUE;
*pnEq = 1;
TRACE(("... best is rowid\n"));
return 0;
}else if( (pExpr = pTerm->pExpr)->pList!=0 ){
/* Rowid IN (LIST): cost is NlogN where N is the number of list
** elements. */
lowestCost = pExpr->pList->nExpr;
lowestCost *= estLog(lowestCost);
}else{
/* Rowid IN (SELECT): cost is NlogN where N is the number of rows
** in the result of the inner select. We have no way to estimate
** that value so make a wild guess. */
lowestCost = 200;
}
TRACE(("... rowid IN cost: %.9g\n", lowestCost));
}
/* Estimate the cost of a table scan. If we do not know how many
** entries are in the table, use 1 million as a guess.
*/
pProbe = pSrc->pTab->pIndex;
cost = pProbe ? pProbe->aiRowEst[0] : 1000000;
TRACE(("... table scan base cost: %.9g\n", cost));
flags = WHERE_ROWID_RANGE;
/* Check for constraints on a range of rowids in a table scan.
*/
pTerm = findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE|WO_GT|WO_GE, 0);
if( pTerm ){
if( findTerm(pWC, iCur, -1, notReady, WO_LT|WO_LE, 0) ){
flags |= WHERE_TOP_LIMIT;
cost = cost * (1/3); /* Guess that rowid<EXPR eliminates two-thirds
or rows */
}
if( findTerm(pWC, iCur, -1, notReady, WO_GT|WO_GE, 0) ){
flags |= WHERE_BTM_LIMIT;
cost = cost * (1/3); /* Guess that rowid>EXPR eliminates two-thirds
of rows */
}
TRACE(("... rowid range reduces cost to %.9g\n", cost));
}else{
flags = 0;
}
/* If the table scan does not satisfy the ORDER BY clause, increase
** the cost by NlogN to cover the expense of sorting. */
if( pOrderBy ){
if( sortableByRowid(iCur, pOrderBy, &rev) ){
flags |= WHERE_ORDERBY|WHERE_ROWID_RANGE;
if( rev ){
flags |= WHERE_REVERSE;
}
}else{
cost += cost*estLog(cost);
TRACE(("... sorting increases cost to %.9g\n", cost));
}
}
if( cost<lowestCost ){
lowestCost = cost;
bestFlags = flags;
}
/* Look at each index.
*/
for(; pProbe; pProbe=pProbe->pNext){
int i; /* Loop counter */
double inMultiplier = 1;
TRACE(("... index %s:\n", pProbe->zName));
/* Count the number of columns in the index that are satisfied
** by x=EXPR constraints or x IN (...) constraints.
*/
flags = 0;
for(i=0; i<pProbe->nColumn; i++){
int j = pProbe->aiColumn[i];
pTerm = findTerm(pWC, iCur, j, notReady, WO_EQ|WO_IN, pProbe);
if( pTerm==0 ) break;
flags |= WHERE_COLUMN_EQ;
if( pTerm->operator & WO_IN ){
Expr *pExpr = pTerm->pExpr;
flags |= WHERE_COLUMN_IN;
if( pExpr->pSelect!=0 ){
inMultiplier *= 100;
}else if( pExpr->pList!=0 ){
inMultiplier *= pExpr->pList->nExpr + 1;
}
}
}
cost = pProbe->aiRowEst[i] * inMultiplier * estLog(inMultiplier);
nEq = i;
if( pProbe->onError!=OE_None && (flags & WHERE_COLUMN_IN)==0
&& nEq==pProbe->nColumn ){
flags |= WHERE_UNIQUE;
}
TRACE(("...... nEq=%d inMult=%.9g cost=%.9g\n", nEq, inMultiplier,
cost));
/* Look for range constraints
*/
if( nEq<pProbe->nColumn ){
int j = pProbe->aiColumn[nEq];
pTerm = findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE|WO_GT|WO_GE,
pProbe);
if( pTerm ){
flags |= WHERE_COLUMN_RANGE;
if( findTerm(pWC, iCur, j, notReady, WO_LT|WO_LE, pProbe) ){
flags |= WHERE_TOP_LIMIT;
cost = cost * (1/3);
}
if( findTerm(pWC, iCur, j, notReady, WO_GT|WO_GE, pProbe) ){
flags |= WHERE_BTM_LIMIT;
cost = cost * (1/3);
}
TRACE(("...... range reduces cost to %.9g\n", cost));
}
}
/* Add the additional cost of sorting if that is a factor.
*/
if( pOrderBy ){
if( (flags & WHERE_COLUMN_IN)==0 &&
isSortingIndex(pParse,pProbe,pSrc->pTab,iCur,pOrderBy,nEq,&rev)
){
if( flags==0 ){
flags = WHERE_COLUMN_RANGE;
}
flags |= WHERE_ORDERBY;
if( rev ){
flags |= WHERE_REVERSE;
}
}else{
cost += cost*estLog(cost);
TRACE(("...... orderby increases cost to %.9g\n", cost));
}
}
/* Check to see if we can get away with using just the index without
** ever reading the table. If that is the case, then halve the
** cost of this index.
*/
if( flags && pSrc->colUsed < (((Bitmask)1)<<(BMS-1)) ){
Bitmask m = pSrc->colUsed;
int j;
for(j=0; j<pProbe->nColumn; j++){
int x = pProbe->aiColumn[j];
if( x<BMS-1 ){
m &= ~(((Bitmask)1)<<x);
}
}
if( m==0 ){
flags |= WHERE_IDX_ONLY;
cost = cost * (1/2);
TRACE(("...... idx-only reduces cost to %.9g\n", cost));
}
}
/* If this index has achieved the lowest cost so far, then use it.
*/
if( cost < lowestCost ){
bestIdx = pProbe;
lowestCost = cost;
assert( flags!=0 );
bestFlags = flags;
bestNEq = nEq;
}
}
/* Report the best result
*/
*ppIndex = bestIdx;
TRACE(("best index is %s, cost=%.9g, flags=%x, nEq=%d\n",
bestIdx ? bestIdx->zName : "(none)", lowestCost, bestFlags,
bestNEq));
*pFlags = bestFlags;
*pnEq = bestNEq;
return lowestCost;
}
Thanking you,
With Regards,
Sankara Narayanan
Philips Innovation Campus
No 1, Murphy Road,
Ulsoor, Bangalore - 560008.
Ph - 0091-80-25579000 Extn 5121
"Utthistatha Jaagrata Praapya Varaan Nibodhatha"
