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: [EMAIL PROTECTED]
__________________________________________________________________
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hitchman, Peter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
