Database Software

Hi All,

Having Oracle 10g 10.2.0.1, Solaris 10

Below query is performing very slow...for 25 k xml documents of about just 100kb.

SELECT HQ_FLAG ,
    REGSTR_FLAG ADDRESS_STAMP ,
    BUILDING ,
    street_address ,
    PO_BOX ,
    CITY ,
    STATE_REGION ,
    POSTAL_CODE ,
    country_code ,
    a.ORG_ID
FROM common.BID_XML_DATA a,
    xmltable( 'for $i in IntegrationBID/Organization   
return <Row OrgID="{$i/OrgID}">{ $i/OrgGeneralInformation/Address } </Row>' passing xml_data 
columns 
hq_flag varchar2(5) path 'Address/@HeadquartersFlag', 
regstr_flag varchar2(5) path 'Address/@RegisteredFlag', 
address_stamp varchar(10) path 'Address/@LastUpdated', 
building varchar(40) path 'Address/Building', 
street_address varchar(40) path 'Address/StreetAddress', 
po_box varchar(40) path 'Address/POBox', 
city varchar(40) path 'Address/City', 
state_region character(3) path 'Address/StateOrRegion', 
postal_code varchar(10) path 'Address/PostalCode', 
country_code character(3) path 'Address/Country/@Code', 
Org_ID NUMBER(19) path '@OrgID') b;

SQL> desc common.BID_XML_DATA
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 XML_DATA                                                                                     XMLTYPE
 BID_INSERT_STAMP                                                                             DATE
 RN                                                                                           NUMBER(5)
 ORG_ID                                                                                       NUMBER(19)

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |  8168 |   749K|    27   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN               |                        |  8168 |   749K|    27   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                 | BID_XML_DATA           |     1 |    92 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT                       |                        |  8168 | 16336 |    24   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
--------------------------------------------------------------------------------------------------------------

I was trying to create index on this table...but in vain..

SQL> CREATE INDEX common.XML_DATA_IDX ON common.BID_XML_TABLE (xml_data)
  2  INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATHS (INCLUDE (IntegrationBID/Organization/OrgID
  3    4                                                  IntegrationBID/Organization/OfficerInformation/Officer/ID
  5                                                  IntegrationBID/Organization/WebLinkInfo
  6  IntegrationBID/Organization/FilingSpecificInformation/FilingInformation/CompanyID
IntegrationBID/Organization/FilingSpecificInformation/FilingInformation/ContactInfo
  7    8        IntegrationBID/Organization/FilingSpecificInformation/FilingInformation/Advisors/TransferAgent/Name
  9        IntegrationBID/Organization/OfficerInformation/Officer/PersonInformation/Name
 10        IntegrationBID/Organization/OfficerInformation/Officer/OfficerContacts
 11        IntegrationBID/Organization/OfficerInformation/Officer/PersonInformation/EducationHistory/Education
 12        IntegrationBID/Organization/OfficerInformation/Officer/PositionInformation/TenureDates
 13        IntegrationBID/Organization/OfficerInformation/Officer/PositionInformation/Titles/Designation
 14        IntegrationBID/Organization/OfficerInformation/Officer/PositionInformation/CommitteeMemberships/Committee
      IntegrationBID/Organization/OfficerInformation/Officer/SalaryInformation/CompensationPeriod
 15   16                              )
 17                      NAMESPACE MAPPING
 18                           (xmlns="http://www.schemas.thomsonreuters.com/IntBID03")
 19                     )
 20               PATH TABLE      BID_XML_path_table
 21               PATH ID INDEX   BID_XML_pathid_ix
 22               ORDER KEY INDEX BID_XML_orderkey_ix
 23               ASYNC (SYNC ALWAYS) STALE (FALSE)
 24             ');
CREATE INDEX common.XML_DATA_IDX ON common.BID_XML_TABLE (xml_data)
*
ERROR at line 1:
ORA-29896: Length of PARAMETER string longer than 1000 characters


SQL> CREATE INDEX common.BID_XML_DATA_IDX ON common.BID_XML_DATA (xml_data)
  2  INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PATHS (INCLUDE (IntegrationBID/Organization/OrgID
  3    4                               IntegrationBID/Organization/OfficerInformation/Officer/ID
  5                               IntegrationBID/Organization/WebLinkInfo
  6                          IntegrationBID/Organization/FilingSpecificInformation/FilingInformation/CompanyID
                            )
  7    8                      NAMESPACE MAPPING
  9                           (xmlns="http://www.schemas.thomsonreuters.com/IntBID03")
                   )
 10   11               PATH TABLE      BID_XML_path_table
 12               PATH ID INDEX   BID_XML_pathid_ix
 13               ORDER KEY INDEX BID_XML_orderkey_ix
 14               ASYNC (SYNC ALWAYS) STALE (FALSE)
 15             ');
CREATE INDEX common.BID_XML_DATA_IDX ON common.BID_XML_DATA (xml_data)
*
ERROR at line 1:
ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-30955: internal event to enable value index creation

Could anyone help me in creating xmlindex....and also to tune this query.

-Yasser