In this blog post I’ll be writing about a little imperfection in the Oracle optimizer which revealed itself when MINUS set operator have been used in a subquery. What I mean by that is that the cardinalities might be hugely overestimated in such case. The example I’ll be using here is just a simplification of a production query which suffered from this problem.<\/p>\n
Firstly, I’ll create some tables and fill them with data:<\/p>\n
create table t1 ( n1 number ) ;\ninsert into t1\n select 1 \n from dual connect by level <=50000 ; \n \ncreate table t2 ( n1 number ) ;\ninsert into t2\n select 2\n from dual connect by level <=100000 ;\n\ncreate table t3 ( n1 number ) ; \ninsert into t3\n select level \n from dual connect by level <=150000 ;\n\ncommit ;\n\nexec dbms_stats.gather_table_stats( null, 'T1' ) ;\nexec dbms_stats.gather_table_stats( null, 'T2' ) ;\nexec dbms_stats.gather_table_stats( null, 'T3' ) ;<\/code><\/pre>\nIt’s worth noting that the n1 column of the table t3 is very selective while the same column in the tables t1 and t2 contains just a bunch of repeating values. Now, let’s take a look at the following query and its execution plan:<\/p>\n
select \/*+ gather_plan_statistics *\/ * \nfrom t3 where n1 in (\n select n1 from t1 \n minus\n select n1 from t2 \n) ;\n\n N1\n----------\n 1\n\t\t \n-----------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 524 | | | |\n|* 1 | HASH JOIN | | 1 | 50000 | 1 |00:00:00.01 | 524 | 2545K| 2545K| 810K (0)|\n| 2 | VIEW | VW_NSO_1 | 1 | 50000 | 1 |00:00:00.01 | 275 | | | |\n| 3 | MINUS | | 1 | | 1 |00:00:00.01 | 275 | | | |\n| 4 | SORT UNIQUE<\/span> | | 1 | 50000 | 1<\/span> |00:00:00.01 | 84 | 2048 | 2048 | 2048 (0)|\n| 5 | TABLE ACCESS FULL| T1 | 1 | 50000 | 50000 |00:00:00.01 | 84 | | | |\n| 6 | SORT UNIQUE | | 1 | 100K| 1 |00:00:00.01 | 191 | 2048 | 2048 | 2048 (0)|\n| 7 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 191 | | | |\n| 8 | TABLE ACCESS FULL | T3 | 1 | 150K| 150K|00:00:00.01 | 249 | | | |\n-----------------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nAs you can see, the optimizer estimated that the subquery block with MINUS would return 50’000<\/span> rows instead of just 1<\/span>. Apparently, the NDV of t1.n1 was not used at all for estimating the number of rows that are going to be returned by the SORT UNIQUE<\/span> step.<\/p>\nTherefore, I recommended introducing DISTINCT when selecting t1.n1 as a workaround in this case. After doing so, the optimizer immediately came up with much better cardinality estimates.<\/p>\n
For instance, we can see below a significant improvement of the estimated cardinality after adding DISTINCT<\/span> into the subquery:<\/p>\nselect \/*+ gather_plan_statistics *\/ * \nfrom t3 where n1 in (\n select distinct<\/span> n1 from t1 \n minus\n select n1 from t2 \n) ;\n\n-----------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 524 | | | |\n|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 524 | 2545K| 2545K| 808K (0)|\n| 2 | VIEW | VW_NSO_1 | 1 | 1 | 1 |00:00:00.01 | 275 | | | |\n| 3 | MINUS | | 1 | | 1 |00:00:00.01 | 275 | | | |\n| 4 | SORT UNIQUE | | 1 | 1<\/span> | 1 |00:00:00.01 | 84 | 2048 | 2048 | 2048 (0)|\n| 5 | TABLE ACCESS FULL| T1 | 1 | 50000 | 50000 |00:00:00.01 | 84 | | | |\n| 6 | SORT UNIQUE | | 1 | 100K| 1 |00:00:00.01 | 191 | 2048 | 2048 | 2048 (0)|\n| 7 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 191 | | | |\n| 8 | TABLE ACCESS FULL | T3 | 1 | 150K| 150K|00:00:00.01 | 249 | | | |\n-----------------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nAll releases I’ve tested with, i.e. 11.2, 12.1 and 12.2, have behaved in the same way.<\/p>\n
SQL Server<\/h1>\n
Furthermore, I cross-checked the quality of the cardinality estimation in SQL Server.<\/p>\n
Firstly, here are the commands to create exactly the same data set:<\/p>\n
create table t1 ( n1 integer ) ;\n\ninsert into t1 select top(50000)\n 1\nFROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\nOPTION (MAXDOP 1);\n\ncreate table t2 ( n1 integer ) ;\n\ninsert into t2 select top(100000)\n 2\nFROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\nOPTION (MAXDOP 1);\n\ncreate table t3 ( n1 integer ) ;\n\ninsert into t3 select top(150000)\n ROW_NUMBER() OVER (ORDER BY s1.[object_id])\n FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\nOPTION (MAXDOP 1);<\/code><\/pre>\nSecondly, the select has to be slightly changed. I mean the key word “minus” must be replaced with “except” in SQL Server:<\/p>\n
select * \nfrom t3 where n1 in (\n select n1 from t1 \n except\n select n1 from t2 \n) ;\n\nset showplan_all on\n\nEstimateRows\tStmtText\n1 select * from t3 where n1 in ( select n1 from t1 except select n1 from t2 ) ;\n1 |--Hash Match(Inner Join, HASH:([master].[dbo].[t1].[n1])=([master].[dbo].[t3].[n1]), RESIDUAL:([master].[dbo].[t3].[n1]=[master].[dbo].[t1].[n1]))\n1 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([master].[dbo].[t1].[n1]))\n1<\/span> | |--Hash Match(Aggregate, HASH:([master].[dbo].[t1].[n1])<\/span>, RESIDUAL:([master].[dbo].[t1].[n1] = [master].[dbo].[t1].[n1]))\n50000 | | |--Table Scan(OBJECT:([master].[dbo].[t1]))\n1 | |--Top(TOP EXPRESSION:((1)))\n1\t | |--Table Scan(OBJECT:([master].[dbo].[t2]), WHERE:([master].[dbo].[t1].[n1] = [master].[dbo].[t2].[n1]))\n150000\t |--Table Scan(OBJECT:([master].[dbo].[t3]))\n<\/code><\/pre>\nObviously, the estimation was perfect even without DISTINCT.<\/p>\n
Likewise, I conducted the test on multiple releases, in particular on SQL Server 2014 and 2016.<\/p>\n
Conclusion<\/h1>\n
In summary, Oracle optimizer doesn’t seem to use columns statistics when calculating the cardinality of a result set returned by the MINUS operator. Consequently, cardinality mentioned might be massively overestimated which in turn can give rise to suboptimal execution plans. This is especially true when the first select of the MINUS operator returns a non-selective column. In such cases, SELECT DISTINCT in the subquery might be an appropriate workaround for obtaining more accurate estimation.<\/p>\n
In contrast, SQL Server optimizer produces excellent cardinality estimations event without DISTINCT.<\/p>\n
Updates<\/h1>\n2nd March 2018 – set-join conversion (SJC)<\/h2>\n
Stefan Koehler<\/a> suggested using SJC transformation instead of adding a redundant DISTINCT into the subquery. SJC is transformation which converts a set operation to JOIN. The obvious advantage of this approach is that you wouldn’t need to change each and every query to get better execution plans. On the other hand, it doesn’t seem to be a widely used feature, so some caution and extensive testing are needed before rolling it out into production.<\/p>\nIf you decide to go with it, you’ll have to explicitly enable it by setting the hidden parameter, for instance:<\/p>\n
alter session set \"_convert_set_to_join\" = true ;<\/code><\/pre>\nWe’ve got indeed the accurate estimate in our example after enabling SJC:<\/p>\n
-----------------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |\n-----------------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.05 | 527 | | | |\n|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.05 | 527 | 2545K| 2545K| 812K (0)|\n| 2 | VIEW | VW_NSO_1 | 1 | 1 | 1 |00:00:00.05 | 275 | | | |\n| 3 | HASH UNIQUE | | 1 | 1 | 1<\/span> |00:00:00.05 | 275 | 2442K| 2442K| 780K (0)|\n|* 4 | HASH JOIN ANTI | | 1 | 500 | 50000 |00:00:00.05 | 275 | 3987K| 2659K| 4476K (0)|\n| 5 | TABLE ACCESS FULL| T1 | 1 | 50000 | 50000 |00:00:00.01 | 84 | | | |\n| 6 | TABLE ACCESS FULL| T2 | 1 | 100K| 100K|00:00:00.01 | 191 | | | |\n| 7 | TABLE ACCESS FULL | T3 | 1 | 150K| 150K|00:00:00.01 | 249 | | | |\n-----------------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nApril 13, 2018 – Bug<\/h2>\n
A bug with the following reference was filed for this problem: 27853032 – IMPROVE CARDINALITY ESTIMATES FOR MINUS OPERATOR<\/p>\n
September 2, 2021 – Oracle 21c<\/h2>\n
The problem hasn’t been fixed.<\/p>\n","protected":false},"excerpt":{"rendered":"
Mind inaccurate cardinality estimations when using MINUS operator in subqueries. Continue Reading