How can we prove the above conclusively with statistics
Nag, November 19, 2001 - 6:21 pm UTC
SQL> select deptno, avg(sal)
2 from emp
3 where rownum<5
4 group by deptno ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
1 physical reads
0 redo size
264 bytes sent via SQL*Net to client
253 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL> select deptno, avg(sal)
2 from emp
3 group by deptno ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
1 physical reads
0 redo size
325 bytes sent via SQL*Net to client
253 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
Can we consider bytes sent via SQL*Net to client as conclusive proof that less rows are being read when where condition is given using rownum.
November 19, 2001 - 6:38 pm UTC
You cannot prove conclusively from this output that less rows were actually read, only that less rows were sent back to the client.
There is insufficient data in the table to see the effect of rownum in this case.
Conlusive proof -
Nag, November 20, 2001 - 9:57 am UTC
Can the increasing consistent gets, bytes sent via SQL*Net to client , below when using rownum, be considered conlusive proof of lesser work being done when requesting lesser number of rows via <rownum.
SQL> select object_name from all_objects;
14610 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
234213 consistent gets
16 physical reads
0 redo size
436887 bytes sent via SQL*Net to client
38200 bytes received via SQL*Net from client
975 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14610 rows processed
SQL> select object_name from all_objects
2 where rownum <100;
99 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
397 consistent gets
5 physical reads
0 redo size
2778 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99 rows processed
SQL> select object_name from all_objects
2 where rownum <300 ;
299 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
3596 consistent gets
4 physical reads
0 redo size
7601 bytes sent via SQL*Net to client
994 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
299 rows processed
SQL> select object_name from all_objects
2 where rownum <600 ;
599 rows selected.
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
10953 consistent gets
1 physical reads
0 redo size
13394 bytes sent via SQL*Net to client
1774 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
599 rows processed
November 20, 2001 - 10:58 am UTC
the consistent gets tell the story. The bytes transmitted over the network tell you nothing about the work done on the server.
Try:
select count(*) from all_objects where rownum < ....
they all have the same bytes sent (for the count) but the consistent gets will vary based on the rows processed.
ROWNUM in Top-N queries
Andre Whittick Nasser, November 20, 2001 - 4:03 pm UTC
Please correct me if I am wrong... (or confirm it if I am right):
Query a) select ename, sal
from emp
where rownum<=10
order by sal desc;
Query b) select ename, sal
from ( select ename, sal
from emp
order by sal desc)
where rownum<=10;
Oracle8i introduced the possibility of using order bys in in-line views, and now it has the capability of doing a "smart" sort.
Important, not that the the processing will flow like indicated in the query. Oracle will identify the Top-N syntax and do this "smart" sort.
What I suppose the queries do:
Query a) will first sort all the rows in the emp table -- quicksort style, I imagine -- and then extract the first 10 rows, that is, the top 10 salaries.
Query b) will do a scan on the table to simply get the top 10 values, like storing 10 MAX()'s in some memory positions. The result: the same as query a) except for the processing complexity.
We go from an O(n log n) to an O(1) algorithm, which is just fantastic if you are considering huge, petabyte data farms and VLDB's. That might mean years as opposed to minutes on large amounts of data.
>> Is this so deterministic ? <<
Another consideration:
>> Is it possible to use parallel query when sorting ? <<
That does not make much sense in coarse-grained computing -- in our general case, SMP machines -- since sorting algorithms can only be really optimized in fine-grained architectures. In SMP machine you would still be getting O[(n log n)/m] -- m being the number of processors -- which ends up in O(n log n).
November 20, 2001 - 5:12 pm UTC
Query a ) will find 10 records and then sort them. Rownum is assigned before the ORDER BY.
Query b ) will sort the entire emp table by SAL desc (unless an index exists, in which case we'll do something different) and then take the first 10 rows.
If we have an index on sal, we can read it backwards -- avoiding the sort, get the 10 rows and be done with it.
query a and query b return different answers -- almost certainly (a may accidently return the same answer but it probably won't).
This is easy to see:
scott@ORA817DEV.US.ORACLE.COM> select ename, sal
2 from emp
3 where rownum<=10
4 order by sal desc;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
WARD 1250
MARTIN 1250
A 800
10 rows selected.
scott@ORA817DEV.US.ORACLE.COM>
scott@ORA817DEV.US.ORACLE.COM> select ename, sal
2 from ( select ename, sal
3 from emp
4 order by sal desc)
5 where rownum<=10;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
10 rows selected.
You do not get your reduction at all.
If you have an index on SAL, the optimizer is smart enough to figure out that it can read it to get the 10 rows it needs.
Yes, you can use PQ when sorting. Scanners feed sorters, sorters feed a single merger.
PLAY OF THE DAY
Andre Whittick Nasser, November 20, 2001 - 9:23 pm UTC
Well Tom...
You will have to find a good interjection for this one... so will I...
This is TOTALLY confusing. I researched the on-line books and they are reticent about this issue.
Excerpt from the SQL Reference:
----------------------------------------------------------
" For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM emp WHERE ROWNUM < 10;
If an ORDER BY clause follows ROWNUM in the same query, the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:
SELECT * FROM emp WHERE ROWNUM < 11 ORDER BY empno;
If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the 10 smallest employee numbers. This is sometimes referred to as a "top-N query":
SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11;
In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by EMPNO in the subquery. "
----------------------------------------------------------
OK, this is what you said.
BUT... Read this text from the Oracle University Book, which seems to be undocumented in the on-line books: "Oracle8i: New Features for Administrators", page 3-22
----------------------------------------------------------
"
(slide)
Top-N SQL
-Views and in-line views now allow ordering
-Sorts only the >>>required<<< number of rows
SELECT *
FROM ( SELECT class_id, crs_id, start_date
FROM classes
ORDER BY start_date DESC )
WHERE ROWNUM < 10
...
(text)
As shown in the slide, >>>when only the top 10 rows are requested, Oracle does not sort all the rows in the table to produce the result<<<. Oracle stores a maximum of 10 rows as data is retrieved from the table. When the 11th row is read, it discards the row with the lowest margin. This operation continues until all the data is read and the >>>sorting takes place only on the 10 rows at the end of the operation<<<. Here is the execution plan for this operation:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'CLASSES'
"
----------------------------------------------------------
What now ?
---------> AskDavidAustin.oracle.com ...
November 21, 2001 - 9:30 am UTC
Well, we are NOT magic ;)
If there is no index on start_date, we obviously MUST sort the data -- otherwise it would behave just like the original
select * from t where rownum < 11 order by x;
So, it does in fact sort the entire result set and then returns the first 9. Now, if there is an index, it can (and will) use it. Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from ( select owner, object_name, object_id
3 from t
4 order by object_id desc )
5 where rownum < 10;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE T 23087
OPS$TKYTE MD5STRING 23086
OPS$TKYTE MD5 23085
OPS$TKYTE THE_OLD_IN_OUT 23084
OPS$TKYTE STRING_ARRAY 23083
OPS$TKYTE ArrayDemo 23082
OPS$TKYTE ArrayDemo 23081
A T 23078
OPS$TKYTE PRINT_TABLE 23048
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 SORT (ORDER BY STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'T'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets<b>
238 consistent gets</b>
234 physical reads
0 redo size
936 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
<b>note the blocks processed -- 238. Thats the entire table, as we'll see below. We read and sorted everything</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_id);
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from ( select owner, object_name, object_id
3 from t
4 order by object_id desc )
5 where rownum < 10;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE T 23087
OPS$TKYTE MD5STRING 23086
OPS$TKYTE MD5 23085
OPS$TKYTE THE_OLD_IN_OUT 23084
OPS$TKYTE STRING_ARRAY 23083
OPS$TKYTE ArrayDemo 23082
OPS$TKYTE ArrayDemo 23081
A T 23078
OPS$TKYTE PRINT_TABLE 23048
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T'
4 3 INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets<b>
9 consistent gets</b>
1 physical reads
0 redo size
936 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
<b>Now, if we look at the size of the raw table:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space('T' )
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................21
Unused Bytes............................172032
Last Used Ext FileId....................7
Last Used Ext BlockId...................246537
Last Used Block.........................43
PL/SQL procedure successfully completed.
<b>We can see the 238 blocks = 256 - 21 - 1 + 4
Total Not Overhead full scan
Blocks Used for Table read seg. hdr four times
</b>
it read the entire table, sorted it then gave us the answer.
Rownumis only Record Number Saved
Mohammed Al-moayed, November 21, 2001 - 7:44 am UTC
Hi, As I noticed That rownum is the rownumber and the sequence of inserting the Record to the Table.
If you use
Select rownum,empno,ename
from emp
order by empno ;
The rownum for each Record retrived will not be in sequence
1,2,3,4,....
you can insert into emp table any Record ,and select max(rownum ) from that table .
and you will see ....
November 21, 2001 - 9:56 am UTC
NO ITS NOT!!!!!! (if you get Oracle Magazine, I just published an article on this as many many people seemed to be totally confused by this).
Rownum is assigned to rows as they are retrieved from the table. Data in a table is NOT STORED in order of INSERTION.
quick and dirty example that hopefully puts this to rest:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t
2 ( x int,
3 a char(2000) default 'x',
4 b char(2000) default 'x',
5 c char(2000) default 'x');
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 1 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 2 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 3 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from t where x = 2;
1 row deleted.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t (x) values ( 4 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, rownum from t;
X ROWNUM
---------- ----------
1 1
4 2
3 3
<b>FACTS:
o rows are not stored in order of insertion.
o rownum is not the sequence of inserting the rows.
</b>
Where can an 'order by in an inline view be useful'
Nag, November 21, 2001 - 8:29 am UTC
Tom
I read above
"Oracle8i introduced the possibility of using order bys in in-line views"
Can you demonstrate the utility of using an order by in an inline view.
What and in what cases this can make a difference.
November 21, 2001 - 10:13 am UTC
see my comment that starts with:
Well, we are NOT magic ;)
Magic ???
Andre Whittick Nasser, November 21, 2001 - 11:03 am UTC
Sorry Tom, with all due respect...
Questions:
1) What do you mean by 'we are not magic' ? The algorithm described in the course book storing the MAX's makes A LOT of sense if there is not an index in place.
2) So Oracle charges nearly US$ 1000,00 for a course and the all excited instructor explains a new internal feature and I in turn get across how wonderful it is, and it does not simply work in fact ?
3) Is the Oracle University material 'bugged' ? So, there nothing SO great about the Top-N query feature, only the possibility of putting an order by in the in-line view ? The OU instructor explained that Oracle would identify the syntax and arrange an special execution plan, blah, blah, blah... In sum, the "smart sort" is a legend ?
Buddy, I regret to inform you that at this time there is an army of instructors out there saying this... thing.
November 21, 2001 - 12:40 pm UTC
Look at the paragraph -- I did not say he was wrong. Lets digest this:
......
As shown in the slide, >>>when only the top 10 rows are requested, Oracle does
not sort all the rows in the table to produce the result<<<. Oracle stores a
maximum of 10 rows as data is retrieved from the table. When the 11th row is
read, it discards the row with the lowest margin. This operation continues until
all the data is read and the >>>sorting takes place only on the 10 rows at the
end of the operation<<<. Here is the execution plan for this operation:
....
Perhaps we are mincing terms here but.... we read the entire table and in fact sorted it (keeping just the 10 you were interested in). Lets be more clear about this feature then. We'll look at it in a little more detail. (i myself do not see any conflicts between what you cut and pasted from OU and what we see here).
We'll measure what happens when we use the "new way" with rownum versus the DIY (do it yourself way) -- the results are huge. My point was:
o we read the entire table, no getting around that. we in fact sort it as well -- just smarter then we would if we did not know you wanted the top n.
o if there is an index, we can do this really well.
Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table run_stats ( runid varchar2(15), name varchar2(80), value int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch;
View created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
2 select object_name, object_id, owner
3 from all_objects
4 where rownum < 101;
100 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 65536;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 begin
4 insert into run_stats select 'before', stats.* from stats;
5
6 l_start := dbms_utility.get_time;
7 for i in 1 .. 100
8 loop<b>
9 for x in ( select *
10 from ( select * from t order by object_name )
11 where rownum < 10 )</b>
12 loop
13 null;
14 end loop;
15 end loop;
16 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
17
18 insert into run_stats select 'after 1', stats.* from stats;
19
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 100
22 loop<b>
23 for x in ( select a.*, rownum r
24 from ( select * from t order by object_name ) a )
25 loop</b>
26 exit when ( x.r >= 10 );
27 end loop;
28 end loop;
29 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
30
31 insert into run_stats select 'after 2', stats.* from stats;
32 end;
33 /<b>
22 hsecs
2306 hsecs</b>
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.done queue latch 2 1 -1
LATCH.ksfv messages 4 3 -1
STAT...calls to get snapshot scn: kcmgss 506 505 -1
STAT...consistent gets 3812 3811 -1
LATCH.multiblock read objects 2 0 -2
STAT...parse time cpu 3 1 -2
STAT...parse time elapsed 1 3 2
LATCH.library cache 544 547 3
STAT...redo entries 16 13 -3
STAT...db block changes 27 22 -5
LATCH.active checkpoint queue latch 0 8 8
LATCH.session timer 0 8 8
LATCH.shared pool 159 147 -12
LATCH.redo allocation 16 30 14
LATCH.redo writing 3 29 26
STAT...prefetched blocks 33 0 -33
LATCH.checkpoint queue latch 2 36 34
LATCH.messages 3 44 41
STAT...redo size 21484 21428 -56
STAT...free buffer requested 38 103 65
STAT...enqueue requests 5 103 98
STAT...enqueue releases 4 103 99
LATCH.file number translation table 1 101 100
STAT...recursive calls 1031 1131 100<b>
STAT...sorts (memory) 103 3 -100
STAT...sorts (disk) 1 101 100</b>
STAT...recursive cpu usage 27 149 122
LATCH.cache buffers lru chain 38 208 170
STAT...session logical reads 5038 5230 192
STAT...db block gets 1226 1419 193
LATCH.enqueue hash chains 6 206 200
LATCH.loader state object freelist 2 202 200
LATCH.enqueues 13 413 400
LATCH.sort extent pool 6 606 600
STAT...physical reads 43 809 766
STAT...physical reads direct 9 809 800
LATCH.row cache objects 85 985 900
STAT...physical writes 9 2509 2500
STAT...physical writes direct 9 2509 2500
STAT...physical writes non checkpoint 9 2509 2500
LATCH.cache buffers chains 10197 17102 6905
41 rows selected.
As you can see -- there is an ENORMOUS difference (and that guy was worth the $1,000 bucks). It does work -- never meant to say it didn't -- only that its not magic, it still has to read the entire table, finding the top "N". Perhaps they should use some framework like I do above to help quantify this, the above shows very clearly the effect.... However, compared to what you can do with a well placed index on a top-n:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
2 select object_name, object_id, owner
3 from all_objects
4 where rownum < 101;
100 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t2;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 as select * from t;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t2_idx on t2(object_name);
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t2 modify object_name NOT NULL;
Table altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set sort_area_size = 65536;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 begin
4 insert into run_stats select 'before', stats.* from stats;
5
6 l_start := dbms_utility.get_time;
7 for i in 1 .. 100
8 loop
9 for x in ( select *
10 from ( select * from t order by object_name )
11 where rownum < 10 )
12 loop
13 null;
14 end loop;
15 end loop;
16 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
17
18 insert into run_stats select 'after 1', stats.* from stats;
19
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 100
22 loop
23 for x in ( select *
24 from ( select * from t2 order by object_name )
25 where rownum < 10 )
26 loop
27 null;
28 end loop;
29 end loop;
30 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
31
32 insert into run_stats select 'after 2', stats.* from stats;
33 end;
34 /
21 hsecs
14 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.active checkpoint queue latch 1 0 -1
LATCH.enqueues 13 14 1
LATCH.session idle bit 1 2 1
STAT...free buffer requested 5 6 1
STAT...total file opens 1 0 -1
STAT...parse time elapsed 1 2 1
STAT...enqueue releases 4 3 -1
LATCH.direct msg latch 2 0 -2
LATCH.library cache load lock 0 2 2
STAT...parse time cpu 1 3 2
STAT...enqueue requests 5 3 -2
LATCH.session queue latch 2 0 -2
LATCH.session allocation 2 4 2
LATCH.redo writing 4 2 -2
LATCH.done queue latch 2 0 -2
LATCH.checkpoint queue latch 5 8 3
STAT...redo entries 16 13 -3
STAT...physical reads 9 12 3
STAT...parse count (total) 6 9 3
STAT...opened cursors cumulative 6 9 3
STAT...execute count 105 108 3
STAT...cluster key scans 0 3 3
STAT...cluster key scan block gets 0 3 3
LATCH.messages 5 2 -3
LATCH.ksfv messages 4 0 -4
LATCH.cache buffers lru chain 5 11 6
STAT...db block changes 28 22 -6
LATCH.row cache objects 85 91 6
LATCH.redo allocation 16 23 7
LATCH.shared pool 165 155 -10
STAT...recursive cpu usage 28 18 -10
STAT...recursive calls 1031 1106 75
LATCH.library cache 519 601 82
STAT...sorts (memory) 103 7 -96
STAT...table scans (short tables) 100 0 -100
STAT...redo size 21512 21404 -108
STAT...calls to get snapshot scn: kcmgss 506 108 -398
LATCH.undo global data 401 0 -401
STAT...table fetch by rowid 8 909 901
STAT...buffer is not pinned count 3416 2421 -995
STAT...db block gets 1228 19 -1209
STAT...no work - consistent read gets 3408 1712 -1696
STAT...consistent gets 3812 1922 -1890
STAT...session logical reads 5040 1941 -3099
STAT...table scan blocks gotten 3400 0 -3400
STAT...session uga memory 0 4128 4128
LATCH.cache buffers chains 10168 3921 -6247
STAT...sorts (rows) 12317 2321 -9996
STAT...table scan rows gotten 10000 0 -10000
STAT...session uga memory max 24808 0 -24808
50 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
that is good
mano, November 21, 2001 - 3:39 pm UTC
tom thanks for that answer.
can you guide whether i can achieve the following in any better way or any oracle 8i features to help the same.
I HAVE WRITTEN A PROC THAT ACCEPTS TWO PARAMS I_PAGE_NUM AND I_ROWS_PER_PAGE.DEPENDING ON THIS I SHOULD RETURN ROWS USING A REF CURSOR.
CREATE OR REPlace procedure
myproc(i_page_num number,i_rows_per_page number,o_ref_cur t_ref_cur)
BEGIN
OPEN O_REF_CUR for select * from (select rownum rno,a.* from emp a) where rno between
((i_page_num - 1) * i_rows_per_page) + 1 and ((i_page_num) * i_rows_per_page);
end;
is there a better way using any oracle 8i new features or using existing features.
rgds
November 21, 2001 - 3:51 pm UTC
I generally code this as:
select *
from ( select a.*, rownum r
from ( YOUR_QUERY_HERE ) a
where rownum <= :MAX_ROW )
where r >= :MIN_ROW
/
For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner query after MAX_ROW rows and then just through away the first MIN_ROW rows. Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( object_name char(2000), object_id number, owner varchar2(30) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
2 select object_name, object_id, owner
3 from all_objects
4 where rownum < 101;
100 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
2 l_start number;
3 l_min_row number default 50;
4 l_max_row number default 60;
5 begin
6 insert into run_stats select 'before', stats.* from stats;
7
8 l_start := dbms_utility.get_time;
9 for i in 1 .. 1000
10 loop
11 for x in ( select *
12 from ( select rownum r, t.* from t )
13 where r between l_min_row and l_max_row )
14 loop
15 null;
16 end loop;
17 end loop;
18 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
19
20 insert into run_stats select 'after 1', stats.* from stats;
21
22 l_start := dbms_utility.get_time;
23 for i in 1 .. 1000
24 loop
25 for x in ( select *
26 from ( select rownum r, a.*
27 from ( select * from t ) a
28 where rownum <= l_max_row )
29 where r >= l_min_row )
30 loop
31 null;
32 end loop;
33 end loop;
34 dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
35
36 insert into run_stats select 'after 2', stats.* from stats;
37 end;
38 /
198 hsecs
170 hsecs
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.name, b.value-a.value run1, c.value-b.value run2,
2 ( (c.value-b.value)-(b.value-a.value)) diff
3 from run_stats a, run_stats b, run_stats c
4 where a.name = b.name
5 and b.name = c.name
6 and a.runid = 'before'
7 and b.runid = 'after 1'
8 and c.runid = 'after 2'
9 and (c.value-a.value) > 0
10 and (c.value-b.value) <> (b.value-a.value)
11 order by abs( (c.value-b.value)-(b.value-a.value))
12 /
NAME RUN1 RUN2 DIFF
---------------------------------------- ---------- ---------- ----------
LATCH.active checkpoint queue latch 1 0 -1
LATCH.session timer 0 1 1
STAT...enqueue releases 4 3 -1
STAT...parse time elapsed 1 2 1
STAT...calls to get snapshot scn: kcmgss 5006 5005 -1
LATCH.messages 4 2 -2
STAT...enqueue requests 5 3 -2
LATCH.multiblock read objects 2 0 -2
LATCH.library cache 2288 2291 3
STAT...redo entries 16 13 -3
LATCH.redo writing 3 0 -3
STAT...db block changes 28 22 -6
STAT...db block gets 12026 12017 -9
LATCH.redo allocation 13 23 10
LATCH.shared pool 175 159 -16
STAT...recursive cpu usage 177 150 -27
LATCH.cache buffers lru chain 36 6 -30
STAT...prefetched blocks 33 0 -33
STAT...physical reads 34 0 -34
STAT...free buffer requested 38 2 -36
STAT...redo size 21532 21420 -112
STAT...buffer is not pinned count 41016 27016 -14000
STAT...consistent gets 45011 31011 -14000
STAT...table scan blocks gotten 41000 27000 -14000
STAT...no work - consistent read gets 41008 27008 -14000
STAT...session logical reads 57037 43028 -14009
LATCH.cache buffers chains 114127 86132 -27995
STAT...table scan rows gotten 100000 60000 -40000
28 rows selected.
Quite a difference in the buffers processed....
it is good
mano, November 21, 2001 - 4:06 pm UTC
sorry tom,
i understood the example but not the theory behind that.
can you please explain me more about your
"For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner
query after MAX_ROW rows and then just through away the first MIN_ROW rows"
I don't get it yet...
Andre Whittick Nasser, November 21, 2001 - 4:21 pm UTC
So, from what I gathered, what you first said:
"Query b ) will >>>sort<<< the entire emp table by SAL desc (unless an index exists, in which case we'll do something different) and then take the first 10 rows."
That is:
1) Sorting (supposing there is no index)
2) Getting the top 10 values
>>>contradicts<<< what the OU book says:
"As shown in the slide, >>>when only the top 10 rows are requested, Oracle does not sort all the rows in the table to produce the result<<<. Oracle stores a maximum of 10 rows as data is retrieved from the table. When the 11th row is read, it discards the row with the lowest margin. This operation continues until all the data is read and the >>>sorting takes place only on the 10 rows at the end of the operation<<<."
That is:
1) "Collecting" the top 10 values NOT in a sorted state hrough a FTS, grabbing the greatest values along the way.
2) Sorting the resulting, >>>unordered<<< 10 values.
I understand that you might be mistakenly considering the last 1) a "sort". This is where we are diverging. You WILL have to agree this is no kind of sorting, at best some kind of MAXing.
I read your code carefully. Sorry I am sometimes a bit too slow, I saw the performance difference between both queries. I am impressed.
But I did not see anyhthing accounting for the discrepancy of concepts. If there was something else I missed, please show me more clearly.
Excuse me, but I am not convinced yet the 1000 bucks were worth it in the case the teacher told half the story: yes, faster, but working differently.
November 21, 2001 - 4:37 pm UTC
Ok, i retract everything. You win.
Let me rephrase.
It get the 10 rows from EMP such that they have the 10 biggest salaries, it'll do this in a super good way.
It sorts no data whatsoever. It compares values to find out which is bigger then then other. It kept the 10 biggest ones. It did a super efficient "kind of sort that is not a sort". I was too loose with my terminology.
Hopefully the exhaustive example clears it all up and removes all confusion. The stats don't lie in this case, they show the difference.
it is good
mano, November 21, 2001 - 4:41 pm UTC
sorry tom,
i understood the example but not the theory behind that.
can you please explain me more about your
"For the reason that sometimes (NOT EVERYTIME) the optimizer can stop the inner
query after MAX_ROW rows and then just through away the first MIN_ROW rows"
November 21, 2001 - 5:00 pm UTC
The optimizer recognizes "where rownum < N" and knows -- Hey, if at all possible, I should STOP processing.
The optimizer sees "where rno between min and max" and doesn't have that ability.
Hence, by breaking it into two inline views, we can manytimes quickly build the result set for "where rownum < max_row" (faster then building the ENTIRE result set) and then applying the "where rno > min_row".
It is just a fact that when there is a rownum in the inline view/subquery, Oracle will materialize that result set. So, when you went:
select *
from ( select emp.*, rownum rno from emp )
where rno between A and B;
The query: "select emp.*, rownum rno from emp" was performed in FULL and then the predicate was applied.
In my case, the query:
select a.*, rownum r
from ( select * from t ) a
where rownum <= MAX_ROWS
was performed -- faster then yours since it only had to do MAX_ROWS rows -- and then the final predicate "where r >= min_rows" was applied.
SUN CHEN
SUN CHEN, July 23, 2002 - 11:35 pm UTC
HI Tom,
Why the following procedure cann't work?
CREATE OR REPLACE procedure mi_clm_dscv is
begin
execute immediate 'delete from te_mi_clm_mvnt';
insert into te_mi_clm_mvnt
(prcp_abbr,
clm_no,
ac_yr_mth,
chnnl_nm,
isrd_nm,
grs_rsrv_bf,
net_rsrv_bf,
grs_paid,
net_paid,
grs_rsrv_cf,
net_rsrv_cf,
grs_incrrd,
net_incrrd,
grs_paid_ytd,
net_paid_ytd,
clm_ind,
mgt_cls_desc,
rn_l,
rn_s)
select e.prcp_abbr,
m.clm_no,
m.ac_yr_mth,
s.chnnl_nm,
p.isrd_nm,
m.grs_rsrv_bf,
m.net_rsrv_bf,
m.grs_paid,
m.net_paid,
m.grs_rsrv_cf,
m.net_rsrv_cf,
m.grs_incrrd,
m.net_incrrd,
m.grs_paid_ytd,
m.net_paid_ytd,
m.clm_ind,
b.mgt_cls_desc,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd desc) rn_l,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd asc) rn_s
from mi_clm_mvnt m,
mi_clm_m c,
mi_clm_pcy p,
mi_sbu_main_cd_m s,
mi_prcp_m e,
mi_mgt_cls_m b,
mi_mgt_cls_dtl a
where m.clm_no = c.clm_no
and c.pcy_no = p.pcy_no
and c.endt_srl = p.endt_srl
and c.endt_yr = p.endt_yr
and c.itm_no = p.itm_no
and m.sbu_main_cd = s.sbu_main_cd
and m.prcp_cd = e.prcp_cd
and c.bsc_typ_cd = a.bsc_typ_cd
and m.pcy_sub_typ_cd = a.pcy_sub_typ_cd
and a.mgt_cls_cd = b.mgt_cls_cd;
end;
July 24, 2002 - 12:03 am UTC
you make it *really* hard when you don't include any information -- like for example -- versions of software, actual error message/code.....
Ok, so I go into "compiler" mode and say "hey, if I was an 8i database, running 8i plsql -- that analytic function row_number() would bug me and I would fail the compliation"
See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3027089372477 <code>
for what is happening (if I guessed right)
btw: why do the delete dynamically? use static sql whenever possible.
SUN CHEN
SUN CHEN, July 24, 2002 - 12:24 am UTC
Hi Tom,
Thank you for your reponse immediately.
why the following store procedure get errors:
CREATE OR REPLACE procedure mi_clm_dscv is
begin
execute immediate 'delete from te_mi_clm_mvnt';
insert into te_mi_clm_mvnt
(prcp_abbr,
clm_no,
ac_yr_mth,
chnnl_nm,
isrd_nm,
grs_rsrv_bf,
net_rsrv_bf,
grs_paid,
net_paid,
grs_rsrv_cf,
net_rsrv_cf,
grs_incrrd,
net_incrrd,
grs_paid_ytd,
net_paid_ytd,
clm_ind,
mgt_cls_desc,
rn_l,
rn_s)
select e.prcp_abbr,
m.clm_no,
m.ac_yr_mth,
s.chnnl_nm,
p.isrd_nm,
m.grs_rsrv_bf,
m.net_rsrv_bf,
m.grs_paid,
m.net_paid,
m.grs_rsrv_cf,
m.net_rsrv_cf,
m.grs_incrrd,
m.net_incrrd,
m.grs_paid_ytd,
m.net_paid_ytd,
m.clm_ind,
b.mgt_cls_desc,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd desc) rn_l,
row_number() over (partition by m.ac_yr_mth, e.prcp_abbr, m.clm_ind order by m.net_paid_ytd asc) rn_s
from mi_clm_mvnt m,
mi_clm_m c,
mi_clm_pcy p,
mi_sbu_main_cd_m s,
mi_prcp_m e,
mi_mgt_cls_m b,
mi_mgt_cls_dtl a
where m.clm_no = c.clm_no
and c.pcy_no = p.pcy_no
and c.endt_srl = p.endt_srl
and c.endt_yr = p.endt_yr
and c.itm_no = p.itm_no
and m.sbu_main_cd = s.sbu_main_cd
and m.prcp_cd = e.prcp_cd
and c.bsc_typ_cd = a.bsc_typ_cd
and m.pcy_sub_typ_cd = a.pcy_sub_typ_cd
and a.mgt_cls_cd = b.mgt_cls_cd
update te_mi_clm_mvnt
set rank_ind = 'Largest'
where rn_l <= 50;
update te_mi_clm_mvnt
set rank_ind = 'Smallest'
where rn_s <= 50;
update te_mi_clm_mvnt
set rank_ind = 'Others'
where rank_ind is null;
end;
/
The errors:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
, from
I run it in oracle 817 and the sql can work if I remark the analyst function.Is that analyst function cann't be used in stored procedure? or others errors have occured? thanks....
order by rownum,x
PN, August 13, 2002 - 5:43 am UTC
Hi Tom
thanks for previous helps.
How to make use of Rownum in query to display Rownum,empno
in ASC order (without emp index ,with emp index)
Exapmle
SELECT ROWNUM,EMPNO FROM SCOTT.EMP
ORDER BY ROWNUM,EMPNO
output=
ROWNUM EMPNO
1 7369
2 7499
3 7521
4 7566
5 7654
6 7698
7 7500
8 5
9 2
10 1
We want as
output=
ROWNUM EMPNO
1 1
2 2
3 5
4 7369
5 7499
6 7500
7 7521
8 7566
9 7654
10 7698
Thanks
PN
August 13, 2002 - 4:57 pm UTC
select rownum, empno
from ( select empno
from emp
order by empno );
sort AND then assign rownum.
Does rownum behave differently using RBO?
Kashif, October 31, 2002 - 1:15 pm UTC
Hi Tom,
you mention that if rownum <= n is used, the server will not expend the energy to get all rows and then return the 'n' rows as limited by rownum, but rather just get the 'n' rows requested. Does this work any differently when RBO is being used? TIA.
Kashif
P.S. I am unable to download from the link you have provided on your home page to your presentation on searchdatabase.com. (the 'download the slides from here (no sound)' link).
October 31, 2002 - 9:16 pm UTC
It will work differently with different query plans (eg: you can get a different N rows back with different plans -- add an index and you might get a different N rows then without it).
So -- since the RBO might pick a different plan -- yes, the RBO and CBO might well return different answers depending on HOW rownum was used.
As for the PS -- its been downloaded 548 times and I haven't heard from anyone else having issues -- anyone else having problems?
rownum vs. distinct
VJ, March 12, 2003 - 4:55 pm UTC
Tom,
I was told that using rownum would be faster than using distinct in a query. Can you provide an example if this statement is correct or direct me?
March 13, 2003 - 7:31 am UTC
rownum is not a replacement for distinct???
I don't know what you mean.
rownum vs. distinct
VJ, March 13, 2003 - 2:44 pm UTC
what i mean is that if a query is returning multiple rows with same values (duplicate)then use rownum =1 rather than use distinct in the query. But i got a doubt is that if i use rownum=1 then it is going to return only one row and not distinct values. So this question about rownum vs. distinct.
If it still doesnt drive a point...let just leave it!!!
Error on your page today 03-14-2003
vj, March 14, 2003 - 12:29 pm UTC
Hi Tom,
I saw this error message on your site today..
report error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
I was gonna ask you this very same question today as i was getting this in one of my packages yesterday and saw this on your site. Can you tell me why this error?
rownum
mo, May 09, 2003 - 6:53 pm UTC
Tom:
I have a table of 900 stock numbers where stock number is PK. I am trying to get the order of a given stock number in the table meaning the position. What I did is :
select rownum from (select rownum,stock_number from stock_item order by stock_number)
where stock_number='MS208'
and it is still giving me rownum=1. How can I get the rownum for that stock number which is 800.
Thank you
May 09, 2003 - 7:15 pm UTC
there is no such thing as the "positition in a table".
rownum is a fleeting number assigned to a row in a result set. It exists for the brief moment in time that result set does. There is no such thing as a row having a positition or place in a table.
You do not want what you ask for -- trust me.
rownum
mo, May 09, 2003 - 10:50 pm UTC
Tom:
1. I am sure there has to be a way to get a corresponding number for a value in a table. You say rownum would not work. is there anything else like a random number that can be assigned in incrementing order? I am trying to pass this number (x) to a javascript array value
'document.form.field[0].option['||x||'].selected = true
Basically I have a html for with a dropdown list for all stock numbers. The user can do a lookup where a child window is open and he enters a keyword and oracle does a search and pulls all matching records. Then he clicks on select for one stock number and I am assigning that number to the value in the dropdown on the parent page. I can not find a way in javascript to access the different values in the dropdown to find the location of the matching value so i am trying to get it in oracle.
May 10, 2003 - 7:34 am UTC
if you say "i am sure", you would lose that bet.
rows having "order" is nonsense in a relational database.
If you assigned a sequence number to this (application data) -- you could use that, but to ask the database to assign a "number to a row" isn't going to happen.
Position in table
Jim Tran, May 12, 2003 - 10:21 am UTC
I believe that reviewer "Mo" wants to rewrite the query:
select rownum from
(select rownum,stock_number from stock_item
order by stock_number)
where stock_number='MS208'
as:
select RN from
(select rownum RN,stock_number from stock_item
order by stock_number)
where stock_number='MS208'
I think the confusion is that "rownum" in the outer query is NOT the same "rownum" in the inner query. The "rownum" in the outer query is an entirely new "rownum".
query
mo, May 13, 2003 - 6:26 pm UTC
Thanks
RN worked. I thought rownum on the outer were coming from the inner query but it was not.
Helena Marková, August 12, 2003 - 2:29 am UTC
rownum = 1 and rwnum < 2
A reader, January 28, 2004 - 12:25 pm UTC
Hi
I have this query which runs happily using rownum < 2 but slowly using rownum = 1
1 SELECT bo_id_contact, interaction_id, interact_begin,
2 TO_CHAR (interact_begin, 'YYYY-MM-DD-HH24.MI.SS'), interact_status
3 FROM sysadm.ps_ri_interaction partition(SYS_P692)
4* WHERE ROWNUM < 2
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2761 Card=1 Bytes=33)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'PS_RI_INTERACTION' (Cost=2761 Card=1057390 Bytes=34893870)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
8 physical reads
0 redo size
815 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1 SELECT bo_id_contact, interaction_id, interact_begin,
2 TO_CHAR (interact_begin, 'YYYY-MM-DD-HH24.MI.SS'), interact_status
3 FROM sysadm.ps_ri_interaction partition(SYS_P692)
4* WHERE ROWNUM = 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2761 Card=1 Bytes=33)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'PS_RI_INTERACTION' (Cost=2761 Card=1057390 Bytes=34893870)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18297 consistent gets
18165 physical reads
0 redo size
815 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
I think rownum = 1 is an incorrect use?
January 28, 2004 - 2:35 pm UTC
something fishy -- can we see a tkprof with the real plans?
seems that cursor_sharing is the guilty boy
A reader, January 29, 2004 - 8:36 am UTC
Hi
cursor_sharing = FORCE change rownum = 1 into rownum = :"SYS_B_0"
seems like that is the problem!
which one is better ?
A reader, February 24, 2004 - 4:46 pm UTC
which one is better ?
select * from ( select id from t1 where name = v_name)
where rownum = 1;
or
select max(id) from t
where name= v_name;
(In the view "t" there are multiple rows with the "same name" and "same id" and I have to select only one of them)
eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z
which one is faster ?
there can be 10 maximum repetation based on other_attr field. Total record in table are 4k
The explain plan shows only one difference
count stopkey vs. sort aggregate
please advise !!
February 24, 2004 - 9:02 pm UTC
well, they are not comparable, they answer different questions
q1) give me a random ID where name = v_name
q2) give me the MAX id where name = v_name
they are differnet and cannot never ever be compared for performance based on that fact.
really ?
A reader, February 25, 2004 - 10:38 am UTC
Hi tom, Thanks for your reply.
First of all sorry for my mistake. the view name is wrong both the views are t (not t1 in one of the cases) so we quering against the same view t now.
I listed the view t below...
eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z
if I query it by
select * from ( select id from t where name = 'peter')
where rownum = 1;
I will get 1
or
if I query it using..
select max(id) from t
where name= 'peter';
I will get 1 too..
so why aren't they compareable ?
please explain !!
February 25, 2004 - 11:32 am UTC
ops$tkyte@ORA920PC> create table t1 ( id number, name varchar2(10) );
Table created.
ops$tkyte@ORA920PC> insert into t1 values ( 1, 'peter' );
1 row created.
ops$tkyte@ORA920PC> insert into t1 values ( 1000, 'peter' );
1 row created.
ops$tkyte@ORA920PC> select * from ( select id from t1 where name= 'peter')
2 where rownum = 1;
ID
----------
1
ops$tkyte@ORA920PC> select max(id) from t1 where name = 'peter';
MAX(ID)
----------
1000
it is exactly as I said - query 1 returns a random ID for peter. query 2 returns the max(id) for peter.
tell me -- which do you want? that'll drive which query you use.
once again...
A reader, February 25, 2004 - 12:34 pm UTC
sorry tom, I think I am not able to explain you the senerio or something.
the objective of this query is to get single row out of multiple rows with the same (id,name) pair...
eg. id name other_attr
1 peter x
1 peter y
2 john x
3 tom z
1 peter z
if you see this table.
peter will have ONLY one ID that is 1 ever. but multiple occurance of (id,name) pair
John will have only one ID that is 2 ever. but multiple occurance of (id,name) pair
let's put it in other way
consider there is another table "user" where id and username are part of the pk . T is "depandent table" on users. so pk(id,name) pair is migrated from users (fk) into t and is part of the pk in "T"
in this case name and id pair is unique. so peter will never have id = 1000 peter will always have id = 1 but , basted on the other_attr, there can be a redudancy,
=> same values of (id,name) pair will be repeated with the different values of other_attr.
so, it will not be a random number but always same id.
so, inthe result set there will be multiple rows with SAME
(id,name) pair, but we are selecting the first one returned.
in rownum technique
where as we are selecting the maximum (of the same id's)
form group of records with the same ids just to get the 1 row out of multiple rows
Hope this will make it clearer...:)
February 25, 2004 - 12:41 pm UTC
given all of those additional constraints, just
select id from t where name = 'peter' and rownum = 1;
is sufficient.
Thanks you...
A reader, February 25, 2004 - 1:28 pm UTC
cool. I don't know why I was doing all other things.. this is cool. I should have done this in the first place.. my bad. you are the man !!
Materialized Views created using ROWNUMS
Suren Dharanikota, May 13, 2004 - 2:32 pm UTC
Hello Tom,
It was very nice meeting you at IOUG-04,Toronto - CN
I am facing a strange problem during the refresh process on the materialized views.
When the refresh is run for the first time it shows as successfully completed and when we do a select count(*) on the view it shows " 0 rows ". But when the same view is ran
for the second time it UPDATES successfully and the total rows are displayed.
Here is the syntax for the Materialized View.
CREATE MATERIALIZED VIEW MATVIEW.MV2_XYZ
PCTFREE 0
PCTUSED 40
MAXTRANS 255
TABLESPACE MATVIEW_DATA
NOLOGGING
STORAGE(INITIAL 16M
PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS SELECT * FROM fiowner.xyz@linkdb
WHERE ROWNUM >0
/
SOURCE DATABASE:
----------------
Master Database:
SQL*Plus: Release 9.2.0.4.0 - Production on Fri May 7 11:05:37 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select count(*) from xyz;
COUNT(*)
----------
5811420
SQL>
Refresh/Materialized View Database:
-----------------------------------
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> set time on;
10:54:45 SQL> set timing on;
10:54:49 SQL> set serveroutput on;
10:54:58 SQL> exec dbms_mview.refresh('MATVIEW.MV2_XYZ','C');
PL/SQL procedure successfully completed.
Elapsed: 00:06:38.84
11:01:59 SQL> select count(*) from mv2_XYZ;
COUNT(*)
----------
0
Elapsed: 00:00:00.01
11:02:28 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
11:04:54 SQL> select count(*) from mv2_xyz;
COUNT(*)
----------
0
Elapsed: 00:00:00.01
11:05:03 SQL> select count(*) from xyz@dblink;
COUNT(*)
----------
5811420
Elapsed: 00:00:18.77
11:07:54 SQL>
It is not the only Materialized View we are having problems with, There are a couple more. Two of them are always common and a couple random.
Note*: The source tables are Partitioned tables. These are used as Rollup tables for the ETL process(using SQL-LOADER) and these same tables are used for refreshing the Materialized views once the Nightly load finishes.
We did not face any problems while we were in Oracle 8i on 8.1.7.4, but these problems are showing up every now and then since we moved to 9.2.0.4
I have logged a TAR with Oracle and they said i might be hitting a bug# 2980403. I installed the fix and was successfully updating the rows for the MVs untill two days.
But today we ran into the same problem.
Now Oracle Support says that, The defining query of the materialized view cannot contain any non-repeatable expressions like ROWNUM, SYSDATE etc + all the source tables "SHOULD HAVE PRIMARY KEY DEFINED" for creating the Materialized Views, without which they are saying that they cannot even accept a test case.
1) Is this true about what Oracle says ? What is the harm in using the condition where rownum > 0 in the create Materialized View statement ?
2) If that is true, then the complete refresh for all the tables which now take around like 45 minutes will take about 2 to 3 hours with the primary keys on it as it has to validate all the rowns in the tables.Added to that some of the tables do not have primary keys defined as they are rollup tables.
Is there any other way for us to create the Materialized views without the primary keys ?
Your help is greatly appreciated.
Thanks,
Suren
May 13, 2004 - 3:28 pm UTC
what's up with "where rownum > 0"?
1) rownum would have very wacky, unpredicable side effects yes. when I saw it and even before reading the rest of the text, little red flags popped up all over the place.
as for the primary key -- thats not correct. you can use rowids as well.
what is the purpose of the rownum????
Spelling numbers in spanish
Julian Ortiz, May 13, 2004 - 4:18 pm UTC
Hello, I Modified the function for "SPANISH" language:
function spell_number_espa( p_number in number )
return varchar2
as
begin
return replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace( replace( replace( replace(
replace( replace(
lower( spell_number( p_number ))
, 'duodecillion' , 'duodecillon' )
, 'quintillion' , 'quintillon' )
, 'thousand' , 'mil' )
, 'hundred' , 'cien' )
, 'ninety' , 'noventa')
, 'eighty' , 'ochenta' )
, 'seventy' , 'setenta' )
, 'sixty' , 'sesenta' )
, 'fifty' , 'cincuenta' )
, 'forty' , 'cuarenta' )
, 'thirty' , 'treinta' )
, 'twenty' , 'veinte' )
, 'nineteen' , 'diecinueve' )
, 'eighteen' , 'dieciocho' )
, 'seventeen' , 'diecisiete' )
, 'sixteen' , 'dieciseis' )
, 'fifteen' , 'quince' )
, 'fourteen' , 'catorce' )
, 'thirteen' , 'trece' )
, 'twelve' , 'doce' )
, 'eleven' , 'once' )
, 'ten' , 'diez' )
, 'nine' , 'nueve' )
, 'eight' , 'ocho' )
, 'seven' , 'siete' )
, 'five' , 'cinco' )
, 'six' , 'seis' )
, 'four' , 'cuatro' )
, 'three' , 'tres' )
, 'two' , 'dos' )
, 'one' , 'uno' )
, 'zero' , 'cero' )
, '-' , ' y ' )
, 'veinte y ' , 'veinti')
, 'sediezta' , 'setenta')
, 'uno cien ' , 'ciento ' )
, 'uno cien' , 'cien' )
, 'dos cien' , 'docientos' )
, 'tres cien' , 'trescientos' )
, 'cuatro cien' , 'cuatrocientos' )
, 'cinco cien' , 'quinientos' )
, 'seis cien' , 'seicientos' )
, 'siete cien' , 'setecientos' )
, 'ocho cien' , 'ochocientos' )
, 'nueve cien' , 'novecientos')
, ' uno mil ' , ' un mil ' )
, 'uno mil ' , 'mil ' )
, ' million ' , ' millones ' )
, 'uno millones' , 'un millon' )
, 'ciento millones', 'cien millones')
, ' billion ' , ' billones ' )
, 'uno billones' , 'un billon' )
, 'ciento billones', 'cien billones')
, ' trillion ' , ' trillones ' )
, 'uno trillones' , 'un trillon' )
, 'ciento trillones', 'cien trillones')
, ' quadrillion ' , ' cuatrillones ' )
, 'uno cuatrillon' , 'un cuatrillon' )
, 'ciento cuatrillones', 'cien cuatrillones')
, ' quintillon ' , ' quintillones ' )
, 'uno quintillones' , 'un quintillon' )
, 'ciento quintillones', 'cien quintillones')
;
end spell_number_espa;
Materialize Views with ROWNUMS
Suren Dharanikota, May 13, 2004 - 5:16 pm UTC
Thanks Tom !!!
We are testing the Creation and Refresh of MVs using rowids with complete refresh and ON DEMAND options. So far a couple of views we tested are working fine.
We would be testing more and do some benchmarks. The rownum > 0 option was used to find out if the refresh process was more faster than the ones created with Primary Keys. It not only was "NOT" creating the Primary Keys but in fact was much faster. As there were no problems we had to move along with them.
But now, we are retracting from using the "ROWNUMS".
One final question:
1) Can you suggest any other methods that could be used in the Exec dbms_mview.refresh(parameters) that can make the refresh complete faster ?
Regards,
Suren
May 14, 2004 - 9:51 am UTC
1) that is a truncate plus insert /*+ APPEND */ of the defining query -- as long as the snapshot is not in a snapshot group.
So, make the defining query faster. see what it is waiting on (use a 10046 level 12 trace with a refresh, analyze the tkprof (if 9i, you'll see waits, if 8i, you'll have to review the trace to see the waits) and see if you can remove the waits.
if part of a snapshot group, it'll be delete plus insert -- consider that. not as fast as truncate plus insert append.
sorting by the index
Fernando Sánchez, June 14, 2004 - 6:46 pm UTC
Hello.
I've been told it is possible sorting a result set without the 'order by', simply by using a unique index clause but I can't make it work.
Maybe it works for other rdms but I really don't know.
I had never to be worried about execution plans but it seems I have to begin with it.
This is what I'm trying:
CSR>select /*+INDEX(ir_tb_env_clts,IN_PK_ENV_CLTS)*/
2 CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D
3 from ir_tb_env_clts
4 where CO_MSDN_V >= '35899995000'
5 and FX_FECH_ENV_D >= '01/01/2004'
6 and CO_IMSI_V >= '10000000000000'
7 and SQ_ECL_01 > 0
8 and rownum <=10;
CO_MSDN_V CO_IMSI_V SQ_ECL_01 FX_FECH_
------------------ --------------- ---------- --------
35899997758 100000000002758 11224 12/06/04
35899997759 100000000002759 11223 12/06/04
35899997760 100000000002760 11222 12/06/04
35899997761 100000000002761 11221 12/06/04
35899997762 100000000002762 11220 12/06/04
35899997763 100000000002763 11219 12/06/04
35899997764 100000000002764 11218 12/06/04
35899997765 100000000002765 11217 12/06/04
35899997766 100000000002766 11216 12/06/04
35899997767 100000000002767 11215 12/06/04
10 filas seleccionadas.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=42)
1 0 COUNT (STOPKEY)
2 1 PARTITION RANGE (ITERATOR)
3 2 INDEX (RANGE SCAN) OF 'IN_PK_ENV_CLTS' (UNIQUE) (Cost=
5 Card=1 Bytes=42)
But I wanted was this:
CSR>select *
2 from (select /*+INDEX(ir_tb_env_clts,IN_PK_ENV_CLTS)*/
3 CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D
4 from ir_tb_env_clts
5 where CO_MSDN_V >= '35899995000'
6 and FX_FECH_ENV_D >= '01/01/2004'
7 and CO_IMSI_V >= '10000000000000'
8 and SQ_ECL_01 > 0
9 order by CO_MSDN_V, CO_IMSI_V, SQ_ECL_01, FX_FECH_ENV_D)
10 where rownum <=10;
CO_MSDN_V CO_IMSI_V SQ_ECL_01 FX_FECH_
------------------ --------------- ---------- --------
35899995000 100000000000000 13982 13/06/04
35899995001 100000000000001 13981 13/06/04
35899995002 100000000000002 13980 13/06/04
35899995003 100000000000001 13984 13/06/04
35899995003 100000000000003 13979 13/06/04
35899995004 100000000000004 13978 13/06/04
35899995005 100000000000005 13977 13/06/04
35899995006 100000000000006 13976 13/06/04
35899995007 100000000000007 13975 13/06/04
35899995008 100000000000008 13974 13/06/04
10 filas seleccionadas.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=42)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=7 Card=1 Bytes=42)
3 2 SORT (ORDER BY STOPKEY) (Cost=7 Card=1 Bytes=42)
4 3 PARTITION RANGE (ITERATOR)
5 4 INDEX (RANGE SCAN) OF 'IN_PK_ENV_CLTS' (UNIQUE) (C
ost=5 Card=1 Bytes=42)
The problem with this second query is that when the table is full enough it takes a lot more the application can afford, that's why I was trying the trick I was told.
Two more questions: what does STOPKEY mean? can it me help me somehow?
Thanks in advance.
June 15, 2004 - 8:32 am UTC
you have been LIED TO.
you have been misled.
you would be mistaken in trying to get sorted data without using an order by.
YOU MUST, REPEAT MUST, use order by if you want sorted data.
there are NO if's, and's or buts about it.
you want sorted data, you use order by. period.
Returns from start_row to end_row
Jennifer Chen, June 16, 2004 - 4:16 pm UTC
Hi Tom,
We need a stored procedure that accepts start_row and end_row parameters and returns the values of these row.
Since I can't use rownum between start_row and end_row, I couldn't think of a way that using sql not pl/sql to accomplish this.
What do you think?
Thanks in advance for your time and help.
June 16, 2004 - 4:22 pm UTC
select *
from (select a.*, rownum r
from (select * from t order by...) a
where rownum <= :end )
where r >= :strt
Thank you
Jennifer Chen, June 16, 2004 - 9:36 pm UTC
ThatÂ’s so brilliant, Tom!
This site is much more helpful and responsive compare to Metalink and OTN technical forumsÂ…
Thank you.
Generating additional records using rownum
Praveen, December 21, 2004 - 10:50 am UTC
Hi Tom,
I have a table t(id integer, num integer), which contains 3 records (1, 3), (2, 4), (3, 6). Is it possible to generate duplicate records for each id so that total number of duplicate records for each id is determined by that id's num column.
For example:
create table t (id integer, num integer)
insert into t values(1,3);
insert into t values(2,4);
insert into t values(3,6);
And the output looks like:
rownum, id, num
---------------
1 1 3
2 1 3
3 1 3
---
1 2 4
2 2 4
3 2 4
4 2 4
---
1 3 6
2 3 6
3 3 6
4 3 6
5 3 6
6 3 6
-------------
The query
"select t.* from t, all_objects a where rownum <= t.num"
is giving incorrect output. But I think table t has to join with a large table and records are limited based on rownum. Is it possible without using analytical queries?
Thanks and regards
Praveen
December 21, 2004 - 1:09 pm UTC
ops$tkyte@ORA9IR2> create or replace type myTabletype as table of Number
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function generate_rows( p_n in number ) return myTableType
2 pipelined
3 is
4 begin
5 for i in 1 .. p_n
6 loop
7 pipe row(i);
8 end loop;
9 return;
10 end;
11 /
Function created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( id int, num int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1, 3 );
1 row created.
ops$tkyte@ORA9IR2> insert into t values ( 2, 4 );
1 row created.
ops$tkyte@ORA9IR2> select *
2 from t, table( generate_rows(t.num) )
3 /
ID NUM COLUMN_VALUE
---------- ---------- ------------
1 3 1
1 3 2
1 3 3
2 4 1
2 4 2
2 4 3
2 4 4
7 rows selected.
Ref: Generating additional records using rownum
Praveen, December 23, 2004 - 1:07 am UTC
Hi Tom,
Thankyou very much for the solution. At the same time, the new feature you taught me, "pipe", is quite astonishing. I am currently experimenting with this. However, I would like to know, which would perform better, for the given problem - a single query written using analytical functions (if it is possible) or the above solution you provided?
Thanks, Tom
Praveen.
December 23, 2004 - 11:12 am UTC
analytics don't apply, analytics cannot "make up records".
A reader, January 04, 2005 - 3:40 pm UTC
All of these info is very helpfull. Thanks.
I am dealing with a small variation. Our tables consist of multi million rows. We need to do distinct and we want to get exactly N rows back, say 10. Note: First 10 distinct rows, not distinct of first 10. We used rownum < 10, it is always applied first and then the distinct is done on the 10 records.
I know
A)select * from (select distinct * from x) where rownum < 10
will work. But I am worry about performance. Would this performance better..
B) select a, b from x where rownum < 10 group by a, b
My objective is to find a way that get me the first 10 distinct records and then stop, do you know which query achieve that quickest?
Thanks
January 05, 2005 - 9:01 am UTC
what could the possible business case for this be? seems very strange.
what is the client going to do with these? what is the client?
probably easiest to just open a cursor as a full scan on the table and let the client pull records until it gets 10 unique ones.
A reader, January 05, 2005 - 11:20 am UTC
We are adding support of ORACLE for our product application. We have this available in DB2 as
select distinct * from x fetch first 10 rows;
All we are trying to do is support the same functionlity without minimal hit in performance. All I need to know is which way is the best to achieve that requirement.
Thanks
January 05, 2005 - 11:33 am UTC
select * from (select distinct * from x) where rownum <= 10;
but, that's going to get the table, distinct it and then return 10 rows, pretty much what db2 would have to do as well.
why not
Matthias Rogel, January 05, 2005 - 11:43 am UTC
probably easiest to just open a cursor as a full scan on the table and let the
*server* pull records until it gets 10 unique ones (use ptf)
?
January 05, 2005 - 12:27 pm UTC
ptf?
OK
Kumar, January 05, 2005 - 12:40 pm UTC
Hi Tom,
I tried to sort rows by sal desc. and dynamically get
rows by passing rownum.But it is not working properly.How
to correct this?
select * from(
select rownum,a.* from (select * from emp order by sal desc)a
)
where rownum = &n
January 05, 2005 - 12:55 pm UTC
select * from(
select rownum R,a.* from (select * from emp order by sal desc)a
)
where R = &n
Confusing about rownum
Gerald, January 05, 2005 - 2:04 pm UTC
Hello,Tom,
In your preevious example:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_id);
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from ( select owner, object_name, object_id
3 from t
4 order by object_id desc )
5 where rownum < 10;
OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
OPS$TKYTE T 23087
OPS$TKYTE MD5STRING 23086
OPS$TKYTE MD5 23085
OPS$TKYTE THE_OLD_IN_OUT 23084
OPS$TKYTE STRING_ARRAY 23083
OPS$TKYTE ArrayDemo 23082
OPS$TKYTE ArrayDemo 23081
A T 23078
OPS$TKYTE PRINT_TABLE 23048
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T'
4 3 INDEX (FULL SCAN DESCENDING) OF 'T_IDX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
936 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
i don't understand :if object_id is an index ,so why i can'not do like this:
select owner, object_name, object_id
from t
order by object_id desc
where rownum < 10;
if object_id is an index and i have order by object_id desc
so it will access the index in descending order and then bo rowid from index get to the row and creates the rownum.
Am i right? Clarify me please the point.
January 05, 2005 - 7:08 pm UTC
because ROWNUM is special like that.
rownum is assigned to a row as it flows out of the where clause (else you would even be able to where on it)
and where clauses happen BEFORE ordering
so, that would get the first 10 rows and sort them, we wanted the first 10 rows AFTER sorting.
You cannot *rely* on it using an index, it can and will and is free to NOT use the index. do not rely on data being in any order without an order by.
Continue rownum
Gerald, January 06, 2005 - 12:59 am UTC
Hello,Tom,
If i add where object_id>7 for example and object_id is an index :
select owner, object_name, object_id
from t
where object_id>7 and rownum < 10
do i still need an order clause and make SQL like this :
select * from (select owner, object_name, object_id
from t
where object_id>7 order by object_id) where rownum < 10
or Oracle will get through the index and bring me 9 rows ordered by object id?
January 06, 2005 - 10:39 am UTC
you cannot rely on any index EVER being used.
if you need data sorted, SORT IT.
if you want the first 10 object ids that are greater than 7 then you need to:
select * from t where object_id > 7 order by object_id
and get the first N rows.
select * from (THAT_QUERY) where rownum <10;
unless your query includes an order by, you cannot say ANYTHING about the order of the rows.
ptf = pipelined table function
Matthias Rogel, January 06, 2005 - 3:54 am UTC
sorry for my lazyness not typing three words.
I meant, "pull records until you get n unique ones" is a thing
the server can also do as good as the client.
Please have a look at this example
( I am a bit shocked about it, because it lets me think that
Rule No. 1 "whatever you can do in SQL, do in SQL, don't use PL/SQL"
isn't worth too much )
SQL> describe bigtable
Name Null? Typ
----------------------------------------- -------- --------------------
COL1 NUMBER
COL2 VARCHAR2(40)
SQL> set timi on
SQL> select * from (select distinct * from bigtable) where rownum<=10;
...
10 Zeilen ausgewõhlt.
Abgelaufen: 00:04:21.00
SQL> select count(*) from bigtable;
COUNT(*)
----------
165330211
1 Zeile wurde ausgewõhlt.
Abgelaufen: 00:02:30.02
REM
REM now with ptf
REM
create type btt as object (
col1 number,
col2 varchar2(40));
/
create type bttt as table of btt;
/
create or replace function first_n_distinct_rows(n integer)
return bttt pipelined as
bta bttt;
sofar integer;
i integer;
doex boolean;
begin
bta := bttt();
sofar := 0; doex := true;
for b in (select * from bigtable) loop
exit when sofar = n;
if doex then bta.extend; end if;
bta(bta.count) := btt(b.col1, b.col2);
select count(*)
into i
from (select distinct * from table(bta));
if i > sofar then
sofar := i;
doex := true;
else
doex := false;
end if;
end loop;
for j in 1..bta.count loop
pipe row (bta(j));
end loop;
return;
end first_n_distinct_rows;
/
SQL> select * from table(first_n_distinct_rows(10));
...
10 Zeilen ausgewõhlt.
Abgelaufen: 00:00:00.00
So, using PL/SQL (ptf) seems to me much more reasonable
(at least for "small" n's) than using pure SQL.
Question:
Why Optimizer doesn't use same strategy as first_n_distinct_rows ?
(Are you sure DB2-Optimizer doesn't ?)
January 06, 2005 - 10:49 am UTC
I literally had no clue what ptf was.
for you see, the words "pipelined table function" appear on this page -- well, only when you typed them in.
"pipelined table function" is not what they are normally referred to as
ptf made no sense at all.
in technology, in talking, especially when we all use different languages and such -- being clear, being precise, being understandable, well, that is mandatory.
the first N distinct rows is something optimizers wouldn't "recognize", I believe you would be 1 of 1 people needing such a 'feature'.
Follow rownum
Gerald, January 06, 2005 - 2:48 pm UTC
Hi,Tom,
You wrote above:
you cannot rely on any index EVER being used.
if you need data sorted, SORT IT.
if you want the first 10 object ids that are greater than 7 then you need to:
select * from t where object_id > 7 order by object_id
and get the first N rows.
select * from (THAT_QUERY) where rownum <10;
object_id is index;
but you gave above the example with index object_id
select * from (select object_id order by object_id) where rownum <10;
and in explain plan is written 9 rows gets , but i didn't understand why-the inner query (select object_id order by object_id) should have brought all the records-so if i write the query in a such way how the index could affect the performance?
Thank's
Gerald
January 06, 2005 - 7:03 pm UTC
indexes *can* affect performance, however you cannot RELY on an index access happening, therefore to get the correct answer you MUST ORDER BY.
IF you code
select * from (select object_id order by object_id) where rownum <10;
AND there is an index on object_id)
THEN the optmizer will MOST LIKELY use it to do the "sort" and it'll be super fast...
but if you just coded:
select * from (select object_id from t where object_id > 7 ) where rownum < 10;
AND MOST rows were > 7 (as they are), then a FULL SCAN could be the plan of choice -- and you would get seriously burnt.
98 not 99 !!
Rajesh, January 07, 2005 - 2:56 am UTC
Quoting Tom's first response
"and we said...
It depends. If we can get 99 rows and then stop -- yes. For example, if T is a
table:
select * from t where rownum < 99;
will get just the first 99 rows and stop -- it'll not expend the engery to get
all of the rows in T."
the above query would return you first 98 rows, not first 99 rows!
Follow rownum
Gerald, January 07, 2005 - 9:43 am UTC
Hi,Tom,
In the above example how the optimizer will use index to the sort?Will optimizer read first of all the index and then fetchs the row by rowid( will read the data twice)
and will not order the records after fetching?
select * from (select object_id order by object_id) where rownum <10;
AND there is an index on object_id)
THEN the optmizer will MOST LIKELY use it to do the "sort" and it'll be super
fast...
January 07, 2005 - 9:46 am UTC
do you have "Effective Oracle by Design"? i go over rownum in depth there.
the optimizer would index range scan the index, which is sorted, and just reads the first 9 entries in it -- stopping, not reading the entire thing (that is the count stopkey entries you see in the plans)
Rownum
Gerald, January 07, 2005 - 10:17 am UTC
Tnank's, Tom,
Why we have to be sure that in the result set of the inner query the rownum is like :
ROWNUM OBJECT_ID
====== =========
1 5
2 6
3 7
4 8
As i understood the result set of the inner query is also a table so Oracle may fetch the rows not by how they are ordered?
select * from (select object_id order by object_id) where rownum <10;
Thank's.
Gerald
TS, January 13, 2005 - 3:31 pm UTC
Tom,
The below query takes anywhere between 26 secs to
1 minute to execute.
table1 has 3.5 million rows
table2 has 2000 rows
select symbol,tdate,price from
(select c.symbol SYMBOL,b.tdate TDATE,b.price PRICE
FROM table1 b,table2 c
where c.inst_id=b.inst_id
and c.symbol='ABCD'
order by b.tdate desc) a
where rownum < 2000
there is a concatenated index for
inst_id and tdate on table1(b) and
an index for b.tdate.
tkprof output:-
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 135 14.97 42.69 13187 24358 813 1999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 138 14.99 42.70 13187 24358 813 1999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY
0 VIEW
0 SORT ORDER BY STOPKEY
0 TABLE ACCESS BY INDEX ROWID table1
0 NESTED LOOPS
0 TABLE ACCESS BY INDEX ROWID table2
0 INDEX RANGE SCAN FUTURES_KEY (object id 6392)
0 INDEX RANGE SCAN IDX_idx_table1_1 (object id 6401)
idx_table_1 is a concatenated index on INST_ID and TDATE
Is there a way to rewrite this query to make
it run faster?
Any help would be appreciated.
January 13, 2005 - 3:46 pm UTC
can you get the rows in the row source operation to be filled in.
(eg: run the query in sqlplus and EXIT sqlplus and then run tkrprof)
TS, January 13, 2005 - 4:14 pm UTC
Tom,
Here's the tkprof output with rows filled in.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 135 10.39 18.67 909 17133 352 1999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 10.42 18.69 909 17133 352 1999
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22
Rows Row Source Operation
------- ---------------------------------------------------
1999 COUNT STOPKEY
1999 VIEW
1999 SORT ORDER BY STOPKEY
359833 TABLE ACCESS BY INDEX ROWID table1
359835 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID table2
1 INDEX RANGE SCAN FUTURES_KEY (object id 6392)
359833 INDEX RANGE SCAN IDX_idx_table1_1 (object id 7965)
January 13, 2005 - 5:04 pm UTC
problem is you want to order by b.tdate, but the predicate is on c. so, if we use index for predicate, we cannot for sort and vice versa.
so, can you tell me - is SYMBOL unique in table2 C?
if so, perhaps
ops$tkyte@ORA9IR2> create table table1( tdate date, price number, inst_id number );
Table created.
ops$tkyte@ORA9IR2> create index t1_idx on table1(inst_id,tdate);
Index created.
ops$tkyte@ORA9IR2> create table table2( inst_id number, symbol varchar2(5) );
Table created.
ops$tkyte@ORA9IR2> create index t2_idx on table2(symbol);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable symb varchar2(5)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select *
2 from ( select /*+ FIRST_ROWS */ :symb, tdate, price
3 from table1
4 where inst_id = (select inst_id
5 from table2
6 where symbol = :symb )
7 order by inst_id DESC, tdate DESC )
8 where rownum < 2000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=1 Bytes=24)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=1 Card=1 Bytes=24)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE1' (Cost=1 Card=1 Bytes
4 3 INDEX (RANGE SCAN DESCENDING) OF 'T1_IDX' (NON-UNIQUE) (Cost=2
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'TABLE2' (Cost=1 Card=1 By
6 5 INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Car
ops$tkyte@ORA9IR2> set autotrace off
that'll be as good as it gets.
How does the ROWNUM works on joins ?
Purushotham, February 21, 2005 - 4:52 am UTC
Hi Tom,
We have two tables MTL_SYSTEM_ITEMS_B and MTL_ITEM_REVISIONS_B with 1.3 Million and 2.19 Million respectively And below is the query I executed
SQL> select count(*) from mtl_system_items_b where item_catalog_group_id = 515 and organization_id = 204
------------
205061
SQL > select count(*) from mtl_item_revisions_b where inventory_item_id in (
select inventory_item_id from mtl_system_items_b where item_catalog_group_id = 515 and organization_id = 204 )
-------------
305067
Indexes Used (Non-unique)
-------------------
MTL_SYSTEM_ITEMS_B_N1 ( ORGANIZATION_ID,SEGMENT1)
MTL_SYSTEM_ITEMS_B_N6 (ITEM_CATALOG_GROUP_ID)
MTL_ITEM_REVISIONS_B_N1 (INVENTORY_ITEMD_ID,ORGANIZATION_ID)
The below query is used to find if there are 2000 and more records (just a UI requirement to disable the order by options) and then fire the
same query (without count(*) OVER() and ROWNUM filter clause) again to fetch the records with order by or without it based on the rows.
SELECT *
FROM
(
SELECT
I.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID_B
, I.ORGANIZATION_ID AS ORGANIZATION_ID_B
, 'V1' AS ORGANIZATION_CODE
, I.ITEM_CATALOG_GROUP_ID AS ITEM_CATALOG_GROUP_ID
, IR.REVISION_ID AS REVISION_ID
, IR.REVISION AS REVISION
, (SELECT ICGKFV.CONCATENATED_SEGMENTS
FROM
MTL_ITEM_CATALOG_GROUPS_B_KFV ICGKFV
WHERE
ICGKFV.ITEM_CATALOG_GROUP_ID =I. ITEM_CATALOG_GROUP_ID ) AS CATALOG_GROUP
, (SELECT ITL.DESCRIPTION
FROM
MTL_SYSTEM_ITEMS_TL ITL
WHERE
ITL.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND ITL.ORGANIZATION_ID = I.ORGANIZATION_ID
AND ITL.LANGUAGE = USERENV('LANG')
) AS DESCRIPTION
IR.REVISION_LABEL AS REVISION_LABEL
,I.SEGMENT1 AS ITEM_NUMBER
,COUNT(*) OVER() AS CNT
FROM
MTL_SYSTEM_ITEMS_B I
,MTL_ITEM_REVISIONS_B IR
WHERE
I.ORGANIZATION_ID = 204
AND I.ITEM_CATALOG_GROUP_ID IN ( 515)
AND IR.ORGANIZATION_ID = I.ORGANIZATION_ID
AND IR.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND IR.EFFECTIVITY_DATE IN
(
SELECT FIRST_VALUE(IR2.EFFECTIVITY_DATE) OVER (ORDER BY IR2.EFFECTIVITY_DATE DESC)
FROM MTL_ITEM_REVISIONS_B IR2
WHERE IR2.ORGANIZATION_ID = IR.ORGANIZATION_ID
AND IR2.INVENTORY_ITEM_ID = IR.INVENTORY_ITEM_ID
AND IR2.EFFECTIVITY_DATE <= SYSDATE AND
IR2.IMPLEMENTATION_DATE IS NOT NULL
)
AND 1 = 1 AND ROWNUM < 2001)
/
The rowsource plan is
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 6.37 9.15 9002 24620 7 31
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 6.41 9.19 9002 24620 7 31
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 316
Rows Row Source Operation
------- ---------------------------------------------------
31 VIEW
31 WINDOW BUFFER
2000 COUNT STOPKEY
2000 TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B
4000 NESTED LOOPS
2000 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
2000 BITMAP CONVERSION TO ROWIDS
1 BITMAP AND
2 BITMAP CONVERSION FROM ROWIDS
59228 SORT ORDER BY
205064 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N6 (object id 265284)
3 BITMAP CONVERSION FROM ROWIDS
100418 SORT ORDER BY
1066910 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N1 (object id 607654)
2000 INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
2000 FILTER
2000 WINDOW SORT
2000 TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B
2000 INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
I understand this plan. But when we ran this with the performance team they said we need to disable the bitmap conversions in the db.
After turning off _b_tree_bitmap_plans I ran the same query. Here is the tkprof output for the same
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 5 16 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 135 0.35 0.40 442 30410 0 2000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 137 0.39 0.43 447 30426 0 2000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 316
Rows Row Source Operation
------- ---------------------------------------------------
2000 VIEW
2000 WINDOW BUFFER
2000 COUNT STOPKEY
2000 TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B
4000 NESTED LOOPS
2000 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
2003 INDEX RANGE SCAN MTL_SYSTEM_ITEMS_B_N6 (object id 265284)
2000 INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
2000 FILTER
2012 WINDOW SORT
2015 TABLE ACCESS BY INDEX ROWID MTL_ITEM_REVISIONS_B
2015 INDEX RANGE SCAN MTL_ITEM_REVISIONS_B_N1 (object id 265064)
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1344 Card=1 Bytes=2555)
1 0 VIEW (Cost=1344 Card=1 Bytes=2555)
2 1 WINDOW (BUFFER) (Cost=1344 Card=1 Bytes=77)
3 2 COUNT (STOPKEY)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_REVISIONS_B'
5 4 NESTED LOOPS (Cost=1344 Card=1 Bytes=77)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_SYSTEM_ITEMS_B'
7 6 INDEX (RANGE SCAN) OF 'MTL_SYSTEM_ITEMS_B_N6'
8 5 INDEX (RANGE SCAN) OF 'MTL_ITEM_REVISIONS_B_N1'
9 8 FILTER
10 9 WINDOW (SORT) (Cost=17 Card=1 Bytes=26)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_REVISIONS_B'
12 11 INDEX (RANGE SCAN) OF 'MTL_ITEM_REVISIONS_B_N1'
I have too many questions I am desepartely trying to understand couple of things
1. The query (though is used to find out the count) in fact retrieves values from more than 2 tables (columns do a select query). However I don't see them in the plan. I am quite astonished that oracle knows what I am doing do restricts to only two tables ? I can see those tables in the plan if we remove the COUNT(*) OVER()
2. The plan 1 looks fine to me. As, it first scans the records in MTL_SYSTEM_ITEMS table though using (N6 & N1) to get the inventory_item_id and then join with the MTL_REVISIONS table to get the current revision label. The row scan numbers match your expectations.
3. However after turning off bitmap conversion options I am pretty much confused
a) CBO scans MTL_ITEM_REVISIONS_B_N1 and MTL_SYSTEM_ITEMS_B_N6. Will it ensure that 2000 items scaned in REVISIONS table will be of item_catalog_group 515 ? I really doubt this.
b) The ROWNUM filter clause will be applied to all indexes used in the plan or only the driving table indexes (assuming that indexes are used to scan the rows ) ?
Please help me here Tom. I have been searching for the answers for more than a week but without any success.
Thanks
February 21, 2005 - 10:31 am UTC
am I understanding... do alot of work to figure out if we need to prevent them from sorting?
this could be a new question at some point but it is too "big and new" for a review/followup to the original
Thanks for looking into my questions
Purushotham, February 22, 2005 - 1:19 am UTC
Tom,
Thanks for looking into my queries. I hope I will get opportunity to start a new thread. But unforunately I sit in other part of the world. I tried so many times to post a new question but I always see "Sorry.." message. Hope I will get lucky in near future.
what does rownum > 0 do
A reader, March 08, 2005 - 10:40 am UTC
hi
what does rownum > 0 do?
is it same as no_merge i.e force an inline view is evaluated first? how does it work? is it in the documentation ?
cheers
March 08, 2005 - 11:42 am UTC
rownum, like intersect, union, minus, .... are non-mergeable set operations.
so it sort of 'documented' in as much as the set operations are non-mergable operations.
so rownum > 0 is
A reader, March 08, 2005 - 12:14 pm UTC
Hi
so rownum > 0 == /*+ NO_MERGE */ in a inline view?
cheers
March 08, 2005 - 3:07 pm UTC
pretty much in most cases
> so rownum > 0 == /*+ NO_MERGE */ in a inline view?
Alberto Dell'Era, March 08, 2005 - 1:51 pm UTC
No, and since "all it takes is a single counter-example" :
dellera@ORACLE9I> create table t (x int primary key, y char(100) default 'x');
dellera@ORACLE9I> insert into t (x) select rownum from all_objects where rownum <= 1000;
dellera@ORACLE9I> exec dbms_stats.gather_table_stats (user, 't', cascade=>true);
======================================================================
select * from (
select x, y from t
) where x = 1
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
======================================================================
select * from (
select x, y from t where rownum > 0
) where x = 1
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
|* 1 | VIEW | | 1000 | 112K| 4 |
| 2 | COUNT | | | | |
|* 3 | FILTER | | | | |
| 4 | TABLE ACCESS FULL| T | 1000 | 101K| 4 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=1)
3 - filter(ROWNUM>0)
======================================================================
select * from (
select /*+ no_merge */ x, y from t
) where x = 1
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | VIEW | | 1 | 115 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"=1)
no_merge prevents merging, rownum > 0 prevents merging AND predicate pushing. They are not equivalent (there's a nice discussion about merging vs predicate pushing here on asktom).
Note: plans fetched from v$sql_plan.
March 08, 2005 - 3:21 pm UTC
good point -- it does both (since rownum must be considered without the predicates)
> so rownum > 0 == /*+ NO_MERGE */ in a inline view?
Alberto Dell'Era, March 08, 2005 - 1:57 pm UTC
No, and since "all it takes is a single counter-example" :
dellera@ORACLE9I> create table t (x int primary key, y char(100) default 'x');
dellera@ORACLE9I> insert into t (x) select rownum from all_objects where rownum <= 1000;
dellera@ORACLE9I> exec dbms_stats.gather_table_stats (user, 't', cascade=>true);
======================================================================
select * from (
select x, y from t
) where x = 1
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X"=1)
======================================================================
select * from (
select x, y from t where rownum > 0
) where x = 1
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 |
|* 1 | VIEW | | 1000 | 112K| 4 |
| 2 | COUNT | | | | |
|* 3 | FILTER | | | | |
| 4 | TABLE ACCESS FULL| T | 1000 | 101K| 4 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"=1)
3 - filter(ROWNUM>0)
======================================================================
select * from (
select /*+ no_merge */ x, y from t
) where x = 1
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | VIEW | | 1 | 115 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 104 | 2 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0025886 | 1 | | 1 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("X"=1)
no_merge prevents merging, rownum > 0 prevents merging AND predicate pushing. They are not equivalent (there's a nice discussion about merging vs predicate pushing here on asktom).
Note: plans fetched from v$sql_plan.
rownum + OR + CONTAINS
Andy, March 09, 2005 - 4:55 am UTC
Hi Tom,
I've encountered behaviour when using rownum with OR-ed CONTAINS terms that I don't understand. Here is a reduced case to illustrate:
mires@WS2TEST> create table t as select object_id as id, object_name as txt1, object_name as txt2 from all_objects;
Tabelle wurde angelegt.
mires@WS2TEST> create index fti_1 on t(txt1) indextype is ctxsys.context;
Index wurde angelegt.
mires@WS2TEST> create index fti_2 on t(txt2) indextype is ctxsys.context;
Index wurde angelegt.
mires@WS2TEST> alter index fti_1 rebuild;
Index wurde geändert.
mires@WS2TEST> alter index fti_2 rebuild;
Index wurde geändert.
mires@WS2TEST> select count(*) from t where contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0;
COUNT(*)
----------
10
So I have 10 rows that meet this criteria...
mires@WS2TEST> select count(*) from t where contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0 and rownum < 12;
COUNT(*)
----------
2
I expect here that, in the absence of clarifying brackets, the first 11 rows are fetched and then checked to see if the fulltext index is satisfied.
mires@WS2TEST> select count(*) from t where (contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0) and rownum < 12;
COUNT(*)
----------
10
..so, with brackets, I get what I expect.
mires@WS2TEST> select count(*) from (select * from t where (contains(txt1, 'default%', 1) > 0 or contains(txt2, 'profile%', 2) > 0)) where rownum < 12;
COUNT(*)
----------
2
This last one i don't understand: the inner query is the same as the first one I executed above, with a "window" applied to the whole result to get the first 11 rows. What am I missing?
March 09, 2005 - 7:49 am UTC
last one looks like "a bug", please contact support.
No bug!
Urs, March 09, 2005 - 8:27 am UTC
March 09, 2005 - 8:29 am UTC
I said the last one:
select count(*)
from (
select *
from t
where (contains(txt1,'default%', 1) > 0
or contains(txt2, 'profile%', 2) > 0)
)
where rownum < 12;
that can only apply the rownum <12 AFTER the or's have all been done.
using rownum to get extra rows
Eric Peterson, March 29, 2005 - 4:52 pm UTC
My coworker and I have been racking our brains to solve this. We'd like ten items returned for every feature, whether or not there are actually ten. We've come up with:
SELECT
NVL ( B.feature, ' ' ) AS feature,
concat_all ( concat_expr ( NVL ( B.item, ' ' ), '|' )) items
FROM
(
SELECT
ROWNUM AS dr
FROM
emp
WHERE
ROWNUM < 11
) A
LEFT JOIN
(
SELECT
*
FROM
(
SELECT
feature,
TRIM ( item ) AS item,
DENSE_RANK () OVER ( PARTITION BY feature
ORDER BY item ) dr
FROM
features
)
WHERE
dr < 11
) B
ON ( A.dr = B.dr )
GROUP BY
NVL ( B.feature, ' ' )
/
But this returns
fea1 item1|item2|item3|item4|item5|item6|item7|item8|item9|item10
fea2 item21|item22
whereas we'd like to see
'fea2 item21|item22| | | | | | | | '
<quotes added to show the tenth space, concat_all doesn't end with a delimiter, which is fine>
We thought the left join to a defined set of 10 would force each of the second query to always have ten items. But we're missing something. Can you point us in the right direction?
Thanks much.
Eric
March 29, 2005 - 8:02 pm UTC
ops$tkyte@ORA10G> create table t ( feature varchar2(4), item varchar2(6) );
Table created.
ops$tkyte@ORA10G> insert into t select 'fea1', 'item' || rownum from emp;
14 rows created.
ops$tkyte@ORA10G> insert into t select 'fea2', 'item' || (20+rownum) from emp where rownum <= 2;
2 rows created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> column items format a40
ops$tkyte@ORA10G> variable n number
ops$tkyte@ORA10G> exec :n := 10
PL/SQL procedure successfully completed.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select feature,
2 n,
3 case when n > :n
4 then substr( items, 1, instr(items,'|',1,:n)-1 )
5 when n < :n
6 then items || replace(rpad('|',:n-n-1,'|'), '|', '| ' )
7 else items
8 end items
9 from (
10 select feature, (length(items)-length(replace(items,'|',''))) n, items
11 from (
12 select feature, replace(stragg(item),',','|') items
13 from t
14 group by feature
15 )
16 )
17 /
FEAT N ITEMS
---- ---------- ----------------------------------------
fea1 13 item1|item2|item3|item5|item7|item9|item
11|item13|item14|item12
fea2 1 item21|item22| | | | | | | |
does that work ok?
using rownum to get extra rows
Eric Peterson, March 30, 2005 - 4:32 pm UTC
Yes, this is very helpful. We were caught up in trying to get the sub-query to only return 10 items, we did not consider getting all the data and then limit to the first 10 elements.
But I am still curious to why the left join did not work. (Data and tables from Oracle sample database, not the Scott/Tiger but the newer one.) Note that we are on Oracle 9iR2.
SELECT
*
FROM
(
SELECT
ROWNUM AS dr
FROM
HR.employees
WHERE
ROWNUM < 11
) A
LEFT JOIN
(
SELECT
department_id,
last_name,
salary,
DENSE_RANK() OVER ( PARTITION BY department_id
ORDER BY salary,
last_name ) dr
FROM
HR.employees
) B
ON ( A.dr = B.dr )
ORDER BY
B.department_id,
A.dr,
B.last_name,
B.salary
returns data like:
DR DEPARTMENT_ID LAST_NAME SALARY DR
--- ------------- ------------------------- ------------ ------------
1 10 Whalen 4400 1
1 20 Fay 6000 1
2 20 Hartstein 13000 2
1 30 Colmenares 2500 1
2 30 Himuro 2600 2
3 30 Tobias 2800 3
4 30 Baida 2900 4
5 30 Khoo 3100 5
6 30 Raphaely 11000 6
....
Whereas the use of the left join would lend me to believe that department 10 should be listed sort of like this:
DR DEPARTMENT_ID LAST_NAME SALARY DR
--- ------------- ------------------------- ------------ ------------
1 10 Whalen 4400 1
2
3
4
5
6
7
8
9
10
Am I using the left join incorrectly here? Is the optimizer tuning out the first sub-query? Is the use of ROWNUM not really the best use here?
Thanks much for your insight and help.
Eric
March 30, 2005 - 5:25 pm UTC
because you need a 10g PARTITIONED outer join!!
you generated the rows 1, 2, ... 10
you outer joined that set to the other table. Well, rownum "5" had a mate in the first feature, it didn't need to "make one up"
you could have:
a) generated the 10
b) generated the set of unique features
c) cartesian product aXb to get feature,1 feature,2, ... for every feature/number
d) outer join to that.
see:
</code>
https://www.oracle.com/technetwork/issue-archive/2014/14-jan/o14asktom-2079690.html <code>
near the bottom for a example.
holy cr*p, it worked!
Andrew Dubry, May 26, 2005 - 12:26 pm UTC
Using the clues on this page, I was able to construct a query using count and group by and then having it return JUST results 5 thru 10, out of the 15 I limited it to!!
Where Rownum >= 5 and Rownum <= 10 (pseudo code)
That's the holy grail of SQL queries and I figured out how to do it!!! woo hoo!!! thankyou thankyou.. here's my query, BTW:
select * from (
SELECT x, y, rownum r from
(select pd.base_id as X, COUNT(pd.full_id) as Y
FROM prod_detail pd
HAVING COUNT(pd.full_id) > 20
GROUP BY pd.base_id
ORDER BY COUNT(pd.full_id)
), dual
where rownum <= 15
)
where r >= 5
and r <= 10
Thanks!!
gr8
A reader, June 01, 2005 - 10:49 am UTC
Saket
Saket, January 02, 2008 - 11:15 am UTC
Hi Tom,
Please accept my best wishes and a very Happy New Year 2008. I thank you Tom and all your team members for everything.
I doubt Mr. Andrew's solution. I am afraid it still does full table scan, as it has order by count(pd.full_id).
I am dealing with a different scenario. I tried this solution in my scenario and did not find any performance gain. I am developing an auditing application, which needs to select random sample rows from a large table for auditing purpose.
Scenario:
I have a table with about 150 million rows. To select say 10 random rows from this table (every time) I use following query (a simplified version)
Select * from (SELECT ct.*
FROM cost_tracking ct
ORDER BY (ct.cost_tracking_id + dbms_random.random) DESC)
Where rownum <=10
Question:
As the above query does full table scan on cost_tracking table, I am looking for an alternate and efficient solution. Is there any better way of selecting 10 random rows form a large table?
Waiting for your reply.
Thanks
Saket
January 02, 2008 - 2:39 pm UTC
I don't think anyone every said "full scan is avoided there" so - I don't get your point about that.... so what?
have you read about the SAMPLE query??? there is a SAMPLE clause for sql.
go for a random percentage of the table, and then take 10 of that.
sample rows
saket, January 16, 2008 - 4:56 pm UTC
Well, we are talking of performance here. What Mr. Andrew claims that he is just accessing first 15 rows (return JUST results 5 thru 10, out of the 15 I limited it to!!), which is wrong? He still access full table to retrieve 10 rows. Or may be I misunderstood his statement!
2. I have gone through sample clause. It is not suitable in my application because, its argument expects %, which may not give me desired results in all possible table sizes.
What I am looking for is; Instead of getting n% of sample rows, Can I get n sample rows?
Thanks
Saket
runstats prob
Harrison Picot, March 24, 2008 - 5:16 pm UTC
HiTom,
Thanks for all the great help. I am having what seems to
be a pretty basic problem, but I have been working on it for a full day and not getting any closer. When I run this code from the part above on setting up runstats, it errors:
create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
/
hpicot@ora10g> @part1
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges
If I just do the select, runs, but when I try to creat the view in either 10.2 or 11G, I get that error. I queried all_objects for an existing object named 'STATS' on 11G and got nothing, and I ran it as system to see if there are any permission errors, and tried changing the view name, but those changes generated the same error. Like I say, I am sure it something simple but certainly has not occurred to me yet. The oerr help says (like) that I am trying to change a password without authority, but that seems off also.
rownum >0 & insert.
Dhairyasheel Ttawde, March 26, 2008 - 3:15 am UTC
Hi Tom,
a few months back, we had a unique problem at our site.
1. We were selecting from a normal table
2. & inserting into a global temp table.
the insert would not work for even 10 records. It would just hang.
then the d.b.a out here put another filter in the where clause of the select statement, that was
..... and rownum >0;
and the insert started to work.
Whats happening here....
Sorry can't provide any technical details for the above.
asc and desc in top-n
dk, March 31, 2008 - 4:40 am UTC
Hello Tom,
I've tried you example
ops$tkyte@ORA817DEV.US.ORACLE.COM> select *
2 from ( select owner, object_name, object_id
3 from t
4 order by object_id desc )
5 where rownum < 10;
Could you please explain why the index is not used if changing to ascending order?
March 31, 2008 - 9:36 am UTC
I cannot explain that which doesn't happen.
order by object_id desc
order by object_id asc
either one or both can and will use an index on object_id.
ops$tkyte%ORA10GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(object_id) ;
Index created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from (select * from t order by object_id ASC) where rownum < 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3993548034
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1152 | 3
|* 1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 9 | 1152 | 3
| 3 | TABLE ACCESS BY INDEX ROWID| T | 49794 | 4522K| 3
| 4 | INDEX FULL SCAN | T_IDX | 9 | | 2
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
ops$tkyte%ORA10GR2> select * from (select * from t order by object_id DESC) where rownum < 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 3806458071
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 1152 | 3
|* 1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 9 | 1152 | 3
| 3 | TABLE ACCESS BY INDEX ROWID| T | 49794 | 4522K| 3
| 4 | INDEX FULL SCAN DESCENDING| T_IDX | 9 | | 2
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
ops$tkyte%ORA10GR2> set autotrace off
it's working
dk, April 02, 2008 - 1:08 am UTC
I must cancel my last question. I've checked once more. It's using the index properly.
Will ROWNUM return same row everytime ?
ankitv, September 12, 2010 - 11:37 am UTC
Hi Tom, the article (along with the rest ones) have been very helpful. I have a small query :
select * from A where rownum = 1
Will the above query return same row everytime in the future if:
1. No row is ever added into the table.
2. There is constant addition of rows in the table.
September 13, 2010 - 2:22 pm UTC
answer is definitely NO, it does not have to return the same row and you certainly cannot count on it.
1) if rows are updated or deleted, the 'first' row can easily change.
2) if you add rows, we add them where ever we feel like and that could certainly also be "right at the top". The last row inserted could easily be the first row returned.
also, anything that causes a query plan change will change the order of the rows being returned.
Will ROWNUM return same row everytime ?
ankitv, September 17, 2010 - 3:07 am UTC
But what if no change happens in table (no add/upd/delete) , suppose its a Parametrization table, will
select * from table where rownum =1
will return same row every time.
September 17, 2010 - 7:13 am UTC
no, it does not have to.
will it? probably...
can you count on it? no more than you can count on group by sorting data - which you cannot.
If you have a concept of a 'first row' you better have some idea how to identify it - and then use that technique to deterministically find it each and every time.
vaibhaw, July 24, 2013 - 2:00 pm UTC
if i have to fetch 2(like 2 and 3) row by using rownum then its possible?
July 31, 2013 - 6:56 pm UTC
huh? no idea what you are trying to say
confusion two times filter
Ranjan, September 15, 2013 - 2:59 pm UTC
Hi Tom,
Hope you are ok and doing great.
I have a doubt regarding the explain plan of query using rownum.
################
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 15 20:01:28 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password: ********
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table demo1(a number,b number);
Table created.
SQL> insert into demo1 select level,level+10 from dual connect by level<5;
4 rows created.
SQL> insert into demo1 select * from demo1;
4 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'DEMO1');
PL/SQL procedure successfully completed.
SQL> set lines 100 pages 90
SQL> set autotrace traceonly explain
SQL> select * from (select a,count(*) from demo1 group by a) where rownum<3;
A COUNT(*)
---------- ----------
1 2
2 2
Execution Plan
----------------------------------------------------------
Plan hash value: 1574380217
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 52 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 4 | 104 | 4 (25)| 00:00:01 |
|* 3 | SORT GROUP BY STOPKEY| | 4 | 12 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEMO1 | 8 | 24 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
3 - filter(ROWNUM<3)
###############################
My doubt is why it uses two time filters.
I think it should be 3-sort group by instead of "3-sort group by stopkey" .
But thinking deep(keeping in mind for huge number of data)
,then I thought that it sorting for 2 different values(like 1,2 in this case) and grouping those two diff values and leave all remaining values as they are under it and then do
the final sort for two record(which is 1-count stopkey.
It even showing two filter for query like
#######
select * from (select * from demo1 order by a) where rownum <3;
###########
But I am not sure if that really does as I explained.
Could you please clarify and clear my above doubt.
September 23, 2013 - 6:14 pm UTC
it is just an artifact, is "nuance" it doesn't really rownum < 3 twice, it is just that an explain plan is in imperfect representation of the binary program generated to run the plan.
typo error
Ranjan, September 15, 2013 - 3:10 pm UTC
"final search for 2 records" instead of "final sort for 2 record"
hmm got it :)
A reader, September 23, 2013 - 6:37 pm UTC