Dbms_pclxutil package for “Intra” and “Inter” partition wise parallelism for local indexes

rajesh@ORA11G> set feedback off

rajesh@ORA11G> drop table t purge;

rajesh@ORA11G> create table t

  2  partition by hash(object_id)

  3  ( partition p1 ,

  4    partition p2,

  5    partition p3,

  6    partition p4 )

  7  parallel 4

  8  as

  9  select * from big_table;

rajesh@ORA11G> alter table t noparallel;

rajesh@ORA11G> create index t_idx on t(owner,object_type,object_name) LOCAL unusable;

rajesh@ORA11G> set feedback on

rajesh@ORA11G> column partition_name format a10

rajesh@ORA11G> select partition_name,partition_position,status

  2  from user_ind_partitions

  3  where index_name  ='T_IDX';

PARTITION_ PARTITION_POSITION STATUS

---------- ------------------ --------

P1                          1 UNUSABLE

P2                          2 UNUSABLE

P3                          3 UNUSABLE

P4                          4 UNUSABLE

4 rows selected.

We have local index defined in Table ‘T’ all in unusable status.

rajesh@ORA11G> show parameter job_queue

NAME                                          TYPE        VALUE

--------------------------------------------- ----------- -----------------

job_queue_processes                           integer     1000

We have job_queue_processes set to 1000 in database.  Here is an option rebuild all the local indexes concurrently in database using dbms_pclxutil.

rajesh@ORA11G> exec dbms_pclxutil.build_part_index(3,4,'T','T_IDX');

INFO: Job #83 created for partition P1 with 4 slaves

INFO: Job #84 created for partition P2 with 4 slaves

INFO: Job #85 created for partition P3 with 4 slaves

INFO: Job #86 created for partition P4 with 4 slaves

PL/SQL procedure successfully completed.

We requested to go with three jobs concurrently, which each job spans four parallel threads to rebuild all unusable local indexes T_IDX on table T.

While that above scripts run in database, we can monitor the concurrent jobs and parallel slaves from different session.

rajesh@ORA11G> select * from dba_jobs_running order by sid;

       SID        JOB   FAILURES LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC   INSTANCE

---------- ---------- ---------- ----------- -------- ----------- -------- ----------

         9         84                                 13-JAN-2016 09:48:35          0

        73         85                                 13-JAN-2016 09:48:35          0

       206         83                                 13-JAN-2016 09:48:35          0

3 rows selected.

rajesh@ORA11G>

rajesh@ORA11G> select qcsid,count(*) as cnt0,

  2     count(distinct sid) cnt1,

  3     count(distinct server_set) cnt2,

  4     count(distinct server#) cnt3

  5  from v$px_session

  6  where qcsid in (select sid from dba_jobs_running)

  7  group by qcsid

  8  order by qcsid;

     QCSID       CNT0       CNT1       CNT2       CNT3

---------- ---------- ---------- ---------- ----------

         9          9          9          2          4

        73          9          9          2          4

       206          9          9          2          4

3 rows selected.

Each job has 2 parallel server sets(operating in producer and consumer model) and each server set has 4 px_servers, so in total each job has 2 PX_server_sets * 4 Px_servers_per_server_set + 1 QC = 9 sid's per job.

Looking into the v$sql the database has internally submitted multiple jobs for each partition.

rajesh@ORA11G> select sql_text

  2  from v$sql

  3  where upper(sql_text) like '%T_IDX%'

  4  and upper(sql_text) like 'DECLARE%' ;

SQL_TEXT

-------------------------------------------------------------------------------------------------------------------------------------------------------

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P4" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P3" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P3" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P2" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P2" parallel (degree 4)'); END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P1" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T

_IDX" rebuild partition "P4" parallel (degree 4)'); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN dbms_utility.exec_ddl_statement('alter index "RAJESH"."T_ID

X" rebuild partition "P1" parallel (degree 4)'); END;

8 rows selected.

rajesh@ORA11G> 

Amazing Optimization to get distinct and TopN values from index - Part III

This will be extension of previous two posts (link1 and link2), in case if you have not gone through that, please read that before to proceed here.

In case not interested in using hints index_desc, we can utilize a bit of plsql here, to be index friendly.

 

rajesh@ORA11G> create or replace function foo(p_owner varchar2)

  2  return sys.odcivarchar2list

  3  as

  4     l_data sys.odcivarchar2list ;

  5  begin

  6     select to_char(object_id,'fm000000')||object_name

  7             bulk collect into l_data

  8     from (

  9     select object_id,object_name,

 10             row_number() over(order by object_id desc) rn

 11     from t

 12     where owner = p_owner

 13              )

 14     where rn <= 5 ;

 15     return l_data;

 16  end;

 17  /

 

Function created.

 

A Table function to provide top five entries from table ‘T’ for a given input.

 

rajesh@ORA11G> with datas(x) as

  2  ( select min(owner) from t

  3    union all

  4    select ( select min(t2.owner) from t t2

  5                     where t2.owner > t1.x )

  6    from datas t1

  7    where x is not null )

  8  select * from datas

  9  where x is not null ;

 

X

------------------------------

APEX_030200

APPQOSSYS

. . . . .

. . . . .

. . . . .

WMSYS

XDB

 

31 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 946542369

 

--------------------------------------------------------------------------------

| Id  | Operation                                 | Name  | Rows  | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                          |       |     2 |     4   (0)|

|*  1 |  VIEW                                     |       |     2 |     4   (0)|

|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |       |            |

|   3 |    SORT AGGREGATE                         |       |     1 |            |

|   4 |     INDEX FULL SCAN (MIN/MAX)             | T_IDX |     1 |     2   (0)|

|   5 |    SORT AGGREGATE                         |       |     1 |            |

|   6 |     FIRST ROW                             |       |     1 |     2   (0)|

|*  7 |      INDEX RANGE SCAN (MIN/MAX)           | T_IDX |     1 |     2   (0)|

|*  8 |    RECURSIVE WITH PUMP                    |       |       |            |

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("X" IS NOT NULL)

   7 - access("T2"."OWNER">:B1)

   8 - filter("X" IS NOT NULL)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         35  consistent gets

          0  physical reads

          0  redo size

        935  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         33  sorts (memory)

          0  sorts (disk)

         31  rows processed

 

rajesh@ORA11G> set autotrace off

rajesh@ORA11G>

 

So given 31 distinct keys available in the owner column, invoking plsql table function once for each owner will produced 31 recursive calls in total, we can see that clearly from Autotrace statistics section below.

 

rajesh@ORA11G> set autotrace traceonly explain statistics

rajesh@ORA11G>

rajesh@ORA11G> with datas(x) as

  2  ( select min(owner) from t

  3    union all

  4    select ( select min(t2.owner) from t t2

  5                     where t2.owner > t1.x )

  6    from datas t1

  7    where x is not null )

  8  select /*+ cardinality(d2,5) */ d1.x ,

  9     to_number(substr( column_value ,1,6)) as object_id ,

 10     substr(column_value,7) as object_name

 11  from datas d1 , table(foo(d1.x)) d2

 12  where d1.x is not null ;

 

148 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 93707171

 

---------------------------------------------------------------------------------

| Id  | Operation                                  | Name  | Rows  | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                           |       |    10 |   106   (1)|

|   1 |  NESTED LOOPS                              |       |    10 |   106   (1)|

|*  2 |   VIEW                                     |       |     2 |     4   (0)|

|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |       |            |

|   4 |     SORT AGGREGATE                         |       |     1 |            |

|   5 |      INDEX FULL SCAN (MIN/MAX)             | T_IDX |     1 |     2   (0)|

|   6 |     SORT AGGREGATE                         |       |     1 |            |

|   7 |      FIRST ROW                             |       |     1 |     2   (0)|

|*  8 |       INDEX RANGE SCAN (MIN/MAX)           | T_IDX |     1 |     2   (0)|

|*  9 |     RECURSIVE WITH PUMP                    |       |       |            |

|  10 |   COLLECTION ITERATOR PICKLER FETCH        | FOO   |     5 |    51   (0)|

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("D1"."X" IS NOT NULL)

   8 - access("T2"."OWNER">:B1)

   9 - filter("X" IS NOT NULL)

 

 

Statistics

----------------------------------------------------------

         31  recursive calls

          0  db block gets

        144  consistent gets

          0  physical reads

          0  redo size

       4722  bytes sent via SQL*Net to client

        499  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         33  sorts (memory)

          0  sorts (disk)

        148  rows processed

 

rajesh@ORA11G>

rajesh@ORA11G> set autotrace off

 

And Tkprof output shows that sql invoked from plsql table function remains index friendly.

 

SELECT TO_CHAR(OBJECT_ID,'fm000000')||OBJECT_NAME

FROM

 ( SELECT OBJECT_ID,OBJECT_NAME, ROW_NUMBER() OVER(ORDER BY OBJECT_ID DESC)

  RN FROM T WHERE OWNER = :B1 ) WHERE RN <= 5

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute     31      0.01       0.00          0          0          0           0

Fetch       31      0.00       0.00          0        109          0         148

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total       63      0.01       0.00          0        109          0         148

 

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 90     (recursive depth: 1)

Number of plan statistics captured: 1

 

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         5          5          5  VIEW  (cr=3 pr=0 pw=0 time=38 us cost=65 size=85742 card=1994)

         5          5          5   WINDOW NOSORT STOPKEY (cr=3 pr=0 pw=0 time=31 us cost=65 size=55832 card=1994)

         6          6          6    TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=16 us cost=65 size=55832 card=1994)

         6          6          6     INDEX RANGE SCAN DESCENDING T_IDX (cr=2 pr=0 pw=0 time=17 us cost=8 size=0 card=1994)(object id 93174)

Amazing Optimization to get distinct and TopN values from index - Part II

This will be extension of previous post, in case if you have not gone through that, please read that before to proceed here.

Say we need to get TopN entries for each owner. That is for each owner, I need to get the top-five maximum object_id’s along with its object_name.

 

The standard query using Analytics would be this way.

 

rajesh@ORA11G> select owner,object_id,object_name

  2  from (

  3  select object_id,owner,object_name,

  4    row_number() over(partition by owner order by object_id desc) rn

  5  from t

  6       )

  7  where rn <= 5

  8  order by owner,object_id ;

 

148 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3060612387

 

-------------------------------------------------------------------------------

| Id  | Operation                 | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      | 84718 |  4963K|       |  3185   (3)|

|   1 |  SORT ORDER BY            |      | 84718 |  4963K|  6008K|  3185   (3)|

|*  2 |   VIEW                    |      | 84718 |  4963K|       |  1536   (3)|

|*  3 |    WINDOW SORT PUSHED RANK|      | 84718 |  2316K|  3336K|  1536   (3)|

|   4 |     TABLE ACCESS FULL     | T    | 84718 |  2316K|       |   632   (2)|

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("RN"<=5)

   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "OWNER" ORDER BY

              INTERNAL_FUNCTION("OBJECT_ID") DESC )<=5)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1214  consistent gets

          0  physical reads

          0  redo size

       4696  bytes sent via SQL*Net to client

        500  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

        148  rows processed

 

rajesh@ORA11G> set autotrace off

 

The actual plan from library cache would be this

 

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  ca0u3qvm5yujf, child number 1

-------------------------------------

select owner,object_id,object_name from ( select

object_id,owner,object_name,   row_number() over(partition by owner

order by object_id desc) rn from t      ) where rn <= 5 order by

owner,object_id

 

Plan hash value: 3060612387

 

--------------------------------------------------------------------------------------------

| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |      |      1 |        |    148 |00:00:00.15 |    1214 |

|   1 |  SORT ORDER BY            |      |      1 |  84718 |    148 |00:00:00.15 |    1214 |

|*  2 |   VIEW                    |      |      1 |  84718 |    148 |00:00:00.15 |    1214 |

|*  3 |    WINDOW SORT PUSHED RANK|      |      1 |  84718 |    176 |00:00:00.15 |    1214 |

|   4 |     TABLE ACCESS FULL     | T    |      1 |  84718 |  84718 |00:00:00.02 |    1214 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("RN"<=5)

   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "OWNER" ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=5)

 

 

25 rows selected.

 

rajesh@ORA11G>

 

Here is the explanation of the process

 

• Full scan the table “T”
• Logically partition them into subsets based on the values from OWNER column.
• Within each partition, sort the rows based on the object_id in Descending order, till we find five values per partition.
• Finally sort the result based on “order by” clause of sql statement, to produce the result set.

 

 

With the new modified approach it would be like this.

 

rajesh@ORA11G> with datas(x) as

  2  (

  3     select min(owner) from t

  4     union all

  5     select (select min(owner) from t t2

  6                             where t2.owner > datas.x )

  7     from datas

  8     where x is not null )

  9  select x, to_number(substr(column_value,1,6)) as object_id,

 10        substr(column_value,7) as object_name

 11  from datas d1,

 12     table(cast( multiset( select /*+ index_desc(t,t_idx) */

 13          to_char(object_id,'fm000000')||object_name txt

 14     from t

 15     where t.owner = d1.x

 16     and rownum <=5 ) as sys.odcivarchar2list))

 17  where x is not null

 18  order by x,object_id ;

 

148 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 254218082

 

---------------------------------------------------------------------------------

| Id  | Operation                                   | Name  | Rows  |Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                            |       | 16336 |  240   (3)|

|   1 |  SORT ORDER BY                              |       | 16336 |  240   (3)|

|   2 |   NESTED LOOPS                              |       | 16336 |  107   (2)|

|*  3 |    VIEW                                     |       |     2 |    4   (0)|

|   4 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |       |           |

|   5 |      SORT AGGREGATE                         |       |     1 |           |

|   6 |       INDEX FULL SCAN (MIN/MAX)             | T_IDX |     1 |    2   (0)|

|   7 |      SORT AGGREGATE                         |       |     1 |           |

|   8 |       FIRST ROW                             |       |     1 |    2   (0)|

|*  9 |        INDEX RANGE SCAN (MIN/MAX)           | T_IDX |     1 |    2   (0)|

|* 10 |      RECURSIVE WITH PUMP                    |       |       |           |

|  11 |    COLLECTION ITERATOR SUBQUERY FETCH       |       |  8168 |   51   (0)|

|* 12 |     COUNT STOPKEY                           |       |       |           |

|  13 |      TABLE ACCESS BY INDEX ROWID            | T     |  2733 |   87   (2)|

|* 14 |       INDEX RANGE SCAN DESCENDING           | T_IDX |  2733 |   10   (0)|

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("X" IS NOT NULL)

   9 - access("T2"."OWNER">:B1)

  10 - filter("X" IS NOT NULL)

  12 - filter(ROWNUM<=5)

  14 - access("T"."OWNER"=:B1)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        142  consistent gets

          0  physical reads

          0  redo size

       4692  bytes sent via SQL*Net to client

        500  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

         34  sorts (memory)

          0  sorts (disk)

        148  rows processed

 

rajesh@ORA11G> set autotrace off

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

 

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7axgncuqtg1r2, child number 0

-------------------------------------

with datas(x) as (  select min(owner) from t  union all  select (select

min(owner) from t t2     where t2.owner > datas.x )  from datas  where

x is not null ) select x, to_number(substr(column_value,1,6)) as

object_id,       substr(column_value,7) as object_name from datas d1,

 table(cast( multiset( select /*+ index_desc(t,t_idx) */

to_char(object_id,'fm000000')||object_name txt    from t    where

t.owner = d1.x    and rownum <=5 ) as sys.odcivarchar2list)) where x is

not null order by x,object_id

 

Plan hash value: 254218082

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                                   | Name  | Starts | E-Rows | A-Rows |Buffers |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                            |       |      1 |        |    148 |    142 |

|   1 |  SORT ORDER BY                              |       |      1 |  16336 |    148 |    142 |

|   2 |   NESTED LOOPS                              |       |      1 |  16336 |    148 |    142 |

|*  3 |    VIEW                                     |       |      1 |      2 |     31 |     35 |

|   4 |     UNION ALL (RECURSIVE WITH) BREADTH FIRST|       |      1 |        |     32 |     35 |

|   5 |      SORT AGGREGATE                         |       |      1 |      1 |      1 |      2 |

|   6 |       INDEX FULL SCAN (MIN/MAX)             | T_IDX |      1 |      1 |      1 |      2 |

|   7 |      SORT AGGREGATE                         |       |     31 |      1 |     31 |     33 |

|   8 |       FIRST ROW                             |       |     31 |      1 |     30 |     33 |

|*  9 |        INDEX RANGE SCAN (MIN/MAX)           | T_IDX |     31 |      1 |     30 |     33 |

|  10 |      RECURSIVE WITH PUMP                    |       |     32 |        |     31 |      0 |

|  11 |    COLLECTION ITERATOR SUBQUERY FETCH       |       |     31 |   8168 |    148 |    107 |

|* 12 |     COUNT STOPKEY                           |       |     31 |        |    148 |    107 |

|  13 |      TABLE ACCESS BY INDEX ROWID            | T     |     31 |   2733 |    148 |    107 |

|* 14 |       INDEX RANGE SCAN DESCENDING           | T_IDX |     31 |   2733 |    148 |     63 |

-------------------------------------------------------------------------------------------------   

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("X" IS NOT NULL)

   9 - access("T2"."OWNER">:B1)

  12 - filter(ROWNUM<=5)

  14 - access("T"."OWNER"=:B1)

 

 

41 rows selected.

 

rajesh@ORA11G>

 

The recursive “With” clause looks very promising compared to Analytics approach.

 

But looking into deep one might think that below portion of code “not” returns the Top five maximum object_ids, instead it returns the random five records per owner.

 

  12     table(cast( multiset( select /*+ index_desc(t,t_idx) */

 13          to_char(object_id,'fm000000')||object_name txt

 14     from t

 15     where t.owner = d1.x

 16     and rownum <=5 )

 

The query only selects values from the T_IDX index (owner,object_id and rowid). So Oracle can do an index range scan descending. Walking along the index like this will return the values in (descending) index order. Oracle will then stop when it has read 5 of these and then access the table using those rowid’s to get “object_name” values.

 

If Oracle chooses a FTS, then yes, the results will be random. To guard against this I have included the index_desc hint:

 

rajesh@ORA11G>

rajesh@ORA11G> select /*+ full(t) */ owner,object_id

  2  from t

  3  where owner ='SYS'

  4  and rownum <=5 ;

 

OWNER       OBJECT_ID

---------- ----------

SYS                20

SYS                46

SYS                28

SYS                15

SYS                29

 

5 rows selected.

 

rajesh@ORA11G> select /*+ index_desc(t,t_idx) */ owner,object_id

  2  from t

  3  where owner ='SYS'

  4  and rownum <=5 ;

 

OWNER       OBJECT_ID

---------- ----------

SYS             92922

SYS             92921

SYS             92920

SYS             92730

SYS             92729

 

5 rows selected.

 

rajesh@ORA11G>

Values from the sql using index_desc hint matches exactly with analytics output.

 

rajesh@ORA11G>

rajesh@ORA11G> select owner,object_id

  2  from (

  3  select owner,object_id,

  4    row_number() over(order by object_id desc) rn

  5  from t

  6  where owner ='SYS'

  7       )

  8  where rn <= 5

  9  /

 

OWNER       OBJECT_ID

---------- ----------

SYS             92922

SYS             92921

SYS             92920

SYS             92730

SYS             92729

 

5 rows selected.

 

rajesh@ORA11G>