You could read the docs and find out that you should never analyze the sys
schema.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Edzard" <edzard_at_volcanomail.com> wrote in message
news:5d75e934.0306050134.28f5e17d_at_posting.google.com...
> 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 - 09:17:56 CDT
