Kbase P70489: When does the SQL-92 IN Predicate cause a full TABLE SCAN?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/26/2007 |
|
Status: Verified
GOAL:
When does the SQL-92 IN Predicate cause a full TABLE SCAN?
GOAL:
Table Scan versus index scan with SQL-92 clients
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x
FIX:
If the field being compared to the set of values is NOT an indexed field then the query will do a TABLE SCAN. For example, since the Customer.City in the Sports2000 database Customer table is not an indexed field, the following query does a TABLE SCAN:
SELECT CustNum, Name, SalesRep from PUB.Customer WHERE City IN ('Boston', 'New York', 'Paris');
If the field being compared to the set of values IS an indexed field then the query will do an INDEX SCAN. For example, since the Customer.SalesRep in the Sports2000 database Customer table IS an indexed field, the following query does an INDEX SCAN:
SELECT CustNum, Name, SalesRep from PUB.Customer WHERE SalesRep IN ('KIK', 'SLS', 'BBB');