1

Topic: Result of request (column) to break and deduce in N-columns

I welcome !
Whether it is possible to deduce at once means SQL result of request in some N-columns?
Probably proceeding from a record count.
Result:
NUMBERS
7312312
7314314
7318318
7319319
7324324
7326326
7329329
7330233
7331833
7331933
7332133
7332433
7332633
7332833
7334133
7334533
7334633
7334833
It is necessary to receive:
NUMBERS NUMBERS NUMBERS NUMBERS
7312312 7326326 7332133 7334133
7314314 7329329 7332433 7334533
7318318 7330233 7332633 7334633
7319319 7331833 7332833 7334833
7324324 7331933
Result of the most simple request:

SELECT
ph. NOMERA AS NOMERA
FROM
NUMBERS ph

2

Re: Result of request (column) to break and deduce in N-columns

with t as (
select empno e1;
lead (empno) over (order by rownum) e2;
lead (empno, 2) over (order by rownum) e3;
lead (empno, 3) over (order by rownum) e4;
rownum rn
from emp
)
select e1;
e2;
e3;
e4
from t
where mod (rn, 4) = 1
order by rn
/
E1 E2 E3 E4
---------- ---------- ---------- ----------
7369 7499 7521 7566
7654 7698 7782 7788
7839 7844 7876 7900
7902 7934
SQL>

SY.

3

Re: Result of request (column) to break and deduce in N-columns

Alexander Warlord;

SQL> select e1, e2, e3, e4 from
2 (select empno, mod (rownum-1,4) c, trunc ((rownum-1)/4) r from emp)
3 pivot (max (empno) for c in (0 as e1,1 as e2, 2 as e3,3 as e4))
4 order by r
5 /
E1 E2 E3 E4
-------- -------- -------- --------
7369 7499 7521 7566
7654 7698 7782 7788
7839 7844 7876 7900
7902 7934

....
stax

4

Re: Result of request (column) to break and deduce in N-columns

Thanks!) thought just through mod () to do, only not especially it turned out.

5

Re: Result of request (column) to break and deduce in N-columns

SY;

Execution Plan
----------------------------------------------------------
Plan hash value: 773907260
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 910 | 5 (40) | 0:00:01 AM |
| 1 | SORT ORDER BY | | 14 | 910 | 5 (40) | 0:00:01 AM |
|* 2 | VIEW | | 14 | 910 | 4 (25) | 0:00:01 AM |
==> | 3 | WINDOW SORT | | 14 | 56 | 4 (25) | 0:00:01 AM |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0) | 0:00:01 AM |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):

Interesting, what for to sort? Sampling is arranged on on rownum
....
stax

6

Re: Result of request (column) to break and deduce in N-columns

Stax wrote:

it is interesting, what for to sort? Sampling is arranged on on rownum

RN it ROWNUM in CTE a not in main query. And I have no concept as the optimizer will fulfill main query - suddenly a? Therefore I write ORDER BY RN. And why the optimizer not glades WINDOW SORT is to Larry smile
SY.

7

Re: Result of request (column) to break and deduce in N-columns

Though under WINDOW SORT calculation LEAD, therefore there is no saying disappears also and whether there was boy SORT or not.
SY.

8

Re: Result of request (column) to break and deduce in N-columns

SY;
About RN it is clear, I about over (order by rownum)
Hoped that will be without a sort ( WINDOW BUFFER)
.....
stax

9

Re: Result of request (column) to break and deduce in N-columns

SY wrote:

Though under WINDOW SORT calculation LEAD, therefore there is no saying disappears also and whether there was boy SORT or not.

Well and how differently to count lead? Without sorting. All remaining variants look farfetched.

Stax wrote:

about RN it is clear, I about over (order by rownum)
Hoped that will be without a sort ( WINDOW BUFFER)

WINDOW BUFFER here cannot be in any way because analytical sorting is fulfilled by the first. Therefore here pure window sort. On the other hand, it would be more logical to expect that the second sorting will not be. Though actually on this step it, goodness knows, can and is not produced, and the step is left in the plan to show that the optimizer did not forget that the data should be produced in sorting order. Also can be in its upcoming versions (this step) replace any order by nosort.

10

Re: Result of request (column) to break and deduce in N-columns

AmKad wrote:

it is passed...
Well and how differently to count lead? Without sorting. All remaining variants look farfetched.
it is passed...
WINDOW BUFFER here cannot be in any way because analytical sorting is fulfilled by the first. Therefore here pure window sort. On the other hand, it would be more logical to expect that the second sorting will not be. Though actually on this step it, goodness knows, can and is not produced, and the step is left in the plan to show that the optimizer did not forget that the data should be produced in sorting order. Also can be in its upcoming versions (this step) replace any order by nosort.

About the second at me questions are not present

1 select empno
2, lead (empno) over (order by empno) le
3* from emp where EMPNO> 7369
SQL> /
EMPNO LE
-------- --------
7499 7521
7521 7566
7566 7654
7654 7698
7698 7782
7782 7788
7788 7839
7839 7844
7844 7876
7876 7900
7900 7902
7902 7934
7934
13 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4166611438
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 52 | 1 (0) | 0:00:01 AM |
| 1 | WINDOW BUFFER | | 13 | 52 | 1 (0) | 0:00:01 AM |
|* 2 | INDEX RANGE SCAN | I$EMP$ENPNO | 13 | 52 | 1 (0) | 0:00:01 AM |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access ("EMPNO"> 7369)
SQL> set autotrace off

There is no sorting for LEAD

stax

11

Re: Result of request (column) to break and deduce in N-columns

Stax;
It agree.

12

Re: Result of request (column) to break and deduce in N-columns

AmKad wrote:

Well and how differently to count lead? Without sorting.

Generally - in any way. And here in a a case with ORDER BY ROWNUM or ORDER BY 1 a there is no need.
Simply with ORDER BY 1 optimizer knows that sorting according to a literal is senseless and here ROWNUM to it the type a variable "and therefore o it c gets as". C ORDER BY 1:

SQL> explain plan for
2 select lead (sal) over (order by 1)
3 from emp
4 /
Explained.
SQL> select * from table (dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1066788578
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 3 (0) | 0:00:01 AM |
| 1 |>>> WINDOW BUFFER <<<| | 14 | 56 | 3 (0) | 0:00:01 AM |
| 2 | TABLE ACCESS FULL | EMP | 14 | 56 | 3 (0) | 0:00:01 AM |
---------------------------------------------------------------------------
9 rows selected.
SQL>

SY.

13

Re: Result of request (column) to break and deduce in N-columns

By the way, if o ORDER BY 1 sortings will not be and we receive as ROWNUM. Here from the point of view of result basically it is not very well sorted/not it is sorted, the main thing that for all LEAD was fulfilled equally.
SY.

14

Re: Result of request (column) to break and deduce in N-columns

SY wrote:

By the way if o ORDER BY 1 sortings will not be and we receive as ROWNUM. Here from the point of view of result basically it is not very well sorted/not it is sorted, the main thing that for all LEAD was fulfilled equally.
SY.

c 1  as it there will be added, and from with rownum should be guaranteed equally
.....
stax