You Asked
I was looking for a pattern in some data and typed out this query(with more columns)
very quickly without thinking it through.
The result was what I wanted, but after a second of thought, not what I expected.
I know from the distribution of data that the rownum that forms the stopkey can't be
the same rownum as the one used in the filter. They are 80% 'E' and the first 100, at
least, on disk are 'E'.
What's going on? Can the Oracle engine read minds? ;)
Thanks,
Chuck
SQL> select rownum,
2 sale_v_endo
3 from tax.lien_sales
4 where rownum <= 10
5 and ((mod(rownum, 2) = 0 and sale_v_endo = 'S')
6 or(mod(rownum, 2) = 1 and sale_v_endo = 'E'));
ROWNUM S
---------- -
1 E
2 S
3 E
4 S
5 E
6 S
7 E
8 S
9 E
10 S
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=84 Card=54071 Bytes=
54071)
1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'LIEN_SALES' (Cost=84 Card=5407
1 Bytes=54071)
and Tom said...
Well, it worked because rownum is only incremented after a SUCCESSFUL row is returned.
That query is somewhat equivalent to this procedural code:
rownum = 1
for x in ( select * from tax.lien_sales )
loop
exit when rownum > 10; (your where rownum <= 10 condition)
if ( (mod(rownum,2) = 0 and x.sale_v_endo = 'S')
or
(mod(rownum,2) = 1 and x.sale_v_endo = 'E') )
then
OUTPUT ROW;
rownum = rownum + 1;
end if;
end loop
So, this query started reading tax.lien_sales. rownum is <= 10 (it is one). mod(rownum,2) = 1. Until it found a row with 'E' in it -- rownum would stay at 1. So, suppose the first row we found had sale_v_endo = 'X' -- the predicate would not be satisfied, the rownum would not be incremented. We would goto the second fetched row. Suppose this did have an 'E'. Now we would output the row and increment rownum. Now it would continue through the rows until it found an 'S' (rownum = 2 until we find an 'S')
As long as there were enough 'E's and 'S's in the right order -- we would get what we wanted (as long as there were at least 5 E's in the table that were followed by at least one S -- PHYSICALLY on disk.
Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( sale_v_endo varchar2(1) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 'E' from all_users where rownum <= 10;
10 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select 'S' from all_users where rownum <= 10;
10 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select sale_v_endo, count(*) from t group by sale_v_endo;
S COUNT(*)
- ----------
E 10
S 10
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select rownum, sale_v_endo
2 from t
3 where rownum <= 10
4 and ((mod(rownum, 2) = 0 and sale_v_endo = 'S')
5 or(mod(rownum, 2) = 1 and sale_v_endo = 'E'));
ROWNUM S
---------- -
1 E
2 S
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> truncate table t;
Table truncated.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select decode( mod(rownum,2) ,1,'E','S' ) from all_users where rownum <= 20;
20 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select sale_v_endo, count(*) from t group by sale_v_endo;
S COUNT(*)
- ----------
E 10
S 10
ops$tkyte@ORA817DEV.US.ORACLE.COM> select rownum, sale_v_endo
2 from t
3 where rownum <= 10
4 and ((mod(rownum, 2) = 0 and sale_v_endo = 'S')
5 or(mod(rownum, 2) = 1 and sale_v_endo = 'E'));
ROWNUM S
---------- -
1 E
2 S
3 E
4 S
5 E
6 S
7 E
8 S
9 E
10 S
10 rows selected.
now, in both cases -- there were 20 rows, 10 of each. Note how the rownum query was RADICALLY affected by the physical order of the data in the table!!!
Beware!!! (not really beware -- just be aware of how it works)
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment