Menu
▾
▴
[Firebird-checkins] CVS: vulcan/src/jrd btr.cpp,1.14,1.15 nav.cpp,1.12,1.13 opt.cpp,1.31,1.32 Optimizer.cpp,1.11,1.12 Optimizer.h,1.7,1.8 validation.cpp,1.7,1.8
|
From: Arno B. <arn...@us...> - 2006-01-19 00:16:05
|
Build Version : V2.0.0.4313 Vulcan 1.0 Development (writeBuildNum.sh,v 1.1.1.287 2006/01/19 00:15:54 arnobrink ) Update of /cvsroot/firebird/vulcan/src/jrd In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25594 Modified Files: btr.cpp nav.cpp opt.cpp Optimizer.cpp Optimizer.h validation.cpp Log Message: Synchronize changes from FB2-head Index: btr.cpp =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/btr.cpp,v retrieving revision 1.14 retrieving revision 1.15 diff -b -U3 -r1.14 -r1.15 --- btr.cpp 11 Jan 2006 23:34:16 -0000 1.14 +++ btr.cpp 19 Jan 2006 00:15:54 -0000 1.15 @@ -3531,8 +3531,7 @@ key->key_length = isr->isr_key_length; memcpy(key->key_data, record, key->key_length); - if (useJumpInfo && (newAreaPointers[0] < pointer) - && (bucket->btr_length + totalJumpSize[0] + newNode.prefix + 6 < lp_fill_limit)) + if (useJumpInfo && (newAreaPointers[0] < pointer)) { // Create a jumpnode @@ -3540,6 +3539,13 @@ jumpNode.prefix = BTreeNode::computePrefix(jumpKey->keyData, jumpKey->keyLength, key->key_data, newNode.prefix); jumpNode.length = newNode.prefix - jumpNode.prefix; + + const USHORT jumpNodeSize = + BTreeNode::getJumpNodeSize(&jumpNode, flags); + // Ensure the new jumpnode fits in the bucket + if (bucket->btr_length + totalJumpSize[0] + jumpNodeSize < lp_fill_limit) + { + // Initialize the rest of the jumpnode jumpNode.offset = (newNode.nodePointer - (UCHAR*)bucket); jumpNode.data = FB_NEW(*tdbb->tdbb_default) UCHAR[jumpNode.length]; memcpy(jumpNode.data, key->key_data + jumpNode.prefix, jumpNode.length); @@ -3550,13 +3556,14 @@ // Store new data in jumpKey, so a new jump node can calculate prefix - MOVE_FAST(jumpNode.data, jumpKey->keyData + jumpNode.prefix, jumpNode.length); + memcpy(jumpNode.data, jumpKey->keyData + jumpNode.prefix, jumpNode.length); jumpKey->keyLength = jumpNode.length + jumpNode.prefix; // Set new position for generating jumpnode newAreaPointers[0] += jumpInfo.jumpAreaSize; - totalJumpSize[0] += BTreeNode::getJumpNodeSize(&jumpNode, flags); + totalJumpSize[0] += jumpNodeSize; + } } // If there wasn't a split, we're done. If there was, propogate the @@ -3777,16 +3784,20 @@ bucket->btr_prefix_total += prefix; levelPointer = BTreeNode::writeNode(&levelNode[level], levelPointer, flags, false); - if (useJumpInfo && (newAreaPointers[level] < levelPointer) - && (bucket->btr_length - + totalJumpSize[level] - + levelNode[level].prefix + 6 < pp_fill_limit)) + if (useJumpInfo && (newAreaPointers[level] < levelPointer)) { // Create a jumpnode IndexJumpNode jumpNode; jumpNode.prefix = BTreeNode::computePrefix(pageJumpKey->keyData, pageJumpKey->keyLength, temp_key.key_data, levelNode[level].prefix); jumpNode.length = levelNode[level].prefix - jumpNode.prefix; + + const USHORT jumpNodeSize = + BTreeNode::getJumpNodeSize(&jumpNode, flags); + // Ensure the new jumpnode fits in the bucket + if (bucket->btr_length + totalJumpSize[level] + jumpNodeSize < pp_fill_limit) + { + // Initialize the rest of the jumpnode jumpNode.offset = (levelNode[level].nodePointer - (UCHAR*)bucket); jumpNode.data = FB_NEW(*tdbb->tdbb_default) UCHAR[jumpNode.length]; memcpy(jumpNode.data, temp_key.key_data + jumpNode.prefix, @@ -3805,7 +3816,8 @@ // Set new position for generating jumpnode newAreaPointers[level] += jumpInfo.jumpAreaSize; - totalJumpSize[level] += BTreeNode::getJumpNodeSize(&jumpNode, flags); + totalJumpSize[level] += jumpNodeSize; + } } // Now restore the current key value and save this node as the @@ -3841,6 +3853,9 @@ // and update the final page length bucket->btr_length = pointer - (UCHAR*)bucket; + if (bucket->btr_length > dbb->dbb_page_size) { + BUGCHECK(205); // msg 205 index bucket overfilled + } // Store jump nodes on page if needed. Index: nav.cpp =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/nav.cpp,v retrieving revision 1.12 retrieving revision 1.13 diff -b -U3 -r1.12 -r1.13 --- nav.cpp 28 Sep 2005 21:46:07 -0000 1.12 +++ nav.cpp 19 Jan 2006 00:15:54 -0000 1.13 @@ -204,7 +204,7 @@ // The bitmap is only valid when we are continuing on in one // direction. It is of no help when we change direction, // and so we need to reset in that case. - +#ifdef SCROLLABLE_CURSORS if (((impure->irsb_flags & irsb_backwards) && direction != RSE_get_backward) || (!(impure->irsb_flags & irsb_backwards) && direction != RSE_get_forward)) RecordBitmap::reset(impure->irsb_nav_records_visited); @@ -213,6 +213,7 @@ impure->irsb_flags &= ~irsb_backwards; else if (direction == RSE_get_backward) impure->irsb_flags |= irsb_backwards; +#endif // find the last fetched position from the index WIN window(impure->irsb_nav_page); @@ -226,9 +227,10 @@ IndexRetrieval* retrieval = (IndexRetrieval*) retrieval_node->nod_arg[e_idx_retrieval]; // set the upper (or lower) limit for navigational retrieval - - temporary_key upper, lower; - + temporary_key upper; +#ifdef SCROLLABLE_CURSORS + temporary_key lower; +#endif if ((direction == RSE_get_forward) && retrieval->irb_upper_count) { upper.key_length = impure->irsb_nav_upper_length; @@ -239,11 +241,13 @@ MOVE_FAST((impure->irsb_nav_data + rsb->keyLength), upper.key_data, upper.key_length); #endif } +#ifdef SCROLLABLE_CURSORS else if ((direction == RSE_get_backward) && retrieval->irb_lower_count) { lower.key_length = impure->irsb_nav_lower_length; MOVE_FAST((impure->irsb_nav_data + rsb->keyLength),lower.key_data, lower.key_length); } +#endif // In the case of a DISTINCT, we must detect whether the key changed since the last // time a record was returned from the rsb. It is not good enough to know whether the @@ -319,7 +323,9 @@ { if (node.isEndLevel) { +#ifdef SCROLLABLE_CURSORS impure->irsb_flags |= irsb_eof; +#endif break; } @@ -411,6 +417,7 @@ break; } +#ifdef SCROLLABLE_CURSORS if ((direction == RSE_get_backward) && retrieval->irb_lower_count && compare_keys(idx, key.key_data, key.key_length, &lower, retrieval->irb_generic & (irb_descending | @@ -420,6 +427,7 @@ //RSE_MARK_CRACK(tdbb, rsb, irsb_crack); break; } +#endif // skip this record if: // 1) there is an inversion tree for this index and this record @@ -433,13 +441,18 @@ || RecordBitmap::test(impure->irsb_nav_records_visited, number.getValue()) || ((rsb->rsb_flags & rsb_project) && !(impure->irsb_flags & irsb_key_changed))) { +#ifdef SCROLLABLE_CURSORS if (direction == RSE_get_backward) { nextPointer = BTreeNode::previousNode(&node, pointer, flags, &expanded_node); expanded_next = expanded_node; continue; } - else if (direction == RSE_get_forward) // && !(impure->irsb_flags & irsb_forced_crack)) + if ((direction == RSE_get_forward) + && !(impure->irsb_flags & irsb_forced_crack)) +#else + if (direction == RSE_get_forward) +#endif { nextPointer = BTreeNode::nextNode(&node, pointer, flags, &expanded_node); expanded_next = expanded_node; @@ -805,8 +818,15 @@ // If this is the first time, start at the beginning (or the end) +#ifdef SCROLLABLE_CURSORS if (!window->win_page || impure->irsb_flags & (irsb_bof | irsb_eof)) + { +#else + if (!window->win_page) + { +#endif return nav_open(tdbb, rsb, impure, window, direction, expanded_node); + } exp_index_buf* expanded_page = NULL; @@ -847,11 +867,14 @@ // nav_offset be the last node fetched. Go forward or // backward from that point accordingly. +#ifdef SCROLLABLE_CURSORS if (direction == RSE_get_backward) { pointer = BTreeNode::previousNode(&node, pointer, flags, expanded_node); //node = (btree_nod*) BTR_previous_node( (UCHAR*)node, expanded_node); } - else if (direction == RSE_get_forward) { + else +#endif + if (direction == RSE_get_forward) { pointer = BTreeNode::nextNode(&node, pointer, flags, expanded_node); } return pointer; @@ -868,11 +891,14 @@ page = (btree_page*) window->win_buffer; if (pointer) { *expanded_node = find_current(window->win_expanded_buffer, page, pointer); +#ifdef SCROLLABLE_CURSORS if (direction == RSE_get_backward) { pointer = BTreeNode::previousNode(&node, pointer, flags, expanded_node); //node = (btree_nod*) BTR_previous_node((UCHAR*) node, expanded_node); } - else if (direction == RSE_get_forward && found) { + else +#endif + if (direction == RSE_get_forward && found) { // in the forward case, seek to the next node only if we found // the actual node on page; if we did not find it, we are already // at the next node (such as when the node is garbage collected) @@ -936,11 +962,13 @@ tdbb->tdbb_attachment->att_flags |= ATT_no_cleanup; // HACK } +#ifdef SCROLLABLE_CURSORS // the attempt to get a record, whether successful or not, takes // us off bof or eof (otherwise we will keep trying to retrieve // the first record) impure->irsb_flags &= ~(irsb_bof | irsb_eof); +#endif result = VIO_get(tdbb, rpb, request->req_transaction, request->req_pool); @@ -1039,10 +1067,12 @@ impure->irsb_nav_page = 0; impure->irsb_nav_length = 0; +#ifdef SCROLLABLE_CURSORS if (direction == RSE_get_forward) impure->irsb_flags |= irsb_bof; else if (direction == RSE_get_backward) impure->irsb_flags |= irsb_eof; +#endif // Find the starting leaf page @@ -1051,8 +1081,13 @@ retrieval = (IndexRetrieval*) retrieval_node->nod_arg[e_idx_retrieval]; //index_desc* idx = (index_desc* ) ((SCHAR *) impure + (long) rsb->rsb_arg[RSB_NAV_idx_offset]); index_desc* idx = (index_desc* ) ((SCHAR *) impure + rsb->indexOffset); +#ifdef SCROLLABLE_CURSORS btree_page* page = BTR_find_page(tdbb, retrieval, window, idx, &lower, &upper, (direction == RSE_get_backward)); +#else + btree_page* page = BTR_find_page(tdbb, retrieval, window, idx, &lower, + &upper, false); +#endif impure->irsb_nav_page = window->win_page; Index: opt.cpp =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/opt.cpp,v retrieving revision 1.31 retrieving revision 1.32 diff -b -U3 -r1.31 -r1.32 --- opt.cpp 11 Jan 2006 23:34:16 -0000 1.31 +++ opt.cpp 19 Jan 2006 00:15:54 -0000 1.32 @@ -162,6 +162,7 @@ static RsbSort* gen_sort(thread_db*, OptimizerBlk*, const UCHAR*, const UCHAR*, RecordSource*, jrd_nod*, bool); static bool gen_sort_merge(thread_db*, OptimizerBlk*, RiverStack&); static RecordSource* gen_union(thread_db*, OptimizerBlk*, jrd_nod*, UCHAR *, USHORT, NodeStack*, UCHAR); +static void get_expression_streams(const jrd_nod*, firebird::SortedArray<int>&); static void get_inactivities(const CompilerScratch*, ULONG*); static jrd_nod* get_unmapped_node(thread_db*, jrd_nod*, jrd_nod*, UCHAR, bool); static IndexedRelationship* indexed_relationship(thread_db*, OptimizerBlk*, USHORT); @@ -1566,14 +1567,14 @@ // if all the fields in the sort list are from one stream, check the stream is // the most outer stream, if true update rse and ignore the sort if (sort && !project) { - UCHAR sort_stream = 0; + int sort_stream = 0; bool usableSort = true; const jrd_nod* const* sort_ptr = sort->nod_arg; const jrd_nod* const* const sort_end = sort_ptr + sort->nod_count; for (; sort_ptr < sort_end; sort_ptr++) { if ((*sort_ptr)->nod_type == nod_field) { // Get stream for this field at this position. - const UCHAR current_stream = (UCHAR)(long)(*sort_ptr)->nod_arg[e_fld_stream]; + const int current_stream = (int)(long)(*sort_ptr)->nod_arg[e_fld_stream]; // If this is the first position node, save this stream. if (sort_ptr == sort->nod_arg) { sort_stream = current_stream; @@ -1586,11 +1587,26 @@ } } else { - // This position doesn't use a simple field, thus we can't use - // any index for the sort. + // If this is not the first position node, reject this sort. + // Two expressions cannot be mapped to a single index. + if (sort_ptr > sort->nod_arg) { usableSort = false; break; } + // This position doesn't use a simple field, thus we should + // check the expression internals. + firebird::SortedArray<int> streams; + get_expression_streams(*sort_ptr, streams); + // We can use this sort only if there's a single stream + // referenced by the expression. + if (streams.getCount() == 1) { + sort_stream = streams[0]; + } + else { + usableSort = false; + break; + } + } } if (usableSort) { @@ -1599,6 +1615,16 @@ while (node) { if (node->nod_type == nod_rse) { new_rse = (RecordSelExpr*) node; + + // AB: Don't distribute the sort when a FIRST/SKIP is supplied, + // because that will affect the behaviour from the deeper RSE. + if (new_rse->rse_first || new_rse->rse_skip) { + node = NULL; + break; + } + + // Walk trough the relations of the RSE and see if a + // matching stream can be found. if (new_rse->rse_jointype == blr_inner) { if (new_rse->rse_count == 1) { node = new_rse->rse_relation[0]; @@ -1608,7 +1634,7 @@ for (int i = 0; i < new_rse->rse_count; i++) { jrd_nod* subNode = (jrd_nod*) new_rse->rse_relation[i]; if (subNode->nod_type == nod_relation && - ((USHORT)(long)subNode->nod_arg[e_rel_stream]) == sort_stream && + ((int)(long)subNode->nod_arg[e_rel_stream]) == sort_stream && new_rse != rse) { sortStreamFound = true; @@ -1632,7 +1658,7 @@ } else { if (node->nod_type == nod_relation && - ((USHORT)(long)node->nod_arg[e_rel_stream]) == sort_stream && + ((int)(long)node->nod_arg[e_rel_stream]) == sort_stream && new_rse && new_rse != rse) { new_rse->rse_sorted = sort; @@ -5689,6 +5715,173 @@ } +static void get_expression_streams(const jrd_nod* node, + firebird::SortedArray<int>& streams) +{ +/************************************** + * + * g e t _ e x p r e s s i o n _ s t r e a m s + * + ************************************** + * + * Functional description + * Return all streams referenced by the expression. + * + **************************************/ + DEV_BLKCHK(node, type_nod); + + if (!node) { + return; + } + + RecordSelExpr* rse = NULL; + + int n; + int pos; + + switch (node->nod_type) { + + case nod_field: + n = (int)(long) node->nod_arg[e_fld_stream]; + if (!streams.find(n, pos)) + streams.add(n); + break; + + case nod_rec_version: + case nod_dbkey: + n = (int)(long) node->nod_arg[0]; + if (!streams.find(n, pos)) + streams.add(n); + break; + + case nod_cast: + get_expression_streams(node->nod_arg[e_cast_source], streams); + break; + + case nod_extract: + get_expression_streams(node->nod_arg[e_extract_value], streams); + break; + +/* FB2.0 INTL + case nod_strlen: + get_expression_streams(node->nod_arg[e_strlen_value], streams); + break; +*/ + + case nod_function: + get_expression_streams(node->nod_arg[e_fun_args], streams); + break; + + case nod_procedure: + get_expression_streams(node->nod_arg[e_prc_inputs], streams); + break; + + case nod_any: + case nod_unique: + case nod_ansi_any: + case nod_ansi_all: + case nod_exists: + get_expression_streams(node->nod_arg[e_any_rse], streams); + break; + + case nod_argument: + case nod_current_date: + case nod_current_role: + case nod_current_time: + case nod_current_timestamp: + case nod_gen_id: + case nod_gen_id2: + case nod_internal_info: + case nod_literal: + case nod_null: + case nod_user_name: + case nod_variable: + break; + + case nod_rse: + rse = (RecordSelExpr*) node; + break; + + case nod_average: + case nod_count: + case nod_count2: + case nod_from: + case nod_max: + case nod_min: + case nod_total: + get_expression_streams(node->nod_arg[e_stat_rse], streams); + get_expression_streams(node->nod_arg[e_stat_value], streams); + break; + + // go into the node arguments + case nod_add: + case nod_add2: + case nod_agg_average: + case nod_agg_average2: + case nod_agg_average_distinct: + case nod_agg_average_distinct2: + case nod_agg_max: + case nod_agg_min: + case nod_agg_total: + case nod_agg_total2: + case nod_agg_total_distinct: + case nod_agg_total_distinct2: + case nod_concatenate: + case nod_divide: + case nod_divide2: + case nod_multiply: + case nod_multiply2: + case nod_negate: + case nod_subtract: + case nod_subtract2: + + case nod_upcase: +/* FB2.0 INTL + case nod_lowcase: + case nod_trim: +*/ + case nod_substr: + + case nod_like: + case nod_between: + case nod_sleuth: + case nod_missing: + case nod_value_if: + case nod_matches: + case nod_contains: + case nod_starts: + case nod_equiv: + case nod_eql: + case nod_neq: + case nod_geq: + case nod_gtr: + case nod_lss: + case nod_leq: + { + const jrd_nod* const* ptr = node->nod_arg; + // Check all sub-nodes of this node. + for (const jrd_nod* const* const end = ptr + node->nod_count; + ptr < end; ptr++) + { + get_expression_streams(*ptr, streams); + } + break; + } + + default: + break; + } + + if (rse) { + get_expression_streams(rse->rse_first, streams); + get_expression_streams(rse->rse_skip, streams); + get_expression_streams(rse->rse_boolean, streams); + get_expression_streams(rse->rse_sorted, streams); + get_expression_streams(rse->rse_projection, streams); + } +} + + static void get_inactivities(const CompilerScratch* csb, ULONG* dependencies) { /************************************** @@ -6260,12 +6453,15 @@ if (!OPT_expression_equal(tdbb, opt, idx, field, stream)) return NULL; } - else if (field->nod_type != nod_field) + else + { + if (field->nod_type != nod_field) return NULL; if ((USHORT)(long) field->nod_arg[e_fld_stream] != stream || (USHORT)(long) field->nod_arg[e_fld_id] != idx->idx_rpt[0].idx_field) return NULL; + } jrd_nod* node = make_index_node(tdbb, relation, opt->opt_csb, idx); IndexRetrieval* retrieval = (IndexRetrieval*) node->nod_arg[e_idx_retrieval]; @@ -6343,7 +6539,9 @@ return NULL; } } - else if (field->nod_type != nod_field) + else + { + if (field->nod_type != nod_field) { // dimitr: any idea how we can use an index in this case? // The code below produced wrong results. @@ -6356,8 +6554,8 @@ */ } -/* Every string starts with an empty string so - don't bother using an index in that case. */ + // Every string starts with an empty string so + // don't bother using an index in that case. if (value->nod_type == nod_literal) { @@ -6379,6 +6577,7 @@ || idx->idx_rpt[0].idx_itype >= idx_first_intl_string) || !OPT_computable(opt->opt_csb, value, stream, false, false)) return NULL; + } jrd_nod* node = make_index_node(tdbb, relation, opt->opt_csb, idx); IndexRetrieval* retrieval = (IndexRetrieval*) node->nod_arg[e_idx_retrieval]; Index: Optimizer.cpp =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/Optimizer.cpp,v retrieving revision 1.11 retrieving revision 1.12 diff -b -U3 -r1.11 -r1.12 --- Optimizer.cpp 11 Jan 2006 23:34:16 -0000 1.11 +++ Optimizer.cpp 19 Jan 2006 00:15:54 -0000 1.12 @@ -406,15 +406,6 @@ if ((node1->nod_arg[e_fld_id] == node2->nod_arg[e_fld_id]) && (fld_stream == stream)) { - /* - dsc dsc1, dsc2; - dsc *desc1 = &dsc1, *desc2 = &dsc2; - - CMP_get_desc(tdbb, opt->opt_csb, node1, desc1); - CMP_get_desc(tdbb, opt->opt_csb, node2, desc2); - - if (DSC_GET_COLLATE(desc1) == DSC_GET_COLLATE(desc2)) - */ return true; } } @@ -492,15 +483,6 @@ if (OPT_expression_equal2(tdbb, opt, node1->nod_arg[0], node2->nod_arg[0], stream)) { - /* - dsc dsc1, dsc2; - dsc *desc1 = &dsc1, *desc2 = &dsc2; - - CMP_get_desc(tdbb, opt->opt_csb, node1, desc1); - CMP_get_desc(tdbb, opt->opt_csb, node2, desc2); - - if (DSC_GET_COLLATE(desc1) == DSC_GET_COLLATE(desc2)) - */ return true; } break; @@ -754,6 +736,8 @@ **************************************/ lowerValue = NULL; upperValue = NULL; + excludeLower = false; + excludeUpper = false; scope = 0; scanType = segmentScanNone; } @@ -772,6 +756,8 @@ **************************************/ lowerValue = segment->lowerValue; upperValue = segment->upperValue; + excludeLower = segment->excludeLower; + excludeUpper = segment->excludeUpper; scope = segment->scope; scanType = segment->scanType; @@ -801,9 +787,6 @@ nonFullMatchedSegments = 0; this->idx = idx; - excludeLower = false; - excludeUpper = false; - segments.grow(idx->idx_count); int length = 0; @@ -857,9 +840,6 @@ nonFullMatchedSegments = scratch->nonFullMatchedSegments; idx = scratch->idx; - excludeLower = scratch->excludeLower; - excludeUpper = scratch->excludeUpper; - // Allocate needed segments segments.grow(scratch->segments.getCount()); @@ -1479,6 +1459,11 @@ invCandidate->indexes = 0; invCandidate->selectivity = MAXIMUM_SELECTIVITY; invCandidate->cost = csb->csb_rpt[stream].csb_cardinality; + OptimizerBlk::opt_conjunct* tail = optimizer->opt_conjuncts.begin(); + for (; tail < optimizer->opt_conjuncts.end(); tail++) { + findDependentFromStreams(tail->opt_conjunct_node, + &invCandidate->dependentFromStreams); + } return invCandidate; } @@ -1669,6 +1654,16 @@ invCandidate->unique = unique; invCandidate->selectivity = scratch[i]->selectivity; + // When selectivty is zero the statement is prepared on an + // empty table or the statistics aren't updated. + // Assume a half of the maximum selectivty, so at least some + // indexes are chosen by the optimizer. This avoids some slowdown + // statements on growing tables. + + if (invCandidate->selectivity <= 0) { + invCandidate->selectivity = MAXIMUM_SELECTIVITY / 2; + } + // Calculate the cost (only index pages) for this index. // The constant DEFAULT_INDEX_COST 1 is an average for // the rootpage and non-leaf pages. @@ -1806,10 +1801,18 @@ } i = std::max(indexScratch->lowerCount, indexScratch->upperCount) - 1; - - if ((i >= 0) && (segment[i]->scanType == segmentScanStarting)) + if (i >= 0) + { + if (segment[i]->scanType == segmentScanStarting) retrieval->irb_generic |= irb_starting; + if (segment[i]->excludeLower) + retrieval->irb_generic |= irb_exclude_lower; + + if (segment[i]->excludeUpper) + retrieval->irb_generic |= irb_exclude_upper; + } + /* FB2.0 INTL for (IndexScratchSegment** tail = indexScratch->segments.begin(); tail != indexScratch->segments.end() && ((*tail)->lowerValue || (*tail)->upperValue); ++tail) @@ -1888,12 +1891,6 @@ else if (retrieval->irb_upper_count < idx->idx_count) retrieval->irb_generic |= irb_partial; - if (indexScratch->excludeLower) - retrieval->irb_generic |= irb_exclude_lower; - - if (indexScratch->excludeUpper) - retrieval->irb_generic |= irb_exclude_upper; - // mark the index as utilized for the purposes of this compile idx->idx_runtime_flags |= idx_used; @@ -1920,20 +1917,24 @@ if (inversions->getCount() < 1) return NULL; - // This constant disables our smart index selection algorithm. When we - // deal with a system request, most probably it's being cached at the - // first invocation and hence it may have out-of-date plan at runtime. - // An example is a database restore process which starts with almost - // empty system tables which may significantly grow later. So, as a - // practical rule, we use all available indices for any system request. - // Another special case is an explicit (i.e. user specified) plan which + // This flag disables our smart index selection algorithm. + // It's set for any explicit (i.e. user specified) plan which // requires all existing indices to be considered for a retrieval. const bool acceptAll = csb->csb_rpt[stream].csb_plan; - const double streamCard = csb->csb_rpt[stream].csb_cardinality; + + double streamCardinality = csb->csb_rpt[stream].csb_cardinality; + // When the cardinality is very small then the statement is being + // prepared on an almost empty table, which would meant no indexes + // will be used at all. The prepared statement could be cached + // (such as in system restore process) and cause slowdown when the + // table grows. Set the cardinality to a value so that at least + // some indexes are chosen. + if (streamCardinality <= 5) { + streamCardinality = 5; + } double totalSelectivity = MAXIMUM_SELECTIVITY; // worst selectivity - double totalCost = 0; double totalIndexCost = 0; // Allow indexes also to be used on very small tables. Limit starts @@ -1941,11 +1942,16 @@ // Also when the table is small and a statement is prepared, but would grow // while inserting data into this would really slow down the statement. // An example here is with system tables and the restore process of gbak. + // + // dimitr: TO BE REVIEWED!!! + // + const double maximumCost = (DEFAULT_INDEX_COST * 5) + (streamCardinality * 0.95); + const double minimumSelectivity = 1 / streamCardinality; - const double maximumCost = (DEFAULT_INDEX_COST * 5) + (streamCard * 0.95); double previousTotalCost = maximumCost; - const double minimumSelectivity = streamCard ? 1 / streamCard : 0; + // Force to always choose at least one index + bool firstCandidate = true; int i = 0; InversionCandidate* invCandidate = NULL; @@ -2075,9 +2081,9 @@ { double bestCandidateCost = bestCandidate->cost + - (bestCandidate->selectivity * streamCard); + (bestCandidate->selectivity * streamCardinality); double currentCandidateCost = currentInv->cost + - (currentInv->selectivity * streamCard); + (currentInv->selectivity * streamCardinality); // Do we have very similar costs? double diffCost = currentCandidateCost; @@ -2181,22 +2187,23 @@ } */ - double newTotalSelectivity = bestCandidate->selectivity * totalSelectivity; - double newTotalDataCost = newTotalSelectivity * streamCard; - double newTotalIndexCost = totalIndexCost + bestCandidate->cost; - totalCost = newTotalDataCost + newTotalIndexCost; + const double newTotalSelectivity = bestCandidate->selectivity * totalSelectivity; + const double newTotalDataCost = newTotalSelectivity * streamCardinality; + const double newTotalIndexCost = totalIndexCost + bestCandidate->cost; + const double totalCost = newTotalDataCost + newTotalIndexCost; // Test if the new totalCost will be higher than the previous totalCost // and if the current selectivity (without the bestCandidate) is already good enough. - if (acceptAll || ((totalCost < previousTotalCost) - && (!totalSelectivity || totalSelectivity > minimumSelectivity))) + if (acceptAll || firstCandidate || + ((totalCost < previousTotalCost) && + (totalSelectivity > minimumSelectivity))) { // Exclude index from next pass bestCandidate->used = true; + firstCandidate = false; previousTotalCost = totalCost; totalIndexCost = newTotalIndexCost; - totalSelectivity = newTotalSelectivity; if (!invCandidate) @@ -2374,8 +2381,8 @@ segment[i]->lowerValue = value; segment[i]->upperValue = boolean->nod_arg[2]; segment[i]->scanType = segmentScanBetween; - indexScratch->excludeLower = false; - indexScratch->excludeUpper = false; + segment[i]->excludeLower = false; + segment[i]->excludeUpper = false; } break; @@ -2389,8 +2396,8 @@ { segment[i]->lowerValue = segment[i]->upperValue = value; segment[i]->scanType = segmentScanEquivalent; - indexScratch->excludeLower = false; - indexScratch->excludeUpper = false; + segment[i]->excludeLower = false; + segment[i]->excludeUpper = false; } break; @@ -2398,8 +2405,8 @@ segment[i]->matches.add(boolean); segment[i]->lowerValue = segment[i]->upperValue = value; segment[i]->scanType = segmentScanEqual; - indexScratch->excludeLower = false; - indexScratch->excludeUpper = false; + segment[i]->excludeLower = false; + segment[i]->excludeUpper = false; break; case nod_gtr: @@ -2410,13 +2417,10 @@ || (segment[i]->scanType == segmentScanEquivalent) || (segment[i]->scanType == segmentScanBetween))) { - if (boolean->nod_type == nod_gtr) - { if (forward != isDesc) // (forward && !isDesc || !forward && isDesc) - indexScratch->excludeLower = true; + segment[i]->excludeLower = (boolean->nod_type == nod_gtr); else - indexScratch->excludeUpper = true; - } + segment[i]->excludeUpper = (boolean->nod_type == nod_gtr); if (forward) { @@ -2447,13 +2451,10 @@ || (segment[i]->scanType == segmentScanEquivalent) || (segment[i]->scanType == segmentScanBetween))) { - if (boolean->nod_type == nod_lss) - { if (forward != isDesc) - indexScratch->excludeUpper = true; + segment[i]->excludeUpper = (boolean->nod_type == nod_lss); else - indexScratch->excludeLower = true; - } + segment[i]->excludeLower = (boolean->nod_type == nod_lss); if (forward) { @@ -2488,8 +2489,8 @@ { segment[i]->lowerValue = segment[i]->upperValue = value; segment[i]->scanType = segmentScanStarting; - indexScratch->excludeLower = false; - indexScratch->excludeUpper = false; + segment[i]->excludeLower = false; + segment[i]->excludeUpper = false; } break; @@ -2501,8 +2502,8 @@ { segment[i]->lowerValue = segment[i]->upperValue = value; segment[i]->scanType = segmentScanMissing; - indexScratch->excludeLower = false; - indexScratch->excludeUpper = false; + segment[i]->excludeLower = false; + segment[i]->excludeUpper = false; } break; @@ -2883,7 +2884,7 @@ InnerJoinStreamInfo::InnerJoinStreamInfo(MemoryPool& p) : - indexedRelationships(p), previousExpectedStreams(p) + indexedRelationships(p) { /************************************** * @@ -2902,7 +2903,7 @@ used = false; indexedRelationships.shrink(0); - previousExpectedStreams.shrink(0); + previousExpectedStreams = 0; } bool InnerJoinStreamInfo::independent() const @@ -2920,7 +2921,7 @@ * **************************************/ return (indexedRelationships.getCount() == 0) && - (previousExpectedStreams.getCount() == 0); + (previousExpectedStreams == 0); } @@ -3081,8 +3082,8 @@ // Next those with the lowest previous expected streams - int compare = innerStreams[i]->previousExpectedStreams.getCount() - - tempStreams[index]->previousExpectedStreams.getCount(); + int compare = innerStreams[i]->previousExpectedStreams - + tempStreams[index]->previousExpectedStreams; if (compare < 0) break; @@ -3372,7 +3373,7 @@ if (relationStreamInfo->stream == relationships[index]->stream) { // If the cost of this relationship is cheaper then remove the - // old realtionship and add this one. + // old relationship and add this one. if (cheaperRelationship(relationship, relationships[index])) { processList->remove(index); @@ -3483,6 +3484,8 @@ if (candidate->dependentFromStreams.find(baseStream->stream, pos)) { + if (candidate->indexes) + { // If we could use more conjunctions on the testing stream with the base stream // active as without the base stream then the test stream has a indexed // relationship with the base stream. @@ -3503,7 +3506,8 @@ break; baseStream->indexedRelationships.insert(index, indexRelationship); - testStream->previousExpectedStreams.add(baseStream->stream); + } + testStream->previousExpectedStreams++; } delete candidate; Index: Optimizer.h =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/Optimizer.h,v retrieving revision 1.7 retrieving revision 1.8 diff -b -U3 -r1.7 -r1.8 --- Optimizer.h 11 Jan 2006 23:34:16 -0000 1.7 +++ Optimizer.h 19 Jan 2006 00:15:54 -0000 1.8 @@ -109,6 +109,8 @@ jrd_nod* lowerValue; // lower bound on index value jrd_nod* upperValue; // upper bound on index value + bool excludeLower; // exclude lower bound value from scan + bool excludeUpper; // exclude upper bound value from scan int scope; // highest scope level segmentScanType scanType; // scan type @@ -131,9 +133,6 @@ int nonFullMatchedSegments; // double cardinality; // Estimated cardinality when using the whole index - bool excludeLower; // - bool excludeUpper; // - firebird::Array<IndexScratchSegment*> segments; }; @@ -240,7 +239,7 @@ bool used; IndexedRelationships indexedRelationships; - firebird::Array<int> previousExpectedStreams; + int previousExpectedStreams; }; typedef firebird::HalfStaticArray<InnerJoinStreamInfo*, 8> StreamInfoList; Index: validation.cpp =================================================================== RCS file: /cvsroot/firebird/vulcan/src/jrd/validation.cpp,v retrieving revision 1.7 retrieving revision 1.8 diff -b -U3 -r1.7 -r1.8 --- validation.cpp 17 Jan 2006 19:05:55 -0000 1.7 +++ validation.cpp 19 Jan 2006 00:15:54 -0000 1.8 @@ -1696,9 +1696,12 @@ // Only check record-number if this isn't the first page in // the level and it isn't a MARKER. + // Also don't check on primary/unique keys, because duplicates aren't + // sorted on recordnumber, except for NULL keys. - if (useAllRecordNumbers && down_page->btr_left_sibling - && !(downNode.isEndBucket || downNode.isEndLevel)) + if (useAllRecordNumbers && down_page->btr_left_sibling && + !(downNode.isEndBucket || downNode.isEndLevel) && + (!unique || (unique && nullKeyNode)) ) { // Check record number if key is equal with node on // pointer page. In that case record number on page |