Rhaoni,
> I could .. but this way I wont be used because Oracle doesn't accept such
> sintax ! I changed gsames00.ano_mes from varchar to text ! But it still not
> fast enough to take Oracle's place !!!
> I still trying to do so ...
Well, your basic problem is that performance tuning for *any* da
Rhaoni,
> ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
>Then it uses the gsames00 index instead of a SeqScan 'cuz it is
> camparing same data type, but .. I don't want to create this function 'cuz
> this aplication is used with Oracle too.
You should have said that
Rhaoni Chiu Pereira <[EMAIL PROTECTED]> writes:
> I need to know if there is a way to set the to_char output to varchar instead of
> text !
Why don't you change the datatype of ano_mes to text, instead? It's
unlikely your application would notice the difference. (You could set
a CHECK constraint
I solve this problem doing this:
create function date_to_mm( timestamp ) returns gsames00.ano_mes%type as
'select to_char($1, ''MM'');
' language sql immutable strict;
And changing the SQL where clause:
... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
to:
...
Rhaoni,
First off, thanks for posting such complete info.
>... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'MM') AND ...
>
>ftnfco00.data_emissao is a timestamp. When I run the explain analyze it
says:
>
> ...
> -> Seq Scan on gsames00 (cost=1.00..10006.72 rows=
Hi list,
I have a table like this:
CREATE TABLE "gsames00" (
"ano_mes" varchar(6) NOT NULL,
"descricao" varchar(30),
PRIMARY KEY ("ano_mes")
);
and an index like this:
CREATE INDEX GSAMES01 ON GSAMES00 (ANO_MES);
When I run a explain analyze with this where claus