Hi!
Attached to e-mail I've sent an VB app to do some performance
considerations...
If, in query I put "select * from tbalunocadastro order by nome", clicking
button "Last" and after, "previous" takes A LOT of time to be processed...
Well, goint to the point: my table tbalunocadastro is something like:
CREATE TABLE tbAlunoCadastro (
AL_Codigo char(10) NOT NULL primary key ,
Nome varchar(50) NOT NULL ,
Endereco varchar(50) NOT NULL ,
Bairro varchar(25) NOT NULL ,
Cidade varchar(25) NOT NULL ,
Estado char(2) NOT NULL ,
CEP char(8) NOT NULL ,
Fone1 varchar(25) NOT NULL ,
Fone2 varchar(25) NOT NULL ,
FoneRecado varchar(25) NOT NULL ,
FAX varchar(20) NOT NULL ,
Sexo char(1) NOT NULL ,
EstadoCivil char(1) NOT NULL ,
DataNascimento timestamp NOT NULL ,
CidadeNascimento varchar(25) NULL ,
EstadoNascimento varchar(25) NULL ,
PaisNascimento varchar(25) NULL ,
ExAluno char(1) NOT NULL ,
senha varchar(25) NULL ,
EMail varchar(50) NULL
)
GO
CREATE INDEX ind_tbalunocad_diginotas ON tbAlunoCadastro(AL_Codigo, Nome)
GO
CREATE INDEX ind_tbAlunoCadastro_nome ON tbAlunoCadastro(Nome)
GO
When I execute
"explain select count(nome) from tbalunocadastro"
I get "INDEX SCAN", but when I execute
"explain select * from tbalunocadastro order by nome"
I get a TABLE SCAN!!! I think that this is the response for why this is a
problem... And I think that this is why my app is too slow (see remarks at
the start of the mail)...
Thanks,
Edson Carlos Ericksson Richter
Gerente de Tecnologia
ECONET Solu��es Web
+55 61 326 5115
-----Mensagem original-----
De: Zabach, Elke [mailto:[EMAIL PROTECTED]]
Enviada em: sexta-feira, 10 de maio de 2002 02:53
Para: 'Edson Carlos Ericksson Richter'; SapDB
Assunto: RE: Use of index with order by
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
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.361 / Virus Database: 199 - Release Date: 07/05/02
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.361 / Virus Database: 199 - Release Date: 07/05/02
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
