OPTIMIZER STATISTICS AND COST MODEL FOR IN-MEMORY TABLES

摘要

Techniques are provided for determining costs for alternative execution plans for a query, where at least a portion of the data items required by the query are in in-memory compression-units within volatile memory. The techniques involve maintaining in-memory statistics, such as statistics that indicate what fraction of a table is currently present in in-memory compression units, and the cost of decompressing in-memory compression units. Those statistics are used to determine, for example, the cost of a table scan that retrieves some or all of the necessary data items from the in-memory compression-units.

主权项

1. A method comprising:
receiving, at a database server, a query that requires performance of certain operations; wherein the certain operations include operations that access certain data items; wherein copies of the certain data items reside in a particular row-major table on persistent storage; wherein additional copies of at least a subset of the certain data items reside, in volatile memory, in one or more column-major in-memory compression-units; evaluating a plurality of alternative execution plans for performance of the certain operations; wherein the plurality of alternative execution plans includes at least:
a first execution plan that performs a full table scan that accesses copies of data items in the one or more column-major in-memory compression-units;a second execution plan that does not perform a full table scan that accesses copies of data items in the one or more column-major in-memory compression-units; for each execution plan of the plurality of alternative execution plans, determining a cost associated with the execution plan; wherein determining the cost for the first execution plan includes at least one of:
determining what fraction of the particular row-major table is duplicated in the one or more column-major in-memory compression units;determining how many column-major in-memory compression-units will need to be scanned, as part of the full table scan, after pruning non-matching column-major in-memory compression-units based on (a) filter predicates of the query, and (b) minimum and maximum values maintained for each column-major in-memory compression-unit;determining a decompression cost for decompressing those column-major in-memory compression-units to be used during performance of the full table scan;determining a row-stitching cost that represents overhead required to produce row-major results from data items extracted from column-major in-memory compression-units; ordetermining a journal-scan cost for scanning a journal, maintained in volatile memory, that contains updates for data items that have become invalid in the column-major in-memory compression-units; selecting a particular execution plan, of the plurality of alternative execution plans, based on the cost determined for the particular execution plan; obtaining a result set for the query by executing the particular execution plan; and returning the result set of the query.