Home » RDBMS Server » Performance Tuning » Query not hitting index on date column (Oracle 10G)
Query not hitting index on date column [message #554876] Fri, 18 May 2012 03:58 Go to next message
priyankt
Messages: 10
Registered: February 2011
Junior Member
Hello ,

I am working with following select clause:
select distinct S.ID ID
from
ods.hso_Scheduled H,
ods.SO_SCHEDULED S
where 
S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
and H.ID=S.ID 


Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.

I have created following indexes on these tables:

Indexes on SO_SCHEDULED:
Index name Column name
SS_IDX1 ID, SO_SUB_ITEM__ID
SS_IDX2 INSERTION_DATE
SS_IDX3 ID, INSERTION_DATE
SS_IDX4 ID, SO_SUB_ITEM__ID, INSERTION_DATE
SO_SCHEDULED_ID_PK ID

Indexes on HSO_SCHEDULED:

HSS_IDX1 ID, SO_SUB_ITEM__ID, LAST_UPDATING_DATE
HSS_IDX2 ID, LAST_UPDATING_DATE
HSS_IDX3 ID

My problem is despite of having relevant indexes present, my query is not hitting them and hence the performance is very bad.

Explain Plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 574170360

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |               |   814K|    38M|       |  9574   (1)| 00:02:15 |        |      |            |
|   1 |  PX COORDINATOR           |               |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)     | :TQ10002      |   814K|    38M|       |  9574   (1)| 00:02:15 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE            |               |   814K|    38M|   185M|  9574   (1)| 00:02:15 |  Q1,02 | PCWP |            |
|*  4 |     HASH JOIN             |               |  2653K|   124M|       |  9564   (1)| 00:02:14 |  Q1,02 | PCWP |            |
|   5 |      PX JOIN FILTER CREATE| :BF0000       |   814K|    22M|       |  3903   (1)| 00:00:55 |  Q1,02 | PCWP |            |
|   6 |       PX RECEIVE          |               |   814K|    22M|       |  3903   (1)| 00:00:55 |  Q1,02 | PCWP |            |
|   7 |        PX SEND HASH       | :TQ10000      |   814K|    22M|       |  3903   (1)| 00:00:55 |  Q1,00 | P->P | HASH       |
|   8 |         PX BLOCK ITERATOR |               |   814K|    22M|       |  3903   (1)| 00:00:55 |  Q1,00 | PCWC |            |
|*  9 |          TABLE ACCESS FULL| SO_SCHEDULED  |   814K|    22M|       |  3903   (1)| 00:00:55 |  Q1,00 | PCWP |            |
|  10 |      PX RECEIVE           |               |    14M|   272M|       |  5654   (1)| 00:01:20 |  Q1,02 | PCWP |            |
|  11 |       PX SEND HASH        | :TQ10001      |    14M|   272M|       |  5654   (1)| 00:01:20 |  Q1,01 | P->P | HASH       |
|  12 |        PX JOIN FILTER USE | :BF0000       |    14M|   272M|       |  5654   (1)| 00:01:20 |  Q1,01 | PCWP |            |
|  13 |         PX BLOCK ITERATOR |               |    14M|   272M|       |  5654   (1)| 00:01:20 |  Q1,01 | PCWC |            |
|  14 |          TABLE ACCESS FULL| HSO_SCHEDULED |    14M|   272M|       |  5654   (1)| 00:01:20 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("H"."ID"="S"."ID")
   9 - filter("S"."INSERTION_DATE">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "S"."INSERTION_DATE"<TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))



Kindly help me in understanding, what can be done to improve the performance of this query.

Re: Query not hitting index on date column [message #554878 is a reply to message #554876] Fri, 18 May 2012 04:31 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
1.Are the statistics on SO_SCHEDULED up to date? 2.Have you tried the following index INSERTION_DATE,ID? insertion_date first for the filter and the id for the join.
Re: Query not hitting index on date column [message #555008 is a reply to message #554878] Sun, 20 May 2012 23:11 Go to previous messageGo to next message
priyankt
Messages: 10
Registered: February 2011
Junior Member
Hello Flyby,

1. Statistics of these tables are captured on weekly basis.
2. I checked we have index present on Insertion date , ID.

We were executing the query in enable parallel query mode.

When i executed the same query in disable parallel query mode, plan showed index are in use.
Re: Query not hitting index on date column [message #555010 is a reply to message #555008] Sun, 20 May 2012 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Re: Query not hitting index on date column [message #555060 is a reply to message #555008] Mon, 21 May 2012 02:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
priyankt wrote on Mon, 21 May 2012 05:11
When i executed the same query in disable parallel query mode, plan showed index are in use.


There comes a point where sufficient parallel will cause oracle to ignore an index, as your results are showing. Quite what degree this happens at will vary.

However, you mention you actively had to turn on parallel query to get the results you are seeing so...surely the solution is to simple not execute the query in parallel?
Re: Query not hitting index on date column [message #555202 is a reply to message #554876] Tue, 22 May 2012 07:40 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Can you try and rewrite the query as:

select S.ID ID
from ods.SO_SCHEDULED S
where S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
  AND EXISTS ( SELECT 1 FROM  ods.hso_Scheduled H WHERE H.ID = S.ID ) 
?

What is the result of:

SELECT COUNT(*) FROM ods.SO_SCHEDULED S
where S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
?

Re: Query not hitting index on date column [message #555208 is a reply to message #554876] Tue, 22 May 2012 08:21 Go to previous message
John Watson
Messages: 8596
Registered: January 2010
Location: Global Village
Senior Member
The explain plan ID9 shows that 814k rows are expected from so_scheduled after applying the filter, which is > 10% of the table. If that is correct, I would not want an index to be used.
Previous Topic: Problem in Update
Next Topic: Application Slow in EE but fast in XE (2 Merged)
Goto Forum:
  


Current Time: Mon Sep 27 12:05:21 CDT 2021