Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to construct sequences in Oracle. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The way Oracle constructs sequences changes from version to version. In versions prior to 9i, the common method was:

Select rownum rn from all_objects where rownum select count (*) from (select rownum rn from xmltable ('1 to 67108864'); COUNT (*)-67108864 elapsed time: 00: 00: 37.00

It's about the same time as connect by's. From the above tests, it can be seen that when constructing a larger sequence, the Cartesian product is * *. Using connect by alone will run out of memory, while using xmltable alone will take more time.

Now let's take a look at the basic use of pure table joins to construct sequences of the same size, starting with 1m.

Lastwinner@lw > with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from brecinct brecinct brecinceb, 3 blemagerie brecedicalb, 4 brecedbrecallybleglegb, 5brecedbrecallybrecedbforceb) 6 select count (*) from c; COUNT (*)-1048576 used time: 00: 00: 00.33

And 64m.

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from brecinct brecinct brecinceb, 3brecincebrecedence brecrieb, 4brecincebrecincebrecinceb, 5brecincebrecedence brecrieb, 6brecincebrecedencebrecrencebforceb). B) 7 * select count (*) from c lastwinner@lw > / COUNT (*)-67108864 elapsed time: 00: 00: 16.62

This speed is not fast, but it is already faster than direct xmltable.

In fact, 64m, that is, 64m ^ 20 can be expressed as (2 ^ 5) ^ 5mm 2, so let's rewrite 64m sql.

Lastwinner@lw > with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from b as), 3 d as (select rownum r from c Leng c Liao b) 4 select count (*) from d; COUNT (*)-67108864 elapsed time: 00: 00: 04.53

As you can see, from 16s to 4s, it is much faster. This example tells us that intermediate table c plays a good role in improving speed.

But when it is constructed to 1G, it is still slower.

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from b select rownum r from breco b), 3 d as (select rownum r from c from d lastwinner@lw > / COUNT (*)-1073741824 used time: 00: 01: 11.48

Try a relatively fast way of writing, one more layer of intermediate table

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from breco breco b), 3 d as (select rownum r from creco c), 4 e as (select rownum r from dlemdLind c) 5 * select count (*) from e lastwinner@lw > / COUNT (*)-1073741824 elapsed time: 00: 01: 06.89

Faster (idea, 32 ^ 2 = 1024, 1G = 2 ^ 30 = (2 ^ 5) ^ 6 = ((2 ^ 5) ^ 2) ^ 3.)

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from b select rownum r from b, select rownum r from c), 3 d as (select rownum r from c), 4 e as (select rownum r from d as) 5 * select count (*) from e lastwinner@lw > / COUNT (*)-1073741824 elapsed time: 00: 01: 05.21

At this time, we replace 2 ^ 5 = 32 with a directly constructed way.

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select rownum r from dual connect by rownum / COUNT (*)-1073741824 elapsed time: 00: 01: 05.07

It can be seen that the time spent is about the same.

From this we can also conclude that the cost of table join is actually expensive, properly reducing the number of table join and properly using the intermediate table in with can effectively improve the performance of the system.

Repeat the scene in which 64m (2 ^ 26) was just constructed.

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from brecinct brecinct brecinceb, 3brecincebrecedence brecrieb, 4brecincebrecincebrecinceb, 5brecincebrecedence brecrieb, 6brecincebrecedencebrecrencebforceb). B) 7 * select count (*) from c lastwinner@lw > / COUNT (*)-67108864 elapsed time: 00: 00: 16.62

A total of 25 table joins, 1 layer of nesting, so that the speed is very slow. Increase it a little bit (26 times 4 times 3 times 2 times 2 times), a total of 8 table joins, 3 layers of nesting.

Lastwinner@lw > ed has been written to file afiedt.buf 1 with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from b as breco b), 3 d as (select rownum r from c recital c), 4 e as (select rownum r from d Leng d Leng b) 5 * select count (*) from e lastwinner@lw > / COUNT (*)-67108864 used time: 00: 00: 04.00

The efficiency has been increased by four times. Note that in this case, it is not always better to have fewer table joins, and the number of nesting layers is also an indicator of concern. If you are interested in implementing the plan, go and see it for yourself. I will not list it. In the above example, there are three intermediate tables generated by the system.

Finally, it is concluded that when constructing larger sequences, for example, 64m sequences are also constructed, oracle has an obvious advantage in using table joins when dealing with them. However, considering the convenience of writing, when constructing smaller sequences, such as sequences no more than 1K, it would be better to use connect by or xmltable directly.

Attached: newkid reply method, which means that it is more flexible. Interested students can try:

Create or replace function generator (n pls_integer) return sys.odcinumberlist pipelined is m pls_integer: = trunc (n / 10); r pls_integer: = n-10 * m; begin for i in 1. M loop pipe row (null); end loop; for i in 1. R loop pipe row (null); end loop; end; / alter function generator compile plsql_code_type = native; SQL > select count (*) from table (generator (67108864)) COUNT (*)-67108864 Elapsed: 00with b as 06.68 SQL > with b as (select 1 r from dual union all select 2 from dual), 2 c as (select rownum r from breco breco b), 3 d as (select rownum r from creco c c), 4 e as (select rownum r from djue dpenge b) 5 select count (*) from e COUNT (*)-67108864 Elapsed: 06.32 is how to construct the sequence in Oracle. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.