Databases Reference
In-Depth Information
traffic loads. Imagine the impact of an SQL query that inadvertently selects
most of a 1-million row table.
B-trees incur additional overhead in order to return a count of qualified
records. A qualifying count requires retrieval and tabulation of the under-
lying data.
Inverted index keyword searches are both
case and position insensitive. Users can quickly find the rows that meet
their selection criteria wherever the keyword appears and regardless of
whether the value is in upper case, lower case, or a combination of both.
Case and Position Insensitivity.
B-trees typically require an exact match to the stored data. If a name was
entered as SMITH, JOHN, but users searched for Smith, John, they would
not find the record. To perform the most efficient index search, the retriev-
al criteria must exactly match the value in the data base, including upper
or lower case letters, spaces and punctuation, and the order entered.
An inverted index lets users index and find records regardless of the da-
ta's format. Users can easily find “SMITH,” whether it was entered as
“Smith,” “SMITH,” or even “sMith.” In addition, because inverted indexing
is nonpositional, a retrieval using “JOHN OR JIM AND SMITH” will find any
of the following names:
JOHN SMITH
John jim smith
SMITH, JOHN
Smith, Jim JOhn
With inverted indexing, users can enter a
combination of keys to invoke a multiple index query. This capability al-
lows users to easily and quickly query any number of criteria across one or
more columns, across one or more tables. Thus, true multidimensional
function is delivered without the added maintenance and limitations of
multidimensional data bases.
Multidimensional Capabilities.
For example, consider a SALES-HISTORY data base whose PRODUCT
and CUSTOMER tables have inverted indexes on STATE, PRODUCT, DATE,
and STATUS. Users can enter any combination of values for a retrieval. A
sample retrieval could be: PRODUCT = “ABC OR XYZ,” DATE = “95*,” STA-
TUS = “Shipped,” and STATE = “CA.” The inverted indexes on STATE, PROD-
UCT, DATE, and STATUS invoke a search across multiple indexes, without
retrieving the individual data records.
Most RDBMSs can use only one index per SELECT statement. Even if
there are indexes on more than one column, the RDBMS uses only index. An
option in relational data bases is to concatenate the columns into one in-
dex to provide a keyed retrieval.
Moreover, RDBMSs require a different index definition for each compo-
nent combination. To retrieve any combination of five columns in a table,
a large number of relational indexes (5 factorial, or 120) would be needed.
Search WWH ::
Custom Search