Fetch First performing worse than Rownum

Hibernate ORM
1

I understand recent Hibernate versions use the ‘fetch first n rows’ clause as the default limit handler for Oracle. Having recently moved to Hibernate 6.6, I am facing some trouble with this - the fetch first clause leads to significantly poorer query performance with higher query cost as opposed to the older ‘rownum’ clause. In one particular case, the fetch first query is 244 times as costly as the rownum version. The two queries are otherwise completely identical and are being executed on the exact same database (Oracle 19c SE2, FTR).

I know that using the legacy Oracle dialect would throw me back to the old ‘rownum’ way, but I would not prefer having to go down this route just because I am facing an issue with this particular case.

Any ideas on why this could be happening? Also, it would be great if someone could tell me why the fetch first clause was adopted as the default within Hibernate - e.g. any resources that document that this is somehow categorically better than the older ‘rownum’ way.

I can also post the queries and query plans with costs, if that helps. Thanks!

2

If I were you, I would ask Oracle to fix that in their database. Having said that, you will have to show us examples of HQL queries and the SQL which performs bad.
There is a possibility to switch back to the rownum rendering also in newer Oracle versions, and for some feature combinations it’s even necessary to do so, but we try to use the new syntax when possible.

3

Thanks for your input :slight_smile: Here’s the SQL that I get out of hibernate that exhibits the reported performance issue:

SELECT m_table0_.*
FROM m_table m_table0_
LEFT OUTER JOIN m_table m_table1_ ON
(TO_NUMBER(substr(m_table0_.column1, 23))= m_table1_.column2
	OR TO_NUMBER(substr(m_table0_.column7, 23))= m_table1_.column2
		AND (m_table0_.column1 IS NULL))
LEFT OUTER JOIN m_table user2_ ON
(TO_NUMBER(substr(m_table0_.column3, 23))= user2_.column2)
WHERE (m_table0_.column4 IN (12 , 3 , 8 , 4 , 2 , 6 , 0 , 7)
	OR (m_table0_.column4 IN (7 , 8))
		AND m_table0_.column7 = 'value1')
AND (m_table0_.column6 IS NULL
	OR m_table0_.column6 = 0)
AND (m_table0_.column5 NOT IN (50 , 1))
AND m_table0_.column8 <> 'name'
ORDER BY CASE WHEN m_table0_.column4 = '2'
OR m_table0_.column4 = '6' THEN 1
ELSE 2
END, lower(m_table0_.column8) ASC 
NULLS LAST, m_table0_.column9, m_table0_.column2 
FETCH FIRST 1 ROWS ONLY;

Also, when you talk about a “possibility to switch back to the rownum rendering” within newer Oracle versions, does it allow the use of rownum regardless of whether the SQL query uses rownum or fetch first? I ask this because Hibernate will force the use of fetch first, so this Oracle feature would have to override that and use rownum instead.

4

Please also share a schema and query plans.
Yes, we can always force the rownum rendering if we determine that it is faster.

5

Here’s the plan from the fetch first query:


-----------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 | 18946 |       |    32M  (1)| 00:21:26 |
|   1 |  SORT ORDER BY            |                 |     1 | 18946 |       |    32M  (1)| 00:21:26 |
|*  2 |   VIEW                    |                 |     1 | 18946 |       |    32M  (1)| 00:21:26 |
|*  3 |    WINDOW SORT PUSHED RANK|                 | 32120 |    13M|    13M|    32M  (1)| 00:21:26 |
|   4 |     MERGE JOIN OUTER      |                 | 32120 |    13M|       |    32M  (1)| 00:21:26 |
|   5 |      NESTED LOOPS OUTER   |                 | 32120 |    13M|       |  1028   (1)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL   | m_table         | 32120 |    12M|       |  1026   (1)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN   | SYS_C0015524    |     1 |     6 |       |     0   (0)| 00:00:01 |
|   8 |      BUFFER SORT          |                 |     1 |       |       |    32M  (1)| 00:21:26 |
|   9 |       VIEW                | VW_LAT_18E0BD02 |     1 |       |       |  1024   (1)| 00:00:01 |
|* 10 |        TABLE ACCESS FULL  | m_table         |     1 |   421 |       |  1024   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Here’s the plan from the ‘rownum’ variant:


---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 | 17893 |       |   135K  (1)| 00:00:06 |
|*  1 |  COUNT STOPKEY                      |                 |       |       |       |            |          |
|   2 |   VIEW                              |                 | 64216 |  1095M|       |   135K  (1)| 00:00:06 |
|*  3 |    SORT ORDER BY STOPKEY            |                 | 64216 |    26M|    27M|   135K  (1)| 00:00:06 |
|   4 |     MERGE JOIN OUTER                |                 | 64216 |    26M|       |   129K  (1)| 00:00:06 |
|   5 |      NESTED LOOPS OUTER             |                 | 32108 |    13M|       |  1028   (1)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL             | m_table         | 32108 |    12M|       |  1026   (1)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN             | SYS_C0015524    |     1 |     6 |       |     0   (0)| 00:00:01 |
|   8 |      BUFFER SORT                    |                 |     2 |       |       |   135K  (1)| 00:00:06 |
|   9 |       VIEW                          | VW_LAT_12222494 |     2 |       |       |     4   (0)| 00:00:01 |
|  10 |        CONCATENATION                |                 |       |       |       |            |          |
|* 11 |         FILTER                      |                 |       |       |       |            |          |
|* 12 |          TABLE ACCESS BY INDEX ROWID| m_table         |     1 |   420 |       |     2   (0)| 00:00:01 |
|* 13 |           INDEX UNIQUE SCAN         | SYS_C0015524    |     1 |       |       |     1   (0)| 00:00:01 |
|* 14 |         TABLE ACCESS BY INDEX ROWID | m_table         |     1 |   420 |       |     2   (0)| 00:00:01 |
|* 15 |          INDEX UNIQUE SCAN          | SYS_C0015524    |     1 |       |       |     1   (0)| 00:00:01 |

Also, please let me know if you have any inputs on how to get the ‘rownum forcing’ feature switched on within Oracle - that’d be super helpful.

6

Hello @orm_s,
Please provide the answers to these questions:

1/ Could you please provide the output of the runtime execution
Have a look at examples C and D of https://blogs.oracle.com/optimizer/post/how-to-generate-a-useful-sql-execution-plan

2/ Would you have this parameter set at the database level?
_optimizer_cbqt_or_expansion=off

If so, could you please by setting it to on at the session level and provide again the output as in 1/

ALTER SESSION SET "_optimizer_cbqt_or_expansion"=on;

Thanks

7

@orm_s please provide the details that @loiclefevre requested. He is an Oracle employee and can give deeper insights into what is happening. Chances are, your database is misconfigured and there is an easy fix. The info is interesting nonetheless, because if there is an optimizer bug, this will help Oracle engineers to fix the problem.

1 Like
8

Thanks for the help @beikov and @loiclefevre :grin:

Could you please let me know what the _optimizer_cbqt_or_expansion property would do and how it might help in our particular case? I will get this done via my DB team who are a little nervy about fiddling with DB properties, which is kinda understandable as this is a Prod DB.

We actually have official Oracle support as well, so if there’s something that can directly be queried of them, I could try that as well.

Thanks!

9

Hi @orm_s,
_optimizer_cbqt_or_expansion is meant to control the optimizer

ON is the default and will enable the cost-based OR transformation. This might not help and make both plans worse (!), but it’s where we’d start. If you are going to test anything, you must ensure your optimizer statistics are up-to-date and that you have flushed out old cursors (e.g. no old execution plan remains in database memory).

Hence, what is the current value?

Again, the ROWNUM vs. FETCH FIRST issue was solved in 19c, so what we believe happened is another execution plan generation hiccup that impacts the use of INDEX SCAN.

Thanks

10

Hi @loiclefevre

Will get in touch with my team and update on this as soon as I can.

Thanks again for the help :smiley:

11

Just an update - I am still waiting on inputs on this from my DB Team, will let you guys know as soon as I can :sweat_smile:

In the meantime, could you guys (esp @beikov) help with the reason why Hibernate decided to use the fetch-first clause as the default? Any pointers on this will be helpful in understanding this better over on our side.

12

It is the SQL standard syntax which is also easier for you to read, so obviously we prefer that over complicated query nesting with rownum column filtering.

2 Likes
13

While I get updates on the actual queries/parameter changes from my DB team, we asked the team members from our official Oracle support team for their inputs on this, and we were told that:

“Most of the time differences in performance of both of them(first N rows vs rownum) and are non-noticeable (though rownum is comparatively faster if we look closely), which gives an advantage to the developers writing the SQL using First N rows.”

and also that:

“First n rows” is good for syntax purpose. Rownum method is better in terms of performance as it does not use analytic window function that First n rows use."

Can anyone confirm this?

14

I am happy to change the Hibernate ORM code to use rownum based pagination if Oracle engineers confirm that it is the way to go, but so far, I was told that the fetch first syntax is the proper solution. AFAIU, there might be slight differences between the two approaches depending on statistics etc., but those should be negligible.
Please let us know once you figured out with the Oracle support what issue you were running into.

15

Dear @loiclefevre

I have the same issue on Oracle 19c having fetch first is slower than rownum filter.
I did capture the runtime execution of both queries and here is the result :

The run exection for rownum filter query :

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dquut4kms4h0j, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ t.c_airline,C_CITY_NAME,C_USERNAME
from     (select         c1_0.c_airline,         c2_0.C_CITY_NAME,
   p1_0.C_USERNAME,         concat(concat(p1_0.c_first_name, ' '),
p1_0.c_last_name),         concat(concat(p1_0.c_persian_first_name, '
'), p1_0.c_persian_last_name)     from         t_contract c1_0     left
join         t_city c2_0             on c2_0.id=c1_0.f_city     left
join         t_profile p1_0             on p1_0.id=c1_0.f_profile
where         c1_0.f_agency=11         and c1_0.b_last_version=1
order by         c1_0.id desc     ) t where ROWNUM <= 10

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1633855969

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |      1 |        |       |    32 (100)|          |     10 |00:00:00.01 |      53 |
|*  1 |  COUNT STOPKEY                  |             |      1 |        |       |            |          |     10 |00:00:00.01 |      53 |
|   2 |   VIEW                          |             |      1 |     11 | 16896 |    32   (0)| 00:00:01 |     10 |00:00:00.01 |      53 |
|   3 |    NESTED LOOPS OUTER           |             |      1 |     11 |  1430 |    32   (0)| 00:00:01 |     10 |00:00:00.01 |      53 |
|   4 |     NESTED LOOPS OUTER          |             |      1 |     11 |   561 |    21   (0)| 00:00:01 |     10 |00:00:00.01 |      34 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  5 |      TABLE ACCESS BY INDEX ROWID| T_CONTRACT  |      1 |  32330 |  1073K|    10   (0)| 00:00:01 |     10 |00:00:00.01 |      12 |
|   6 |       INDEX FULL SCAN DESCENDING| SYS_C007650 |      1 |     12 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T_CITY      |     10 |      1 |    17 |     1   (0)| 00:00:01 |     10 |00:00:00.01 |      22 |
|*  8 |       INDEX UNIQUE SCAN         | SYS_C007634 |     10 |      1 |       |     0   (0)|          |     10 |00:00:00.01 |      12 |
|   9 |     TABLE ACCESS BY INDEX ROWID | T_PROFILE   |     10 |      1 |    79 |     1   (0)| 00:00:01 |     10 |00:00:00.01 |      19 |
|* 10 |      INDEX UNIQUE SCAN          | SYS_C007846 |     10 |      1 |       |     0   (0)|          |     10 |00:00:00.01 |       9 |
-----------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - SEL$1
   2 - SEL$49DCB01B / T@SEL$1
   3 - SEL$49DCB01B
   5 - SEL$49DCB01B / C1_0@SEL$2
   6 - SEL$49DCB01B / C1_0@SEL$2
   7 - SEL$49DCB01B / C2_0@SEL$2
   8 - SEL$49DCB01B / C2_0@SEL$2
   9 - SEL$49DCB01B / P1_0@SEL$3
  10 - SEL$49DCB01B / P1_0@SEL$3

Outline Data

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$49DCB01B")
      MERGE(@"SEL$1BEB249F" >"SEL$D2E4DCB5")
      OUTLINE_LEAF(@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$D2E4DCB5")
      ANSI_REARCH(@"SEL$4")
      OUTLINE(@"SEL$1BEB249F")
      MERGE(@"SEL$1A566D0B" >"SEL$791DB105")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$791DB105")
      ANSI_REARCH(@"SEL$F52A8B21")
      OUTLINE(@"SEL$1A566D0B")
      ANSI_REARCH(@"SEL$2")
      OUTLINE(@"SEL$F52A8B21")
      ANSI_REARCH(@"SEL$3")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$1" "T"@"SEL$1")
      INDEX_DESC(@"SEL$49DCB01B" "C1_0"@"SEL$2" ("T_CONTRACT"."ID"))
      INDEX_RS_ASC(@"SEL$49DCB01B" "C2_0"@"SEL$2" ("T_CITY"."ID"))
      INDEX_RS_ASC(@"SEL$49DCB01B" "P1_0"@"SEL$3" ("T_PROFILE"."ID"))
      LEADING(@"SEL$49DCB01B" "C1_0"@"SEL$2" "C2_0"@"SEL$2" "P1_0"@"SEL$3")
      USE_NL(@"SEL$49DCB01B" "C2_0"@"SEL$2")
      USE_NL(@"SEL$49DCB01B" "P1_0"@"SEL$3")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   5 - filter(("C1_0"."F_AGENCY"=11 AND "C1_0"."B_LAST_VERSION"=1))
   8 - access("C2_0"."ID"="C1_0"."F_CITY")
  10 - access("P1_0"."ID"="C1_0"."F_PROFILE")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - "T"."C_AIRLINE"[VARCHAR2,1020], "C_CITY_NAME"[VARCHAR2,1020], "C_USERNAME"[VARCHAR2,1020]
   2 - "T"."C_AIRLINE"[VARCHAR2,1020], "C_CITY_NAME"[VARCHAR2,1020], "C_USERNAME"[VARCHAR2,1020]
   3 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C2_0"."C_CITY_NAME"[VARCHAR2,1020], "P1_0"."C_USERNAME"[VARCHAR2,1020]
   4 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
   5 - "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22]
   6 - "C1_0".ROWID[ROWID,10]
   7 - "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
   8 - "C2_0".ROWID[ROWID,10]
   9 - "P1_0"."C_USERNAME"[VARCHAR2,1020]
  10 - "P1_0".ROWID[ROWID,10]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


100 rows selected.

The run exection for fetch first filter query :

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  buw5ac3cjqw34, child number 0
-------------------------------------
    select /*+ gather_plan_statistics */         c1_0.c_airline,
 c2_0.C_CITY_NAME,         p1_0.C_USERNAME,
concat(concat(p1_0.c_first_name, ' '), p1_0.c_last_name),
concat(concat(p1_0.c_persian_first_name, ' '),
p1_0.c_persian_last_name)     from         t_contract c1_0     left
join         t_city c2_0             on c2_0.id=c1_0.f_city     left
join         t_profile p1_0             on p1_0.id=c1_0.f_profile
where         c1_0.f_agency=11         and c1_0.b_last_version=1
order by         c1_0.id desc     offset         0 rows     fetch

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  first 10 rows only

Plan hash value: 1577094853

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |      1 |        |       |    32 (100)|          |     10 |00:00:00.01 |      53 |
|*  1 |  VIEW                           |             |      1 |     10 | 36100 |    32   (0)| 00:00:01 |     10 |00:00:00.01 |      53 |
|*  2 |   WINDOW NOSORT STOPKEY         |             |      1 |     11 |  1430 |    32   (0)| 00:00:01 |     10 |00:00:00.01 |      53 |
|   3 |    NESTED LOOPS OUTER           |             |      1 |     11 |  1430 |    32   (0)| 00:00:01 |     10 |00:00:00.01 |      53 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   4 |     NESTED LOOPS OUTER          |             |      1 |     11 |   561 |    21   (0)| 00:00:01 |     10 |00:00:00.01 |      34 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| T_CONTRACT  |      1 |  32330 |  1073K|    10   (0)| 00:00:01 |     10 |00:00:00.01 |      12 |
|   6 |       INDEX FULL SCAN DESCENDING| SYS_C007650 |      1 |     12 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|   7 |      TABLE ACCESS BY INDEX ROWID| T_CITY      |     10 |      1 |    17 |     1   (0)| 00:00:01 |     10 |00:00:00.01 |      22 |
|*  8 |       INDEX UNIQUE SCAN         | SYS_C007634 |     10 |      1 |       |     0   (0)|          |     10 |00:00:00.01 |      12 |
|   9 |     TABLE ACCESS BY INDEX ROWID | T_PROFILE   |     10 |      1 |    79 |     1   (0)| 00:00:01 |     10 |00:00:00.01 |      19 |
|* 10 |      INDEX UNIQUE SCAN          | SYS_C007846 |     10 |      1 |       |     0   (0)|          |     10 |00:00:00.01 |       9 |
-----------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - SEL$63F05DA2 / from$_subquery$_006@SEL$4
   2 - SEL$63F05DA2
   5 - SEL$63F05DA2 / C1_0@SEL$1
   6 - SEL$63F05DA2 / C1_0@SEL$1
   7 - SEL$63F05DA2 / C2_0@SEL$1
   8 - SEL$63F05DA2 / C2_0@SEL$1
   9 - SEL$63F05DA2 / P1_0@SEL$2
  10 - SEL$63F05DA2 / P1_0@SEL$2

Outline Data

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$63F05DA2")
      MERGE(@"SEL$66B15841" >"SEL$41465E65")
      OUTLINE_LEAF(@"SEL$4")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$41465E65")
      ANSI_REARCH(@"SEL$3")
      OUTLINE(@"SEL$66B15841")
      MERGE(@"SEL$8812AA4E" >"SEL$10B37F33")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$10B37F33")
      ANSI_REARCH(@"SEL$948754D7")
      OUTLINE(@"SEL$8812AA4E")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$948754D7")
      ANSI_REARCH(@"SEL$2")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$4" "from$_subquery$_006"@"SEL$4")
      INDEX_DESC(@"SEL$63F05DA2" "C1_0"@"SEL$1" ("T_CONTRACT"."ID"))
      INDEX_RS_ASC(@"SEL$63F05DA2" "C2_0"@"SEL$1" ("T_CITY"."ID"))
      INDEX_RS_ASC(@"SEL$63F05DA2" "P1_0"@"SEL$2" ("T_PROFILE"."ID"))
      LEADING(@"SEL$63F05DA2" "C1_0"@"SEL$1" "C2_0"@"SEL$1" "P1_0"@"SEL$2")
      USE_NL(@"SEL$63F05DA2" "C2_0"@"SEL$1")
      USE_NL(@"SEL$63F05DA2" "P1_0"@"SEL$2")
      END_OUTLINE_DATA
  */

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(("from$_subquery$_006"."rowlimit_$$_rownumber"<=10 AND "from$_subquery$_006"."rowlimit_$$_rownumber">0))
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("C1_0"."ID") DESC )<=10)
   5 - filter(("C1_0"."F_AGENCY"=11 AND "C1_0"."B_LAST_VERSION"=1))
   8 - access("C2_0"."ID"="C1_0"."F_CITY")
  10 - access("P1_0"."ID"="C1_0"."F_PROFILE")

Column Projection Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------

   1 - "from$_subquery$_006"."C_AIRLINE"[VARCHAR2,1020], "from$_subquery$_006"."C_CITY_NAME"[VARCHAR2,1020],
       "from$_subquery$_006"."C_USERNAME"[VARCHAR2,1020], "from$_subquery$_006"."CONCAT(CONCAT(P1_0.C_FIRST_NAME,''),P1_0.C_LAST_NAME)"[
       VARCHAR2,2044], "from$_subquery$_006"."CONCAT(CONCAT(P1_0.C_PERSIAN_FIRST_NAME,''),P1_0.C_PERSIAN_LAST_NAME)"[VARCHAR2,2044],
       "from$_subquery$_006"."rowlimit_$$_rownumber"[NUMBER,22]
   2 - (#keys=1) "C1_0"."ID"[NUMBER,22], "C1_0".ROWID[ROWID,10], "P1_0"."C_USERNAME"[VARCHAR2,1020],
       "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22], "C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22],
       "C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020],
       "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22], "P1_0"."C_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_LAST_NAME"[VARCHAR2,1020],
       "P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020], ROW_NUMBER() OVER ( ORDER BY

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       INTERNAL_FUNCTION("C1_0"."ID") DESC )[22]
   3 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
       "C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10],
       "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020], "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22],
       "P1_0"."C_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020],
       "P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_USERNAME"[VARCHAR2,1020]
   4 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
       "C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22], "C2_0".ROWID[ROWID,10],
       "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
   5 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22], "C1_0"."C_AIRLINE"[VARCHAR2,1020], "C1_0"."B_LAST_VERSION"[NUMBER,22],
       "C1_0"."F_AGENCY"[NUMBER,22], "C1_0"."F_CITY"[NUMBER,22], "C1_0"."F_PROFILE"[NUMBER,22]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   6 - "C1_0".ROWID[ROWID,10], "C1_0"."ID"[NUMBER,22]
   7 - "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22], "C2_0"."C_CITY_NAME"[VARCHAR2,1020]
   8 - "C2_0".ROWID[ROWID,10], "C2_0"."ID"[NUMBER,22]
   9 - "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22], "P1_0"."C_FIRST_NAME"[VARCHAR2,1020],
       "P1_0"."C_LAST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_FIRST_NAME"[VARCHAR2,1020], "P1_0"."C_PERSIAN_LAST_NAME"[VARCHAR2,1020],
       "P1_0"."C_USERNAME"[VARCHAR2,1020]
  10 - "P1_0".ROWID[ROWID,10], "P1_0"."ID"[NUMBER,22]


118 rows selected.

The _optimizer_cbqt_or_expansion was on in the session as you suggested.

_Hamid

1 Like