I am posting this thread to help myself and hopefully others tie all the information togather. I spent last night reviewing the XML DB developers guide and have come away with more questions than answers. This is probably due to the fact that I could not find examples relating specifically to my situation. So here are some questions I have.
When should people consider using XML DB. I have been given the task of integrating an existing system with a 3rd party system. This 3rd party system will provide me information via Web Services. I am under the impression that this is an ideal situation for using XML DB, is this a correct assumption?
One reason why I came to this assumption is implementating a solution to get the data into our system by parsing the object returned from the WS took 1000 LOCs to develop and using XML DB it's around 30 LOCs.
When should I use structured/unstructured storage? I am more concerned with using the data once it's in our system. So I have decided to use structured storage in the hopes I can create indexs to speed up data access. I have read from several threads that I should set storeVaraayAsTable="true" and have tables auto generate during registration so I have done that. But the documentation says that don't do this if you need to use Oracle Text indexes, what are these and how do I know if I need to use these?
Here is a copy of my schema.
<?xml version="1.0" encoding="utf-8"?>
<s:schema xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified" xdb:storeVarrayAsTable="true">
<s:element name="Organization" type="Organization" xdb:defaultTable="ADT_ORG"/>
<s:element name="Person" type="Person" xdb:defaultTable="ADT_PERSON"/>
<s:complexType name="Organization" xdb:SQLType="Organization">
<s:sequence>
<s:element name="Name" type="s:string" nillable="true"/>
<s:element name="LongName" type="s:string" nillable="true"/>
<s:element name="Description" type="s:string" nillable="true"/>
<s:element name="FWANumber" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="OrganizationTypes" type="ArrayOfOrganizationType" minOccurs="0"/>
<s:element name="OrganizationSynonyms" type="ArrayOfOrganizationSynonym" minOccurs="0"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfOrganizationType" xdb:SQLType="ArrayOfOrganizationType">
<s:sequence>
<s:element name="OrganizationType" type="OrganizationType" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="OrganizationType" xdb:SQLType="OrganizationType">
<s:sequence>
<s:element name="OrganizationID" type="s:int" xdb:SQLType="VARCHAR2"/>
<s:element name="Type" type="s:string" nillable="true"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="SiteEstablishmentStatus" type="s:string" nillable="true"/>
<s:element name="SiteEstablishmentStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="IPFNumber" type="s:int"/>
<s:element name="DUNSNumber" type="s:int"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LegacyCodes" type="ArrayOfLegacyCode" minOccurs="0"/>
<s:element name="Addresses" type="ArrayOfAddress" minOccurs="0"/>
<s:element name="ContactMechanisms" type="ArrayOfContactMechanism" minOccurs="0"/>
<s:element name="Associations" type="ArrayOfAssociation" minOccurs="0"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfLegacyCode" xdb:SQLType="ArrayOfLegacyCode">
<s:sequence>
<s:element name="LegacyCode" type="LegacyCode" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="LegacyCode" xdb:SQLType="LegacyCode">
<s:sequence>
<s:element name="Code" type="s:string" nillable="true"/>
<s:element name="NetworkID" type="s:int" xdb:SQLType="VARCHAR2"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfAddress" xdb:SQLType="ArrayOfAddress">
<s:sequence>
<s:element name="Address" type="Address" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="Address" xdb:SQLType="Address">
<s:sequence>
<s:element name="StreetName1" type="s:string" nillable="true"/>
<s:element name="StreetName2" type="s:string" nillable="true"/>
<s:element name="StreetName3" type="s:string" nillable="true"/>
<s:element name="StreetName4" type="s:string" nillable="true"/>
<s:element name="CityName" type="s:string" nillable="true"/>
<s:element name="CityGUID" type="s:string" nillable="true"/>
<s:element name="CityCreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CityLastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CityRecordStatus" type="s:string" nillable="true"/>
<s:element name="CityRecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="StateName" type="s:string" nillable="true"/>
<s:element name="StateGUID" type="s:string" nillable="true"/>
<s:element name="StateCreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="StateLastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="StateRecordStatus" type="s:string" nillable="true"/>
<s:element name="StateRecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CountryName" type="s:string" nillable="true"/>
<s:element name="CountryCode" type="s:string" nillable="true"/>
<s:element name="CountryGUID" type="s:string" nillable="true"/>
<s:element name="CountryCreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CountryLastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CountryRecordStatus" type="s:string" nillable="true"/>
<s:element name="CountryRecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="ZipPostalCode" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="Type" type="s:string" nillable="true"/>
<s:element name="TypeOtherSpecify" type="s:string" nillable="true"/>
<s:element name="InternalOffice" type="s:string" nillable="true"/>
<s:element name="MailStopCode" type="s:string" nillable="true"/>
<s:element name="PreferredFlag" type="s:string" nillable="true"/>
<s:element name="ActiveFromDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfContactMechanism" xdb:SQLType="ArrayOfContactMechanism">
<s:sequence>
<s:element name="ContactMechanism" type="ContactMechanism" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="ContactMechanism" xdb:SQLType="ContactMechanism">
<s:sequence>
<s:element name="ContactType" type="s:string" nillable="true"/>
<s:element name="ContactTypeOtherSpecify" type="s:string" nillable="true"/>
<s:element name="ContactValue" type="s:string" nillable="true"/>
<s:element name="ContactAreaCode" type="s:string" nillable="true"/>
<s:element name="ContactCountryCallingCode" type="s:int"/>
<s:element name="ContactTollFreeFlag" type="s:string" nillable="true"/>
<s:element name="ContactGUID" type="s:string" nillable="true"/>
<s:element name="ContactCreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="ContactLastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="ContactRecordStatus" type="s:string" nillable="true"/>
<s:element name="ContactRecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="Role" type="s:string" nillable="true"/>
<s:element name="PhoneExtension" type="s:string" nillable="true"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="ActiveFromDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="ModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="PreferredFlag" type="s:string" nillable="true"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfAssociation" xdb:SQLType="ArrayOfAssociation">
<s:sequence>
<s:element name="Association" type="Association" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="Association" xdb:SQLType="Association">
<s:sequence>
<s:element name="Role" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="Addresses" type="ArrayOfAddress" minOccurs="0"/>
<s:element name="ContactMechanisms" type="ArrayOfContactMechanism" minOccurs="0"/>
<s:element name="Persons" type="ArrayOfPerson" minOccurs="0"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfPerson" xdb:SQLType="ArrayOfPerson">
<s:sequence>
<s:element name="Person" type="Person" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="Person" xdb:SQLType="Person">
<s:sequence>
<s:element name="PersonID" type="s:int"/>
<s:element name="Prefix" type="s:string" nillable="true"/>
<s:element name="FirstName" type="s:string" nillable="true"/>
<s:element name="MiddleName" type="s:string" nillable="true"/>
<s:element name="LastName" type="s:string" nillable="true"/>
<s:element name="Suffix" type="s:string" nillable="true"/>
<s:element name="PreferredName" type="s:string" nillable="true"/>
<s:element name="Gender" type="s:string" nillable="true"/>
<s:element name="PrimaryLanguage" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="PersonDegrees" type="ArrayOfPersonDegree" minOccurs="0"/>
<s:element name="PersonSpecialties" type="ArrayOfPersonSpecialty" minOccurs="0"/>
<s:element name="PersonTypes" type="ArrayOfPersonType" minOccurs="0"/>
<s:element name="Addresses" type="ArrayOfAddress" minOccurs="0"/>
<s:element name="ContactMechanisms" type="ArrayOfContactMechanism" minOccurs="0"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfPersonDegree" xdb:SQLType="ArrayOfPersonDegree">
<s:sequence>
<s:element name="PersonDegree" type="PersonDegree" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="PersonDegree" xdb:SQLType="PersonDegree">
<s:sequence>
<s:element name="Code" type="s:string" nillable="true"/>
<s:element name="Major" type="s:string" nillable="true"/>
<s:element name="ListingOrder" type="s:int"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfPersonSpecialty" xdb:SQLType="ArrayOfPersonSpecialty">
<s:sequence>
<s:element name="PersonSpecialty" type="PersonSpecialty" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="PersonSpecialty" xdb:SQLType="PersonSpecialty">
<s:sequence>
<s:element name="Name" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfPersonType" xdb:SQLType="ArrayOfPersonType">
<s:sequence>
<s:element name="PersonType" type="PersonType" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="PersonType" xdb:SQLType="PersonType">
<s:sequence>
<s:element name="Type" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="QCDoneStatus" type="s:string" nillable="true"/>
<s:element name="QCDoneStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
<s:complexType name="ArrayOfOrganizationSynonym" xdb:SQLType="ArrayOfOrganizationSynonym">
<s:sequence>
<s:element name="OrganizationSynonym" type="OrganizationSynonym" minOccurs="0" maxOccurs="unbounded"/>
</s:sequence>
</s:complexType>
<s:complexType name="OrganizationSynonym" xdb:SQLType="OrganizationSynonym">
<s:sequence>
<s:element name="Name" type="s:string" nillable="true"/>
<s:element name="GUID" type="s:string" nillable="true"/>
<s:element name="CreatedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="LastModifiedDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
<s:element name="RecordStatus" type="s:string" nillable="true"/>
<s:element name="RecordStatusDate" type="s:dateTime" xdb:SQLType="VARCHAR2"/>
</s:sequence>
</s:complexType>
</s:schema>
Again this is provided to from an outside vendor so I don't know how much flexability I will have as far as altering the structure.
For some reason I am unable to get queries to use indexs I create? Here is an example. I renamed the nested table that maps to the organization type to ORG_TYPE.
Example with no index created
SQL>
SQL> set timing on
SQL>
SQL> select
2 extractvalue(value(ot),'/OrganizationType/OrganizationID')
3 from adt_org o,
4 table(xmlsequence(extract(value(o),'/Organization/OrganizationTypes/OrganizationType'))) ot
5 where extractvalue(value(ot),'/OrganizationType/OrganizationID') = '30005';
EXTRACTVALUE(VALUE(OT),'/ORGANIZATIONTYPE/ORGANIZATIONID')
--------------------------------------------------------------------------------
30005
Elapsed: 00:00:02.88
SQL>
SQL> set timing off
SQL>
SQL> explain plan for
2 select
3 extractvalue(value(ot),'/OrganizationType/OrganizationID')
4 from adt_org o,
5 table(xmlsequence(extract(value(o),'/Organization/OrganizationTypes/OrganizationType'))) ot
6 where extractvalue(value(ot),'/OrganizationType/OrganizationID') = '30005';
Explained.
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30520 | 25 |
|* 1 | INDEX RANGE SCAN | SYS_IOT_TOP_65649 | 1 | 14043 | 2 |
|* 2 | INDEX RANGE SCAN | SYS_IOT_TOP_65654 | 1 | 76098 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_65659 | 1 | 44079 | 2 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_65664 | 1 | 24142 | |
|* 5 | FILTER | | | | |
| 6 | NESTED LOOPS | | 1 | 30520 | 23 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | TABLE ACCESS FULL | ADT_ORG | 12 | 3684 | 11 |
|* 8 | INDEX RANGE SCAN | SYS_IOT_TOP_65647 | 1 | 30213 | 1 |
|* 9 | INDEX RANGE SCAN | SYS_IOT_TOP_65649 | 1 | 14043 | 2 |
|* 10 | INDEX RANGE SCAN | SYS_IOT_TOP_65654 | 1 | 76098 | 3 |
|* 11 | INDEX RANGE SCAN | SYS_IOT_TOP_65659 | 1 | 44079 | 2 |
|* 12 | INDEX RANGE SCAN | SYS_IOT_TOP_65664 | 1 | 24142 | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("SYS_NTHE71m+WMph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
2 - access("SYS_NTHE71m+WNph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
3 - access("SYS_NTHE71m+WOph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
4 - access("SYS_ALIAS_3"."NESTED_TABLE_ID"=:B1)
5 - filter(EXTRACTVALUE(SYS_MAKEXML('1C4EF59BE459A61EE04011ACA05A2DD4',1
6827,SYS_ALIAS_4."XMLEXTRA",SYS_ALIAS_8."SYS_NC_ROWINFO$"),'/Organ
izationTy
pe/OrganizationID')='30005')
7 - filter(SYS_CHECKACL("SYS_ALIAS_4"."ACLOID","SYS_ALIAS_4"."OWNERID",x
mltype(''<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xd
b/dav.xsd
"><read-properties/><read-contents/></privilege>''))=1)
8 - access("SYS_ALIAS_8"."NESTED_TABLE_ID"="SYS_ALIAS_4"."SYS_NC00021000
22$")
9 - access("SYS_NTHE71m+WMph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
10 - access("SYS_NTHE71m+WNph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
11 - access("SYS_NTHE71m+WOph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - access("SYS_ALIAS_3"."NESTED_TABLE_ID"=:B1)
Note: cpu costing is off
43 rows selected.
SQL> spool off
Example index created
SQL>
SQL> create index orgID_idx on org_type ("OrganizationID");
Index created.
SQL>
SQL> set timing on
SQL>
SQL> select
2 extractvalue(value(ot),'/OrganizationType/OrganizationID')
3 from adt_org o,
4 table(xmlsequence(extract(value(o),'/Organization/OrganizationTypes/OrganizationType'))) ot
5 where extractvalue(value(ot),'/OrganizationType/OrganizationID') = '30005';
EXTRACTVALUE(VALUE(OT),'/ORGANIZATIONTYPE/ORGANIZATIONID')
--------------------------------------------------------------------------------
30005
Elapsed: 00:00:03.15
SQL>
SQL> set timing off
SQL>
SQL> explain plan for
2 select
3 extractvalue(value(ot),'/OrganizationType/OrganizationID')
4 from adt_org o,
5 table(xmlsequence(extract(value(o),'/Organization/OrganizationTypes/OrganizationType'))) ot
6 where extractvalue(value(ot),'/OrganizationType/OrganizationID') = '30005';
Explained.
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30520 | 25 |
|* 1 | INDEX RANGE SCAN | SYS_IOT_TOP_65649 | 1 | 14043 | 2 |
|* 2 | INDEX RANGE SCAN | SYS_IOT_TOP_65654 | 1 | 76098 | 3 |
|* 3 | INDEX RANGE SCAN | SYS_IOT_TOP_65659 | 1 | 44079 | 2 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_65664 | 1 | 24142 | |
|* 5 | FILTER | | | | |
| 6 | NESTED LOOPS | | 1 | 30520 | 23 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 7 | TABLE ACCESS FULL | ADT_ORG | 12 | 3684 | 11 |
|* 8 | INDEX RANGE SCAN | SYS_IOT_TOP_65647 | 1 | 30213 | 1 |
|* 9 | INDEX RANGE SCAN | SYS_IOT_TOP_65649 | 1 | 14043 | 2 |
|* 10 | INDEX RANGE SCAN | SYS_IOT_TOP_65654 | 1 | 76098 | 3 |
|* 11 | INDEX RANGE SCAN | SYS_IOT_TOP_65659 | 1 | 44079 | 2 |
|* 12 | INDEX RANGE SCAN | SYS_IOT_TOP_65664 | 1 | 24142 | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - access("SYS_NTHE71m+WMph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
2 - access("SYS_NTHE71m+WNph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
3 - access("SYS_NTHE71m+WOph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
4 - access("SYS_ALIAS_3"."NESTED_TABLE_ID"=:B1)
5 - filter(EXTRACTVALUE(SYS_MAKEXML('1C4EF59BE459A61EE04011ACA05A2DD4',1
6827,SYS_ALIAS_4."XMLEXTRA",SYS_ALIAS_8."SYS_NC_ROWINFO$"),'/Organ
izationTy
pe/OrganizationID')='30005')
7 - filter(SYS_CHECKACL("SYS_ALIAS_4"."ACLOID","SYS_ALIAS_4"."OWNERID",x
mltype(''<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd"
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xd
b/dav.xsd
"><read-properties/><read-contents/></privilege>''))=1)
8 - access("SYS_ALIAS_8"."NESTED_TABLE_ID"="SYS_ALIAS_4"."SYS_NC00021000
22$")
9 - access("SYS_NTHE71m+WMph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
10 - access("SYS_NTHE71m+WNph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
11 - access("SYS_NTHE71m+WOph7gQBGsoFot1A=="."NESTED_TABLE_ID"=:B1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
12 - access("SYS_ALIAS_3"."NESTED_TABLE_ID"=:B1)
Note: cpu costing is off
43 rows selected.
SQL> spool off;
Can anyone please help clear these things up for me. And provide insite on why the index I created in the above example is not being used?
Also I am using 9.2.0.4 I know people on this forum are recommending upgrading to 10.2 and I have made that request to our DBA but the upgrade has not happened yet.
Thanks