Weak Points of oracle update and temporary tables!

During the interview, some companies asked ORACLE about the development shortcomings? I didn't understand it at the time. Recently, I think that is the use of UPDATE and temporary tables.

 

Originally, I wanted to generate statistical data for a table. The table structure is as follows:

 

Weekly, weekly, store, visitors, number of buyers, number of visitors, and number of buyers.

200928, Monday, women's clothing shop.

 

 

Although it looks very simple, in fact, each person and number of people are obtained by subqueries. Another problem is that some stores may not be open, so the number should be zero.

 

That is to say, the generation of time (Weeks, weeks) is not based on the shop's business hours for the generation standard, rely on natural time. From the start time of the first store to the end time of statistics.

 

V_startday --> trunc (sysdate, 'D') and insert it cyclically.

For I in 0 .. (v_endate-v_startday)

Loop

Insert into

Select to_char (v_currday, 'yyyymw'), to_char (v_currday, 'day'), store_name, B. num, C. num, D. num, E. num

Left join (select B. num from... where open_day between v_currday and v_currday + 1) on .....

Left join (select c. num from... where open_day between v_currday and v_currday + 1) on .....

Left join (select d. num from... where open_day between v_currday and v_currday + 1) on .....

Left join (select e. num from... where open_day between v_currday and v_currday + 1) on .....

Order ......

End loop

 

The speed is too slow and the IO volume is too low. Let's look at one item from the 10 Gb EM.

 

2. The next step is to insert the time series and then UPDATE the NULL data.

 

For I in 0... (v_enddate-v_startday)

Loop

Insert into a (f_yeaweek, f_weekday)

Values (to_char (v_currday, 'yyyymw'), to_char (v_currday, 'day ');

End loop;

 

Update

Set (store_name, looknum, buynum, looks, buys) =

(

Select f_yearweek, f_weekday, S. store_name, B. Num, C. Num, D. Num, E. Num

From

(Select * from S

Left join (select count () from B where open_day between v_startdayand v_endate + 1) on...

Left join (select count () from B where open_day between v_startdayand v_endate + 1) on...

Left join (select count () from B where open_day between v_startdayand v_endate + 1) on...

Left join (select count () from B where open_day between v_startdayand v_endate + 1) on...

) F

Where a. f_yearweek = f. f_yearweek and a. f_weekday = f. f_weekday

)

Where a. f_yearweek between to_char (v_startday, 'yyyyiw') and to_char (v_enddate, 'yyyyiw ')

;

 

The results are as slow as they are. You can see in the EM diagram that the query is executed for each update, although the conditions are the same! Because a query takes 5 minutes.

 

3. Use a temporary table. First insert the time data into the temporary table and then

Insert into

Select *

From tmp

Left jon ..

Left jon...

Left jon ....

 

The problem is that temporary ORACLE tables must be dynamically executed! Crazy! It cannot be like ms SQL.

SQL: = "create... tmep ...."

Execute SQL;

For I in ....

Loop

SQL: = '...'; -- dynamic parameter assignment is troublesome.

Exuecute SQL;

End loop

 

SQL: ='

Insert into

Select *

From tmp

Left jon ..

Left jon...

Left jon ....

'

 

Execute SQL;

This is big.