Why is Oracle choosing a different execution plan?

From: <oracle-l-bounce_at_freelists.org>
Date: Fri, 14 Oct 2005 11:54:59 +0200
Message-ID: <99585773B6B1BA42BC4FE5DF14BB842F0481C324@SERV211.wwk-group.com>

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

Received on Fri Oct 14 2005 - 04:57:17 CDT