RE: Why is Oracle choosing a different execution plan?

From: <JayMiller_at_TDWaterhouse.com>
Date: Fri, 14 Oct 2005 11:22:57 -0400
Message-ID: <5B257A26B4845C469B87871B6CEFE5070331AEEE@usnjc04wmx003.tdwaterhouse.com>

Also note that in case 2 it's still reading the entire index (fast *full* scan). This will certainly be faster than a full table scan but implies that either the cardinality is very low so Oracle believes a full scan will be faster or that there's an issue with the index.

What %age of rows does 'J' take up (if you have a histogram on the column) or how many unique values of that column exist (if there is no histogram)?

Also, what columns is IDX_ODJ_ARCHIVIEREN indexed on?

Thanks,
Jay Miller
Sr. Oracle DBA
x68355

-----Original Message-----

From: Reidy, Ron [mailto:Ron.Reidy_at_arraybiopharma.com] Sent: Friday, October 14, 2005 6:11 AM
To: HELMUT.DAIMINGER_at_wwk.de; oracle-l_at_freelists.org Subject: RE: Why is Oracle choosing a different execution plan?

Because in query #1, you are asking for all columns. The optimizer sees this and makes the decision to FTS because (most likely) going to the index and then to the table will entail the use of more I/O. Query 2 is asking for a count of rows, not the data from all the rows.

--

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
Sent: Friday, October 14, 2005 3:55 AM
To: oracle-l_at_freelists.org
Subject: Why is Oracle choosing a different execution plan?

Hi!

We are experiencing somewhat weird behavior when executing the following statements...

Why is Oracle perfomring a full table scan in statement 1 and an index scan in statement 2? The table has a little over 200k rows and all statistics are newly calculated.

select * from odin_job where odj_archivieren = 'J';

...

PROD_1313235         ENV_1009473                   1 14.10.2005 02:12:39
eff2           
PROD_1317238         ENV_1013349                   1 14.10.2005 02:15:16
eff2           
PROD_1317240         ENV_1007975                   1 14.10.2005 02:15:16
eff2

...

4827 rows selected.

Elapsed: 00:00:37.05

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1194 Card=49213 Bytes=21309229)

1 0 TABLE ACCESS (FULL) OF 'ODIN_JOB' (Cost=1194 Card=49213 Bytes=21309229)

2) SQL> select count(*) from odin_job where odj_archivieren = 'J';

COUNT(*)

4827

1 row selected.

Elapsed: 00:00:00.05

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=1 Bytes=2) 1 0 SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'IDX_ODJ_ARCHIVIEREN' (NON-UNI
          QUE) (Cost=36 Card=49213 Bytes=98426)

Do you have an ideas?

Thanks,
Helmut

This message is confidential and sent by TD Waterhouse solely for use by the intended recipient. If you are not the intended recipient, you are hereby notified that any use, distribution or copying of this communication is strictly prohibited. This should not be deemed as an offer or solicitation, to buy or sell any product. Any 3rd party information contained herein was prepared by sources deemed reliable, but is not guaranteed. TD Waterhouse does not accept electronic instructions that would require an original signature. Information received by or sent from TD Waterhouse is stored, subject to review, and may be produced to regulatory authorities or others with a legal right to such.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 14 2005 - 10:25:59 CDT