Edson Carlos Ericksson Richter wrote: > I have created a table as > > create table teste ( > codigo varchar(10) not null primary key, > nome varchar(100) not null) > > then an index > > create index ndx_teste_nome on teste (nome) > > > When I execute > > explain select * from teste order by nome > > > or I execute > > > explain select * from teste order by codigo > > > or I execute > > > explain select count(nome) from teste > > > I see "SINGLE INDEX COLUMN USED (INDEX SCAN) > > But when I execute > > explain select count(codigo) from teste > > > I get a TABLE SCAN (should not be using primary key?)!!!
Mhm, where is the problem? Ooh, you believe, that PRIMARY KEY is implemented using an index, don't you? Then I understand your astonishing. But: SAP DB does not implement the PRIMARY KEY using an index. The table itself is ordered according to the specified primary key columns. Therefore: you do not have an index in that case, which could be used. If there is a chance that the space needed for storing the index specified on nome is smaller than the space needed for the table itself, then you can say SELECT count(nome) from teste. If you will use COUNT(*) (all of these 3 version should result in the same number) then the SAP DB optimizer should use that index (if it is smaller than the table itself--> less I/O). But I remember that there were/are versions of SAP DB in which the optimizer did not do it. Elke SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
