os.optimization_ratio_or_status

David_Young

The following is from a database designer log. 3|NON-OPTIMIZABLE|SELECT COUNT(*) FROM sq1 WHERE K10K = 2; 4|1|SELECT COUNT(*) FROM sq1 WHERE K1K = 2; 5|UNOPTIMIZED|SELECT COUNT(*) FROM sq1 WHERE K100 = 2; Q1 - What are the differences among NON-OPTIMIZABLE, 1, and UNOPTIMZED for os.optimization_ratio_or_status? Tried looking in QUERY_EVENTS, nothing obvious showed up. Q2 - Most optimized queries return a value of 1, some return less than 1, some return greater than 1. What does this value indicate? Q3 - Queries with a GROUP BY, queries with an OR as the top level predicate are not optimized, are there more cases where a query is NON-OPTIMIZABLE or UNOPTIMZED? Thank you. EXPLAIN OUTPUT sq100db=> explain select count(*) from sq1 where k10k = 2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select count(*) from sq1 where k10k = 2; Access Path: +-GROUPBY NOTHING [Cost: 219K, Rows: 1] (PATH ID: 1) | Aggregates: count(*) | +---> STORAGE ACCESS for sq1 [Cost: 219K, Rows: 503K] (PATH ID: 2) | | Projection: public.SQ1_DBD_1_rep_sq100mdbd_node0001 | | Filter: (sq1.K10K = 2) sq100db=> explain select count(*) from sq1 where k1k = 2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select count(*) from sq1 where k1k = 2; Access Path: +-GROUPBY NOTHING [Cost: 266, Rows: 1] (PATH ID: 1) | Aggregates: count(*) | +---> STORAGE ACCESS for sq1 [Cost: 265, Rows: 503K (1 RLE)] (PATH ID: 2) | | Projection: public.SQ1_DBD_1_rep_sq100mdbd_node0001 | | Filter: (sq1.K1K = 2) sq100db=> explain select count(*) from sq1 where k100 = 2; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------ QUERY PLAN DESCRIPTION: ------------------------------ explain select count(*) from sq1 where k100 = 2; Access Path: +-GROUPBY NOTHING [Cost: 6K, Rows: 1] (PATH ID: 1) | Aggregates: count(*) | +---> STORAGE ACCESS for sq1 [Cost: 6K, Rows: 1M] (PATH ID: 2) | | Projection: public.SQ1_DBD_1_rep_sq100mdbd_node0001 | | Filter: (sq1.K100 = 2)