Home » SQL & PL/SQL » SQL & PL/SQL » different results in differen databases on the same tables containing the same data (merged 4)
different results in differen databases on the same tables containing the same data (merged 4) [message #404027] Wed, 20 May 2009 02:33 Go to next message
royyaa_sh
Messages: 3
Registered: May 2009
Junior Member
Hello everybody,
I'm new in this forum and this is my first question, I hope my first try would end up well Wink
I've encountered a problem that caused so many questions arise on my mind.
Here is the case:
I have 2 environments (2 databases), TEST and PROD. I've created a table in both TEST and PROD environment and imported the same data in both tables. This table is inv_group_ranges.
This table has 3 columns:
group_id number,
min_value varchar2(50) and
max_value varchar2(50)
min_value and max_value can contain both numeric data (like 15, 13.5,...) or string data (like 'color', 'color123', '1color2',...). I know that, for example if group_id is 483, then min_value and max_value contains numeric data for sure.
Now I want to run this query:

select gr.subgroup_id,gr.property_id
from xxau.inv_group_ranges gr
where gr.group_id=483
and to_number('1')>=to_number(gr.min_value)
and to_number('1')<=to_number(gr.max_value)

Here is where all weird problems start. If I run this query in TEST environment, invalid number error will occur, but the strange thing for me is that if I cut the first condition 'gr.group_id=483' from beginning and put it as the last condition, like what you can see here:

select gr.subgroup_id,gr.property_id
from xxau.inv_group_ranges gr
where
to_number('1')>=to_number(gr.min_value)
and to_number('1')<=to_number(gr.max_value)
and gr.group_id=483

then there you go, query is executed successfully!

I used Explain plan to see the plan. It showed that Oracle optimizer chooses full scan table to execute this query. I have no idea how come the result of query depends on order of statements in where clause and this is my first question.
This is not end of my story!
Now see what is going on in PROD environment.
If I run this query

select gr.subgroup_id,gr.property_id
from xxau.inv_group_ranges gr
where gr.group_id=483
and to_number('1')>=to_number(gr.min_value)
and to_number('1')<=to_number(gr.max_value)

in PROD environment, you can see that the scenario is totally reversed. I mean the above query will work in PROD with exactly the same order of statements in where clause, but if I cut the first condition 'gr.group_id=483' from beginning and put it as the last condition, then surprisingly invalid number error will occur!!!!
Explain plan shows that optimizer chooses full scan table again, but I don't know why the result is different from TEST environment.
Could you please help me on understanding what is going on.

[Updated on: Wed, 20 May 2009 02:49] by Moderator

Report message to a moderator

Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404037 is a reply to message #404027] Wed, 20 May 2009 02:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm new in this forum and this is my first question,

Please read OraFAQ Forum Guide to learn how to proper post.
Don't multipost your question, if you have any problem go back to forum post list to check if your post is not there, if it is not then and only then repost.

"to_number('1')" is silly why not just "1"?
"to_number(gr.min_value)" if min_value is a number this is also silly and may lead to the error you got.

First fix that and come back.

Regards
Michel
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404084 is a reply to message #404027] Wed, 20 May 2009 05:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is caused by your table design - you're storing mixed numeric and string data in a single column, and then trying to perform an operation on it that is only valid for numerical data.

If the CBO decides to do an Full Table scan, it will read each row from the table and apply the terms in the WHERE clause to see if the data is valid. The order that these terms are applied in certainly varies between release, and I don't believe that it's guaranteed to remain the same inside a release.

Options:

1) Redesign your table and hold numbers in a seperate pair of columns to strings
2) Add an index to the table to allow the query to select by index rather than having to read all the rows in the table
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404092 is a reply to message #404084] Wed, 20 May 2009 05:28 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Wed, 20 May 2009 11:11

2) Add an index to the table to allow the query to select by index rather than having to read all the rows in the table

You can't guarantee that's going to work surely?
Even if you get this query to always use the index others might not.
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404151 is a reply to message #404092] Wed, 20 May 2009 11:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It'll work in any query that uses the index, but you're right that it's not total fix - that's why it was number 2 on the list.

Oddly, in 10g, with this test case, I can't manage to get an invalid number error no matter what I try:
drop table test_204;

create table test_204 (col_1 number, col_2 varchar2(10));

insert into test_204 select lv1
                           ,case when lv1 in (999,1) then 'A'||to_char(lv2) else to_char(lv2) end
                     from   (select level lv1 from dual connect by level <= 1000) x1
                           ,(select level lv2 from dual connect by level <= 100) x2;

create index test_204_idx on test_204(col_1,col_2);

drop index test_204_idx;

select * from test_204 order by col_1;
                           
select col_1, col_2 ,col_1
from   test_204
where  1=1
and    to_number(col_2) >= 5
and    to_number(col_2) <=7
and    col_1+0 = 1000
;
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404158 is a reply to message #404037] Wed, 20 May 2009 11:22 Go to previous messageGo to next message
royyaa_sh
Messages: 3
Registered: May 2009
Junior Member
Michel Cadot wrote on Wed, 20 May 2009 02:53
Quote:
I'm new in this forum and this is my first question,

Don't multipost your question, if you have any problem go back to forum post list to check if your post is not there, if it is not then and only then repost.



I said I'm new in this forum but it doesn't mean that I'm not familiar with whole forum thing. so if you see that there is multipost of my question you could guess that maybe sth wrong has occured, maybe my connection has failed so that I could not find out that my question had been registered.

Quote:

"to_number('1')" is silly why not just "1"?
"to_number(gr.min_value)" if min_value is a number this is also silly and may lead to the error you got.



I have to say that this is not just a query that I run in pl/sql to get a report. This is a part of a bigger query that I have used in a complicated form which has interactions with user. in fact, this query is the simplest form of the primary one and '1' is just as a sample here as 'group_id=483' is. On the other hand I have used to_number so that I can prevent user from entering string in the case he has to enter number (can not explain about the form here). So I think this form of my query doesn't seem that silly any more.
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404165 is a reply to message #404027] Wed, 20 May 2009 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for PROD server system.
Post Oracle version to 4 decimal place of PROD Oracle s/w.
Post Operating System (OS) name & version for TEST system.
Post Oracle version to 4 decimal place of TEST Oracle s/w.
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404171 is a reply to message #404158] Wed, 20 May 2009 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
On the other hand I have used to_number so that I can prevent user from entering string in the case he has to enter number

This is the problem of the client application not of SQL.
Check it at client level not in the query.
And this does not answer to the second part: to_number(gr.min_value). Either min_value and it is silly and wrong, either it is not and you have a flaw in your design as it should be.

Quote:
So I think this form of my query doesn't seem that silly any more.

Yes, I'm afraid it still does.

As I said: "First fix that and come back."

Regards
Michel
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404173 is a reply to message #404158] Wed, 20 May 2009 11:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
So I think this form of my query doesn't seem that silly any more.

No - no matter which way you slice it, it's a poor design, and an error waiting to happen.

Anything that selects rows from that table and does a to_number on them runs the risk of the CBO deciding that it's going to process the Where clause in a different order, or merge this query with another one, and then it's going to fail.
It's just a when, not an if.

On a related note, I found out how to get my test case to error (see earlier post) - you just have to switch to the RBO.

Are you using the RBO or the CBO? If you'd posted a plan for the query we'd know this, but I guess we'll muddle through.


Quote:
maybe my connection has failed so that I could not find out that my question had been registered.

But if your connection failed, then how were you able to repost the question without re-conecting? And if you reconnected, how come you didn't check your post was there?
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #404174 is a reply to message #404084] Wed, 20 May 2009 11:44 Go to previous messageGo to next message
royyaa_sh
Messages: 3
Registered: May 2009
Junior Member
JRowbottom wrote on Wed, 20 May 2009 05:11
The
1) Redesign your table and hold numbers in a seperate pair of columns to strings


Thank you for your guidance but I'm afraid to say I can't redesign
my table.
Quote:

2) Add an index to the table to allow the query to select by index rather than having to read all the rows in the table


about index scan: by just adding index to a table, you can not make sure that the optimizer will use index scan in it's plan except for optimizer in RBO mode. that is because CBO uses statistics to decide about execution plan. in some cases like when the table is small or when the retrieved records are significant in comparison with the whole table records, optimizer will choose Full Table Scan. so adding index is not reliable.
Re: different results in differen databases on the same tables containing the same data (merged 4) [message #406770 is a reply to message #404174] Fri, 05 June 2009 14:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Although the design is poor, there is a workaround until that can be fixed. You can create your own function to use instead of to_number to either return a valid number or null value if it is not a number, by capturing the exception within the function, so that the query then sees only valid numbers and null values. This should work reliably under any circumstances. Please see the simplified reproduction and solution below.

-- smplified reproduction of problem:
SCOTT@orcl_11g> create table inv_group_ranges
  2    (group_id   number,
  3  	min_value  varchar2 (10),
  4  	max_value  varchar2 (10))
  5  /

Table created.

SCOTT@orcl_11g> insert all
  2  into inv_group_ranges values (483, 'A', 'B')
  3  into inv_group_ranges values (483, 1, 2)
  4  select * from dual
  5  /

2 rows created.

SCOTT@orcl_11g> select *
  2  from   inv_group_ranges
  3  where  group_id = 483
  4  and    to_number (min_value) <= 1
  5  and    to_number (max_value) >= 1
  6  /
and    to_number (min_value) <= 1
       *
ERROR at line 4:
ORA-01722: invalid number


-- solution:
SCOTT@orcl_11g> create or replace function to_number_or_null
  2    (p_string in varchar2)
  3    return number
  4  as
  5  begin
  6    return to_number (p_string);
  7  exception
  8    when value_error then
  9  	 return to_number (null);
 10  end to_number_or_null;
 11  /

Function created.

SCOTT@orcl_11g> select *
  2  from   inv_group_ranges
  3  where  group_id = 483
  4  and    to_number_or_null (min_value) <= 1
  5  and    to_number_or_null (max_value) >= 1
  6  /

  GROUP_ID MIN_VALUE  MAX_VALUE
---------- ---------- ----------
       483 1          2

SCOTT@orcl_11g>

Re: different results in differen databases on the same tables containing the same data (merged 4) [message #406780 is a reply to message #406770] Fri, 05 June 2009 17:09 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Whilst I completely agree that Barbara's solution is the preferred, most robust, and best one; there is one more hack that would be possible and could be robust ENOUGH.

SELECT /*+ NO_MERGE(x) NO_PUSH_PRED(x)*/
       gr.subgroup_id,gr.property_id
FROM   (
    SELECT gr.subgroup_id,gr.property_id
    ,      to_number(gr.min_value) AS min_value
    ,      to_number(gr.max_value) AS max_value
    from xxau.inv_group_ranges gr
    where gr.group_id=483
) x
WHERE 1 BETWEEN gr.min_value AND gr.max_value


Ross Leishman
Previous Topic: Supressing count on timestamp
Next Topic: demo tables(emp,dept) recovery in scott
Goto Forum:
  


Current Time: Fri Dec 02 22:58:08 CST 2016

Total time taken to generate the page: 0.06277 seconds