Why is Oracle choosing a different execution plan?
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Oct 2005 11:54:59 +0200
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
