Re: Query gives incorrect results

This last case seems related to cost-based optimizer. Try again after  EXECUTE sys.dbms_utility.analyze_schema ('SYS', method=>'COMPUTE'); or
 EXECUTE sys.dbms_utility.analyze_database (method=>'COMPUTE');

The first case (with the SUBSTR inside a TO_DATE pictute string) is persistent in my database. I don't see how it can be a consequence of upgrade from Oracle7 as that was only an import of application data (so it was a migration, not an upgrade). Could the platform make any difference (Intel/Linux)? It is a pity that I still can not see the information in metalink (bugnr 2977019).

Edzard

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<RVyDa.602$BG1.327_at_news01.roc.ny.frontiernet.net>...
> This might just be a BUG/problem related to your 7 -> 9i upgrade.
> I do not see problems with any of the queries you posted in our 9.2.0.3 db's.
>
> Anurag
>
> "Edzard" <edzard_at_volcanomail.com> wrote in message news:5d75e934.0306040440.7cb23b11_at_posting.google.com...
> > Here is another case. Again it is an artificial query, written just to
> > reproduce certain behaviour of 9.2.0.3 RDBMS.
> >
> > SELECT COUNT (*)
> > FROM dba_indexes i, dba_ind_columns ic, dba_tab_columns c
> > WHERE i.table_owner = 'SYS'
> > AND ic.index_name = i.index_name
> > AND ic.table_name = i.table_name
> > AND ic.table_owner = i.table_owner
> > AND ic.column_position =
> > (SELECT MIN (ic2.column_position)
> > FROM dba_ind_columns ic2
> > WHERE ic2.index_name = ic.index_name
> > AND ic2.table_name = ic.table_name
> > AND ic2.table_owner = ic.table_owner)
> > AND c.table_name = ic.table_name
> > AND c.owner = ic.table_owner
> > AND c.column_name = ic.column_name
> > AND c.data_type = 'VARCHAR2'
> > /
> >
> > COUNT(*)
> > ----------
> > 306
> >
> > Now change COUNT (*) to * and at the bottom of the output you get:
> >
> > 51 rows selected.
Received on Thu Jun 05 2003 - 04:34:15 CDT