-
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.
-
Originally posted by jmodic
However it is also obvious that your suggesten was equaly useless and wrong.
Well, yes its useless.. was in haste.. & never thought of a case with gaps more than 1.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Interesting thread. There are a few recent features which can help out here too, for example using analytic functions to find the gaps.
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>
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:
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>
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> 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>
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 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!"
-
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.
Yesss! That's the right answer.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by jmodic
Yesss! That's the right answer.
Analytic functions are the right answer to any question.
-
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
-
Originally posted by tamilselvan
The simple solution to find missing numbers is using cube:
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 ...
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))
/
So anyway, you could ...
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
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|