Fwd: Why is Oracle choosing a different execution plan?

In the first query you are fetching all columns ==> Oracle has to access to the entire table (FULL SCAN)
In the second query you are counting records ==> Index full scan.  Both queries are differents.
 HTH
 Enrique

 On 10/14/05, oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org > wrote:
>
> 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
>

--
------------------------------------------------
Enrique

--
------------------------------------------------
Enrique

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