Oracle Performance Optimization twos

i do not have much time to use Oracle, but I have accumulated some lessons in the project to make it easier for me and others to solve similar problems.

1.temp space exceeds the limit problem

Problem Scenario:

in the complex ETL query, sometimes a fact table logic complex, from a number of table joins, I used to write the way of the CTE,

but The CTE is placed in memory, so it is fast, but if the amount of data is too large, it will be spool into the temporary space, and if the DB server is not configured well, this error is most likely to occur.

Solution: When I encountered a similar problem on SQL Server before, I was directly into the physical table, then I created the index on the physical table and then used the subsequent join. This way the colleagues here use MV(said to be Oracle 's silver Bullet), the original complex logic is disassembled into a plurality of MV, and then later when the call, it is manually forced to brush the MV after use.

2. the inserted data contains &

Problem Scenario:

There are two static configuration tables in the database, and the data needs to be written manually INSERT into configuration data. Discover that the inserted data contains &. If prompted directly in Sqlplus or in Oracle SQL Develper , because they recognize the string after & as a variable.

Solution:

use set define off; This will also identify the part that contains & as a normal string.

performance issues with 3.FULL JOIN

Problem Scenario:

The ticket(problem description list) contains countries and regions, but the countries and regions here are not exactly matched, that is, the relationship to the standard national and regional tables in our library is not exactly the same. What we need to do is, according to certain rules, to match the relationship between the right country and the region, then the reservation, the country that does not match is empty, and the region is reserved. Need to use full join.

But the problem is that when I use full join, this MV will change from dozens of seconds to about 20 minutes, which is unbearable. The amount of data is about more than 100,000.

solution: After a colleague searches, understand that the full join is The result set of the Union full join and the left join, and then try to rewrite the full join to leave the join Unio n Right JOIN,MV change back to more than 10 seconds to brush.

Discuss a question:

How do I read a complex SQL? and discover possible data problems?

find the most basic primary table and follow the join field between the table and the table to see if there is a null value and miss the value.

see if the relationship between the table and the table is 1:1 or m:n.

??

??

Oracle Performance Optimization twos