I’ve seen quite a few applications with tables containing both “current” and “historical” rows. In this blog post I’m going to demonstrate how devastating this pattern to cardinality estimates can be.<\/p>\n
To illustrate this point, I’m going to create a table which stores product_ids<\/em> with prices<\/em>:<\/p>\n is_current<\/em> is 1 for current prices and 0 for the historical rows. Any given product_id<\/em> can have only one current price. Consequently, the product_id<\/em> is unique for is_current=1<\/em>. Note that this is something that a human would know intuitively, but the optimizer misses this information completely.<\/p>\n Let’s continue with generating a data set for the test case. First, I’m going to populate the table with 100000 current prices:<\/p>\n Second, I’m going to insert historical records (is_current=0):<\/p>\n Finally, I’m going to add a lot of history records for a small number of products. This will lead to several popular values which will pop up in the histogram as the endpoint values:<\/p>\n At the end, we’ve got a table with 500000 rows, of which are 100000 current:<\/p>\n To select all the prices of currently active products we have to perform the following self-join:<\/p>\n As we already know, there can be at most only one active price for any given product (remember, the optimizer doesn’t have this information!). Therefore, the self-join will be one-to-many. As a consequence, such a self-join can’t possibly return more rows than the total number of rows in the table. But let’s check the optimizer’s estimation:<\/p>\n Ouch. The optimizer estimated that the join would return 205 million rows, which is, of course, out of the ballpark.<\/p>\n By running the following query it can be verified that the largest contributor to the join cardinality are the popular values:<\/p>\n The query above assumes that there is a frequency histogram on the column prices<\/em> and a hybrid histogram for the column product_id<\/em>.<\/p>\n Luckily, there is a solution to this problem which doesn’t require any changes to the application code. The secret weapon is partitioning the table on is_current<\/em>:<\/p>\n Being able to rely on the partition statistics, the optimizer can now accurately estimate the number of “current” rows which is driving the join.<\/p>\n As we can see, this solution is very effective, but it comes with a price tag. You would need to license the enterprise edition and the partitioning option.<\/p>\n If your data model forsees storing “current” and “historical” rows in the same table, there is a chance that there will be queries doing self-joins drived by the “current” rows. This model can give rise to wrong join cardinality estimates applied on skewed data distributions. The more emphasized the skew is, the larger is the error. Table partitioning turns out to be a good solution in such cases.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" If your data model forsees storing “current” and “historical” rows in the same table, there is a chance that there will be queries doing self-joins drived by the “current” rows. This model can give rise to wrong join cardinality estimates applied on skewed data distributions. The more emphasized the skew is, the larger is the error. Table partitioning turns out to be a good solution in such cases. Continue Reading create table prices (product_id number, price number, is_current number) ;<\/code><\/pre>\nexec dbms_random.seed(1) ;\n\ninsert into prices \n select rownum,trunc(dbms_random.value(100,10000),2),1 \n from dual connect by level <=100000 ; <\/code><\/pre>\ninsert into prices \n select \n trunc(dbms_random.normal * 50000 ),\n trunc(dbms_random.value(100,10000),2),0 \n from dual connect by level <=300000 ;<\/code><\/pre>\ninsert into prices \n select mod(rownum,10),trunc(dbms_random.value(100,10000),2),0\n from dual connect by level <= 100000 ; \ncommit ; \n\nbegin\n dbms_stats.gather_table_stats( \n user,'PRICES',method_opt=>'for all columns size skewonly'\n );\nend;\n\n<\/code><\/pre>\nselect is_current,count(*) from prices group by is_current ;\nIS_CURRENT\tCOUNT(*)\n1\t 100000\n0\t 400000\n<\/code><\/pre>\nSelf-Join<\/h1>\n
select count(*) from prices active,prices history \n where active.is_current = 1 and active.product_id = history.product_id ;\n\nCOUNT(*)\n332901\n<\/code><\/pre>\n\n--------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |\n--------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 13 | | 1053 (38)| 00:00:01 |\n| 1 | SORT AGGREGATE | | 1 | 13 | | | |\n|* 2 | HASH JOIN | | 205M<\/span>| 2551M| 1984K| 1053 (38)| 00:00:01 |\n|* 3 | TABLE ACCESS FULL| PRICES | 100K| 781K| | 164 (2)| 00:00:01 |\n| 4 | TABLE ACCESS FULL| PRICES | 500K| 2441K| | 163 (1)| 00:00:01 |\n--------------------------------------------------------------------------------------\n<\/code><\/pre>\nselect \n(select \n sum(\n endpoint_repeat_count*endpoint_repeat_count*num_rows*num_rows\/\n ucs.sample_size\/ucs.sample_size\n ) \n from\n user_tab_histograms uth\n ,user_tab_col_statistics ucs\n ,user_tables ut\n where\n\t\tuth.table_name = ucs.table_name\n\t\tand uth.table_name = ut.table_name\n\t\tand uth.column_name = ucs.column_name\n\t\tand uth.table_name = 'PRICES'\n\t\tand uth.column_name = 'PRODUCT_ID'\n\t\tand (uth.endpoint_repeat_count - ucs.sample_size\/ucs.num_buckets) > 0\n)\n* (\n select \n(\n select samples from \n (\n select \n endpoint_value,\n (endpoint_number - nvl(lag(endpoint_number)over (order by endpoint_number asc) , 0 )) samples \n from user_tab_histograms \n where table_name = 'PRICES' and column_name='IS_CURRENT'\n ) where endpoint_value=1\n) \/ ( select num_rows from user_tables where table_name='PRICES' ) ratio_current\n from dual ) join_card_populars\n from dual ;\n\n205599507.076327<\/span>\n<\/code><\/pre>\nPartitioning<\/h1>\n
\ncreate table prices_partitioned\n (product_id number, price number, is_current number)\n partition by list (is_current)\n ( partition current_p values(1), partition history values(0) ) ;\n\ninsert into prices_partitioned select * from prices ;\n\ncommit ;\n\nbegin \n dbms_stats.gather_table_stats(\n user,'PRICES_PARTITIONED',method_opt=>'for all columns size skewonly'\n );\nend ;\n\/ \n<\/code><\/pre>\n\n----------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |\n----------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 13 | | 731 (1)| 00:00:01 | | |\n| 1 | SORT AGGREGATE | | 1 | 13 | | | | | |\n|* 2 | HASH JOIN | | 271K<\/span>| 3442K| 1984K| 731 (1)| 00:00:01 | | |\n| 3 | PARTITION LIST SINGLE| | 99991<\/span> | 781K| | 131 (1)| 00:00:01 | KEY | KEY |\n| 4 | TABLE ACCESS FULL | PRICES_PARTITIONED | 99991 | 781K| | 131 (1)| 00:00:01 | 1 | 1 |\n| 5 | PARTITION LIST ALL | | 500K| 2441K| | 261 (1)| 00:00:01 | 1 | 2 |\n| 6 | TABLE ACCESS FULL | PRICES_PARTITIONED | 500K| 2441K| | 261 (1)| 00:00:01 | 1 | 2 |\n----------------------------------------------------------------------------------------------------------------------\n\nselect num_rows from user_tab_partitions \n where table_name='PRICES_PARTITIONED' and partition_name='CURRENT_P';\n\nNUM_ROWS\n100000\n<\/span><\/code><\/pre>\nConclusion<\/h1>\n
References<\/h1>\n
\n