RE: TBLORIDXPARTNUM - anyone know this operator?

From: Herald ten Dam <>
Date: Wed, 9 Dec 2009 11:05:55 +0100
Message-ID: <BACE06B587FB3C47AC31296417C11434016C13E64A82_at_thnms004.TheHumanNetwork.local>


there is the function TBL$OR$IDX$PART$NUM. The TBL$OR$IDX$PART$NUM() function gives you the appropriate partition number for a table value or set of values. It's an undocumented; Metalink gives some documents (almost bugs). Maybe the variant without $-sign is a synonym.

Herald ten Dam

Van: [] namens Martin Bach [] Verzonden: woensdag 9 december 2009 10:22 Aan: oracle-l
Onderwerp: TBLORIDXPARTNUM - anyone know this operator?

Hi listers,

I am trying to reproduce a problem most likely related to statistics gathering which happened this night. The alert for long running sessions fired after this statement crossed the threshold:

SELECT MIN(minbkt),

   substrb(dump(MIN(val),16,0,32),1,120) minval,    substrb(dump(MAX(val),16,0,32),1,120) maxval,

   SUM(rep) sumrep,
   SUM(repsq) sumrepsq,
   MAX(rep) maxrep,

   COUNT(*) bktndv,
     WHEN rep=1
     THEN 1
     ELSE 0

   END) unqrep
   (SELECT val,
     MIN(bkt) minbkt,
     MAX(bkt) maxbkt,
     COUNT(val) rep,
     COUNT(val)*COUNT(val) repsq
       /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */
       dateCol val,
       ntile(254) over (order by dateCol) bkt
     FROM owner.table t
     WHERE TBLORIDXPARTNUM(owner.table,0,4,0,ROWID) = :objn
     AND dateCol IS NOT NULL

   GROUP BY val
GROUP BY maxbkt
ORDER BY maxbkt

Trying to execute this statement fails:

ERROR at line 27:
ORA-00904: "TBLORIDXPARTNUM": invalid identifier

Metalink/Google drew blanks for TBLORIDXPARTNUM, and it's not in dba_objects either. Tried to execute as sys and table owner but to the same result.

BTW the table is range partitioned on dateCol with an unpartitioned primary key and a couple of additional local indexes. Oracle RAC on 64bit RHEL 5.3.

So the question is: does anyone know this operator and how to use it?

Any help appreciated.


Martin Bach
OCM 10g
-- Received on Wed Dec 09 2009 - 04:05:55 CST

Original text of this message