?
Solved

on the use of analyze table

Posted on 2003-03-05
8
Medium Priority
?
2,084 Views
Last Modified: 2008-03-17
am just begining to work with oracle and defintiely require help,
the following query when executed gave an invalid number sql exception,

The tables in the join query contain primarykeys,uniquekeys and bitmap  indexes on the various tables used in the query. This query worked perfect after I had analyzed each of the tables used in the query. How do u think analyze table solved
the problem  ???

another question does the analyze table rebuild indexes ??


SELECT
DISTINCT P.ParameterValueOId, P.ParameterValueID, P.FixedValue,
P.LowerLimitValue, P.UpperLimitValue
FROM
TBLRSKGRPPARAMETERVALUE B,
TBLRSKGROUPUSE A,
TBLRSKPARAMETERVALUE P,
TBLRSKGROUP G,
TBLRSKPARAMETER PP,
TBLRSKGROUPPARAMETER GP
WHERE
G.GroupID = 108
AND PP.ParameterID = 59
AND G.MC_DELETE_TS IS NULL
AND PP.MC_DELETE_TS IS NULL
AND GP.GroupOID = G.GroupOID
AND GP.ParameterOID = PP.ParameterOID
AND B.GroupParameterOID = GP.GroupParameterOID
AND B.MC_DELETE_TS IS NULL
AND A.GroupParameterValueGrpOID = B.GrpParamValGrpOID
AND A.LOBID = 123461938
AND A.SequenceProcessingType = 1
AND A.InUse = 1
AND A.ProductID = 0
AND A.DistributorID IS NULL
AND P.ParameterValueOId = B.ParameterValueOID
AND ( ( P.FixedValue IS NULL AND P.LowerLimitValue IS NULL AND
P.UpperLimitValue IS NULL )
OR ( P.FixedValue = 'A' OR ( ( P.FixedValueIndicator = 0 )
AND ( TO_NUMBER( 'A' ) >= TO_NUMBER( P.LowerLimitValue ) )
AND ( TO_NUMBER( 'A' ) <= TO_NUMBER( P.UpperLimitValue ) ) )
) )
AND A.MC_DELETE_TS IS NULL
AND P.MC_DELETE_TS IS NULL
0
Comment
Question by:Roopa_Pandit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:allahabad
ID: 8076924
( TO_NUMBER( 'A' ) is raising the exception, it can not convert this into number.

How do u think analyze table solved
the problem  ??? question not clear.

another question does the analyze table rebuild indexes ?? NO.
0
 

Author Comment

by:Roopa_Pandit
ID: 8077357
Analyze table had actually solved the problem. I made no changes to the query. Analyze table had changed the execution plan  of the above query and I no longer got the inavlid number exception. Hope I made the question clear this time. Wonder how it worked !
0
 
LVL 3

Expert Comment

by:allahabad
ID: 8077456
understood.
The main use of the analyze command is determining performance statistics for the cost-based optimization of how oracle processes sql statements.

But execution plan comes after parsing,it should fail there(to_number('A').
Any comment experts ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:iozturk
ID: 8078038
I am using Oracle 8.1.5 and I have faced with so many block corruptions either indexes or tables. Analyze table rebuild index command eliminates the block corruption in indexes so you can run your query. But I suggest that issue the command dbv on your datafiles to see block corruptions.

i.e.
dbv file=c:\oradata\system01.dbf
0
 
LVL 2

Accepted Solution

by:
Datamonkey earned 300 total points
ID: 8078295
I can probably explain why you had the problem and why the analyze resolved it.

First of all, block corruptions are not the cause of of this sort of error. It might be that if you have a block corruption you don't run into it because of the same reason I'm going to describe but you would certainly get a very different error.

secondly, as allahabad says, the analyze command creates statistics about the table that are used by the cost-based optimizer. For example, without statistics Oracle simply follows the 'rule based optimizer' which has rules like 'if there is a index on a column then use that' because in general that is quite a good thing to do. If there are statistics about the table the optimizer can find out for example that because of the the amount of data it's quicker to read a whole table in than it is to look all the rows up through an index.

The cost based optimizer is a very complex beast but for the purposes of this explanation the only thing you need to know if that creating statistics (by analyzing tables) can lead to a different execution plan.

Now, of course the cause of the error is that "TO_NUMBER( 'A' )" is simply wrong. A cannot be a number. If you're looking for the ascii value you should use ASCII('A').
The question is, why does the error go away after the analyze.
The answer is that the optimizer has now created a different execution plan. In your WHERE clause you have quite a lot of conditions that are ANDed and ORed together. With the use of OR clauses oracle is smart enough to work out that sometimes they don't need to be tested. If a row already comes through the rest of the conditions and the only thing left is are some OR clauses oracle simply skips those because the row will be included in the resultset anyway.
So, the reason that you sometimes get the error and sometimes you don't is that the order in which the different parts of the WHERE clause are looked at has changed and therefore in some circumstances we never get to the TO_NUMBER('A') part.

So, as a conclusion, it might seem like you've resolved the issues by analyzing but that's not entirely true. yes, the error doesn't come up anymore but that's only because that part of the WHERE clause is not needed at this moment - if you change the query a bit it might become needed again and you will hit the problem again. So what you need to do is replace the TO_NUMBERs with something that will work.
--dm
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9940119
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Delete - no points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0
 

Author Comment

by:Roopa_Pandit
ID: 9940150
DataMonkey
Thanks for the explanation and valuable time.
Its clear now.
but I think I accepted the wrong comment as answer ! careless me !
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question