Well YOU might be saying that, but is ORACLE understanding that...
Try adding two where clauses( and col1 is not null and col2 is not null )
and see what happens.
[EMAIL PROTECTED] 03/30/01 08:10AM
Hi,
I don't want it to count the rows with col1 as null. When I specify
col1=col2, I am implicitly saying that they are both non-null and equal to
each other.
regards,
ram
-Original Message-
Sent: Friday, March 30, 2001 6:25 PM
To: Multiple recipients of list ORACLE-L
Well, how will it count the rows where col1 is null if it just uses the
index? There are no entries in the index for those rows...
[EMAIL PROTECTED] 03/30/01 06:30AM
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
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).
--
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
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).
