db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Re: I need help on getting Derby to use a primary key index on a query
Date Tue, 27 Aug 2013 02:10:34 GMT
I think i found were the issue occurs.  I am able to reproduce the problem with Derby 10.8.2.2.   The database has about 7 million records in APP.CONFIGURATION_BUNDLES3 and about 48 million records in COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3.   On  COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3, there is a non-unique index on COSEDDROPPROFILEDSCPTABLEENTRY_ID.

Sometimes on the same query with specific values, the query works correctly in that the query plan chooses to do an index lookup on COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 using the index and then an index lookup on APP.CONFIGURATION_BUNDLES3.   Other times on the same query with another query value, the query plan chooses to do an index lookup on APP.CONFIGURATION_BUNDLES3 and then a table scan on APP.CONFIGURATION_BUNDLES3 which is incorrect.   The problem occurs because of the cost and row estimate on COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 for specific values.

Here is a query that works correctly:

SELECT * FROM APP.CONFIGURATION_BUNDLE3 T1,
APP.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 3;

and here is the same query with a different value that fails with the table scan:

SELECT * FROM APP.CONFIGURATION_BUNDLE3 T1,
APP.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 22112129;

Note that both of these values will exist, the number of rows in the first will be 42 and the second will be 1.  Also note that the values of COSEDDROPPROFILEDSCPTABLEENTRY_ID are pretty spaced out so the value 22112129 is at about 7% of the sequence of values in order.  Also note that there is a non-unique index on  COSEDDROPPROFILEDSCPTABLEENTRY_ID as I said above.

   In BTreeCostController.java, we have:

        public void getScanCost(
    int                     scan_type,
    long                    row_count,
    int                     group_size,
    boolean                 forUpdate,
    FormatableBitSet                 scanColumnList,
    DataValueDescriptor[]   template,
    DataValueDescriptor[]   startKeyValue,
    int                     startSearchOperator,
    DataValueDescriptor[]   stopKeyValue,
    int                     stopSearchOperator,
    boolean                 reopen_scan,
    int                     access_type,
    StoreCostResult         cost_result)
        throws StandardException
    {
        float       left_of_start;
        float       left_of_stop;
        ControlRow  control_row = null;
        long        input_row_count = (row_count < 0 ? num_rows : row_count);

        try
        {
            // Find the starting page and row slot.
            if (startKeyValue == null)
            {
                left_of_start = 0;
            }
            else
            {
                // Search for the starting row.

                SearchParameters sp = new SearchParameters(
                    startKeyValue,
                    ((startSearchOperator == ScanController.GE) ?
                        SearchParameters.POSITION_LEFT_OF_PARTIAL_KEY_MATCH :
                        SearchParameters.POSITION_RIGHT_OF_PARTIAL_KEY_MATCH),
                    template, this, true);

                control_row =
                    ControlRow.get(this, BTree.ROOTPAGEID).search(sp);

                control_row.release();
                control_row = null;

                left_of_start = sp.left_fraction;
            }

            if (stopKeyValue == null)
            {
                left_of_stop = 1;
            }
            else
            {
                // Search for the stopping row.

                SearchParameters sp =
                    new SearchParameters(
                        stopKeyValue,
                        ((stopSearchOperator == ScanController.GE) ?
                          SearchParameters.POSITION_LEFT_OF_PARTIAL_KEY_MATCH :
                          SearchParameters.POSITION_RIGHT_OF_PARTIAL_KEY_MATCH),
                        template, this, true);

                control_row =
                    ControlRow.get(this, BTree.ROOTPAGEID).search(sp);

                control_row.release();
                control_row = null;

                left_of_stop = sp.left_fraction;
            }

            // System.out.println(
              //   "\n\tleft_of_start = " + left_of_start +
                // "\n\tleft_of_stop  = " + left_of_stop);

            // what percentage of rows are between start and stop?

            float ret_fraction = left_of_stop - left_of_start;

            // If for some reason the stop position comes before the start
            // position, assume 0 rows will return from query.
            if (ret_fraction < 0)
                ret_fraction = 0;

            // Never return estimate of more rows than exist, sometimes
            // the recursive estimation through the btree may return a number
            // like 1.00001.
            if (ret_fraction > 1)
                ret_fraction = 1;

            float estimated_row_count = input_row_count * ret_fraction;

            // first the base cost of positioning on the first row in the scan.
            double cost =
                getFetchFromFullKeyCost(scanColumnList, access_type);

            // add the base cost of bringing each page for the first time into
            // the cache.  This is basically the cost of bringing each leaf
            // uncached into the cache and reading the control row off of it.:
            cost +=
                (num_pages * ret_fraction) * BASE_UNCACHED_ROW_FETCH_COST;

            // Now some magic to try and figure out the cost of doing a
            // scan along the leaf level of the tree.  Mostly just assume
            // the costs are the same as the heap, and ignore qualifier
            // processing and stop row comparisons for now.

            // the base cost of getting each of the rows from a page assumed
            // to already be cached (by the scan fetch) - this is only for all
            // rows after the initial row on the page has been accounted for
            // under the BASE_UNCACHED_ROW_FETCH_COST cost.:
            long cached_row_count = ((long) estimated_row_count) - num_pages;
            if (cached_row_count < 0)
                cached_row_count = 0;

            if (scan_type == StoreCostController.STORECOST_SCAN_NORMAL)
                cost += cached_row_count * BASE_GROUPSCAN_ROW_COST;
            else
                cost += cached_row_count * BASE_HASHSCAN_ROW_FETCH_COST;

            // finally add the cost associated with the number of bytes in row:
            long row_size =
                (input_row_count == 0) ?
                    4 : (num_pages * page_size) / input_row_count;

            cost +=
                (estimated_row_count * row_size) * BASE_ROW_PER_BYTECOST;

            if (SanityManager.DEBUG)
            {
                if (cost < 0)
                    SanityManager.THROWASSERT("cost " + cost);

                if (estimated_row_count < 0)
                    SanityManager.THROWASSERT(
                        "estimated_row_count = " + estimated_row_count);
            }

            // return the cost
            cost_result.setEstimatedCost(cost);

            // RESOLVE - should we make sure this number is > 0?
            cost_result.setEstimatedRowCount(Math.round(estimated_row_count));
        }
        finally
        {
            if (control_row != null)
                control_row.release();
        }

        // System.out.println("BTreeCostController.getScanCost():" +
          //   "\n\t cost = " + cost_result.getEstimatedCost() +
            // "\n\t rows = " + cost_result.getEstimatedRowCount());

        return;
    }

The problem that I am seeing is that on certain queries coming in the "left_of_start" and the "left_of_stop" can be the same value.   When this occurs, the "ret_fraction" becomes 0.0 and the estimated row count becomes "0.0" and the estimated cost becomes "0.0".

When this occurs in the query plan and the join order is APP.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 and then APP.CONFIGURATION_BUNDLE3, this outer cost of "0.0" has the effect of causing the cost of the table scan of APP.CONFIGURATION_BUNDLE3 appear to be the lowest cost.   This is incorrect.

For discrete values that are unique within a large index, it seems to me (and is observed), that the rows left of the start value and rows left of the stop value could very well be the same when the starting and stopping values are the same as in this case.

As a test, I modified the above code to estimate the rows as 1 when this is the case and ran the code and it immediately pick the access path that it should.  Probably 1 row is not correct but I am having a little trouble to come up with the value that it should be when the starting and stopping row percentage are exactly the same (in this case left_of_stop and right_of_stop is 0.07715294 and the number of rows in the table are 48248128).

Below I have the output of the optimizer trace for the two queries that I label CORRECT and iNCORRECT in which this can be seen.

-- CORRECT OPTIMIZER TRACE ---

"SELECT * FROM APP.CONFIGURATION_BUNDLE3 T1,
APP.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 3
Optimization started at time 1377549782052 using optimizer 1655768029


Considering join order:  0 with assignedTableMap = {}


Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Adding unordered optimizable, # of predicates = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost of 1.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0

Remembering join strategy NESTEDLOOP as best for table 0
in best access path
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Adding unordered optimizable, # of predicates = 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 1540600.81, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.5267433370000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 1.5267433370000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 2359582.402, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 2634793.8619999997, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0
No more conglomerates to consider for table 0
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 70394772, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0


Considering join order:  0 1 with assignedTableMap = {0}


Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Scanning heap, but we have a full match on a unique key.
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 2, extra qualifier selectivity is : 0.010000000000000002
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 482481.3300000001, singleScanRowCount == 482481.3300000001 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 8.750136837956523E13, rowCount == 7003486.0, singleScanRowCount == 482481.3300000001 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Scanning heap, but we have a full match on a unique key.

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.3451277644000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.3451277644000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 1.3451277644000001E7, rowCount == 7003486.0, singleScanRowCount == 4824813.3 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.3451277644000001E7, rowCount == 7003486.0, singleScanRowCount == 482481.33 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Guaranteed to match a single row - cost is: 7.8575 for table 1
Lock mode set to MODE_RECORD because all start and stop positions are constant
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.70082898735E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.70082898735E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 1.70082898735E7, rowCount == 7003486.0, singleScanRowCount == 4824813.2 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.70082898735E7, rowCount == 7003486.0, singleScanRowCount == 482481.32000000007 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.3959416302000001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 0.1
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 1, statistics start/stop selectivity is : 1.3264763349989454E-6
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Cost including extra statistics for index being considered start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 24.802827036771248, rowCount == 64.00000530590533, singleScanRowCount == 64.00000530590533 for table 1
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 997551539, cost == 128.99483567478512, rowCount == 64.00000530590533, singleScanRowCount == 64.00000530590533 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 9.034135257206582E8, rowCount == 7003486.0, singleScanRowCount == 64.00000530590533 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 9.034135257206582E8, rowCount == 7003486.0, singleScanRowCount == 6.400000530590534 for table 1
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.4915061182000002E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.010000000000000002
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 997551539, cost == 9.346302007800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 9.346302007800001E7, rowCount == 7003486.0, singleScanRowCount == 4.8248132E7 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 9.346302007800001E7, rowCount == 7003486.0, singleScanRowCount == 482481.32000000007 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 8.544554508045316, rowCount == 4.0, singleScanRowCount == 4.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 997551539, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 1.0544836870533226E8, rowCount == 7003486.0, singleScanRowCount == 4.0 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.0544836870533226E8, rowCount == 7003486.0, singleScanRowCount == 0.4 for table 1
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1
No more conglomerates to consider for table 1
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 70394772, cost == 5.9364160905E7, rowCount == 7003486.0, singleScanRowCount == 1.0
We have a complete join order.
This is the cheapest plan so far.
Plan is a normal plan.
Cost of cheapest plan is Level2CostEstimateImpl: at 70394772, cost == 5.9364160905E7, rowCount == 7003486.0, singleScanRowCount == 1.0
Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 69248303, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 69248303, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table 0 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 2067575494, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0, coveringIndexScan == true, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 2067575494, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0, coveringIndexScan == true, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table 1 for plan type  normal



Remembering join order as best:  0 1 with assignedTableMap = {0, 1}




Considering join order:  1 with assignedTableMap = {}


Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Adding unordered optimizable, # of predicates = 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.2493973484000001E7, rowCount == 1.11908647187577, singleScanRowCount == 1.11908647187577 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Adding unordered optimizable, # of predicates = 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.60550464935E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.60550464935E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 1.60550464935E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 1.60550464935E7, rowCount == 1.1190864486813705, singleScanRowCount == 1.1190864486813705 for table 1
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 1.3959416302000001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 997551539, cost == 9.250737519800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 9.250737519800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 9.250737519800001E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 997551539, cost == 9.250737519800001E7, rowCount == 1.1190864486813705, singleScanRowCount == 1.1190864486813705 for table 1
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 997551539, cost == 8.544554508045316, rowCount == 4.0, singleScanRowCount == 4.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 997551539, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 997551539, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1
No more conglomerates to consider for table 1
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 70394772, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0


Considering join order:  1 0 with assignedTableMap = {1}


Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Scanning heap, but we have a full match on a unique key.
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 457048813, cost == 4334269.66, rowCount == 700348.6000000001, singleScanRowCount == 700348.6000000001 for table 0
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 1.733707864E7, rowCount == 4.0, singleScanRowCount == 700348.6000000001 for table 0

Remembering join strategy NESTEDLOOP as best for table 0
in best access path
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Scanning heap, but we have a full match on a unique key.

# hash key columns = 1hashKeyColumns[0] = 3

# hash key columns = 1hashKeyColumns[0] = 3
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 4470393.76, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 4470393.76, rowCount == 4.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 457048813, cost == 4470393.76, rowCount == 4.0, singleScanRowCount == 700348.6000000001 for table 0
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Guaranteed to match a single row - cost is: 6.286 for table 0
Lock mode set to MODE_RECORD because all start and stop positions are constant
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 25.144, rowCount == 4.0, singleScanRowCount == 1.0 for table 0
Index does not cover query - cost including base row fetch is: 7.84 for table 0

Remembering join strategy NESTEDLOOP as best for table 0
in best access path
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 1679861.03, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.540669359E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 1.540669359E7, rowCount == 4.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 457048813, cost == 1.540669359E7, rowCount == 4.0, singleScanRowCount == 700348.6000000001 for table 0
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 2359582.402, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 6.4345659848000005E7, rowCount == 4.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 457048813, cost == 6.4345659848000005E7, rowCount == 4.0, singleScanRowCount == 700348.6000000001 for table 0
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} scan for table number 0 is :
Level2CostEstimateImpl: at 457048813, cost == 2634793.8619999997, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 457048813, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 4.0 scans is: Level2CostEstimateImpl: at 457048813, cost == 6.5446505688E7, rowCount == 4.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 457048813, cost == 6.5446505688E7, rowCount == 4.0, singleScanRowCount == 700348.6000000001 for table 0
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0
No more conglomerates to consider for table 0
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 70394772, cost == 48.04055450804531, rowCount == 4.0, singleScanRowCount == 1.0
We have a complete join order.
This is the cheapest plan so far.
Plan is a normal plan.
Cost of cheapest plan is Level2CostEstimateImpl: at 70394772, cost == 48.04055450804531, rowCount == 4.0, singleScanRowCount == 1.0
Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 726246088, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 726246088, cost == 15.056554508045316, rowCount == 4.0, singleScanRowCount == 4.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table 1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID}, costEstimate == Level2CostEstimateImpl: at 1868435115, cost == 32.983999999999995, rowCount == 4.0, singleScanRowCount == 1.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID}, costEstimate == Level2CostEstimateImpl: at 1868435115, cost == 32.983999999999995, rowCount == 4.0, singleScanRowCount == 1.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table 0 for plan type  normal



Remembering join order as best:  1 0 with assignedTableMap = {0, 1}


Modifying access paths using optimizer 1655768029
Changing access path for table 1
Changing access path for table 0
"

--- INCORRECT OPTIMIZER TRACE ---

"SELECT * FROM APP.CONFIGURATION_BUNDLE3 T1,
APP.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3 t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 22112129
Optimization started at time 1377549702099 using optimizer 2101481450


Considering join order:  0 with assignedTableMap = {}


Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Adding unordered optimizable, # of predicates = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost of 1.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0

Remembering join strategy NESTEDLOOP as best for table 0
in best access path
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Adding unordered optimizable, # of predicates = 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 1540600.81, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.5267433370000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 1.5267433370000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 2359582.402, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 2634793.8619999997, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 1.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 0 predicates.

Considering join strategy NESTEDLOOP for table 0
No more conglomerates to consider for table 0
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 245104354, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0


Considering join order:  0 1 with assignedTableMap = {0}


Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Scanning heap, but we have a full match on a unique key.
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 2, extra qualifier selectivity is : 0.010000000000000002
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 482481.3300000001, singleScanRowCount == 482481.3300000001 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 8.750136837956523E13, rowCount == 7003486.0, singleScanRowCount == 482481.3300000001 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Scanning heap, but we have a full match on a unique key.

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.3451277644000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.3451277644000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 1.3451277644000001E7, rowCount == 7003486.0, singleScanRowCount == 4824813.3 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.3451277644000001E7, rowCount == 7003486.0, singleScanRowCount == 482481.33 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Guaranteed to match a single row - cost is: 7.8575 for table 1
Lock mode set to MODE_RECORD because all start and stop positions are constant
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.70082898735E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.70082898735E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 1.70082898735E7, rowCount == 7003486.0, singleScanRowCount == 4824813.2 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.70082898735E7, rowCount == 7003486.0, singleScanRowCount == 482481.32000000007 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.3959416302000001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 0.1
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 1, statistics start/stop selectivity is : 1.3264763349989454E-6
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Cost including extra statistics for index being considered start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 24.802827036771248, rowCount == 64.00000530590533, singleScanRowCount == 64.00000530590533 for table 1
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 572153096, cost == 128.99483567478512, rowCount == 64.00000530590533, singleScanRowCount == 64.00000530590533 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 9.034135257206582E8, rowCount == 7003486.0, singleScanRowCount == 64.00000530590533 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 9.034135257206582E8, rowCount == 7003486.0, singleScanRowCount == 6.400000530590534 for table 1
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.4915061182000002E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.010000000000000002
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 572153096, cost == 9.346302007800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 9.346302007800001E7, rowCount == 7003486.0, singleScanRowCount == 4.8248132E7 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 9.346302007800001E7, rowCount == 7003486.0, singleScanRowCount == 482481.32000000007 for table 1
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 15.715, rowCount == 2.0, singleScanRowCount == 2.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 572153096, cost == 18.971, rowCount == 2.0, singleScanRowCount == 2.0 for table 1
Cost of 7003486.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 1.32863132906E8, rowCount == 7003486.0, singleScanRowCount == 2.0 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.32863132906E8, rowCount == 7003486.0, singleScanRowCount == 0.2 for table 1
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 2 predicates.

Considering join strategy NESTEDLOOP for table 1
No more conglomerates to consider for table 1
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 245104354, cost == 5.9364160905E7, rowCount == 7003486.0, singleScanRowCount == 1.0
We have a complete join order.
This is the cheapest plan so far.
Plan is a normal plan.
Cost of cheapest plan is Level2CostEstimateImpl: at 245104354, cost == 5.9364160905E7, rowCount == 7003486.0, singleScanRowCount == 1.0
Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 2063323644, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 2063323644, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table 0 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 714552952, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0, coveringIndexScan == true, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 714552952, cost == 5.5029891245E7, rowCount == 7003486.0, singleScanRowCount == 1.0, coveringIndexScan == true, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table 1 for plan type  normal



Remembering join order as best:  0 1 with assignedTableMap = {0, 1}




Considering join order:  1 with assignedTableMap = {}


Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Adding unordered optimizable, # of predicates = 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 4.8248133E7, singleScanRowCount == 4.8248133E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 4824813.3, singleScanRowCount == 4824813.3 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.2493973484000001E7, rowCount == 1.11908647187577, singleScanRowCount == 1.11908647187577 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1328 name = 989c00e1-0140-b54e-7bb1-ffff93450f77 uuid = a0d440e2-0140-b54e-7bb1-ffff93450f77 indexable = false for table 1
Adding unordered optimizable, # of predicates = 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.60550464935E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.60550464935E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 1.60550464935E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 1.60550464935E7, rowCount == 1.1190864486813705, singleScanRowCount == 1.1190864486813705 for table 1
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1345 name = SQL130825075045880 uuid = 782c40df-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID, COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 1.3959416302000001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 572153096, cost == 9.250737519800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 9.250737519800001E7, rowCount == 4.8248132E7, singleScanRowCount == 4.8248132E7 for table 1
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 9.250737519800001E7, rowCount == 4824813.2, singleScanRowCount == 4824813.2 for table 1
Selectivity from statistics found. It is 2.3194399498852528E-8
Cost including extra selectivity from statistics start/stop selectivity is : Level2CostEstimateImpl: at 572153096, cost == 9.250737519800001E7, rowCount == 1.1190864486813705, singleScanRowCount == 1.1190864486813705 for table 1
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1361 name = SQL130825075046150 uuid = 597e80e5-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEBUNDLE_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} scan for table number 1 is :
Level2CostEstimateImpl: at 572153096, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 572153096, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0 for table 1
Cost of 1.0 scans is: Level2CostEstimateImpl: at 572153096, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0 for table 1

Remembering join strategy NESTEDLOOP as best for table 1
in best access path
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy HASH for table 1

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID} for table 1
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T0 with 1 predicates.

Considering join strategy NESTEDLOOP for table 1
No more conglomerates to consider for table 1
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 245104354, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0


Considering join order:  1 0 with assignedTableMap = {1}


Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Scanning heap, but we have a full match on a unique key.
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 4334269.66, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 1, extra qualifier selectivity is : 0.1
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 1.0
Lock mode set to MODE_TABLE because no start or stop position
Cost including extra qualifier start/stop selectivity is : Level2CostEstimateImpl: at 1625417063, cost == 4334269.66, rowCount == 700348.6000000001, singleScanRowCount == 700348.6000000001 for table 0
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 700348.6000000001 for table 0

Remembering join strategy NESTEDLOOP as best for table 0
in best access path
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Scanning heap, but we have a full match on a unique key.

# hash key columns = 1hashKeyColumns[0] = 3

# hash key columns = 1hashKeyColumns[0] = 3
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 4470393.76, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 4470393.76, rowCount == 0.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 1625417063, cost == 4470393.76, rowCount == 0.0, singleScanRowCount == 700348.6000000001 for table 0
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Guaranteed to match a single row - cost is: 6.286 for table 0
Lock mode set to MODE_RECORD because all start and stop positions are constant
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 1.0 for table 0
Index does not cover query - cost including base row fetch is: 0.0 for table 0
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0

# hash key columns = 1hashKeyColumns[0] = 0

# hash key columns = 1hashKeyColumns[0] = 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1281 name = SQL130825075032410 uuid = 2ba7c09b-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {ID} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 1679861.03, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 0, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.540669359E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 1.540669359E7, rowCount == 0.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 1625417063, cost == 1.540669359E7, rowCount == 0.0, singleScanRowCount == 700348.6000000001 for table 0
Skipping access path due to excess memory usage, maximum is 1048576
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 2359582.402, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.6086414962000001E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 700348.6000000001 for table 0
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1297 name = CONFIGURATION_BUNDLE3_IX_2 uuid = 0ddd00a9-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {BUNDLE_NAME} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Estimating cost of conglomerate: ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Cost of conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} scan for table number 0 is :
Level2CostEstimateImpl: at 1625417063, cost == 2634793.8619999997, rowCount == 7003486.0, singleScanRowCount == 7003486.0
        Number of extra first column predicates is : 0, extra first column selectivity is : 1.0
        Number of extra start/stop predicates is : 0, extra start/stop selectivity is : 1.0
        Number of start/stop statistics predicates is : 0, statistics start/stop selectivity is : 1.0
        Number of extra qualifiers is : 0, extra qualifier selectivity is : 1.0
        Number of extra non-qualifiers is : 1, extra non-qualifier selectivity is : 0.1
Lock mode set to MODE_TABLE because no start or stop position
Index does not cover query: cost including row fetch is: Level2CostEstimateImpl: at 1625417063, cost == 1.6361626422E7, rowCount == 7003486.0, singleScanRowCount == 7003486.0 for table 0
Cost of 0.0 scans is: Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 7003486.0 for table 0
Cost including extra non-qualifier start/stop selectivity is : Level2CostEstimateImpl: at 1625417063, cost == 0.0, rowCount == 0.0, singleScanRowCount == 700348.6000000001 for table 0
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy HASH for table 0

Considering conglomerate ConglomerateDescriptor: conglomerateNumber = 1313 name = CONFIGURATION_BUNDLE3_IX_1 uuid = a65c80ac-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {DTYPE} for table 0
Skipping HASH JOIN because there are no hash key columns
Calling nextAccessPath() for base table T1 with 1 predicates.

Considering join strategy NESTEDLOOP for table 0
No more conglomerates to consider for table 0
Total cost of non-sort-avoidance plan is Level2CostEstimateImpl: at 245104354, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 700348.6000000001
We have a complete join order.
This is the cheapest plan so far.
Plan is a normal plan.
Cost of cheapest plan is Level2CostEstimateImpl: at 245104354, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 700348.6000000001
Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 2057836940, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1377 name = COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY3_IX_0 uuid = 629f40ea-0140-b54e-7bb1-ffff93450f77 indexable = true, key columns = {COSEDDROPPROFILEDSCPTABLEENTRY_ID}, costEstimate == Level2CostEstimateImpl: at 2057836940, cost == 7.8575, rowCount == 0.0, singleScanRowCount == 0.0, coveringIndexScan == false, nonMatchingIndexScan == false, joinStrategy == NESTEDLOOP, lockMode == 6, optimizer level == 2 as truly the best for table 1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 959713667, cost == 0.0, rowCount == 0.0, singleScanRowCount == 700348.6000000001, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table -1 for plan type  normal

Remembering access path cd == ConglomerateDescriptor: conglomerateNumber = 1264 name = cbf5809d-0140-b54e-7bb1-ffff93450f77 uuid = 6c44409f-0140-b54e-7bb1-ffff93450f77 indexable = false, costEstimate == Level2CostEstimateImpl: at 959713667, cost == 0.0, rowCount == 0.0, singleScanRowCount == 700348.6000000001, coveringIndexScan == false, nonMatchingIndexScan == true, joinStrategy == NESTEDLOOP, lockMode == 7, optimizer level == 2 as truly the best for table 0 for plan type  normal



Remembering join order as best:  1 0 with assignedTableMap = {0, 1}


Modifying access paths using optimizer 2101481450
Changing access path for table 1
Changing access path for table 0
"


On Aug 24, 2013, at 8:00 AM, "Bergquist, Brett" <BBergquist@canoga.com> wrote:

> Derby developers (Mike, Manta, Bryan, Rick ,Knut, and if I forgot anyone that has help me in the past), I just want to thank you for your help!   I realize that you guys have day jobs just like me an taking the time out to give me some pointers is really appreciated!
>
> I am trying to produce reduced size and readily reproducible case but would you knot know it, the symptoms change :(   I saved a database that was working with 10.8.2.2 with (after shutting down) tar cvEf - db | compress >db.8.tar.Z earlier.   So just to verify again that this did indeed work correctly, I restored this database and the 10.8.2.2 libraries performed the queue and darn if the 10.8.2.2 did not exhibit the same problem.    So then last night I deleted the database and created it fresh again from the same exact script that allowed 10.8.2.2 to work and again it still has the same problem.
>
> So now I have no confidence of my test setup and also don't think the issue is confined to 10.9.1.1.   So I will be quiet until I get a case where it works and then understand why and what triggers it to not work.
>
>
> On Aug 23, 2013, at 6:39 PM, mike matrigali <mikemapp1@gmail.com> wrote:
>
>> On 8/23/2013 2:55 PM, Bergquist, Brett wrote:
>>> Mike I would love to open a Jira but having a reproducible case is important as I realize.  Right now, the reproducible database is 2Gb compressed which is really not practical to upload.
>>>
>>> My goal right now is to fix the problem.  The problem is in production, a large telecomm company, and the real database is about 200Gb.  The database has been hard upgraded and has been in use for about 2 weeks so rolling back to backup before the hard upgrade is not possible.  Only going forward is possible.   So I have the debugger connected up right now and am going through the code.  I am also comparing 10.8.2.2 version 10.9.1.0 to look for any suspicious differences.  I have to fix this now, not months from now.
>>>
>>> What would be really useful would be to:
>>>
>>> 1. understand how to turn on the optimizer tracing facility that I see in the 10.9.1.0 source.  For example:
>>>
>>>             optimizerTrace = lcc.getOptimizerTrace();
>>>   I can figure this out, but if anyone can point to a wiki page or some other resource to enable the magic, sharing it will be most welcome.
>> I have not used the following but have been pointed at it by those who do:
>> http://wiki.apache.org/db-derby/OptimizerTracing
>>>
>>> 2. understand if it would be possible to access my test database that has been hard upgraded to 10.9.1.0 with 10.8.2.2.  I know that when I boot the database it will complain and not boot but if I am just doing a read-only query on the database with 10.8.2.2 and the query works, this will give me real confidence that the problem is in the code changes from 10.8.2.2 to 10.9.1.1 and not something related to the structure now stored in the database.
>>>
>>> So I am working on this, am looking for pointers of where to look in the code, and will open a Jira also supply a patch when I have this fixed.
>>
>> this one is hard, definitely nothing is supported to to do this, and no
>> magic property is coming to mind.  If it does not take days to load your
>> test db, I would just build a 10.8 db and load it.
>>
>> Mostly catalog issues are what happen in hard upgrade, so doing a query
>> with old code on a new hard upgrade db "probably" works.  But even
>> booting might write stuff that would then be a problem using it later,
>> even if your intent is only to run read-only queries.
>>
>> You could build your own 10.8 and just change the code to skip the
>> checks that don't allow it to boot.  This might work or it might corrupt
>> the db during the boot process.  If you are trying this I would make
>> a copy of the test db to try i on.
>>>
>>
>>
>
>
>



Mime
View raw message