RE: TBLORIDXPARTNUM - anyone know this operator?

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

Hi,

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: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] namens Martin Bach [development_at_the-playground.de] 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),

   maxbkt,
   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,
   SUM(
   CASE

     WHEN rep=1
     THEN 1
     ELSE 0

   END) unqrep
FROM
   (SELECT val,

     MIN(bkt) minbkt,
     MAX(bkt) maxbkt,
     COUNT(val) rep,
     COUNT(val)*COUNT(val) repsq
   FROM
     (SELECT
       /*+ 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 10.2.0.4.1 RAC on 64bit RHEL 5.3.

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

Any help appreciated.

Martin
--

Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com
--

http://www.freelists.org/webpage/oracle-l-- http://www.freelists.org/webpage/oracle-l Received on Wed Dec 09 2009 - 04:05:55 CST