Oracle-L: Using an index defined on members of a user defined type

From: Hitchman, Peter <peter.hitchman_at_derwent.co.uk>
Date: Tue, 28 Oct 2003 09:09:28 -0800
Message-ID: <F001.005D4D19.20031028090928@fatcity.com>

Hi,
I am looking at Oracle schema for IBM's MQ Workflow and I have a table that has an index on members of a user defined type. This index is not getting used when it could help a delete statement. Using the 10053 trace I got this snippet in the trace:

SINGLE TABLE ACCESS PATH
No statistics type defined for function TIMESTAMP_WF No default selectivity defined for function TIMESTAMP_WF

Does anyone have any experience in setting up functions so that the database will use indexes against them?

In this case the table looks like this:

CREATE TABLE AUDIT_TRAIL
(

    CREATED             TIMESTAMP_WF        NOT NULL


  , EVENT               NUMBER(10,0)        NOT NULL

  , TEMPL_VALID_FROM    TIMESTAMP_WF

  , ACTIVITY_TYPE       NUMBER(10,0)

  , ACTIVITY_STATE      NUMBER(10,0)

  , ACTIVITY_RC         NUMBER(10,0)

  , CONTAINER_CONTENT   BLOB

  , PROCESS_NAME        VARCHAR2(63)        NOT NULL

  , PROCESS_ID          VARCHAR2(64)        NOT NULL

  , TOP_LVL_PROC_NAME   VARCHAR2(63)        NOT NULL

  , TOP_LVL_PROC_ID     VARCHAR2(64)        NOT NULL

  , PARENT_PROC_NAME    VARCHAR2(63)

  , PARENT_PROC_ID      VARCHAR2(64)

  , PROC_TEMPL_NAME     VARCHAR2(32)        NOT NULL

  , BLOCK_NAMES         VARCHAR2(254)

  , USER_NAME           VARCHAR2(32)

  , SECOND_USER_NAME    VARCHAR2(32)

  , ACTIVITY_NAME       VARCHAR2(32)

  , SECOND_ACT_NAME     VARCHAR2(32)

  , COMMAND_PARAMETERS  VARCHAR2(1024)

  , ASSOCIATED_OBJECT   VARCHAR2(64)

  , OBJECT_DESCRIPTION  VARCHAR2(254)

  , PROGRAM_NAME        VARCHAR2(32)

, EXTERNAL_CONTEXT VARCHAR2(254)

)
TABLESPACE ADTTRAIL; and the index is:

CREATE INDEX AT_CREATED_PROCID
ON AUDIT_TRAIL
(
CREATED.D, CREATED.S
, PROCESS_ID
) TABLESPACE ADTTRAIL; and here is the type

CREATE OR REPLACE TYPE TIMESTAMP_WF AS OBJECT( D DATE,
S NUMBER(6),
STATIC FUNCTION CONSTRUCT( str VARCHAR2 ) RETURN TIMESTAMP_WF, MEMBER FUNCTION AS_STRING RETURN VARCHAR2, MEMBER FUNCTION AS_DATE_STRING RETURN VARCHAR2, MEMBER FUNCTION ADD_SECONDS( sec INTEGER ) RETURN TIMESTAMP_WF, MEMBER FUNCTION SUB_TIMESTAMP_WF(other_Timestamp IN TIMESTAMP_WF) RETURN INTEGER
,
ORDER MEMBER FUNCTION COMPARE(other_Timestamp IN TIMESTAMP_WF) RETURN INTEGER
);
/

CREATE OR REPLACE TYPE BODY TIMESTAMP_WF AS STATIC FUNCTION CONSTRUCT( str VARCHAR2 ) RETURN TIMESTAMP_WF IS BEGIN

     IF str IS NULL THEN
        RETURN NULL;
     END IF;
     IF LENGTH( str ) >= 26  THEN
        RETURN TIMESTAMP_WF( TO_DATE( SUBSTR( str, 1,19 ),

'yyyy-mm-dd-hh24.mi.s
s' ),

                             TO_NUMBER( SUBSTR( str, 21, 6 ) ));
     END IF;
     IF LENGTH( str ) >= 19  THEN
        RETURN TIMESTAMP_WF( TO_DATE( SUBSTR( str, 1,19 ),

'yyyy-mm-dd-hh24.mi.s
s'), 0 );

     END IF;
     RETURN TIMESTAMP_WF( TO_DATE('ERROR'), 0);

  END;
  MEMBER FUNCTION AS_STRING RETURN VARCHAR2 IS   BEGIN
    RETURN
TO_CHAR(D,'yyyy-mm-dd-hh24.mi.ss')||'.'||SUBSTR(To_Char(S,'0999999'), 3);
  END;
  MEMBER FUNCTION AS_DATE_STRING RETURN VARCHAR2 IS   BEGIN
    RETURN TO_CHAR(D,'yyyy-mm-dd-hh24.mi.ss');   END;
  MEMBER FUNCTION ADD_SECONDS( sec INTEGER ) RETURN TIMESTAMP_WF IS   BEGIN
     RETURN TIMESTAMP_WF( D + sec/86400, S );   END;
  MEMBER FUNCTION SUB_TIMESTAMP_WF(other_Timestamp IN TIMESTAMP_WF) RETURN INTEG
ER IS
  BEGIN
     RETURN ((D - other_TimeStamp.D)*86400);   END;
  ORDER MEMBER FUNCTION COMPARE(other_Timestamp IN TIMESTAMP_WF) RETURN INTEGER
IS
  BEGIN
    IF D > other_Timestamp.D THEN RETURN 1;     ELSIF D < other_Timestamp.D THEN RETURN -1;     END IF;
    RETURN S - other_Timestamp.S;
    END;
END;
/

Regards

Pete

The information contained in this email is confidential and intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. Thomson Scientific will accept no responsibility or liability in respect to this email other than to the addressee. If you have received this communication in error, please notify us immediately via email: ITHelpdesk_at_derwent.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hitchman, Peter
  INET: peter.hitchman_at_derwent.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Oct 28 2003 - 11:09:28 CST