Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Why is Oracle choosing a different execution plan?

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.

  1. 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US