> Hi, > I have a table A with two identical, integer, nullable columns col1 and > col2, both of which are indexed. > The table A has 100,000 rows and takes up 11,000 blocks. > The indexes on the two columns take about 225 leaf blocks each with > BLevel=1. > The optimizer goal is set to CHOOSE and the table analyzed with compute > stats. > Oracle version 8.1.7 on Win' 2000. > > Right, when I now run the query: > > SELECT COUNT(col1) > FROM A > WHERE col1=col2; > > the CBO is doing a full table scan on A (with 11000 physical reads) > rather than just joining > the two indexes to get the result. > > I have two questions: > 1. Why is the CBO not choosing to join the indexes by default? > 2. When I gave an INDEX_JOIN hint > /*+ INDEX_JOIN (A COL1IDX COL2IDX) */, > it did do a hash join of the two indexes which was faster but still > took about 6500 physical > reads while the two indexes put together take only about 452 > blocks. Any idea what the > extra disk reads are doing? > > Thanks in advance, > Ram > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramakrishna V INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
