Performance tuning with date comparison [message #449559] |
Tue, 30 March 2010 08:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sray_suman
Messages: 6 Registered: March 2010
|
Junior Member |
|
|
Hi,
I have a large query with lots of date comparison because of which the performance is getting a hit. Below is a portion of the query where I am doing the date comparison. This comparison is being done on each record of a table having atleast 10,000 records.
SELECT tb.substation_id as Station,
ta.island_id as Island,
ta.area_number as Anumber
FROM islands ta,
nodes tb
WHERE ta.landname = 'ABC'
AND (ta.island_id = tb.island_id
AND ta.start_date = tb.start_date)
AND ta.start_date <= sysdate
AND (ta.end_date > sysdate
OR ta.end_date IS NULL)
GROUP BY ta.area_number
The area of concern is the below block
ta.start_date <= sysdate
AND (ta.end_date > sysdate
OR ta.end_date IS NULL)
is there any other way to achieve this functionality without getting a hit on performance?
Please note that though the indexes are available on start_date and end_date column, these are not being used by oracle while executing the query.(tried to use "hint" still oracle ignores the indexes)
Thanks
|
|
|
|
Re: Performance tuning with date comparison [message #449564 is a reply to message #449562] |
Tue, 30 March 2010 09:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sray_suman
Messages: 6 Registered: March 2010
|
Junior Member |
|
|
Thanks for your quick response.
Here is the explain plan.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
Plan hash value: 351463106
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 175 | 8750 | 3815 (6)| 00:00:46 |
| 1 | SORT GROUP BY | | 175 | 8750 | 3815 (6)| 00:00:46 |
|* 2 | HASH JOIN | | 99614 | 4863K| 3801 (6)| 00:00:46 |
|* 3 | TABLE ACCESS FULL| ISLANDS | 2365 | 73315 | 87 (4)| 00:00:02 |
|* 4 | TABLE ACCESS FULL| NODES | 1639K| 29M| 3685 (5)| 00:00:45 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TA"."ISLAND_ID"="TB"."ISLAND_ID" AND
"TA"."START_DATE"="TB"."START_DATE")
3 - filter("TA"."LANDNAME"='ABC' AND ("TA"."END_DATE" IS NULL OR
"TA"."END_DATE">SYSDATE@!) AND "TA"."START_DATE"<=SYSDATE@!)
4 - filter("TB"."START_DATE"<=SYSDATE@!)
20 rows selected
Thanks
|
|
|
|
Re: Performance tuning with date comparison [message #449567 is a reply to message #449566] |
Tue, 30 March 2010 09:48 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sray_suman
Messages: 6 Registered: March 2010
|
Junior Member |
|
|
Below are the indexes that are available
ISLANDS table
=============
index_1 on island_id, start_date
index_2 on end_date
index_3 on area_number
NODES table
============
index_1 on island_id, start_date
index_2 on island_id
Sorry I could not get your 2nd question. Could you please elaborate on this?
Thanks
|
|
|
|
Re: Performance tuning with date comparison [message #449569 is a reply to message #449567] |
Tue, 30 March 2010 09:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try this index on ISLAND: landname,nvl(end_date,nvl(end_date,to_date('31-dec-2499','dd-mon-yyyy')),start_date
And rewrite the query to :SELECT tb.substation_id as Station,
ta.island_id as Island,
ta.area_number as Anumber
FROM islands ta,
nodes tb
WHERE ta.landname = 'ABC'
AND (ta.island_id = tb.island_id
AND ta.start_date = tb.start_date)
AND ta.start_date <= sysdate
AND nvl(end_date,to_date('31-dec-2499','dd-mon-yyyy')) > sysdate
GROUP BY ta.area_number
What I was wanting with the second question was an idea of how many distinct values there were in the LANDNAME column, and roughly how they were distributed - are most of the records in the ISLAND table for the same LandName, or are they reasonably evenly distributed.
|
|
|
Re: Performance tuning with date comparison [message #449571 is a reply to message #449569] |
Tue, 30 March 2010 10:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JRowbottom wrote on Tue, 30 March 2010 15:57Try this index on ISLAND: landname,nvl(end_date,nvl(end_date,to_date('31-dec-2499','dd-mon-yyyy')),start_date
You appear to have an extraneous nvl, should be:
landname,nvl(end_date,to_date('31-dec-2499','dd-mon-yyyy')),start_date
|
|
|
Re: Performance tuning with date comparison [message #449572 is a reply to message #449569] |
Tue, 30 March 2010 10:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
sray_suman
Messages: 6 Registered: March 2010
|
Junior Member |
|
|
@BlackSwan
How many rows in ISLANDS?
apprx. 38,000
How many rows in NODES?
apprx. 1.6 million
How many rows in ISLANDS where LANDNAME = 'ABC'?
We have 2 values for LANDNAME, 'ABC' and 'XYZ'
ABC is having 10,500 records and rest XYZ
How many rows in NODES where ta.island_id = tb.island_id ?
1.6 million
@JRowbottom
Please find the answer to your 2nd question in the above data.(Answer to the 3rd question)
Thanks
|
|
|
|
|
Re: Performance tuning with date comparison [message #449636 is a reply to message #449559] |
Wed, 31 March 2010 02:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
sray_suman wrote on Tue, 30 March 2010 15:42Hi,
I have a large query with lots of date comparison because of which the performance is getting a hit.
How can you know that? I mean, what makes you believe that the problem is in the date comparison and not somewhere else?
sray_suman wrote on Tue, 30 March 2010 15:42
Below is a portion of the query where I am doing the date comparison. This comparison is being done on each record of a table having atleast 10,000 records.
SELECT tb.substation_id as Station,
ta.island_id as Island,
ta.area_number as Anumber
FROM islands ta,
nodes tb
WHERE ta.landname = 'ABC'
AND (ta.island_id = tb.island_id
AND ta.start_date = tb.start_date)
AND ta.start_date <= sysdate
AND (ta.end_date > sysdate
OR ta.end_date IS NULL)
GROUP BY ta.area_number
...
Your query can't work because "any column expression that is not part of the GROUP BY clause can occur only in the SELECT clause as an argument to a group function."
So, please post a valid query first.
|
|
|
|
|
Re: Performance tuning with date comparison [message #449757 is a reply to message #449736] |
Wed, 31 March 2010 13:13 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
sray_suman wrote on Wed, 31 March 2010 17:27Thanks a lot guys..you rock!!
As per the suggestion, I created the new index and modified the query accordingly. WOW!! the execution time got reduced by 25% which is awesome..
Thanks
If this is the case I think that you can use the original query and create a normal index on (landname,end_date,start_date).
|
|
|
|
Re: Performance tuning with date comparison [message #449845 is a reply to message #449802] |
Thu, 01 April 2010 07:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
@sray_suman
Nvl(end_date...) is basically indexing null. But in a composite index such as "landscape, end_date, etc." null "values" are indexed and the optimizer knows this.
Something likes this
ConnectÚ Ó :
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set linesize 120
SQL> drop table t_ind
2 /
Table supprimÚe.
SQL> create table t_ind as select * from all_objects
2 /
Table crÚÚe.
SQL> alter table t_ind modify created date null
2 /
Table modifiÚe.
SQL> update t_ind set created = null where rownum < 51
2 /
50 ligne(s) mise(s) Ó jour.
SQL> commit
2 /
Validation effectuÚe.
SQL> create index ix_t_ind on t_ind(object_type, created)
2 /
Index crÚÚ.
SQL> exec dbms_stats.gather_table_stats(user,'T_IND',cascade=>true)
ProcÚdure PL/SQL terminÚe avec succÞs.
SQL>
SQL> set autotrace traceonly explain
SQL> select t.object_name
2 from t_ind t
3 where t.object_type = 'SYNONYM'
4 and (created > sysdate - 1000 Or created is null)
5 /
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2224001492
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 279 | 9765 | 25 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_IND | 2 | 70 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_T_IND | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_IND | 277 | 9695 | 23 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX_T_IND | 278 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED" IS NULL)
5 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED">SYSDATE@!-1000 AND
"CREATED" IS NOT NULL)
filter(LNNVL("CREATED" IS NULL))
But, as you are saying that you're satisfied with the function based index you can ignore this
|
|
|
|
Re: Performance tuning with date comparison [message #449895 is a reply to message #449853] |
Fri, 02 April 2010 02:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
JRowbottom wrote on Thu, 01 April 2010 15:11Well, the function based index does have the advantage of only performing one index range scan, rather than two.
Sometimes this difference seems to be so minimal.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> set linesize 120
SQL> drop table t_ind
2 /
Table supprimÚe.
SQL> create table t_ind as select * from all_objects
2 /
Table crÚÚe.
SQL> alter table t_ind modify created date null
2 /
Table modifiÚe.
SQL> update t_ind set created = null where rownum < 51
2 /
50 ligne(s) mise(s) Ó jour.
SQL> commit
2 /
Validation effectuÚe.
SQL>
SQL> create index ix_t_ind on t_ind(object_type, created)
2 /
Index crÚÚ.
SQL> create index ix2_t_ind on t_ind(object_type, NVL(CREATED,TO_DATE('2499-12-31', 'yyyy-mm-dd')))
2 /
Index crÚÚ.
SQL> exec dbms_stats.gather_table_stats(user,'T_IND',cascade=>true)
ProcÚdure PL/SQL terminÚe avec succÞs.
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select t.object_name
2 from t_ind t
3 where t.object_type = 'SYNONYM'
4 and (created > sysdate - 1000 Or created is null)
5 /
248 ligne(s) sÚlectionnÚe(s).
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2224001492
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 280 | 9800 | 25 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_IND | 2 | 70 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX_T_IND | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_IND | 278 | 9730 | 23 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX_T_IND | 279 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED" IS NULL)
5 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED">SYSDATE@!-1000 AND
"CREATED" IS NOT NULL)
filter(LNNVL("CREATED" IS NULL))
Statistiques
----------------------------------------------------------
8 recursive calls
0 db block gets
58 consistent gets
0 physical reads
0 redo size
7828 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
248 rows processed
SQL>
SQL> select
2 t.object_name
3 from t_ind t
4 where t.object_type = 'SYNONYM'
5 and NVL(CREATED,TO_DATE('2499-12-31', 'yyyy-mm-dd'))> sysdate - 1000
6 /
248 ligne(s) sÚlectionnÚe(s).
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2876975224
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 423 | 14805 | 34 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND | 423 | 14805 | 34 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX2_T_IND | 423 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OBJECT_TYPE"='SYNONYM' AND NVL("CREATED",TO_DATE('2499-12-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))>SYSDATE@!-1000 AND
NVL("CREATED",TO_DATE('2499-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) IS NOT
NULL)
Statistiques
----------------------------------------------------------
8 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
7828 bytes sent via SQL*Net to client
556 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
248 rows processed
|
|
|