Oracle Magazine, March/April 2019
Database Developer and DBA ETL guarantee that the SQL statement is actually correct Often the cause of poorly performing SQL is malformed construction which can easily slip through testing cycles if the query results are still plausible especially with small test datasets Hence when Im asked to tune a SQL statement I will spend a few minutes before tackling any performance related avenues making sure that the SQL statement does not have any obvious syntax errors Here are some of the common errors I see that typically cause SQL statements to be misdiagnosed as performance problems Order of operations My children are just completing primary school and in their mathematics classes they use the acronym BIMDAS Many many years ago when I was a similar age it was called BODMAS but the premise was the same The acronym is a simple way of remembering the order of mathematical operations BIMDAS brackets first then indices then multiplication division and finally addition subtraction which is why 2 times 3 plus 5 evaluates to 11 not 16 The same rules apply to the processing order of logical operations within the predicates of a SQL statement and failing to observe the correct ordering can lead to performance issues Business requirements are often given in language within which there is a natural or implicit ordering of operations which can lead to errors when transposed to SQL code For example the requirement For regions in California find the highest transaction amount for sales consultants where the tax levy is more than 10 or the government subsidy ORACLE MAGAZINE MARCH APRIL 2019 106 is nonzero could yield the SQL query shown in Listing 5
You must have JavaScript enabled to view digital editions.
