Re: Strange query result with Order by

christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0411300613.670c7fdb_at_posting.google.com>...
> I'm on win 2K with Oracle 9.2.0.1.0
> I'm the only one who work on the data
> so, its not a commit issue ...
> and its repeatable, one week after the insertion of the records ...
> the second row "disappear" just when i add the "ORDER BY hev_annee_deb
> DESC"
> clause ... without DESC its OK ...
>
> I'm on a LM tablespace and here's the script for the table
>
> CREATE TABLE HIS_EVAL
> (
> HEV_ANNEE_DEB NUMBER(4),
> HEV_ANNEE_FIN NUMBER(4),
> HEV_ID_UEF NUMBER(12),
> HEV_VALEUR_T NUMBER(15),
> HEV_VALEUR_B NUMBER(15),
> HEV_PCT_VARIATION VARCHAR2(15 BYTE),
> HEV_DATE_CREATED DATE DEFAULT SYSDATE
> NOT NULL,
> HEV_DATE_MODIFIED DATE
> )
> TABLESPACE USER_DATA
> PCTUSED 40
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> PCTINCREASE 0
> FREELISTS 1
> FREELIST GROUPS 1
> BUFFER_POOL DEFAULT
> )
> LOGGING
> NOCACHE
> NOPARALLEL;
>
> thanks for helping
> Chris
>
> Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0411251412.7ab90762_at_posting.google.com>...
> > christianboivin1_at_hotmail.com (Chris) wrote in message news:<da20daf0.0411250530.4d7a6d3a_at_posting.google.com>...
> > > Hi all,
> > >
> > > First time i see this ... when i run this query :
> > >
> > > SELECT hev_annee_deb, hev_annee_fin, hev_id_uef, hev_valeur_t,
> > > hev_valeur_b,
> > > NVL (hev_valeur_t, 0) + NVL (hev_valeur_b, 0) valeur_totale,
> > > HEV_PCT_VARIATION
> > > FROM HIS_EVAL
> > > WHERE hev_id_uef = 1449902
> > > ORDER BY hev_annee_deb DESC
> > >
> > > i got 1 row ...
> > >
> > > 2002,2002,1449902,23500,84700,108200,
> > >
> > > if i remove the descending order ... like this
> > >
> > > SELECT hev_annee_deb, hev_annee_fin, hev_id_uef, hev_valeur_t,
> > > hev_valeur_b,
> > > NVL (hev_valeur_t, 0) + NVL (hev_valeur_b, 0) valeur_totale,
> > > HEV_PCT_VARIATION
> > > FROM HIS_EVAL
> > > WHERE hev_id_uef = 1449902
> > > ORDER BY hev_annee_deb
> > >
> > > i got 2 row ...
> > >
> > > 2001,2001,1449902,23500,61700,85200,
> > > 2002,2002,1449902,23500,84700,108200,
> > >
> > > What's the problem ... ?
> > > Thanks
> > > Chris
> >
> > Chris,
> > First, what version of Oracle? On what platform?
> > Second, is this repeatable? As shown the result could be due to
> > someone inserting a row and committing it after you first query
> > started. The second query would then show the new row.
> >
> > HTH -- Mark D Powell --

The 9.2.0.2 patchset lists two bugs fixed for wrong results where PQO is used with nls_sorts and another related to incorrect results when a descending index is used: Bug# 1231199, 1951553, and 2183412 respectively. Explain plans should show if PQO or a descending index are being used to solve the queries.

I also ran into a bug report where a query returned wrong results however when traced it returned correct results even though the explain plans appeared to be the same.

If none of the above appear to apply run a trace on both the working and non-working query then open an iTAR with Oracle.

HTH -- Mark D Powell -- Received on Tue Nov 30 2004 - 20:51:58 CST