|
|
|
If a query runs slower than expected, it is possible that the actual selectivity for one or more of the expressions in the query differs from the default selectivity that the Accredo SQL Engine uses. The selectivity of an expression is the proportion of rows that is expected to be returned out of all possible rows.
For example, take the following query:
SELECT * FROM ARTRAN
WHERE CustomerCode = 'ASHENG'
If table ARTRAN has 600 rows, this is it’s cardinality, meaning the number of different elements.
If there are 60 rows with CustomerCode = 'ASHENG', the actual selectivity of CustomerCode = 'ASHENG' is 60/600 = 0.1
This actual selectivity is very close to the defaults used by the Accredo SQL engine (see below) but in some cases due to differing data distribution the actual selectivity may be very different which may cause the SQL engine to execute the query in a way that is not optimum.
For example:
SELECT * FROM ICWORD
WHERE Word = 'bedroom'
If ICWORD has 140 rows and there is 1 row (card) with word = 'bedroom'.
Accredo’s estimate will be a selectivity of 0.1 which would give a cardinality of 14 which we know is incorrect. To force a selectivity, a selectivity clause is added:
SELECT * FROM ICWORD
WHERE Word = 'bedroom' SELECTIVITY 0.01
A selectivity clause can only be added to any single expression, it cannot be added to compound expressions. The following is not valid:
SELECT * FROM ICWORD
WHERE (Word = 'bedroom' or Word = 'pine') SELECTIVITY 0.01
That the above examples were single table selects; selectivity defaults and overrides will have the most effect when joins are involved. For example:
SELECT * FROM ICWORD
INNER JOIN ICPROD
ON ICPROD.ProductCode = ICWORD.ProductCode
WHERE ICWORD.Word LIKE '%bed%' SELECTIVITY 0.001
Accredo Default Selectivities
Accredo uses the following Selectivities by default, if a Selectivity is not specified:
table.field = value
= 1 / CARD(table) if there is a unique index on Table
= 0.10 otherwise
table1.field1 = table2.field2
= 1 / CARD(table2) if there is a unique index on field2 and the join is Table1 JOIN Table2
= ( CARD(table2) / CARD(table1) ) / CARD(table2) if there is a non-unique index on field2 and the join is Table1 JOIN Table2
= 1 / CARD(table1) if there is a unique index on field1 and the join is Table2 JOIN Table1
= ( CARD(table1) / CARD(table2) ) / CARD(table1) if there is a non-unique index on field1and the join is Table2 JOIN Table1
= 0.1 otherwise
field >, <, >=, <= value
= 0.333
field BETWEEN value1 AND value2
= 0.25
field IN (list of values)
= Min(0.5, InValueCount*0.1)
field <> field or value
= 0.9
NOT (expression)
= 1 – Selectivity of expression
field IS NULL
= 0.1
expr1 AND expr2
= Selectivity of expr1 * Selectivity of exp2
expr1 OR expr2
= Selectivity of expr1 + Selectivity of expr2 - (Selectivity of expr1 * Selectivity of expr2)
field LIKE expression
= Selectivity of equals if expression is a constant, that is, field LIKE ‘product’
= Selectivity of BETWEEN if expression starts with a constant, that is, field LIKE ‘prod%’
= 0.5 otherwise
|
|
|
