| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with ROWNUM and VIEW with compound query
Here is a simplified test case to recreate the issue. I am providing below the
SQL*Plus session of this test case which has all the needed info:
Table dropped.
SQL>
SQL> drop table objects2
2 /
Table dropped.
SQL>
SQL> CREATE TABLE objects1
2 (
3 Object_id INT,
4 Object_Name CHAR(30),
5 owner CHAR(30),
6 Object_type CHAR(30),
7 CONSTRAINT PK_objects1 PRIMARY KEY (Object_id )
8 )
9 /
Table created.
SQL>
SQL> CREATE TABLE objects2
2 (
3 Object_id INT,
4 Object_Name CHAR(30),
5 owner CHAR(30),
6 Object_type CHAR(30),
7 CONSTRAINT PK_objects2 PRIMARY KEY (Object_id )
8 )
9 /
Table created.
SQL>
SQL> -- The following statements create 24,000 records each with different
primary
SQL> -- keys in both Objects1 and Objects2. SQL> -- Object2 table keys start at 24,001. SQL> SQL> insert into objects1 2 (select rownum, object_name, owner, object_type from all_objects 3 where rownum <= 3000 4 union 5 select rownum + 3000, object_name, owner, object_type from 6 all_objects where rownum <= 3000 7 union 8 select rownum + 6000, object_name, owner, object_type from 9 all_objects where rownum <= 3000 10 union 11 select rownum + 9000, object_name, owner, object_type from 12 all_objects where rownum <= 300013 )
12000 rows created.
SQL>
SQL> insert into objects1 select rownum + 12000, object_name, owner,
2 object_type from objects1
3 /
12000 rows created.
SQL>
SQL> insert into objects2 select rownum + 24000, object_name, owner,
2 object_type from objects1
3 /
24000 rows created.
SQL>
SQL> commit
2 /
Commit complete.
SQL>
SQL> analyze table objects1 compute statistics
2 /
Table analyzed.
SQL>
SQL> analyze table objects2 compute statistics
2 /
Table analyzed.
SQL> SQL> set autotrace on explain statistics SQL> /
Table analyzed.
SQL> SQL> set timing on SQL> /
Table analyzed.
Elapsed: 00:00:00.51
SQL> SQL> COLUMN PLAN_PLUS_EXP format A120 SQL> COLUMN OWNER format A9 SQL> column OBJECT_TYPE format A12 SQL> set wrap off SQL> /
Table analyzed.
Elapsed: 00:00:00.71
SQL>
SQL> -- Complex query with union SQL to return roughly 10 rows across both
tables sorted
SQL> -- by Object_name As one can see, this SQL runs very fast and the Execution
plan
SQL> -- shows use of proper indices.
SQL> SELECT * FROM (SELECT *
2 FROM objects1 3 UNION 4 SELECT * 5 FROM objects2) 6 WHERE object_id BETWEEN 23996 AND 24005 order byobject_name
OBJECT_ID OBJECT_NAME OWNER OBJECT_TYPE
---------- ------------------------------ --------- ------------
24001 /15714cfb_ChainedExpression SYS JAVA CLASS
24002 /15780a2e_AuditorInstallerErro SYS JAVA CLASS
24003 /158af209_CustomizerHarnessErr SYS JAVA CLASS
24004 /15907e2f_OracleProvider SYS JAVA CLASS
24005 /15997680_OraRTStatementOraBat SYS JAVA CLASS
23996 /1bf16ee7_MemStatIntGetter SYS JAVA CLASS
23997 /1bf54141_CharacterConverterSJ SYS JAVA CLASS
23998 /1bfe44c6_PropertyList SYS JAVA CLASS
23999 /1c12cda1_OracleXAConnection SYS JAVA CLASS
24000 /1c2a5454_DigestAuthentication SYS JAVA CLASS
10 rows selected.
Elapsed: 00:00:00.11
Execution Plan
2 1 VIEW (Cost=10 Card=10 Bytes=1090) 3 2 SORT (UNIQUE) (Cost=10 Card=10 Bytes=940) 4 3 UNION-ALL 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJECTS1' (Cost=3 Card=5 Bytes=470) 6 5 INDEX (RANGE SCAN) OF 'PK_OBJECTS1' (UNIQUE) (Cost=2 Card=5) 7 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJECTS2' (Cost=3 Card=5 Bytes=470) 8 7 INDEX (RANGE SCAN) OF 'PK_OBJECTS2' (UNIQUE) (Cost=2 Card=5)
Statistics
15 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1321 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
10 rows processed
SQL>
SQL> -- Adding Tom Kytes' paging method to fetch pages of records with second
page of 5
SQL> -- records. This SQL does take quite longer (In my real world case 10's of
minutes)
SQL> -- and the execution plan shows use of full table scans. It appears that
the RowNum
SQL> -- is affecting the inner most query badly SQL> SQL> SQL> SELECT * 2 FROM (SELECT b.*, ROWNUM r 3 FROM (SELECT * FROM (SELECT * 4 FROM objects1 5 UNION 6 SELECT * 7 FROM objects2) 8 WHERE object_id BETWEEN 23996 AND 24005 order by object_name) b 9 WHERE ROWNUM <= 10) c
OBJECT_ID OBJECT_NAME OWNER OBJECT_TYPE R
---------- ------------------------------ --------- ------------ ----------
23996 /1bf16ee7_MemStatIntGetter SYS JAVA CLASS 6
23997 /1bf54141_CharacterConverterSJ SYS JAVA CLASS 7
23998 /1bfe44c6_PropertyList SYS JAVA CLASS 8
23999 /1c12cda1_OracleXAConnection SYS JAVA CLASS 9
24000 /1c2a5454_DigestAuthentication SYS JAVA CLASS 10
Elapsed: 00:00:01.32
Execution Plan
2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=2414 Card=48000 Bytes=5232000) 4 3 SORT (ORDER BY STOPKEY) (Cost=2414 Card=48000 Bytes=5232000) 5 4 VIEW (Cost=886 Card=48000 Bytes=5232000) 6 5 SORT (UNIQUE) (Cost=886 Card=48000 Bytes=4512000) 7 6 UNION-ALL 8 7 TABLE ACCESS (FULL) OF 'OBJECTS1' (Cost=24 Card=24000 Bytes=2256000) 9 7 TABLE ACCESS (FULL) OF 'OBJECTS2' (Cost=24 Card=24000Bytes=2256000)
Statistics
13 recursive calls
8 db block gets
753 consistent gets
1346 physical reads
0 redo size
1021 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
1 sorts (disk)
5 rows processed
SQL>
SQL> --Removing the union inside the innermost query makes it run fast again.
SQL>
3 FROM (SELECT * FROM (SELECT * 4 FROM objects1) 5 WHERE object_id BETWEEN 23996 AND 24005 order by object_name) b 6 WHERE ROWNUM <= 10) c
OBJECT_ID OBJECT_NAME OWNER OBJECT_TYPE R
---------- ------------------------------ --------- ------------ ----------
23996 /1bf16ee7_MemStatIntGetter SYS JAVA CLASS 1
23997 /1bf54141_CharacterConverterSJ SYS JAVA CLASS 2
23998 /1bfe44c6_PropertyList SYS JAVA CLASS 3
23999 /1c12cda1_OracleXAConnection SYS JAVA CLASS 4
24000 /1c2a5454_DigestAuthentication SYS JAVA CLASS 5
Elapsed: 00:00:00.21
Execution Plan
2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=5 Card=5 Bytes=545) 4 3 SORT (ORDER BY STOPKEY) (Cost=5 Card=5 Bytes=470) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJECTS1' (Cost=3 Card=5 Bytes=470) 6 5 INDEX (RANGE SCAN) OF 'PK_OBJECTS1' (UNIQUE) (Cost=2 Card=5)
Statistics
13 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1021 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
5 rows processed
Received on Tue Nov 18 2003 - 04:54:12 CST
 |
 |