Thread: How to force an Index ?

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 clause:

   ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...

   ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says:

...
 ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372 width=10)
(actual time=0.01..0.96 rows=372 loops=19923)
...

   So it is not using the index, and it makes the query too slow to return the
result. If a run the same query without this clause it gets about 1 minute
faster. You you're wondering : If you can run this query without this clause,
Why don't you take it out ?
   I must use it because this query is created by a BI software  and to
change it, I'll have to make a lot of changes in the BI software source. In the
Oracle DB it works fine 'cuz Oracle use the index and do it instantly.
   Any suggestion on how to force PostgreSQL to use this index ???
   I run  Vaccum Full Analyze many time before posting this ...

Here follow the whole query and the whole explain:

Query:

SELECT /*+  */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)),       '+', NVL(ftnfpr00.qtde_duzias,0),       0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+',
NVL(ftnfpr00.vlr_liquido,0), 0) ) ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto ,
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
                         SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) )
FROM
ftprod00 ,
ftnfco00 ,
ftcgma00 ,
ftcgca00 ,
ftspro00 ,
ftclcr00 ,
gsames00 ,
ftcofi00 ,
ftrepr00 ,
gsesta00 ,
ftsupv00 ,
ftgrep00 ,
ftclgr00 ,
ftband00 ,
fttcli00 ,
ftredc00 ,
ftnfpr00
WHERE
ftnfco00.emp = 909 AND
ftnfpr00.fil IN ('101') AND
ftnfco00.situacao_nf = 'N' AND
ftnfco00.data_emissao >= CAST('01-JAN-2003' AS DATE) AND
ftnfco00.data_emissao <= CAST('31-MAR-2003' AS DATE) AND
ftcofi00.grupo_faturamento >= '01' AND
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND
ftcgma00.emp    = ftprod00.emp AND
ftcgma00.fil    = ftprod00.fil AND
ftcgma00.codigo = ftprod00.cla_marca AND
ftcgca00.emp    = ftprod00.emp AND
ftcgca00.fil    = ftprod00.fil AND
ftcgca00.codigo = ftprod00.cla_categoria AND
ftspro00.emp    = ftprod00.emp AND
ftspro00.fil    = ftprod00.fil AND
ftspro00.codigo = ftprod00.situacao AND
ftclcr00.emp           = ftnfco00.emp AND
ftclcr00.fil           = ftnfco00.empfil AND
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND
ftclcr00.codigo        = ftnfco00.cod_cliente AND
gsames00.ano_mes       = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND
ftcofi00.emp           = ftnfco00.emp AND
ftcofi00.fil           = ftnfco00.empfil AND
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND
ftrepr00.emp           = ftnfco00.emp AND
ftrepr00.fil           = ftnfco00.empfil AND
ftrepr00.codigo_repr   = ftnfco00.cod_repres AND
gsesta00.estado_sigla  = ftnfco00.estado_cliente AND
ftsupv00.emp               = ftrepr00.emp AND
ftsupv00.fil               = ftrepr00.fil AND
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND
ftgrep00.emp               = ftrepr00.emp AND
ftgrep00.fil               = ftrepr00.fil AND
ftgrep00.codigo_grupo_rep  = ftrepr00.codigo_grupo_rep AND
ftclgr00.emp               = ftclcr00.emp AND
ftclgr00.fil               = ftclcr00.fil AND
ftclgr00.codigo            = ftclcr00.codigo_grupo_cliente AND
ftband00.emp               = ftclcr00.emp AND
ftband00.fil               = ftclcr00.fil AND
ftband00.codigo            = ftclcr00.bandeira_cliente AND
fttcli00.emp               = ftclcr00.emp AND
fttcli00.fil               = ftclcr00.fil AND
fttcli00.cod_tipocliente   = ftclcr00.codigo_tipo_cliente AND
ftredc00.emp               = ftclcr00.emp AND
ftredc00.fil               = ftclcr00.fil AND
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND
ftredc00.codigo_rede       = ftclcr00.codigo_rede AND
gsesta00.estado_sigla      = ftclcr00.emp_estado AND
ftnfco00.emp           = ftnfpr00.emp AND
ftnfco00.fil           = ftnfpr00.fil AND
ftnfco00.nota_fiscal   = ftnfpr00.nota_fiscal AND
ftnfco00.serie         = ftnfpr00.serie AND
ftnfco00.data_emissao  = ftnfpr00.data_emissao AND
ftprod00.emp           = ftnfpr00.emp AND
ftprod00.fil           = ftnfpr00.empfil AND
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND
ftprod00.codigo_produto= ftnfpr00.cod_produto
GROUP BY
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto



Explain:



                       QUERY
PLAN


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------
 Aggregate  (cost=100027780.66..100027780.69 rows=1 width=818) (actual
time=101278.24..105839.69 rows=363 loops=1)
   ->  Group  (cost=100027780.66..100027780.68 rows=1 width=818) (actual
time=101272.08..101761.18 rows=19923 loops=1)
         ->  Sort  (cost=100027780.66..100027780.67 rows=1 width=818) (actual
time=101272.05..101299.09 rows=19923 loops=1)
               Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento,
((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text),
ftprod00.descricao_produto
               ->  Nested Loop  (cost=100025960.94..100027780.65 rows=1
width=818) (actual time=3476.87..99606.77 rows=19923 loops=1)
                     Join Filter: (("outer".emp = "inner".emp) AND ("outer".fil
= "inner".fil) AND ("outer".codigo_supervisor = "inner".codigo_supervisor) AND
("outer".codigo_grupo_rep = "inner".codigo_grupo_rep))
                     ->  Nested Loop  (cost=100025960.94..100027775.22 rows=1
width=765) (actual time=3476.74..97802.69 rows=19923 loops=1)
                           Join Filter: (("inner".ano_mes)::text = to_char
("outer".data_emissao, 'YYYYMM'::text))
                           ->  Nested Loop  (cost=25960.94..27762.92 rows=1
width=755) (actual time=3475.14..32090.12 rows=19923 loops=1)
                                 Join Filter: (("inner".emp = "outer".emp) AND
("outer".fil = "inner".fil) AND ("outer".codigo = "inner".cla_categoria) AND
("outer".codigo = "inner".cla_marca) AND ("outer".codigo = "inner".situacao))
                                 ->  Nested Loop  (cost=25960.94..27705.22
rows=10 width=665) (actual time=3474.12..17734.21 rows=199230 loops=1)
                                       Join Filter: (("outer".emp
= "inner".emp) AND ("inner".fil = "outer".fil))
                                       ->  Nested Loop
(cost=25960.94..27699.30 rows=1 width=638) (actual time=3474.02..6030.09
rows=19923 loops=1)
                                             Join Filter: (("inner".emp
= "outer".emp) AND ("inner".empfil = "outer".fil))
                                             ->  Merge Join
(cost=25960.94..26128.25 rows=265 width=526) (actual time=3473.78..3841.18
rows=6358 loops=1)
                                                   Merge Cond: (("outer".emp
= "inner".emp) AND ("outer".fil = "inner".fil) AND ("outer".codigo_fiscal
= "inner".cod_fiscal))
                                                   ->  Index Scan using
ftcofi01 on ftcofi00  (cost=0.00..151.73 rows=72 width=52) (actual
time=0.15..6.40 rows=64 loops=1)
                                                         Filter:
((grupo_faturamento >= '01'::character varying) AND ((atual_fatura
= '+'::character varying) OR (atual_fatura = '-'::character varying) OR
(nf_prodgratis = 'S'::character varying)))
                                                   ->  Sort
(cost=25960.94..25965.34 rows=1760 width=474) (actual time=3471.17..3486.98
rows=7666 loops=1)
                                                         Sort Key:
ftnfco00.emp, ftredc00.fil, ftnfco00.cod_fiscal
                                                         ->  Nested Loop
(cost=25687.75..25866.07 rows=1760 width=474) (actual time=2981.05..3241.15
rows=7666 loops=1)
                                                               Join Filter:
(("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil) AND
("outer".codigo = "inner".codigo_grupo_cliente))
                                                               ->  Index Scan
using ftclgr01 on ftclgr00  (cost=0.00..4.68 rows=1 width=32) (actual
time=0.04..0.06 rows=1 loops=1)
                                                               ->  Materialize
(cost=25830.59..25830.59 rows=1760 width=442) (actual time=2980.93..2990.31
rows=7666 loops=1)
                                                                     ->  Hash
Join  (cost=25687.75..25830.59 rows=1760 width=442) (actual
time=2507.55..2945.35 rows=7666 loops=1)
                                                                           Hash
Cond: ("outer".emp_estado = "inner".estado_sigla)
                                                                           ->
Nested Loop  (cost=25683.33..25790.98 rows=1760 width=436) (actual
time=2507.09..2711.66 rows=7666 loops=1)

 Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil))

 ->  Index Scan using ftgrep01 on ftgrep00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.05..0.07 rows=1 loops=1)

 ->  Materialize  (cost=25759.91..25759.91 rows=1760 width=404) (actual
time=2506.98..2516.14 rows=7666 loops=1)

       ->  Nested Loop  (cost=25683.33..25759.91 rows=1760 width=404) (actual
time=2288.68..2474.11 rows=7666 loops=1)

             Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil
= "outer".fil))

             ->  Index Scan using ftsupv01 on ftsupv00  (cost=0.00..4.68 rows=1
width=32) (actual time=0.04..0.05 rows=1 loops=1)

             ->  Materialize  (cost=25728.83..25728.83 rows=1760 width=372)
(actual time=2288.58..2297.79 rows=7666 loops=1)

                   ->  Merge Join  (cost=25683.33..25728.83 rows=1760
width=372) (actual time=2086.89..2265.03 rows=7666 loops=1)

                         Merge Cond: (("outer".emp = "inner".emp) AND
("outer".fil = "inner".fil) AND ("outer".cod_tipocliente
= "inner".codigo_tipo_cliente))

                         ->  Index Scan using fttcli01 on fttcli00
(cost=0.00..5.85 rows=17 width=33) (actual time=0.03..0.25 rows=17 loops=1)

                         ->  Sort  (cost=25683.33..25687.73 rows=1760
width=339) (actual time=2086.71..2095.86 rows=7666 loops=1)

                               Sort Key: ftnfco00.emp, ftredc00.fil,
ftclcr00.codigo_tipo_cliente

                               ->  Nested Loop  (cost=25389.10..25588.46
rows=1760 width=339) (actual time=1729.53..1897.73 rows=7666 loops=1)

                                     Join Filter: (("inner".emp = "outer".emp)
AND ("inner".fil = "outer".fil) AND ("outer".codigo = "inner".bandeira_cliente))

                                     ->  Index Scan using ftband01 on ftband00
(cost=0.00..4.68 rows=1 width=32) (actual time=0.04..0.06 rows=1 loops=1)

                                     ->  Materialize  (cost=25552.99..25552.99
rows=1760 width=307) (actual time=1729.44..1738.69 rows=7666 loops=1)

                                           ->  Nested Loop
(cost=25389.10..25552.99 rows=1760 width=307) (actual time=1566.24..1705.51
rows=7666 loops=1)

                                                 Join Filter: (("inner".emp
= "outer".emp) AND ("inner".fil = "outer".fil))

                                                 ->  Index Scan using ftcgma01
on ftcgma00  (cost=0.00..4.68 rows=1 width=32) (actual time=0.03..0.05 rows=1
loops=1)

                                                 ->  Materialize
(cost=25521.91..25521.91 rows=1760 width=275) (actual time=1566.16..1575.29
rows=7666 loops=1)

                                                       ->  Merge Join
(cost=25389.10..25521.91 rows=1760 width=275) (actual time=1320.59..1542.54
rows=7666 loops=1)

                                                             Merge Cond:
(("outer".codigo = "inner".cod_cliente) AND ("outer".emp_estado
= "inner".estado_cliente) AND ("outer".tipo_cadastro = "inner".tipo_cad_clicre)
AND ("outer".fil = "inner".empfil) AND ("outer".emp = "inner".emp))

                                                             ->  Sort
(cost=6241.05..6269.31 rows=11304 width=166) (actual time=1093.04..1105.44
rows=10478 loops=1)

                                                                   Sort Key:
ftclcr00.codigo, ftclcr00.emp_estado, ftclcr00.tipo_cadastro, ftredc00.fil,
ftredc00.emp

                                                                   ->  Merge
Join  (cost=3920.20..5480.05 rows=11304 width=166) (actual time=516.40..951.73
rows=10956 loops=1)

                                                                         Merge
Cond: (("outer".emp = "inner".emp) AND ("outer".fil = "inner".fil) AND
("outer".tipo_contribuinte = "inner".tipo_contribuinte) AND
("outer".codigo_rede = "inner".codigo_rede))

                                                                         ->
Merge Join  (cost=0.00..1256.74 rows=8906 width=72) (actual time=0.13..180.25
rows=8906 loops=1)


Merge Cond: ("outer".emp = "inner".emp)

                                                                               -
>  Index Scan using ftredc01 on ftredc00  (cost=0.00..1118.47 rows=8906
width=40) (actual time=0.05..72.02 rows=8906 loops=1)

                                                                               -
>  Index Scan using ftcgca01 on ftcgca00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.04..19.14 rows=1 loops=1)

                                                                         ->
Sort  (cost=3920.20..3947.59 rows=10956 width=94) (actual time=516.19..529.77
rows=10956 loops=1)


Sort Key: ftclcr00.emp, ftclcr00.fil, ftclcr00.tipo_contribuinte,
ftclcr00.codigo_rede

                                                                               -
>  Index Scan using ftclcr07 on ftclcr00  (cost=0.00..3185.08 rows=10956
width=94) (actual time=0.09..146.20 rows=10956 loops=1)

                                                             ->  Sort
(cost=19148.05..19167.27 rows=7688 width=109) (actual time=227.46..237.00
rows=7668 loops=1)

                                                                   Sort Key:
ftnfco00.cod_cliente, ftnfco00.estado_cliente, ftnfco00.tipo_cad_clicre,
ftnfco00.empfil, ftnfco00.emp

                                                                   ->  Index
Scan using ftnfco06 on ftnfco00  (cost=0.00..18651.88 rows=7688 width=109)
(actual time=0.16..116.43 rows=7668 loops=1)

                                                                         Index
Cond: ((emp = 909::numeric) AND (situacao_nf = 'N'::character varying) AND
(data_emissao >= '2002-10-01 00:00:00'::timestamp without time zone) AND
(data_emissao <= '2003-03-31 00:00:00'::timestamp without time zone))
                                                                           ->
Hash  (cost=4.33..4.33 rows=33 width=6) (actual time=0.23..0.23 rows=0 loops=1)

 ->  Index Scan using gsesta01 on gsesta00  (cost=0.00..4.33 rows=33 width=6)
(actual time=0.04..0.15 rows=33 loops=1)
                                             ->  Index Scan using ftnfpr05 on
ftnfpr00  (cost=0.00..5.91 rows=1 width=112) (actual time=0.06..0.15 rows=3
loops=6358)
                                                   Index Cond: (("outer".emp =
ftnfpr00.emp) AND ("outer".fil = ftnfpr00.fil) AND (ftnfpr00.fil =
101::numeric) AND ("outer".data_emissao = ftnfpr00.data_emissao) AND
("outer".nota_fiscal = ftnfpr00.nota_fiscal) AND ("outer".serie =
ftnfpr00.serie))
                                       ->  Index Scan using ftspro01 on
ftspro00  (cost=0.00..5.78 rows=10 width=27) (actual time=0.01..0.07 rows=10
loops=19923)
                                 ->  Index Scan using ftprod01 on ftprod00
(cost=0.00..5.74 rows=1 width=90) (actual time=0.04..0.05 rows=1 loops=199230)
                                       Index Cond: ((ftprod00.emp
= "outer".emp) AND (ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro
= "outer".tipo_cad_promat) AND (ftprod00.codigo_produto = "outer".cod_produto))
                           ->  Seq Scan on gsames00
(cost=100000000.00..100000006.72 rows=372 width=10) (actual time=0.01..0.96
rows=372 loops=19923)
                     ->  Index Scan using ftrepr01 on ftrepr00
(cost=0.00..5.41 rows=1 width=53) (actual time=0.04..0.05 rows=1 loops=19923)
                           Index Cond: ((ftrepr00.emp = "outer".emp) AND
(ftrepr00.fil = "outer".empfil) AND (ftrepr00.codigo_repr = "outer".cod_repres))
 Total runtime: 105885.43 msec
(75 rows)



The Oracle functions like NVL, DECODE, and others had been created in
PostgreSQL.


Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122

Rhaoni,

First off, thanks for posting such complete info.

>    ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
>
>    ftnfco00.data_emissao is a timestamp. When I run the explain analyze it
says:
>
> ...
>  ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372
width=10)
> (actual time=0.01..0.96 rows=372 loops=19923)
> ...

Your problem is that you're comparing against a calculated expression based on
ftnfco00, which is being filtered in about 18 other ways.  As a result, the
planner doesn't know what to estimate (see the cost estimate of 100000000,
which is a "blind guess" values) and goes for a seq scan.

 Can I ask you to try this workaround, to create an expressional index on
ftnfco00 (assuming that data_emmisao is of type DATE)

create function date_to_yyyymm( date ) returns text as
'select to_char($1, ''YYYYMM'');
' language sql immutable strict;

create index idx_data_yyyymm on ftnfco00(date_to_yyyymm(data_emmisao));

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

I solve this problem doing this:

 create function date_to_yyyymm( timestamp ) returns gsames00.ano_mes%type as
 'select to_char($1, ''YYYYMM'');
 ' language sql immutable strict;

And changing the SQL where clause:

 ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...

to:

 ... gsames00.ano_mes = date_to_yyyymm(ftnfco00.data_emissao) 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.
I need to know if there is a way to set the to_char output to varchar instead of
text !
  Any Idea ? So, this way I wont have to change my aplication source.



Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122



Citando Josh Berkus <josh@agliodbs.com>:

<> Rhaoni,
<>
<> First off, thanks for posting such complete info.
<>
<> >    ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
<> >
<> >    ftnfco00.data_emissao is a timestamp. When I run the explain analyze it
<>
<> says:
<> >
<> > ...
<> >  ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372
<> width=10)
<> > (actual time=0.01..0.96 rows=372 loops=19923)
<> > ...
<>
<> Your problem is that you're comparing against a calculated expression based
<> on
<> ftnfco00, which is being filtered in about 18 other ways.  As a result, the
<>
<> planner doesn't know what to estimate (see the cost estimate of 100000000,
<> which is a "blind guess" values) and goes for a seq scan.
<>
<>  Can I ask you to try this workaround, to create an expressional index on
<> ftnfco00 (assuming that data_emmisao is of type DATE)
<>
<> create function date_to_yyyymm( date ) returns text as
<> 'select to_char($1, ''YYYYMM'');
<> ' language sql immutable strict;
<>
<> create index idx_data_yyyymm on ftnfco00(date_to_yyyymm(data_emmisao));
<>
<> --
<> -Josh Berkus
<>  Aglio Database Solutions
<>  San Francisco
<>
<>
<> ---------------------------(end of broadcast)---------------------------
<> TIP 4: Don't 'kill -9' the postmaster
<>

Rhaoni Chiu Pereira <rhaoni@sistemica.info> 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 on the length if you really want to duplicate the
behavior of varchar(6).)

Alternatively, try 7.4 beta. I believe this issue goes away in 7.4,
because varchar no longer has separate comparison operators.

Of course there's also the option of modifying to_char's result type
in pg_proc, but I won't promise that doing so wouldn't break things.

            regards, tom lane

Rhaoni,

>  ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') 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 earlier ....

> I need to know if there is a way to set the to_char output to varchar
> instead of text !

Did you try:

... gsames00.ano_mes = (to_char(ftnfco00.data_emissao,'YYYYMM')::VARCHAR) AND
...


--
Josh Berkus
Aglio Database Solutions
San Francisco

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* database often
requires use of database-specific syntax.   You would be having the same
problem, in the opposite direction, if you were trying to port a PostgreSQL
app to Oracle without changing any syntax.

Here's syntax Oracle should accept:

... gsames00.ano_mes = (CAST(to_char(ftnfco00.data_emissao,'YYYYMM') AS
VARCHAR)) AND
...


--
Josh Berkus
Aglio Database Solutions
San Francisco