Quem é mais rápido na comparação de string ? Like ou = ?
Olá pessoal,
pintou uma dúvida aqui no trabalho, afinal, quem é mais rápido numa comparação de string?
Vide exemplo:
select * from alunos a
where a.nome like ´Paulo´ --(ATENÇÂO, digo o like sem o ¬, ou seja, com o nome EXATO);
ou
select * from alunos a
where a.nome = ´Paulo´
Quais dos dois é mais rápido ? ou são exatamente identicos ou parecidos?
pintou uma dúvida aqui no trabalho, afinal, quem é mais rápido numa comparação de string?
Vide exemplo:
select * from alunos a
where a.nome like ´Paulo´ --(ATENÇÂO, digo o like sem o ¬, ou seja, com o nome EXATO);
ou
select * from alunos a
where a.nome = ´Paulo´
Quais dos dois é mais rápido ? ou são exatamente identicos ou parecidos?
Fergus
Curtidas 0
Respostas
Rosterne
28/07/2006
Mesmo desempenho.
Ex:
SQL> select * from hr.employees where last_name = ´Smith´;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -----------
159 Lindsey Smith LSMITH
171 William Smith WSMITH
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=68)
1 0 TABLE ACCESS (FULL) OF ´EMPLOYEES´ (Cost=2 Card=1 Bytes=68
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
997 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from hr.employees where last_name like ´Smith´;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -----------
159 Lindsey Smith LSMITH
171 William Smith WSMITH
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=68)
1 0 TABLE ACCESS (FULL) OF ´EMPLOYEES´ (Cost=2 Card=1 Bytes=68
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
997 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Abraço.
Ex:
SQL> select * from hr.employees where last_name = ´Smith´;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -----------
159 Lindsey Smith LSMITH
171 William Smith WSMITH
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=68)
1 0 TABLE ACCESS (FULL) OF ´EMPLOYEES´ (Cost=2 Card=1 Bytes=68
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
997 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select * from hr.employees where last_name like ´Smith´;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- -------------------- ------------------------- -----------
159 Lindsey Smith LSMITH
171 William Smith WSMITH
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=68)
1 0 TABLE ACCESS (FULL) OF ´EMPLOYEES´ (Cost=2 Card=1 Bytes=68
)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
997 bytes sent via SQL*Net to client
364 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
Abraço.
GOSTEI 0