Comparison of column-to-row methods in Oracle Database 9i and 10g

Separate the strings 1, 2, 3, 4, 5 with commas (,) and output them as rows. That is, 12345 is handwritten. The Oracle10G and above tables are as follows: SQLselect * fromt; IDNAM ..

Separate the strings 1, 2, 3, 4, 5 with commas (,) and output them as rows. That is, 12345 is handwritten. The Oracle10G and above tables are as follows: SQLselect * fromt; IDNAM ..

Separate the strings '1, 2, 3, 4, 5 'with commas and output them as rows, that is

1
2
3
4
5

With one hand written, Oracle 10G or more
The tables are as follows:

ID NAME
------------------
1 0
1 1
1 5
1 2
1 8
1 10
2 9
2 7
2 8
3 Hello
3. He is good
3 Hello everyone

12 rows selected.

Then the customer reported that the database said 9i, and reported that connect by cannot use subqueries.
Then, make a slight modification. If the number of commas is no more than 99, directly write a constant 100, which can support 9i.

It is not good to think about it later, so we suggest creating a specific IOT table and saving 10000 numbers. This is generally enough.
Create an IOT platform with 10000 lines in total and support 9i

Create table tnumber (ele, constraint pk_tnumber primary key (ele) organization index

Select rownum id from dual connect by rownum <= 10000;

Then, the constant 100 is no longer needed to ensure accuracy and good performance.

ID NAME
------------------
1 0
1 1
1 5
1 2
1 8
1 10
2 9
2 7
2 8
3 Hello
3. He is good
3 Hello everyone

12 rows selected.

Next, let's test the performance: switch to the first line. For the U.S. server, test the environment: the DELL D630 has been using an old notebook for four and a half years, and the disks are all fragments.
SQL> insert into t select rownum + 4, '1, 2, 3, 4' from dual connect by rownum <= 500000;

500000 rows created.

SQL> commit;

Commit complete.

Set timi on
SQL> set timi on
SQL> create table t500 nologging
With vmaxnum (
Select ele
From tnumber
Where ele <= (select max (length (name)-length (replace (name, ',', null) + 1 from t ))
Select id,
Decode (pos, 0, substr (name, lagpos + 1), substr (name, lagpos + 1, pos-lagpos-1) name
From (select id, name, ele, pos, nvl (lag (pos) over (partition by id order by ele), 0) lagpos
From (select id, name, ele,
Instr (name, ',', 1, ele) pos
From (select/* + all_rows no_merge (v2) use_merge (t, v2 )*/
T. id, t. name, v2.ele
From t, vmaxnum v2
Where ele <= length (name)-length (replace (name, ',', null) + 1 )))
Order by id, ele;

Table created.

Elapsed: 00:00:16. 48

It takes 16 seconds to write data to the Hong Kong server, including the table creation time.

SQL> select count (*) from t500;

COUNT (*)
----------
2000012

Of course, there are other writing methods, such as simple ones.

Or a regular expression can be used for 10 Gb or more.
This method may not be the most efficient, but it is also possible.
The performance of this SQL statement is related to the number of commas contained in the string. The more commas, the more separated items, and the worse the performance.

This article is from the "Focus on J2EE series specifications..." blog, please be sure to keep this source

, US space