RE: Why is Oracle choosing a different execution plan?

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Fri, 14 Oct 2005 04:10:51 -0600
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B873584@fiji.arraybp.com>

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

PS: This is 9.2 on HP-UX 11i.
--

http://www.freelists.org/webpage/oracle-l

This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 14 2005 - 05:16:09 CDT