Re: Index not being used
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: ksmadduri@xxxxxxxxx
- Date: Tue, 20 Apr 2010 19:38:10 +0200
I'd say, the optimizer believes, the cost for the indexed statement is 2699K
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
9241K| 2414M| 2699K (2)| 08:59:52 |
whereas the full table scan only costs 679K
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 47861 | 12M|
679K (3)| 02:15:51 |
even the estimated rows, bytes and time differs.
you can try to get better numbers by running (with and without hint)
alter session set timed_statistics = true;
--statistics_level => ALL for row-source statistics - dangerous on prod!
alter session set statistics_level=all;
-- statement
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
please provide both outputs, these will highlight some points.
best regards,
Martin
On Tue, Apr 20, 2010 at 18:26, Kumar Madduri <ksmadduri@xxxxxxxxx> wrote:
>
> SQL> explain plan
> 2 for
> 3 SELECT /*+ INDEX(PA_EXPENDITURE_ITEMS_ALL PA_EXPENDITURE_ITEMS_N3) */
> *
>
> 4 FROM pa_expenditure_items_all
> 5 WHERE system_linkage_function = 'VI';
>
> Explained.
>
> SQL> SELECT *
> 2 FROM TABLE(DBMS_XPLAN.DISPLAY);
> Plan hash value: 3707576984
>
> --------------------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows |
> Bytes | Cost (%CPU)| Time |
> --------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 9241K|
> 2414M| 2699K (2)| 08:59:52 |
> |* 1 | TABLE ACCESS BY INDEX ROWID| PA_EXPENDITURE_ITEMS_ALL | 9241K|
> 2414M| 2699K (2)| 08:59:52 |
> | 2 | INDEX FULL SCAN | PA_EXPENDITURE_ITEMS_N3 |
> 36M| | 92416 (2)| 00:18:29 |
> --------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter("SYSTEM_LINKAGE_FUNCTION"='VI')
>
> 14 rows selected.
>
> SQL>
>
> On Tue, Apr 20, 2010 at 5:51 AM, Martin Berger <martin.a.berger@xxxxxxxxx>
> wrote:
>>
>> Can you please provide an execution Plan of the hinted statement on
>> Database 1?
>>
>> regards,
>> Martin
>>
>
--
Martin Berger martin.a.berger@xxxxxxxxx
Lederergasse 27/2/14 +43 660 660 83306
1080 Wien http://berx.at/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
