simple query tunig

Tuning de Banco, Tuning de SQL, Ferramentas de tuning

Poston Tue, 06 May 2014 2:35 pm

Guys, good afternoon.
I have a doubt on a very simple query.

Code: Select all
select *
  from cadastro_pessoa
where replace(replace(replace(num_ident,'.',''),'/',''),'-','') = '5478856321';
This select has an id which is id_cad_pessoa, but actually seek data through num_ident, which is the Id or SSN and this field has dashes, dots and commas, so the replace.

Michae this query takes 15 seconds to return data, does anyone have an idea how I can fix this, I don't know a lot of tuning or almost nothing, could you help me?
Location: SP

Poston Tue, 06 May 2014 6:58 pm

Hi, grosso modo, the use of indexes that determines the query optimization.
You need both the correct construction of tables and indexes as correct use of joins in relationships.
In your case, it is interesting to know if the " field num_ident " has some content.
Anyway, try to NEVER USE ADJUSTMENT UPON FIELDS with indexes to do joins.
When it's done, how you did it:
Code: Select all
replace(replace(replace(num_ident,'.',''),'/',''),'-','') = '5478856321';
any index that had in this field would be ignored because of the overlapping functions.
Location: Fortaleza - CE


Daniel N.N.

Poston Wed, 07 May 2014 6:27 pm

@ccguedes there are various types of indexes in Oracle Database, and only 1 of them is that you can use to optimize your query, if in this case you cannot eliminate the functions on column " num_ident ". Create a function-based index. Below is an example of how to create it:
Code: Select all
CREATE INDEX SCHEMA.IX_NOME ON TABELA([b]replace(replace(replace(num_ident,'.',''),'/',''),'-','') = '5478856321' [/b]);
[] S
Location: São Paulo - SP

Fábio Prado

Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest