I'm wondering if someone can help me understand why a table scan is being done for the SQL below even though I've created what I thought would be appropriate indices. When I do a EXPLAIN QUERY PLAN on the following SQL: SELECT S.ScanID S.Date, S.RootDirID, S.OwningMonitorID, D.Path FROM Check S, Directory D WHERE S.RootDirID = D.DirID AND S.bHasFileInfo = 1 AND S.bComplete= 1 ORDER BY D.PATH ASC, S.DATE DESC it tells me it will do a table scan on Check and Directory CREATE TABLE Directory ( DirID INTEGER PRIMARY KEY, Path TEXT NOT NULL, ParentDirID INTEGER NOT NULL ); CREATE INDEX Ind_Directory_ParentDirID ON Directory (ParentDirID); CREATE UNIQUE INDEX Ind_Directory_Path ON Directory (Path); CREATE TABLE Check ( ScanID INTEGER PRIMARY KEY, Date INTEGER NOT NULL, OwningMonitorID INTEGER NOT NULL, RootDirID INTEGER NOT NULL, bHasDirInfo INTEGER NOT NULL, bHasFileInfo INTEGER NOT NULL, bHasOwnerInfo INTEGER NOT NULL, bComplete INTEGER NOT NULL ); CREATE UNIQUE INDEX Ind_Check_Date ON Check (Date); CREATE UNIQUE INDEX Ind_Check_RootDirID ON Check (RootDirID); What can I do so this statement will use the/an index? Thanks
To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
