One-dimensional and multi-dimensional substring selectivity estimation

Abstract

With the increasing importance of XML, LDAP directories, and text-based information sources on the Internet, there is an ever-greater need to evaluate queries involving (sub)string matching. In many cases, matches need to be on multiple attributes/dimensions, with correlations between the multiple dimensions. Effective query optimization in this context requires good selectivity estimates. In this paper, we use pruned count-suffix trees (PSTs) as the basic data structure for substring selectivity estimation. For the 1-D problem, we present a novel technique called MO (Maximal Overlap). We then develop and analyze two 1-D estimation algorithms, MOC and MOLC, based on MO and a constraint-based characterization of all possible completions of a given PST. For the k-D problem, we first generalize PSTs to multiple dimensions and develop a space-and time-efficient probabilistic algorithm to construct k-D PSTs directly. We then show how to extend MO to multiple dimensions. Finally, we demonstrate, both analytically and experimentally, that MO is both practical and substantially superior to competing algorithms.

The History of Histograms (abridged)

“…These include techniques for selectivity estimation of select-join queries [71] or spatial queries [8], using query feedback to modify stored curve-fitting/parametric information for better selectivity estimation [16], selectivity estimation for alphanumeric/string data in 1-dimensional [43,45] and multi-dimensional environments [41,77], identification of quantiles [6,53,54] and their dynamic maintenance with a priori guarantees [29], approximate query answering for aggregate join queries [4], selectjoin queries [25], and within the general framework of on-line aggregation [33,32,51], computing frequencies of high-frequency items in a stream [52], and others. Despite their suboptimality compared to some of these techniques on the corresponding problems, histograms remain the method of choice, due to their overall effectiveness and wide applicability.…”

Selectivity estimation for string predicates: overcoming the underestimation problem

“…We compare our new regression tree combination estimator, CRT, with several estimators from the literature, which we discussed above: Markov estimators over both q-gram tables (ME) and suffix trees (ME_ST)-referred to as the maximal overlap method in [JKNS00], as well as the QG estimator. We also consider two other weighted combination estimators over suffix trees: WCS and WCIS.…”

“…Jagadish et al [JKNS00] improve the estimation step by relaxing the independence assumption, relying instead on the Markovian "short memory" assumption. According to this assumption, the probability of an attribute value v containing a substring s i+1 only depends on v containing substring s i (and not on the earlier substrings).…”

“…The most frequent class of string predicates-called wildcard predicates-are of the form R.A like %s%, where A is a string-valued (varchar) attribute of a relation R. Several techniques have been proposed for estimating the selectivity of wildcard predicates (e.g., [KVI96], [JKNS00]). These techniques build summary structures (e.g., pruned suffix trees or Markov tables) recording the "frequency" of carefully selected strings.…”

“…At run time, the estimation of the selectivity of a predicate R.A like %s% involves two parts: (i) parsing the query string s into possibly overlapping substrings s 1 , …, s k whose (exact) frequencies-and hence the associated selectivity of each substring predicate R.A like %s i %-can be looked up in the summary structure, and (ii) combining the (exact) selectivities of the substring predicates to estimate the selectivity of the original query predicate. To combine the selectivity of the substring predicates, existing techniques mainly rely either on the independence assumption [KVI96] (the selectivity of the R.A like %s i % predicate is independent of that associated with s j , for all j i), or on the Markov assumption [JKNS00] (the selectivity of the R.A like %s i % predicate is independent of all R.A like %s j % except when j= i-1).…”