| Query not hitting index on date column [message #554876] |
Fri, 18 May 2012 03:58  |
 |
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 #555060 is a reply to message #555008] |
Mon, 21 May 2012 02:52  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
priyankt wrote on Mon, 21 May 2012 05:11When 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 |
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')
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
