TBLORIDXPARTNUM - anyone know this operator?

From: Martin Bach <development_at_the-playground.de>
Date: Wed, 09 Dec 2009 09:22:00 +0000
Message-ID: <4B1F6C38.8010903_at_the-playground.de>

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 Received on Wed Dec 09 2009 - 03:22:00 CST