ok, was confused on the cardinality. I thought I was onto something with that. No its version 9.2.0.3 I need to reformat the tables so I can change column and table names. tab1 has 16m rows tab2 has 870,000 rows. Here it is. Its very hard to format query plans over email. So please bare with me. I'm at a loss. I'm forced to hint in 9.2.0.3 and I do not know why. I have a simple two table joined. I have analyzed both tables as follows exec dbms_stats(cascade=>true). I know the hint is better because of your runstats output. Query two has the hints. SQL> exec runstats_pkg.rs_stop(500); Run1 ran in 1456 hsecs Run2 ran in 1288 hsecs run 1 ran in 113.04% of the time Name Run1 Run2 Diff LATCH.simulator hash latch 898 257 -641 STAT...redo size 72,380 73,300 920 STAT...table fetch by rowid 1 6,293 6,292 STAT...buffer is pinned count 0 7,942 7,942 STAT...session logical reads 14,490 5,535 -8,955 STAT...consistent gets 13,945 4,982 -8,963 STAT...no work - consistent re 13,897 4,927 -8,970 STAT...buffer is not pinned co 13,858 4,812 -9,046 STAT...table scan blocks gotte 13,856 168 -13,688 LATCH.cache buffers chains 30,587 12,668 -17,919 STAT...session uga memory max 0 27,592 27,592 STAT...table scan rows gotten 870,888 130 -870,758 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 33,803 14,975 -18,828 225.73% PL/SQL procedure successfully completed. The interesting thing is that if I use bind variables Oracle chooses the correct plan, if I don't oracle does not. I'm forced to change table and column names. So please bare with me. Here is the query and plan without hints or bind variables. I'm attempting to format this by hand so its readable. Hope it helps. Here is the query: select /*+ ordered index(a TAB2_IND) */ COL3, COL4, COL5 from TAB2 a, TAB1 b WHERE a.col1 = b.col1 AND a.col2 = b.col2 AND a.col3 = b.col3 and col6 = 'hello' and a.searchCol like upper('billy%') order by a.hh_nm, a.hh_id, a.acct_no Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=353 Card=1 Bytes=140) 1 0 VIEW (Cost=353 Card=1 Bytes=140) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=353 Card=1 Bytes=127) 4 3 WINDOW (SORT) (Cost=353 Card=1 Bytes=89) 5 4 WINDOW (SORT) (Cost=353 Card=1 Bytes=89) 6 5 HASH JOIN (Cost=351 Card=1 Bytes=89) 7 6 INDEX (RANGE SCAN) OF 'TAB1_PK'(UNIQUE) (Cost=11 Card=2250 Bytes=36000) 8 6 TABLE ACCESS (FULL) OF 'TAB2'(Cost=337 Card=252624 Bytes=18441552) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 13745 consistent gets 0 physical reads 0 redo size 3379 bytes sent via SQL*Net to client 662 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 25 rows processed CASE 2 Now with hints or bind variables: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=1 Bytes=140) 1 0 VIEW (Cost=346 Card=1 Bytes=140) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=346 Card=1 Bytes=127) 4 3 WINDOW (SORT) (Cost=346 Card=1 Bytes=89) 5 4 WINDOW (SORT) (Cost=346 Card=1 Bytes=89) 6 5 HASH JOIN (Cost=344 Card=1 Bytes=89) 7 6 INDEX (RANGE SCAN) OF 'TAB2_IND'(UNIQUE) (Cost=11 Card=2250 Bytes=36000) 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'TAB2' (Cost=337 Card=43539 Bytes=3178347) 9 8 INDEX (RANGE SCAN) OF 'TAB2_PK' (NON-UNIQUE) (Cost=44 Card=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4764 consistent gets 0 physical reads 0 redo size 3379 bytes sent via SQL*Net to client 662 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 25 rows processed Now its choosing the correct plan. The indexes are as follows TAB1_PK (col1,col2,col3,searchcol) TAB2_IND (searchCol,col1,col2,col3) > > From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> > Date: 2004/03/15 Mon AM 09:24:09 EST > To: <oracle-l@xxxxxxxxxxxxx> > Subject: Re: cardinality in query plans? > > > How about giving us a clue - > > Which version of Oracle ? > > Two tables - we could probably cope with > > select {small (subset) list of columns} > from {two table names} > where {list of join predicates} > and {list of filter predicates} > > And an execution plan on a two-table joins > could only be a maximum of 8 lines. > > I'll take a guess at the bind variable question, though: > your query is using the bind variables to specify a range. > > And if you used the same values for the binds as you > did for the original query, you must be on Oracle 8, > otherwise bind variable peeking would have produced > the same plan. > > The cardinality is the number of rows returned from > that step of the plan - not the number input - so with > a table of 8M rows, the cardinality could literally be > any integer between zero and 8M on the scan line. > > > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > April 2004 Iceland > June 2004 UK - Optimising Oracle Seminar > > > ----- Original Message ----- > From: <ryan.gaffuri@xxxxxxx> > To: <oracle-l@xxxxxxxxxxxxx> > Sent: Monday, March 15, 2004 1:50 PM > 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 -----------------------------------------------------------------