Possibly "my_table" can be used to generate the ROWNUM set? Only Sathy will know that.
Jurij, MINUS does look neater than my NOT IN.
Printable View
Possibly "my_table" can be used to generate the ROWNUM set? Only Sathy will know that.
Jurij, MINUS does look neater than my NOT IN.
Well, yes its useless.. was in haste.. & never thought of a case with gaps more than 1.Quote:
Originally posted by jmodic
However it is also obvious that your suggesten was equaly useless and wrong.
Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.
Of course to get all the values of the gaps themselves we are (as we have seen) going to have to synthesize some rows. An alternative to ALL_OBJECTS here is to use a table function. This gives us an interesting choice - we can either synthesize all the rows up front or we can synthesize rows on a row-by-row basis via the TABLE () syntax as required. First up is the row-by-row approach - perhaps if there are only a few gaps this could be a more efficient approach.Code:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> CREATE OR REPLACE TYPE number_table AS TABLE OF NUMBER (10);
2 /
Type created.
SQL> CREATE TABLE table_name (
2 column_name NUMBER (10));
Table created.
SQL> INSERT INTO table_name
2 SELECT column_value
3 FROM TABLE (number_table (2, 4, 5, 8, 10));
5 rows created.
SQL> COMMIT;
Commit complete.
SQL> VARIABLE min NUMBER;
SQL> VARIABLE max NUMBER;
SQL> EXEC :min := 1; :max := 10;
PL/SQL procedure successfully completed.
SQL> SELECT now + 1 || DECODE (now + 2, nxt, NULL, ' - ' || (nxt - 1)) gap
2 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
3 FROM (SELECT column_name now
4 FROM table_name
5 UNION ALL
6 SELECT column_value
7 FROM TABLE (number_table (:min - 1, :max + 1))))
8 WHERE now + 1 != nxt;
GAP
--------------------------------------------------------------------------
1
3
6 - 7
9
4 rows selected.
SQL>
Alternatively we can (as mentioned) synthesize all the rows up-front using the table function and anti-join, outer-join or MINUS as suggested by Dapi and Jurij.Code:SQL> CREATE OR REPLACE FUNCTION many (
2 p_rows IN NUMBER)
3 RETURN number_table PIPELINED
4 IS
5 BEGIN
6 FOR l_row IN 1..p_rows LOOP
7 PIPE ROW (l_row);
8 END LOOP;
9 RETURN;
10 END;
11 /
Function created.
SQL> SELECT now + column_value now
2 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
3 FROM (SELECT column_name now
4 FROM table_name
5 UNION ALL
6 SELECT column_value
7 FROM TABLE (number_table (:min - 1, :max + 1)))),
8 TABLE (many (nxt - now - 1))
9 WHERE now + 1 != nxt;
NOW
----------
1
3
6
7
9
5 rows selected.
SQL>
If follows that we can extend these two examples to give us all the values (and also know which is fake or real).Code:SQL> SELECT now
2 FROM (SELECT :MIN + column_value - 1 now
3 FROM TABLE (many (:max - :min + 1))) a,
4 table_name b
5 WHERE column_name IS NULL
6 AND column_name(+) = now;
NOW
----------
1
3
6
7
9
5 rows selected.
SQL>
Code:SQL> SELECT DECODE (now, now - 1 + column_value, 'REAL', 'FAKE') src,
2 now - 1 + column_value now
3 FROM (SELECT now, LEAD (now) OVER (ORDER BY now) nxt
4 FROM (SELECT column_name now
5 FROM table_name
6 UNION ALL
7 SELECT column_value
8 FROM TABLE (number_table (:min - 1, :max + 1)))),
9 TABLE (many (NVL (nxt - now, 1)))
10 WHERE now - 1 + column_value NOT IN (:min - 1, :max + 1);
SRC NOW
---- ----------
FAKE 1
REAL 2
FAKE 3
REAL 4
REAL 5
FAKE 6
FAKE 7
REAL 8
FAKE 9
REAL 10
10 rows selected.
SQL> SELECT DECODE (column_name, NULL, 'FAKE', 'REAL') src, now
2 FROM (SELECT :MIN + column_value - 1 now
3 FROM TABLE (many (:max - :min + 1))) a,
4 table_name b
5 WHERE column_name(+) = now;
SRC NOW
---- ----------
FAKE 1
REAL 2
FAKE 3
REAL 4
REAL 5
FAKE 6
FAKE 7
REAL 8
FAKE 9
REAL 10
10 rows selected.
SQL>
"Any sufficiently advanced technology is indistinguishable from magic!"
Yesss! That's the right answer.Quote:
Originally posted by padders
Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.
Analytic functions are the right answer to any question.Quote:
Originally posted by jmodic
Yesss! That's the right answer.
The simple solution to find missing numbers is using cube:
SQL> select * from t1;
ID
----------
10
13
14
17
19
20
6 rows selected.
SQL>
1 select rn
2 from (select /*+ no_merge */ rownum as rn
3 from ( select 1 from dual group by cube (1,1,1,1,1)) a,
4 ( select 1 from dual group by cube (1,1,1,1,1,1)) b
5 )
6 where rn >= (select min(id) from t1) and
7 rn <= (select max(id) from t1)
8 minus
9* select id from t1
SQL> /
RN
----------
11
12
15
16
18
Technically you can generate millions of millions of rows using cube.
On my IBM P690 unix AIX machine (8 cpus with 1500 MH spped, 16 GB RAM), I generated 4 million rows table in 110 seconds.
If you want to generate more number of rows, just add 1s in the cube function.
Tamil
Yes TamilSelvan..It works.. But it take lengthy time...
select rn from
(select rownum as rn from (
select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1)) a,
(select 1 from dual group by cube (1,1,1,1,1,1,1,1,1,1,1,1)) b)
where rn >=(select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
and rn <=(select max(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2)
minus
select to_number(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2
Elapsed Time: 00:00:40.38
Is any opttion to reduce...???
Any Index Problem...???
==========
select min(dadno) from elgidesp.despatchmaster where datype<>2 and daddt>=sysdate-2
==========
You need to to tune the subquery.
Do you have a composite index on daddt,datype, and dadno columns?
Tamil
I think that i would prefer to use either a list of numbers in a table or a pipelined function to generate the required values ...Quote:
Originally posted by tamilselvan
The simple solution to find missing numbers is using cube:
So anyway, you could ...Code:create or replace type TypTabNum as table of number
/
create or replace package utilities
as
function tab_of_numbers (p_start number, p_finish number) return TypTabNum pipelined;
end;
/
create or replace package body utilities
as
function tab_of_numbers (p_start number, p_finish number)
return TypTabNum
pipelined
is
begin
if p_finish > p_start
then
for i in p_start .. p_finish
loop
pipe row (i);
end loop;
else
for i in reverse p_finish .. p_start
loop
pipe row (i);
end loop;
end if;
return;
end;
end;
/
select * from table (utilities.tab_of_numbers(1,10))
/
select * from table (utilities.tab_of_numbers(10,1))
/
Just depends what kind of result set you want ... analytical functions are good for identifying ranges "5-7", this is good for listing individual values.Code:create table my_table
(my_number number);
insert into my_table values (3);
insert into my_table values (4);
insert into my_table values (8);
insert into my_table values (10);
insert into my_table values (12);
commit;
select
column_value as missing
from
table (utilities.tab_of_numbers(
(select min(my_number) from my_table),
(select max(my_number) from my_table)))
minus
select
my_number
from
my_table
/
MISSING
----------
5
6
7
9
11