| Solution Required for Sql Performance issue in oracle due to Null value check(2 Merged) [message #509912] |
Wed, 01 June 2011 10:21  |
 |
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
|
Member |
|
|
Hi All,
I have a Performance issue (time taken is very high)with an sql query.
The query cost is very high and time taken is inacceptible & i found that the reason was the check for null value in the query .
The tables contain more than 100 million records each.
The basic structure of the query is ;
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((a.flag <> '10' and a.flag <> '15') or a.flag is null)
I had a few workaround for this but all this did not reduce the time taken. I will enumerate what all i did for a solution :
1. normal index on flag
create index test_flag on table1 (flag)
result : No difference in time taken (index not used)
2. nvl index on flag col and query change
create index test_flag on table1 (nvl(flag,'NA'))
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((nvl(a.flag,'NA') <> '10' and (nvl(a.flag,'NA') <> '15') )
result : No difference in time taken and index is getting used
3. create index test_flag on table1 (flag)
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ( (a.flag > '10' or a.flag < '10')
and (a.flag > '15' or a.flag > '15')or a.flag is null )
result : No difference in time taken ,index is not used)
if ' or' condition is removed then the index is getting used.
Pls help me since this is a very critical issue in the production environment.
If proper values are substituted for flag eg- a.flag='10' then the query is faster.
regards
Ganesh
|
|
|
|
| Solution Required for Sql Performance issue in oracle due to Null value check [message #509913 is a reply to message #509912] |
Wed, 01 June 2011 10:23  |
|
ganeshkn21
Messages: 36
Registered: June 2011
Location: bangalore
|
Member |
|
|
Hi All,
I have a Performance issue (time taken is very high)with an sql query.
The query cost is very high and time taken is inacceptible & i found that the reason was the check for null value in the query .
The tables contain more than 100 million records each.
The basic structure of the query is ;
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((a.flag <> '10' and a.flag <> '15') or a.flag is null)
I had a few workaround for this but all this did not reduce the time taken. I will enumerate what all i did for a solution :
1. normal index on flag
create index test_flag on table1 (flag)
result : No difference in time taken (index not used)
2. nvl index on flag col and query change
create index test_flag on table1 (nvl(flag,'NA'))
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((nvl(a.flag,'NA') <> '10' and (nvl(a.flag,'NA') <> '15') )
result : No difference in time taken and index is getting used
3. create index test_flag on table1 (flag)
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ( (a.flag > '10' or a.flag < '10')
and (a.flag > '15' or a.flag > '15')or a.flag is null )
result : No difference in time taken ,index is not used)
if ' or' condition is removed then the index is getting used.
Pls help me since this is a very critical issue in the production environment.
If proper values are substituted for flag eg- a.flag='10' then the query is faster.
regards
Ganesh
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Solution Required for Sql Performance issue in oracle due to Null value check [message #509924 is a reply to message #509921] |
Wed, 01 June 2011 10:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
According to those figures this query should return 70000 rows:
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((a.flag = '10' or a.flag = '15') or a.flag is null)
But that's not what you are running. If this is right:
Quote:
How many rows where flag is null? 60,000
How many rows where flag is 10 or 15? flag '10': 5000 rows ,flag '15': 5000 ros
Then your query should return just under 100 million rows. Which will take a while.
EDIT: typo
[Updated on: Wed, 01 June 2011 10:42] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Solution Required for Sql Performance issue in oracle due to Null value check [message #509929 is a reply to message #509926] |
Wed, 01 June 2011 10:59 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Since flag can't be 10 and 15 simultaneously I doubt you are running that query either.
This is the query you said you were running:
select a.*, b.* from table1 a , table2 b
where a.primary_key=b.primary_key
and ((a.flag <> '10' and a.flag <> '15') or a.flag is null)
So, ignoring b for a second, that returns every row in a where flag is not 10 and flag is not 15.
Which is to say it returns every row in table a except the rows where flag is 10 or 15.
You have 10000 rows where flag is 10 or 15 (5000 + 5000).
So that query (ignoring b) will return: 100,000,000 - 10,000 = 999,990,000 rows.
So unless those 999,990,000 rows in a have exactly 70000 matching rows in b, your figures are way off.
You appear to have taken the number of rows where flag is null and added the number of rows where flag is 10 and flag is 15 to get the number of rows the query will return. But that's not what your query is doing.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Solution Required for Sql Performance issue in oracle due to Null value check [message #509934 is a reply to message #509932] |
Wed, 01 June 2011 11:11 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ganeshkn21 wrote on Wed, 01 June 2011 17:04yea i have to query the records that appear in table1 which have flag value not equal to '10' and '15' or where the flag value is blank/null.
I know that. What I'm telling you is that if there are 100 million records in the table, and there are only 10 thousand rows where flag is '10' or '15' then such a query will return 999, 990, 000 rows. which is 10 thousand less than 100 million.
Not 70000.
The problem is nothing to do with nulls, or indexes.
The problem is that the query returns
999,990,000 / 70,000 = 14,285 times more rows than you think it does.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
