As I said pr eviously we had a problem, we fixed using NO_MERGE hint, The reason was the new view complexity in 9i now if you can't still solve your problem, I suggest you to set that parameter, and try. searching hidden parameters I found the name select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and substr(ksppinm,1,1) = '_'; try setting _complex_view_merging=FALSE Any way some body please knows where to do a search in this list? ----- Original Message ----- From: <ryan.gaffuri@xxxxxxx> To: <oracle-l@xxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx> Sent: Tuesday, March 16, 2004 11:38 AM Subject: Re: Re: cardinality in query plans? > we are not using views. > > > > From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx> > > Date: 2004/03/16 Tue AM 10:24:20 EST > > To: <oracle-l@xxxxxxxxxxxxx> > > Subject: Re: cardinality in query plans? > > > > Hi, you are using views > > there is a hidden parameter to disable new cbo feature for view complexity, > > I think this will solve your problem, but I don't remember the name. > > ----- Original Message ----- > > From: <ryan.gaffuri@xxxxxxx> > > To: <oracle-l@xxxxxxxxxxxxx> > > Sent: Monday, March 15, 2004 9:50 AM > > Subject: cardinality in query plans? > > > > > > > I'm doing a two table join and both tables are analyzed. Oracle is > > incorrectly choosing a full tables scan over an index search(I tested it, > > index search has 1/4 the logical I/Os). > > > > > > When I see the cardinality for the full tablescan I see 262,000. However, > > when I do a count(*) of the table or check num_rows in dba_tables I see > > 870,000 records. I'm assuming this is why Oracle is choosing the full table > > scan. > > > > > > table is analyzed as follows: > > > > > > exec dbms_stats.gather_table_stats(cascade=>true). > > > > > > this interesting part is that when I use bind variables Oracle chooses the > > proper plan. Any ideas? > > > > > > ---------------------------------------------------------------- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > > ---------------------------------------------------------------- > > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > > > put 'unsubscribe' in the subject line. > > > -- > > > Archives are at //www.freelists.org/archives/oracle-l/ > > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > > > ----------------------------------------------------------------- > > > > > > > > > ---------------------------------------------------------------- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > ---------------------------------------------------------------- > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- > > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------