RE: Why is Oracle choosing a different execution plan?
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: <HELMUT.DAIMINGER@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 Oct 2005 04:10:51 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Friday, October 14, 2005 3:55 AM
To: oracle-l@xxxxxxxxxxxxx
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.
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
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
