SELECT FMIS_OWNER.FMIS_SITE_LISTING.CUS_ID, FMIS_OWNER.FMIS_SITE_LISTING.SIT_ID, decode(FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_BOR_ID, 2, decode(substr(nvl(FMIS_OWNER_CONTRACTS_CUS.CON_REFERENCE_NUMBER, 'N3'), 1, 2), 'N3', 'N3', 'N2')), FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_CLASS, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_FLAG, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_CHANNELS, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_IDENTIFIER1, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_IDIENTIFIER2, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_INSTALLATION_DATE, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_CEASE_DATE, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_TYPE, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_ORDER_MANAGEMENT_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_SERIAL_NUMBER, FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_SOURCE_SYSTEM, FMIS_OWNER.FMIS_INVENTORY_LISTING.AST_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.LC_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.LOC_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.MAG_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.NDP_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.NUMBER_OF_LICENSES, FMIS_OWNER.FMIS_INVENTORY_LISTING.SIT_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.SLA_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.SSV_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.SOFTWARE_INSTALLED_ELEMENT_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.SUPPLIER_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.COMMENTS, FMIS_OWNER.FMIS_INVENTORY_LISTING.CON_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.CUS_ID, FMIS_OWNER.FMIS_INVENTORY_LISTING.DDI_RANGE, FMIS_OWNER.FMIS_NTN_NTNS.NTN_NTN_ID1, FMIS_OWNER_FMIS_INV_STATUS4.STA_NAME, trim(replace(translate(FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_MAN_IP_ADDRESS, chr(9) || chr(8) || chr(10) || chr(13), ' '), ' ', ' ')), FMIS_OWNER.FMIS_PACKAGE_INSTANCES.PI_PM1, FMIS_OWNER.FMIS_PACKAGE_INSTANCES.PI_IDENTIFIER, NULL ELEMENT_ROLE, -- FMIS_OWNER.FMIS_NETWORK_NODES.NTN_ROLE, INVENTORY_SUPPLIERS.SUP_NAME, FMIS_OWNER.FMIS_SITE_SERVICES.SSV_LABEL FROM FMIS_OWNER.FMIS_SITE_LISTING, FMIS_OWNER.FMIS_BFG_CUSTOMERS, FMIS_OWNER.FMIS_CONTRACTS FMIS_OWNER_CONTRACTS_CUS, FMIS_OWNER.FMIS_INVENTORY_LISTING, FMIS_OWNER.FMIS_NTN_NTNS, FMIS_OWNER.FMIS_INV_STATUS FMIS_OWNER_FMIS_INV_STATUS4, FMIS_OWNER.FMIS_PACKAGE_INSTANCES, --FMIS_OWNER.FMIS_NETWORK_NODES, FMIS_OWNER.FMIS_SUPPLIERS INVENTORY_SUPPLIERS, FMIS_OWNER.FMIS_SITE_SERVICES, --FMIS_OWNER.FMIS_CUSTOMER_LISTING, FMIS_OWNER.FMIS_CONTRACT_SERVICES WHERE (FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_ID = FMIS_OWNER.FMIS_SITE_LISTING.CUS_ID (+)) ---( FMIS_OWNER.FMIS_CUSTOMER_LISTING.CUS_ID=FMIS_OWNER.FMIS_SITE_LISTING.CUS_ID(+) ) AND (FMIS_OWNER.FMIS_SITE_LISTING.SIT_ID = FMIS_OWNER.FMIS_INVENTORY_LISTING.SIT_ID (+)) AND (FMIS_OWNER.FMIS_SITE_SERVICES.SSV_ID (+) = FMIS_OWNER.FMIS_INVENTORY_LISTING.SSV_ID) -- AND ( FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_ID=FMIS_OWNER.FMIS_CUSTOMER_LISTING.CUS_ID(+) ) --AND ( FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_ID=FMIS_OWNER.FMIS_NETWORK_NODES.NTN_ID(+) ) AND (FMIS_OWNER.FMIS_SITE_SERVICES.SSV_CSV_ID = FMIS_OWNER.FMIS_CONTRACT_SERVICES.CSV_ID (+)) AND (FMIS_OWNER.FMIS_CONTRACT_SERVICES.CSV_CON_ID = FMIS_OWNER_CONTRACTS_CUS.CON_ID (+)) AND (FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_ID = FMIS_OWNER.FMIS_NTN_NTNS.NTN_NTN_ID2 (+)) AND (FMIS_OWNER.FMIS_INVENTORY_LISTING.SUPPLIER_ID = INVENTORY_SUPPLIERS.SUP_ID (+)) AND (FMIS_OWNER.FMIS_INVENTORY_LISTING.ELEMENT_PI_ID = FMIS_OWNER.FMIS_PACKAGE_INSTANCES.PI_ID (+)) AND (FMIS_OWNER.FMIS_INVENTORY_LISTING.STA_ID = FMIS_OWNER_FMIS_INV_STATUS4.STA_ID) AND ((FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_BOR_ID = 2) AND decode(FMIS_OWNER.FMIS_BFG_CUSTOMERS.CUS_BOR_ID, 2, decode(substr(nvl(FMIS_OWNER_CONTRACTS_CUS.CON_REFERENCE_NUMBER, 'N3'), 1, 2), 'N3', 'N3', 'N2')) IN ('N2', 'N3')) Plan SELECT STATEMENT CHOOSECost : 319,255,971 Bytes : 154,514,955,462,582 Cardinality : 369,653,003,499 28 MERGE JOIN OUTER Cost : 319,255,971 Bytes : 154,514,955,462,582 Cardinality : 369,653,003,499 25 SORT JOIN Cost : 319,252,973 Bytes : 218,892,483,862 Cardinality : 671,449,337 24 FILTER 23 HASH JOIN OUTER 21 HASH JOIN OUTER Cost : 64,077 Bytes : 1,391,151,190 Cardinality : 4,864,165 19 HASH JOIN OUTER Cost : 49,331 Bytes : 154,795,940 Cardinality : 595,369 17 HASH JOIN OUTER Cost : 46,584 Bytes : 28,385,565 Cardinality : 145,567 15 MERGE JOIN OUTER Cost : 43,256 Bytes : 27,366,596 Cardinality : 145,567 12 SORT JOIN Cost : 41,250 Bytes : 24,018,555 Cardinality : 145,567 11 HASH JOIN Cost : 23,568 Bytes : 24,018,555 Cardinality : 145,567 1 TABLE ACCESS FULL FMIS_OWNER.FMIS_INV_STATUS Cost : 1 Bytes : 170 Cardinality : 17 10 MERGE JOIN OUTER Cost : 23,155 Bytes : 27,915,655 Cardinality : 180,101 7 SORT JOIN Cost : 957 Bytes : 1,463,360 Cardinality : 43,040 6 NESTED LOOPS OUTER Cost : 154 Bytes : 1,463,360 Cardinality : 43,040 3 TABLE ACCESS BY INDEX ROWID FMIS_OWNER.FMIS_BFG_CUSTOMERS Cost : 2 Bytes : 1,976 Cardinality : 76 2 INDEX RANGE SCAN FMIS_OWNER.CUS_BOR_FK_I Cost : 1 Cardinality : 76 5 TABLE ACCESS BY INDEX ROWID FMIS_OWNER.FMIS_SITE_LISTING Cost : 2 Bytes : 453,056 Cardinality : 56,632 4 INDEX RANGE SCAN FMIS_OWNER.FMIS_SITLST_CUSID Cost : 1 Cardinality : 56,632 9 SORT JOIN Cost : 22,199 Bytes : 28,674,943 Cardinality : 236,983 8 TABLE ACCESS FULL FMIS_OWNER.FMIS_INVENTORY_LISTING Cost : 611 Bytes : 28,674,943 Cardinality : 236,983 14 SORT JOIN Cost : 2,007 Bytes : 2,545,801 Cardinality : 110,687 13 TABLE ACCESS FULL FMIS_OWNER.FMIS_SITE_SERVICES Cost : 135 Bytes : 2,545,801 Cardinality : 110,687 16 TABLE ACCESS FULL FMIS_OWNER.FMIS_CONTRACT_SERVICES Cost : 5 Bytes : 25,816 Cardinality : 3,688 18 TABLE ACCESS FULL FMIS_OWNER.FMIS_SUPPLIERS Cost : 1 Bytes : 26,585 Cardinality : 409 20 TABLE ACCESS FULL FMIS_OWNER.FMIS_NTN_NTNS Cost : 2 Bytes : 21,242 Cardinality : 817 22 TABLE ACCESS FULL FMIS_OWNER.FMIS_CONTRACTS Cost : 26 Bytes : 552,160 Cardinality : 13,804 27 SORT JOIN Cost : 2,998 Bytes : 5,064,876 Cardinality : 55,053 26 TABLE ACCESS FULL FMIS_OWNER.FMIS_PACKAGE_INSTANCES Cost : 103 Bytes : 5,064,876 Cardinality : 55,053 INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGING BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE INSTANCES PARTITIONED TEMPORARY GENERATED SECONDARY BUFFER_POOL USER_STATS DURATION PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME PARAMETERS GLOBAL_STATS DOMIDX_STATUS DOMIDX_OPSTATUS FUNCIDX_STATUS BFG_BOR_ID NORMAL FMIS_OWNER FMIS_BFG_CUSTOMERS TABLE NONUNIQUE DISABLED INDEX01 2 255 1048576 1 2147483645 1 1 10 YES 1 4 21 1 6 144 VALID 1455 1455 04/10/2006 11:12 1 1 NO N N N DEFAULT NO NO BFG_CUS_ID NORMAL FMIS_OWNER FMIS_BFG_CUSTOMERS TABLE UNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 1 43 13747 1 1 4861 VALID 13747 13747 04/10/2006 11:12 1 1 NO N N N DEFAULT NO NO CON_PK NORMAL FMIS_OWNER FMIS_CONTRACTS TABLE UNIQUE DISABLED INDEX01 2 255 131072 1 2147483645 1 1 10 YES 1 34 18289 1 1 8088 VALID 18289 18289 04/10/2006 11:15 1 1 NO N N N DEFAULT NO NO CON_REF_I NORMAL FMIS_OWNER FMIS_CONTRACTS TABLE NONUNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 1 63 12235 1 1 13598 VALID 18289 18289 04/10/2006 11:15 1 1 NO N N N DEFAULT NO NO CPE_STATUS_PK NORMAL FMIS_OWNER FMIS_INV_STATUS TABLE UNIQUE DISABLED INDEX01 2 255 131072 1 2147483645 1 1 10 YES 0 1 11 1 1 1 VALID 11 11 04/10/2006 11:29 1 1 NO N N N DEFAULT NO NO CSV_CON_I NORMAL FMIS_OWNER FMIS_CONTRACT_SERVICES TABLE NONUNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 1 46 6494 1 1 9085 VALID 15558 15558 04/10/2006 11:16 1 1 NO N N N DEFAULT NO NO CUS_CUS_NAME_UK NORMAL FMIS_OWNER FMIS_BFG_CUSTOMERS TABLE UNIQUE DISABLED DATA01 2 255 131072 155648 1 4096 1 1 1 10 YES 1 91 13747 1 1 12965 VALID 13747 13747 04/10/2006 11:12 1 1 NO N N N DEFAULT NO NO CUS_CUS_REFERENCE_UK NORMAL FMIS_OWNER FMIS_BFG_CUSTOMERS TABLE UNIQUE DISABLED DATA01 2 255 131072 147456 1 4096 1 1 1 10 YES 1 49 13747 1 1 7404 VALID 13747 13747 04/10/2006 11:12 1 1 NO N N N DEFAULT NO NO ELMT_PK NORMAL FMIS_OWNER FMIS_INVENTORY_LISTING TABLE UNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 0 0 0 0 0 0 VALID 0 0 04/10/2006 11:29 1 1 NO N N N DEFAULT NO NO FMIS_CSV_INDX NORMAL FMIS_OWNER FMIS_CONTRACT_SERVICES TABLE NONUNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 1 59 15761 1 1 6124 VALID 15761 15761 04/10/2006 11:16 1 1 NO N N N DEFAULT NO NO FMIS_SITE NORMAL FMIS_OWNER FMIS_SITE_LISTING TABLE UNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 1 529 220699 1 1 220658 VALID 220699 220699 04/10/2006 01:39 1 1 NO N N N DEFAULT NO NO FMIS_SITE_CUS_I NORMAL FMIS_OWNER FMIS_SITE_LISTING TABLE NONUNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 2 618 5054 1 30 152879 VALID 220699 220699 04/10/2006 01:39 1 1 NO N N N DEFAULT NO NO FMIS_SSV NORMAL FMIS_OWNER FMIS_SITE_SERVICES TABLE UNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 2 1133 375563 1 1 64541 VALID 375563 375563 04/10/2006 01:47 1 1 NO N N N DEFAULT NO NO FMIS_SSV_CSV NORMAL FMIS_OWNER FMIS_SITE_SERVICES TABLE NONUNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 2 1209 15374 1 3 50294 VALID 375563 375563 04/10/2006 01:47 1 1 NO N N N DEFAULT NO NO FMIS_SSV_SIT NORMAL FMIS_OWNER FMIS_SITE_SERVICES TABLE NONUNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 2 1110 244807 1 1 238926 VALID 375563 375563 04/10/2006 01:47 1 1 NO N N N DEFAULT NO NO FMIS_SSV_SLA NORMAL FMIS_OWNER FMIS_SITE_SERVICES TABLE NONUNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 2 1190 8641 1 5 45645 VALID 375563 375563 04/10/2006 01:47 1 1 NO N N N DEFAULT NO NO IDX_ELEMENT_PI_ID NORMAL FMIS_OWNER FMIS_INVENTORY_LISTING TABLE NONUNIQUE DISABLED DATA01 2 255 131072 131072 1 4096 1 1 1 10 YES VALID 1 1 NO N N N DEFAULT NO NO IDX_NTN_NTN_ID1 NORMAL FMIS_OWNER FMIS_NTN_NTNS TABLE NONUNIQUE DISABLED DATA01 2 255 131072 147456 1 4096 1 1 1 10 YES VALID 1 1 NO N N N DEFAULT NO NO IDX_NTN_NTN_ID2 NORMAL FMIS_OWNER FMIS_NTN_NTNS TABLE NONUNIQUE DISABLED DATA01 2 255 131072 147456 1 4096 1 1 1 10 YES VALID 1 1 NO N N N DEFAULT NO NO INV_CON_I NORMAL FMIS_OWNER FMIS_INVENTORY_LISTING TABLE NONUNIQUE DISABLED INDEX01 2 255 16384 1 2147483645 1 1 10 YES 0 0 0 0 0 0 VALID 0 0 04/10/2006 11:29 1 1 NO N N N DEFAULT NO NO INV_SSV_I NORMAL FMIS_OWNER FMIS_INVENTORY_LISTING TABLE NONUNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 0 0 0 0 0 0 VALID 0 0 04/10/2006 11:29 1 1 NO N N N DEFAULT NO NO NTN_NTN1_NTN2_PK NORMAL FMIS_OWNER FMIS_NTN_NTNS TABLE UNIQUE DISABLED INDEX01 2 255 131072 1 2147483645 1 1 10 YES 1 55 19980 1 1 6474 VALID 19980 19980 04/10/2006 11:56 1 1 NO N N N DEFAULT NO NO PI_PK NORMAL FMIS_OWNER FMIS_PACKAGE_INSTANCES TABLE UNIQUE DISABLED DATA01 2 255 16384 8192 1 2147483645 0 1 1 10 YES VALID 1 1 NO N N N DEFAULT NO NO SIT_ID_IND NORMAL FMIS_OWNER FMIS_INVENTORY_LISTING TABLE NONUNIQUE DISABLED INDEX01 2 255 65536 1 2147483645 1 1 10 YES 0 0 0 0 0 0 VALID 0 0 04/10/2006 11:29 1 1 NO N N N DEFAULT NO NO SUP_PK NORMAL FMIS_OWNER FMIS_SUPPLIERS TABLE UNIQUE DISABLED INDEX01 2 255 131072 1 2147483645 1 1 10 YES 1 2 831 1 1 345 VALID 831 831 20/06/2006 21:35 1 1 NO N N N DEFAULT NO NO