Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2118
  • Last Modified:

on the use of analyze table

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
Roopa_Pandit
Asked:
Roopa_Pandit
1 Solution
 
allahabadCommented:
( 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
 
Roopa_PanditAuthor Commented:
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
 
allahabadCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
iozturkCommented:
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
 
DatamonkeyCommented:
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
 
patelgokulCommented:
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
 
Roopa_PanditAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now