Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with ROWNUM and VIEW with compound query

Re: Problem with ROWNUM and VIEW with compound query

From: Venkat <Venkat_member_at_newsguy.com>
Date: 18 Nov 2003 02:54:12 -0800
Message-ID: <bpctok02rtt@drn.newsguy.com>


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 <= 3000
 13 )
 14 /

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 by
object_name
  7
SQL> /
 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



0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=10 Bytes=1090) 1 0 SORT (ORDER BY) (Cost=11 Card=10 Bytes=1090)
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

 10 WHERE r >= 6
 11 /
 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



0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2414 Card=10 Bytes=1220) 1 0 VIEW (Cost=2414 Card=10 Bytes=1220)
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=24000
Bytes=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> 

SQL>
SQL> SELECT *
  2 FROM (SELECT b.*, ROWNUM r
  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

  7 WHERE r >= 1
  8 /
 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



0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=5 Bytes=610) 1 0 VIEW (Cost=5 Card=5 Bytes=610)
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US