and this ?!!
huss, October 01, 2003 - 7:57 am UTC
nice answer ,
but help me in this,
how i make a query that retuen more than one row while not depend on a db table (ie select from dual)
example :
select ...........
from dual
and returns some thing like
id name
--------------------
1 n1
2 n2
3 n3
3 row selected !!
October 01, 2003 - 9:33 am UTC
create a pipelined function in 9i -- you can select * from PLSQL_FUNCTION
search for pipelined
Is it possible in 8i?
Murali, October 01, 2003 - 10:54 am UTC
Tom,
In an Interview, I was asked to print 1 to 50 using dual table. They said, I should not use UNION. Is it possible in 8i?
Thanks
Murali
October 01, 2003 - 1:51 pm UTC
maybe it was a trick question:
select 1, 2, 3, 4, 5, .... 50 from dual;
that prints 1 to 50...
Ian Matyssik, October 01, 2003 - 1:02 pm UTC
select rownum from ( select 1,2,3,4,5,6,7,8,9,10 from dual group by cube (5,6,7,8,9,10)) where rownum <=50;
October 01, 2003 - 1:58 pm UTC
cool -- didn't think about that !
Excellent Ian Matyssik
Pawan, October 01, 2003 - 2:30 pm UTC
Tom,
Can you explain the use of "cube" by Ian.
October 01, 2003 - 6:06 pm UTC
group by cube (or rollup) produces (injects) extra rows into the result set. The rows are the aggregated each and every way -- consider this query:
scott@ORA920> select ename, job, sum(sal),
2 grouping(ename),
3 grouping(job)
4 from emp
5 group by cube(ename,job)
6 /
that says:
show me sal by:
ename, job
ename, <over job>
job, <over ename>
<over both job and ename>
we can use the builtin "grouping" function to see what rows mean what:
ENAME JOB SUM(SAL) GROUPING(ENAME) GROUPING(JOB)
---------- --------- ---------- --------------- -------------
29025 1 1
both ename and job were "grouped over", this is select sum(sal) from emp"
CLERK 4150 1 0
ANALYST 6000 1 0
MANAGER 8275 1 0
SALESMAN 5600 1 0
PRESIDENT 5000 1 0
there was sum(sal) by job -- over ename
FORD 3000 0 1
FORD ANALYST 3000 0 0
there is a row of sum(sal) by ename/job
KING 5000 0 1
and a row of sum(sal) by ename -- over job
KING PRESIDENT 5000 0 0
WARD 1250 0 1
WARD SALESMAN 1250 0 0
ADAMS 1100 0 1
ADAMS CLERK 1100 0 0
ALLEN 1600 0 1
ALLEN SALESMAN 1600 0 0
BLAKE 2850 0 1
BLAKE MANAGER 2850 0 0
CLARK 2450 0 1
CLARK MANAGER 2450 0 0
JAMES 950 0 1
JAMES CLERK 950 0 0
JONES 2975 0 1
JONES MANAGER 2975 0 0
SCOTT 3000 0 1
SCOTT ANALYST 3000 0 0
SMITH 800 0 1
SMITH CLERK 800 0 0
MARTIN 1250 0 1
MARTIN SALESMAN 1250 0 0
MILLER 1300 0 1
MILLER CLERK 1300 0 0
TURNER 1500 0 1
TURNER SALESMAN 1500 0 0
34 rows selected.
So, instead of 14 rows -- we get 34 with the aggregate sum(sal) computed each and every way.
He just used a single row in dual with enough "columns" to trick us into giving enough rows out.
Got it!!
Pawan, October 01, 2003 - 3:19 pm UTC
Tom,
Even the following query will give the same result -
select rownum from ( select 1 from dual group by cube (1,1,1,1,1,1))
where rownum <51
Now I think I understand what CUBE is doing ( just 2^n where n=6 in this case)
Every time I learn something at this site
Thanks
October 01, 2003 - 6:08 pm UTC
guess what -- me too :)
every day, something new
interesting
Dennis, October 01, 2003 - 5:39 pm UTC
Ok,
I still don't really know what cube does but I am intrigued as to why the first query,
select rownum from ( select 1,2,3,4,5,6,7,8,9,10 from dual
group by cube (5,6,7,8,9,10)) where rownum <=50
is more performant than the second one. Is it because the second one has to go up by 1s?
Thanks,
Dennis
Please explain.
Sai, October 01, 2003 - 6:15 pm UTC
Hi Tom,
Can you help me understand the behaviour of "cube" when the column name repeats in expression. For example:
"select username,username from (select username from dba_users where rownum<=2) group by cube (username,username)" is giving null row output for some combinations of groups, while the following query giving no null row output except for final summary:
"select username,user_id from (select username,user_id from dba_users where rownum<=2) group by cube (username,user_id)".
Please explain -- Thanks.
October 01, 2003 - 6:25 pm UTC
it would be sort of "ambigous" to do that
but it doesn't happen for me:
ops$tkyte@ORA920> set null ****
ops$tkyte@ORA920> /
USERNAME USERNAME
------------------------------ ------------------------------
SYS SYS
SYSTEM SYSTEM
SYS SYS
SYSTEM SYSTEM
SYS SYS
SYSTEM SYSTEM
**** ****
7 rows selected.
it doesn't really "make sense"
Could it be a bug?
Sai, October 01, 2003 - 6:33 pm UTC
Hi Tom,
I am using 8.1.7.4 on Windows 2000 professional box. Here is the output I got for the same query you ran.
SQL> select username,username from (select username from dba_users where rownum<
=2)
2 group by cube (username,username);
USERNAME USERNAME
------------------------------ ------------------------------
SYS SYS
SYS SYS
SYSTEM SYSTEM
SYSTEM SYSTEM
6 rows selected.
You got 7 rows selected, I got 6 rows selected. Is it a bug??? -- Thanks.
October 01, 2003 - 6:56 pm UTC
looks like it, yes
I cannot get my head around this ?
A reader, October 02, 2003 - 5:12 am UTC
select rownum from ( select 1 from dual group by cube (1,1,1,1,1,1)) where rownum <51
I just cannot see how this works, despite it being such a simple looking query. Any chance you could break it down ?
Sorry for being so dumb !
October 02, 2003 - 7:54 am UTC
does this make more sense to you:
select rownum from ( select 1 from dual group by cube
('a','b','c','d','e','f')) where
rownum <51
/
or maybe:
select rownum
from ( select c1, c2, c3, c4, c5, c6, count(*)
from (select 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
from dual )
group by cube(c1,c2,c3,c4,c5,c6)
)
where rownum <51
/
they are all functionally equivalent. group by cube(1,1,1,1,1,1) is just short hand for that -- instead of having "real" columns -- it is grouping by literals.
Ian Matyssik, October 02, 2003 - 9:51 am UTC
I do not know what is so confusing about CUBE. All it does is displaying every possible combination of the given variables( in our case literals ). So if you go CUBE(1,2,3) it will give you the same row (1,2,3) in every possible order = 2^3. So that is as simple as that. So the quick hack to get more then 1 row from dual is create some literals instead of columns and group them by CUBE. I by misstake put 10 literals in the first query beacuse it is more confusing to the reader what I could do is 2^6 = 64
SQL> select power(2,6) from dual;
POWER(2,6)
----------
64
so we need total of 50 rows that is closest (64) and just select from that query ROWNUM which will give you sequencial numbers. And that is all trick. So if you do cube of 7 columns and one row you get 128 ans so on.
To answer Dennis from Missouri, USA
Ian Matyssik, October 02, 2003 - 10:02 am UTC
Here is a output of autotrace
SQL>
SQL> select rownum from ( select 1 from dual group by cube (1,1,1,1,1,1))
where rownum <51 2
3 ;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
ROWNUM
----------
12
13
14
15
16
17
18
19
20
21
22
ROWNUM
----------
23
24
25
26
27
28
29
30
31
32
33
ROWNUM
----------
34
35
36
37
38
39
40
41
42
43
44
ROWNUM
----------
45
46
47
48
49
50
50 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 4 RECURSIVE EXECUTION OF 'SYS_LE_4_0'
4 2 TEMP TABLE TRANSFORMATION
5 4 VIEW
6 5 VIEW
7 6 UNION-ALL
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
9 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
10 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
11 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
12 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
13 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
14 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
15 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
16 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
17 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
18 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
19 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
20 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
21 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
22 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
23 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
24 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
25 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
26 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
27 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
28 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
29 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
30 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
31 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
32 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
33 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
34 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
35 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
36 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
37 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
38 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
39 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
40 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
41 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
42 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
43 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
44 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
45 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
46 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
47 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
48 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
49 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
50 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
51 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
52 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
53 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
54 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
55 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
56 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
57 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
58 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
59 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
60 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
61 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
62 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
63 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
64 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
65 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
66 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
67 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
68 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
69 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
70 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_360
3CB5'
71 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6607_360
3CB5'
Statistics
----------------------------------------------------------
133 recursive calls
14 db block gets
185 consistent gets
1 physical reads
2080 redo size
1059 bytes sent via SQL*Net to client
536 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50 rows processed
SQL> select rownum from ( select 1,2,3,4,5,6 from dual group by cube ( 1,2,3,4,5,6 ) );
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
ROWNUM
----------
12
13
14
15
16
17
18
19
20
21
22
ROWNUM
----------
23
24
25
26
27
28
29
30
31
32
33
ROWNUM
----------
34
35
36
37
38
39
40
41
42
43
44
ROWNUM
----------
45
46
47
48
49
50
51
52
53
54
55
ROWNUM
----------
56
57
58
59
60
61
62
63
64
64 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT
2 1 VIEW
3 2 SORT (GROUP BY)
4 3 GENERATE (CUBE)
5 4 SORT (GROUP BY)
6 5 TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1239 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
64 rows processed
This is an output it gave me so as you can see execution was handeled differently. As to why I would ask Tom to help here.
October 02, 2003 - 10:08 am UTC
putting rownum INSIDE the inline view changes the semantics of queries greatly.
In my new book, Effective Oracle By Design, I show how to use that to great effect in tuning some cases.
Here, putting rownum into the inline view would not be appropriate -- it has to generate the entire cube and then select from it. it was like an "all rows" optimization.
The second query allowed us to feed back rows as they were generated -- they didn't need to have a rownum assigned to them -- it was like "first_rows"
Question to Tom.
Ian Matyssik, October 02, 2003 - 10:26 am UTC
But Tom, I see that the rownum was put outside in both queries and the only distinction I see in the first query that only one column was selected and cube was generated based on that single column/literal (CUBE(1,1,1,1,1,1)). So my guess would be that Oracle chose to do something different then CUBEing by distinct values. So I still would like to know what exectly Oracle does when you cube on the same value, or could it be the optimiser doing something wrong?
October 02, 2003 - 11:01 am UTC
sorry -- it was very hard to read and see the queries with all of the stuff in between -- i read it wrong.
don't know. as both queries are really "nonsense" queries -- they would never actually be used in real life -- not going to worry to much about it.
Ok, I'm still confused.
Mark J. Bobak, October 02, 2003 - 11:38 am UTC
I (think I) conceptually understand CUBE(). Thanks to Tom
and Ian for countless examples.
Here's what's baffling me:
select rownum from(select 1 from dual group by cube(1,2,3,4,5,6));
will return 1-64. Ok, fine, 2^6 = 64.
But then, why:
select 1 from dual cube(1,2,3,4,5,6);
Will only return 1??
Shouldn't it return 64 lines? If we wrap:
select rownum from (....); around it, we get 64
rows, so, why only one when the inner query is
executed???
Sorry, perhaps I'm being a little thick, but I don't
get it.
October 02, 2003 - 4:04 pm UTC
dual is special, optmizer "knows" dual does a single row. Watch this (but don't feel compelled to do this on your system and for heavens sake ROLLBACK if you do -- do not commit!!!)
ops$tkyte@ORA920> insert into dual values ( 'x' );
1 row created.
ops$tkyte@ORA920> select * from dual;
D
-
X
<b>where is 'x'???</b>
ops$tkyte@ORA920> select count(*) from dual;
COUNT(*)
----------
2
<b>should be there...</b>
ops$tkyte@ORA920> select * from dual where dummy = 'x';
D
-
x
<b>and it is</b> but the optimizer "knew" dual had a single row. wait'll you see dual in 10g :)
Because ...
TOM_PARTII, October 02, 2003 - 11:50 am UTC
it is a strange effect of the optimzer "knowing" dual should have one row. it
was not expecting the group by cube to explode the data.
Clarification
Richard, October 02, 2003 - 12:03 pm UTC
I found the following helpful, with regard to understanding how CUBE works:
====
SQL:
====
select *
from ( select c1, c2, c3, c4, c5, c6
from (select 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
from dual )
group by cube(c1,c2,c3,c4,c5,c6)
);
=======
Result:
=======
C C C C C C
- - - - - -
a b c d e f
a b c d e
a b c d f
a b c d
a b c e f
a b c e
a b c f
a b c
a b d e f
a b d e
a b d f
a b d
a b e f
a b e
a b f
a b
a c d e f
a c d e
a c d f
a c d
a c e f
a c e
a c f
a c
a d e f
a d e
a d f
a d
a e f
a e
a f
a
b c d e f
b c d e
b c d f
b c d
b c e f
b c e
b c f
b c
b d e f
b d e
b d f
b d
b e f
b e
b f
b
c d e f
c d e
c d f
c d
c e f
c e
c f
c
d e f
d e
d f
d
e f
e
f
64 rows selected.
==================================
The SQL is just Tom's earlier SQL, with * replacing 1.
As can be seen there are indeed 64 combinations!
to Mark
Freek D'Hooge, October 02, 2003 - 12:39 pm UTC
Mark,
you can see this when you do:
==> this will give you 64 rows
select 1
from ( select 1
from user_tables
where rownum = 1
)
group by cube (1,2,3,4,5,6);
==> this will give you 1 row
select 1
from dual
group by cube (1,2,3,4,5,6);
Thanks
Dennis, October 02, 2003 - 12:48 pm UTC
Thanks Ian.
It does seem like a result of selecting the one value instead of selecting a large enough piece that it could just work with as part of the cube.
Thanks,
Dennis
One More thing in CUBE?
Muhammad Ahmad Imran, October 02, 2003 - 12:58 pm UTC
select rownum from (select 1 from dual group by cube(1,1,1,1,1,1)) where rownum<=50;
also prints the 50 numbers
To Original Question
Muhammad Ahmad Imran, October 02, 2003 - 1:03 pm UTC
1 select a.*, b.*,
2 decode( b.x, 2, '<<<<====' )
3 from
4 ( select rownum r, ename,hiredate
5 from emp
6 where rownum <= 10) a,
7 ( select 1 x from dual
8 union all
9 select 2 x from dual) b
10 where b.x = 1
11 or (b.x = 2 and mod(a.r,4) = 0)
12* order by a.hiredate
SQL> /
R ENAME HIREDATE X DECODE(B
---------- ---------- --------- ---------- --------
1 SMITH 17-DEC-80 1
2 ALLEN 20-FEB-81 1
3 WARD 22-FEB-81 1
4 JONES 02-APR-81 1
4 JONES 02-APR-81 2 <<<<====
6 BLAKE 01-MAY-81 1
7 CLARK 09-JUN-81 1
10 TURNER 08-SEP-81 1
5 MARTIN 28-SEP-81 1
9 KING 17-NOV-81 1
8 SCOTT 19-APR-87 1
8 SCOTT 19-APR-87 2 <<<<====
12 rows selected.
SQL>
So I actually want to be duplicate records by hiredate then what to do?
October 02, 2003 - 4:50 pm UTC
sorr, not following you -- what do you mean "by hiredate"
Muhammad Ahmad Imran, October 03, 2003 - 6:19 am UTC
In the last line I put a clause of order by a.hiredate then ruesults are disturbed
************************
Look this is what I want
*************************
R ENAME HIREDATE X DECODE(B
---------- ---------- --------- ---------- --------
1 SMITH 17-DEC-80 1
2 ALLEN 20-FEB-81 1
3 WARD 22-FEB-81 1
4 JONES 02-APR-81 1
4 JONES 02-APR-81 2 <<<<====
6 BLAKE 01-MAY-81 1
7 CLARK 09-JUN-81 1
10 TURNER 08-SEP-81 1
5 MARTIN 28-SEP-81 1
5 MARTIN 28-SEP-81 2 <<<<====
9 KING 17-NOV-81 1
8 SCOTT 19-APR-87 1
12 rows selected.
so I want results displaying twice order by hiredate not by rownum
October 03, 2003 - 8:41 am UTC
it helps to be "clear". I see lots of information every day -- its really hard to "guess" what people mean.
you post a query (that looks reasonable), a result set that looks fine, and say simply
"So I actually want to be duplicate records by hiredate then what to do? "
which isn't really a sentence.... makes it sort of "hard".
you need to assign the rownum to the EMP data AFTER sorting
scott@ORA920> select a.*, b.*,
2 decode( b.x, 2, '<<<<====' )
3 from
4 ( select rownum r,
5 ename,
6 hiredate
7 from ( select ename, hiredate
8 from emp
9 order by hiredate
10 )
11 where rownum <= 10) a,
12 ( select 1 x from dual
13 union all
14 select 2 x from dual) b
15 where b.x = 1
16 or (b.x = 2 and mod(a.r,4) = 0)
17 order by a.hiredate
18 /
R ENAME HIREDATE X DECODE(B
---------- ---------- --------- ---------- --------
1 SMITH 17-DEC-80 1
2 ALLEN 20-FEB-81 1
3 WARD 22-FEB-81 1
4 JONES 02-APR-81 1
4 JONES 02-APR-81 2 <<<<====
5 BLAKE 01-MAY-81 1
6 CLARK 09-JUN-81 1
7 TURNER 08-SEP-81 1
8 MARTIN 28-SEP-81 1
8 MARTIN 28-SEP-81 2 <<<<====
9 KING 17-NOV-81 1
10 JAMES 03-DEC-81 1
12 rows selected.
1 Row ??
Matt, October 03, 2003 - 10:12 am UTC
I understand 64 rows however:
SELECT 1 FROM dual GROUP BY cube (1,1,1,1,1,1)
Returns 25 Rows in my database 8.1.7.4
Why ???
October 03, 2003 - 10:24 am UTC
dual, magic, special, different, many special things shown about it above.
TOAD !
Matt, October 03, 2003 - 10:19 am UTC
Oops it seems it's TOAD thats causing 25 errors - run in SQL*Plus I see one as expected.
Like to know the explanation - but i'll ask the Toad mail list.
Excellent
Muhammad Ahmad Imran, October 04, 2003 - 5:09 am UTC
That's great. Thanks a lot
ARRAYSIZE is the source of this unexpected behavior
Art Metzer, November 03, 2003 - 12:54 pm UTC
To those who've noticed less than POWER(2,n) rows when you perform SELECT NULL FROM DUAL GROUP BY CUBE (1,1,1,1,...) [n 1's], ARRAYSIZE is the culprit, but *ONLY* if you select directly from DUAL. If you wrap the SELECT NULL FROM DUAL GROUP BY CUBE(1,1,1,...) in an in-line view, the expected behavior results.
SQL> SHOW ARRAYSIZE
arraysize 15
SQL> SELECT NULL
2 FROM DUAL
3 GROUP BY CUBE(1,1,1,1,1) -- 32 rows expected
4 /
N
-
15 rows selected.
SQL> SET ARRAYSIZE 50
SQL> SELECT NULL
2 FROM DUAL
3 GROUP BY CUBE(1,1,1,1,1)
4 /
N
-
32 rows selected.
SQL> SET ARRAYSIZE 15
SQL> SELECT ROWNUM
2 FROM
3 (SELECT NULL
4 FROM DUAL
5 GROUP BY CUBE(1,1,1,1,1)
6 ) -- this one wrapped in an in-line view
7 /
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
32 rows selected.
SQL>
Does anyone have any idea why ARRAYSIZE influences GROUP BY CUBE in this manner?
Thanks, and HTH,
Art.
CUBE
Narendar, November 12, 2003 - 10:20 pm UTC
This was good but we seldom use this case in real, Just a brief clear description of the CUBE might have resolved all long conversations and the example of Mr.Richard was good for others to understand how the cube works.
Most posters here
Mikito Harakiri, February 04, 2005 - 9:36 pm UTC
seem to have trouble seing the difference between finite and infinite.
OK, exponentiate the number, it is still produces a finite number (of rows). One day your program with this "cool" cube solution would break just because you have been lazy enough to write a trivial pipeline function. Essentially, cube is as bad as selecting from obj$ (OK, col$ is probably bigger). Well, except that you take your fellow programmer time that has to understand your code, in particular, what group by cube is doing.
select rownum from Table(Integers()) where rownum < 50
is much cleaner and robust.
if you want the simplest number generator
mikito, February 04, 2005 - 9:40 pm UTC
select rownum from dual
connect by 1=1
and rownum < 50
minor simplification
mikito, March 10, 2005 - 2:54 pm UTC
I'm flattered with growing connect by number generator popularity. Removing rediundant 1=1 predicate is a tiny enhancement
select rownum from dual
connect by rownum < 10
Apparently, 10g connect by enhancements changed loop detection criteria which is now based upon the "prior" keyword in the "connect by" clause. This is why this generator doesn't work in the earlier releases.
March 10, 2005 - 7:35 pm UTC
use level, not rownum -- filed a bug on rownum already (and I credited you somewhere on this site for having the row generator idea)...
but select rownum from dual connect by rownum < 51 works fine with 9.2.0.6.0
A reader, March 11, 2005 - 2:59 am UTC
(which in fact is pre 10g :o)
March 11, 2005 - 6:14 am UTC
and it is post 10g that has the issue.
hmmm, i thought that ...
A reader, March 11, 2005 - 7:12 am UTC
select rownum from dual
connect by rownum < 10
would *NOT* work with "earlier releases". this is how i've read the above "this is why THIS generator doesn't work in the ...".
what did i get wrong here?
could you please explain your level instead of rownum tip?
March 11, 2005 - 7:30 am UTC
ops$tkyte@ORA10G> with x
2 as
3 (select rownum from dual
4 connect by rownum < 10)
5 select * from x
6 /
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
ops$tkyte@ORA10G> 4
4* connect by rownum < 10)
ops$tkyte@ORA10G> c/rownum/level
4* connect by level < 10)
ops$tkyte@ORA10G> /
ROWNUM
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
Use level.
To Mikito
A reader, March 11, 2005 - 8:34 am UTC
Care to explain why
select rownum from Table(Integers()) where rownum < 50
is "more performant and robust" than
select rownum ( select 1 from dual group by cube( 1, 1, 1, 1, 1, 1 ) ) where rownum < 50
?
Thanks!
March 11, 2005 - 10:54 am UTC
it is alot easier, more readable, cleaner, understandable.
but benchmark it yourself. tkprof goes along long way here.
In 9i, you'll find the connect by does very little logical IO but group by cube -- does not. In 10g, neither have logical IO
pipelined function
Rajesh, March 12, 2005 - 12:49 am UTC
Dear Tom,
First, thanks for all the stuff you have in our site. it is very useful.
I went thru pipelined function. it was very userful.
Usually I am doing the following in place of pipelined.
select a.col1, b.col1, b.col2
from table1 a, (select col1, col2, col3 from table2) b
where a.col1 = b.col1 and
a.col2 = b.col2;
This solves the same purpose of pipelined.
which is better in these two?
Rajesh
March 12, 2005 - 10:09 am UTC
I have no idea what you mean by this solves the same purpose of a pipelined?
that is a join,
select * from table( f(x) );
is querying a function - a function that produces rows. Not sure what you meant.
Pipeline or Nested Table?
Allan Morstein, April 09, 2007 - 11:26 am UTC
I recently got a request to do a very similar thing (create a way to select N dummy rows). While I did look into using a pipelined function, I'm not sure that it's the best approach in this situation. Using the pipeline, you'd need to iterate through a loop N times and the query would get access to each row as the loop runs.
Instead, I created a function that returns a nested table. All the function has to do is initialize the table, then extend it N times. This way, the entire (empty) table becomes available immediately for the select to use.
CREATE OR REPLACE TYPE nt_dummy_row AS TABLE OF NUMBER (1)
/
CREATE OR REPLACE FUNCTION fn_build_dummy_table (p_quantity IN NUMBER)
RETURN nt_dummy_row
IS
t_dummy nt_dummy_row := nt_dummy_row ();
BEGIN
t_dummy.EXTEND (p_quantity);
RETURN t_dummy;
END fn_build_dummy_table;
/
April 09, 2007 - 1:06 pm UTC
with data
as
(select level l from dual connect by level <= :quantity)
select *
from data
/
as for your comments about how pipelined functions work - that is not entirely true. I would not use a table type, extend it out, return it - why allocate memory for 10,000 things when you never needed to? The invoker of the query starts getting rows from the pipelined function efficiently and quickly. Using your approach - you'd have to allocate memory in the server, allocate the array, save that somewhere and then let the client pick through it row by row (or array fetch by array fetch).
Display records based on date range
David Leland, November 13, 2008 - 12:53 pm UTC
Tom:
I have something very similar to the original question but instead of displaying a record twice, I would like to display the record as many times as there are days between a given date range (in the same table). I am currently using Oracle 9i and have created a table:
CREATE TABLE FSR_TECH
(
ID VARCHAR2(20),
TECHNICIAN VARCHAR2(20),
FROM_DATE DATE,
THRU_DATE DATE
)
I have inserted the following data into it:
INSERT INTO FSR_TECH VALUES('FSR0000001','JIM.POULSEN','30-SEP-2008','01-OCT-2008');
INSERT INTO FSR_TECH VALUES('FSR0000002','DAVE.ROSE','13-OCT-2008','13-OCT-2008');
INSERT INTO FSR_TECH VALUES('FSR0000002', 'LLOYD.MEHANEY', '13-OCT-2008', '13-OCT-2008');
INSERT INTO FSR_TECH VALUES('FSR0000003', 'DAVE.ROSE', '06-OCT-2008', '13-OCT-2008');
COMMIT;
ID TECHNICIAN FROM_DATE THRU_DATE
---------- -------------------- ---------- ----------
FSR0000001 JIM.POULSEN 2008-09-30 2008-10-01
FSR0000002 DAVE.ROSE 2008-10-13 2008-10-13
FSR0000002 LLOYD.MEHANEY 2008-10-13 2008-10-13
FSR0000003 DAVE.ROSE 2008-10-06 2008-10-13
I would like to create a view over it by including all dates between the from and thru dates (inclusively) and have it presented as follows:
TECHNICIAN DATE
------------- ----------
JIM.POULSEN 2008-09-30
JIM.POULSEN 2008-10-01
DAVE.ROSE 2008-10-13
LLOYD.MEHANEY 2008-10-13
DAVE.ROSE 2008-10-06
DAVE.ROSE 2008-10-07
DAVE.ROSE 2008-10-08
DAVE.ROSE 2008-10-09
DAVE.ROSE 2008-10-10
DAVE.ROSE 2008-10-11
DAVE.ROSE 2008-10-12
DAVE.ROSE 2008-10-13
Any ideas?
Dave
November 14, 2008 - 4:44 pm UTC
ops$tkyte%ORA10GR2> select *
2 from fsr_tech t,
3 table( cast( multiset( select from_date+level-1
4 from dual
5 connect by level <= thru_date-from_date+1) as sys.odcidatelist ) )
6 /
ID TECHNICIAN FROM_DATE THRU_DATE COLUMN_VA
-------------------- -------------------- --------- --------- ---------
FSR0000001 JIM.POULSEN 30-SEP-08 01-OCT-08 30-SEP-08
FSR0000001 JIM.POULSEN 30-SEP-08 01-OCT-08 01-OCT-08
FSR0000002 DAVE.ROSE 13-OCT-08 13-OCT-08 13-OCT-08
FSR0000002 LLOYD.MEHANEY 13-OCT-08 13-OCT-08 13-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 06-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 07-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 08-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 09-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 10-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 11-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 12-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 13-OCT-08
12 rows selected.
Display records based on date range - Ansi Join
Ian, August 24, 2010 - 11:29 am UTC
Hi Tom,
I attempted to use the previous example under 10g (10.2.0.4.0) but converted to use the ansi join syntax.
select *
from fsr_tech t cross join
table( cast( multiset( select from_date+level-1
from dual
connect by level <= thru_date-from_date+1) as sys.odcidatelist ));However that generates an ORA-00904: "FROM_DATE": invalid identifier. The cross join syntax works if the cast and multiset are replace by a function invocation:
select *
from fsr_tech t cross join
table(some_pkg.expand_dates(from_date, thru_date));
create or replace package some_pkg as
function expand_dates (frm_dt in date, thru_dt in date) return sys.odcidatelist;
end;
create or replace package body some_pkg as
function expand_dates (frm_dt in date, thru_dt in date) return sys.odcidatelist is
l_date sys.odcidatelist := sys.odcidatelist();
begin
select cast( multiset( select frm_dt+level-1
from dual
connect by level <= thru_dt-frm_dt+1 )
as sys.odcidatelist )
into l_date
from dual;
return l_date;
end;
end;I've been unable to find any documentation to say why it works one way but not the other. Any idea what I'm missing?
Thanks,
Ian.
August 26, 2010 - 10:15 am UTC
you do not do that, it is called table unnesting - it is not truly a join, you use the original syntax and that is all.
just use the comma notation. It works as it should with the 'normal syntax'
it is probably due to the scoping rules of the ansi joins, correlated references are allowed only one level deep - it looks more strict in the ansi join.
but table unnesting shouldn't really be "joined", it is more natural just to say "table this" - no joining - there is no "on" clause to be present.
Mihail Bratu, August 31, 2010 - 7:28 am UTC
Hello,
Let's look from another angle!
CREATE OR REPLACE
VIEW FSR_TECH_OBJ_VIEW
AS
SELECT ID
,TECHNICIAN
,FROM_DATE
,THRU_DATE
,CAST( MULTISET(
SELECT FROM_DATE+LEVEL-1
FROM DUAL
CONNECT BY LEVEL <= THRU_DATE-FROM_DATE+1
) AS SYS.ODCIDATELIST ) days_in_interval
FROM FSR_TECH
/
SELECT * FROM FSR_TECH_OBJ_VIEW
/
ID TECHNICIAN FROM_DATE THRU_DATE DAYS_IN_INTERVAL
-------------------- -------------------- --------- --------- ------------------------------------------------------------------------------------------------------------------------
FSR0000001 JIM.POULSEN 30-SEP-08 01-OCT-08 ODCIDATELIST('30-SEP-08', '01-OCT-08')
FSR0000002 DAVE.ROSE 13-OCT-08 13-OCT-08 ODCIDATELIST('13-OCT-08')
FSR0000002 LLOYD.MEHANEY 13-OCT-08 13-OCT-08 ODCIDATELIST('13-OCT-08')
FSR0000003 DAVE.ROSE 06-OCT-08 13-OCT-08 ODCIDATELIST('06-OCT-08', '07-OCT-08', '08-OCT-08', '09-OCT-08', '10-OCT-08', '11-OCT-08', '12-OCT-08', '13-OCT-08')
Now we can unpack (unnest or flatten) this object view:
SELECT ID
,TECHNICIAN
,t.COLUMN_VALUE day
FROM FSR_TECH_OBJ_VIEW
,TABLE(DAYS_IN_INTERVAL) t
/
ID TECHNICIAN DAY
-------------------- -------------------- ---------
FSR0000001 JIM.POULSEN 30-SEP-08
FSR0000001 JIM.POULSEN 01-OCT-08
FSR0000002 DAVE.ROSE 13-OCT-08
FSR0000002 LLOYD.MEHANEY 13-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08
FSR0000003 DAVE.ROSE 07-OCT-08
FSR0000003 DAVE.ROSE 08-OCT-08
FSR0000003 DAVE.ROSE 09-OCT-08
FSR0000003 DAVE.ROSE 10-OCT-08
FSR0000003 DAVE.ROSE 11-OCT-08
FSR0000003 DAVE.ROSE 12-OCT-08
FSR0000003 DAVE.ROSE 13-OCT-08
12 rows selected.
Regards
Mihail Bratu, August 31, 2010 - 7:35 am UTC
Sorry again me,
Natural join works too:
SELECT ID
,TECHNICIAN
,t.COLUMN_VALUE day
FROM FSR_TECH_OBJ_VIEW
NATURAL JOiN TABLE(DAYS_IN_INTERVAL) t
/
ID TECHNICIAN DAY
-------------------- -------------------- ---------
FSR0000001 JIM.POULSEN 30-SEP-08
FSR0000001 JIM.POULSEN 01-OCT-08
FSR0000002 DAVE.ROSE 13-OCT-08
FSR0000002 LLOYD.MEHANEY 13-OCT-08
FSR0000003 DAVE.ROSE 06-OCT-08
FSR0000003 DAVE.ROSE 07-OCT-08
FSR0000003 DAVE.ROSE 08-OCT-08
FSR0000003 DAVE.ROSE 09-OCT-08
FSR0000003 DAVE.ROSE 10-OCT-08
FSR0000003 DAVE.ROSE 11-OCT-08
FSR0000003 DAVE.ROSE 12-OCT-08
FSR0000003 DAVE.ROSE 13-OCT-08
12 rows selected.
sadeesh, November 24, 2014 - 9:31 am UTC
select level from dual connect by level<=50