Skip to Main Content
  • Questions
  • how to display selective record twice in the query?

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Muhammad Ahmad.

Asked: September 29, 2003 - 9:03 pm UTC

Last updated: August 26, 2010 - 10:15 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hello Tom,

Actually I want to display all the records from a table but alongwith I want to display every record that is divisible by 40 comes twice

I mean if there are 100 records then after using order by the 40th,80th record comes twice and total rows return is 102.

Is This possible in a single query or use some pl

My query is:

select trans.tdate,DESCRIPTION,case when in_out_type = 'INCOME' then amount end debit,
case when in_out_type = 'EXPENDITURE' then amount end credit,bal
from trans,balance where trans.id = balance.bid
--and trans.id > 480
ORDER BY ID

thanks


and Tom said...

I'll use 4 instead of 40 and 10 instead of 100.

The query on lines 4..6 is your current query. We add "rownum" so we can see what rows 4 and 8 are.

We cartesian product this to a 2 row result set

we always output the "first" row
we output the second row when mod(rownum,4) is 0 -- when it is divisible by 4

ops$tkyte@ORA920> select a.*, b.*,
2 decode( b.x, 2, '<<<<====' )
3 from
4 ( select rownum r, object_name
5 from all_objects
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.r
13 /

R OBJECT_NAME X DECODE(B
---------- ------------------------------ ---------- --------
1 /1005bd30_LnkdConstant 1
2 /10076b23_OraCustomDatumClosur 1
3 /10297c91_SAXAttrList 1
4 /103a2e73_DefaultEditorKitEndP 1
4 /103a2e73_DefaultEditorKitEndP 2 <<<<====
5 /1048734f_DefaultFolder 1
6 /10501902_BasicFileChooserUINe 1
7 /105072e7_HttpSessionBindingEv 1
8 /106ba0a5_ArrayEnumeration 1
8 /106ba0a5_ArrayEnumeration 2 <<<<====
9 /106faabc_BasicTreeUIKeyHandle 1
10 /10744837_ObjectStreamClass2 1

12 rows selected.


Rating

  (38 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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 !!


Tom Kyte
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

Tom Kyte
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;

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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. 

Tom Kyte
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 !

Tom Kyte
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. 

Tom Kyte
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?

Tom Kyte
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.



Tom Kyte
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? 

Tom Kyte
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

Tom Kyte
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 ???

Tom Kyte
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.

Tom Kyte
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)

Tom Kyte
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?

Tom Kyte
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!

Tom Kyte
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


Tom Kyte
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;
/

Tom Kyte
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
Tom Kyte
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.
Tom Kyte
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

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here