Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
"NOT" / "<>" and Index Usage |
Thu, Jul 3 2014 4:43 AM | Permanent Link |
durumdara | Dear Support!
Can the engine use index on "non-equal" filter expression? F. e: Create Table(ID integer not null primary key); I write: where (ID <> 5) or where not (ID = 5) Would engine use the primary index somehow? Cos I read in an older SQL book that the "NOT" / "<>" forces some RDBMS to ignore index, because they cannot force the ranged (from-to) logic in index search. As I saw the engine substitutes "NOT" with "<>". In boolean logic I might use "(somebooleanfield = False)" for "not (somebooleanfield)" / "(somebooleanfield <> True)" But in other (numeric, string) expressions I cannot do same substitution. That's why I asked about index usage - how can I calculate with query's speed after index creation. Thank you for any information about it! Regards dd |
Thu, Jul 3 2014 5:23 AM | Permanent Link |
Uli Becker | Hi,
> Can the engine use index on "non-equal" filter expression? Yes, but you can test that yourself by having a look at the execution plan, e.g.: Filtering --------- The following filter condition was applied to the anforderungen table: "gruppe" <> 'IM3' Index scan (Anforderungen.Gruppe_INX): 25914 keys, 396KB estimated cost ================================================================================ 26069 row(s) returned in 0 secs ================================================================================ Uli |
Thu, Jul 3 2014 5:31 AM | Permanent Link |
Matthew Jones | Uli Becker wrote:
> Index scan That being the important part - took me a while to understand that. If it says "Row scan" then there is no index in use. -- Matthew Jones |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |