Неизвестный Oracle DB

Какие интересные вещи есть в оракле, это что-то!

A final pure SQL solution lies in the (ab)use of hierarchical queries. We can make a 'recursive hierarchy' using, yet again, dual:

SELECT ROWNUM n
FROM   ( SELECT 1 just_a_column
     FROM   dual
     CONNECT BY LEVEL <= 365       ) 

The theory behind this is that you connect a row to every row of a level higher by omitting the PRIOR keyword. You have no restriction on joining children to parent rows. And if we omit the START WITH clause too, we indicate that ALL records occur at level 1. With a table of one row this means the following: one record (in fact all records) at root level (level 1) because we have no START WITH clause. And at each iteration your hierarchical tree gets deeper (and the LEVEL pseudo column increases) because you match all records of the table to all records of the level above. We stop the recursive hierarchy creation by our constraint. At LEVEL 366 the condition isn't matched anymore and the query exits its recursive loop.

It should be noted that in later versions of oracle, at least as far back as 10gR1, operations against dual are optimized such that they require no logical or physical I/O operations. This makes them quite fast.

For this mechanism to work we need a table with only one row, like noted before. If we try it with two rows and limit the depth to LEVEL 3 we get:

2*LEVEL1 (all records of the table)

4*LEVEL2 (all records of the table (2 records) get matched to the higher level (2 records))

8*LEVEL3 (all records of the table (2 records) get matched to the higher level (4 records))

This results in a total of 14 records.

orafaq.com/wiki/Oracle_Row_Generator_Techniques

Вы поняли чего-нить? Я понял, что оракл поддерживает иерархические структуры в своем диалекте SQL. Вот жопа (восхищенно крутит головой).

Просто бездонная штука, этот оракл. Похоже, только один человек знает, как оно работает - Том, которого все аскают :)


Сцылки дня:

• Как в оракле строку превратить в колонку
• Свинский грипп, срочно ехать в Мексику
• Гостиница - опупеть
• Мощный обществовед, Кон И.С. (обратите внимание на страницу обсуждения, просто битва интеллекта с воинственной серостью)