ORA-01652 Why does unused row limiter solve this?

user_8234087 0
Viewed 64

If I run a query without a row limiter i get an ora-01652 telling me I am out of temp table space. (I'm not the DBA & I admittedly don't fully understand this error.) If I add a rownum < 1000000000 it runs in a few seconds (yes, it's limited to a billion rows). My inner query only returns about 1,000 rows. How is an absurdly large row limiter, that is never reached, making this query run? There should be no difference between the limited and unlimited queries, no?

select
    col1,
    col2,
    ...
from
        (
            select
                col1, col2,...
            from table1 a
                join table2 b-- limiter for performance
                     on a.column= b.column
                     or a.col= b.col
            where
                filter = 'Y'
                and rownum <1000000000  -- irrelevant but query doesn't run without it.
    )  c
join table3 d
        on c.id  = d.id
temp oracle sql
1 Answers

We need to see the execution plan for the queries with and without the rownum condition. But as an example, adding a "rownum" can change an execution plan

SQL> create table t as select * from dba_objects
  2  where object_id is not null;

Table created.

SQL>
SQL> create index ix on t ( object_id );

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 82262 |    10M|   445   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 82262 |    10M|   445   (2)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID">0)

SQL> select * from t where object_id > 0  and rownum < 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 658510075

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     9 |  1188 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     9 |  1188 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |       |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

This is a simplistic example, but you can get similar things with joins and the like, in particular, the "rownum" clause might be prohibiting the innermost join being folded into the outermost one, and thus yielding a different plan.

user_73672930
Related