This blog post is a short observation on assumptions that Oracle and SQL server optimizers make to estimate the cardinality of a rowset obtained after applying an equality predicate which contains different columns of the same table, for example:<\/p>\n
select count(*) from t where n1 = n2<\/span> ;<\/code><\/pre>\nThe demo is set up as follows:<\/p>\n
\n- The table t has 100,000 rows.<\/li>\n
- The column n1 has 5 distinct values.<\/li>\n
- The column n2 has 10,000 distinct values.<\/li>\n
- The data distribution in both columns is uniform.<\/li>\n
- The most recent versions of both database products are used: Oracle 12.2 and SQL Server 2016.<\/li>\n<\/ul>\n
The conclusions are consistent over various data sets.<\/p>\n
Oracle<\/h1>\ndrop table t ;\r\ncreate table t (n1 number , n2 number ) ; \r\n\r\ninsert into t\r\n SELECT mod(level,5),mod(level,10000)\r\n FROM dual\r\n CONNECT BY level <= 100000<\/span> ;\r\n\r\nexec dbms_stats.gather_table_stats( null , 'T' ) ;\r\n\r\ncolumn column_name format a3\r\nselect column_name,num_distinct ndv,density \r\n from user_tab_columns where table_name='T' ;\r\nCOL NDV DENSITY\r\n--- ---------- ----------\r\nN1 5 .2\r\nN2 10000 .0001<\/span>\r\n\r\nselect count(*) from t where n1 = n2 ;\r\n COUNT(*)\r\n----------\r\n 50\r\n\r\nSELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);\r\n\r\n---------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n---------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | | | 23 (100)| |\r\n| 1 | SORT AGGREGATE | | 1 | 7 | | |\r\n|* 2 | TABLE ACCESS FULL| T | 10<\/span> | 70 | 23 (0)| 00:00:01 |\r\n---------------------------------------------------------------------------\r\n\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n\r\n 2 - filter(\"N1\"=\"N2\")<\/code><\/pre>\nOracle treats the less selective column (n1) as a constant and uses the density of the more selective column (n2) for the cardinality estimation.<\/strong><\/p>\nSQL Server<\/h1>\ndrop table t ;\r\ncreate table t ( n1 integer , n2 integer ) ;\r\n\r\ninsert into t select top(100000<\/span>)\r\n CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) % 5 ,\r\n CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])) % 10000\r\n FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\r\nOPTION (MAXDOP 1);\r\n\r\nset showplan_all on\r\nselect count(*) from t where n1 = n2 ;\r\n\r\nEstimateRows StmtText\r\n1\t select count(*) from t where n1 = n2 ;\r\n1\t |--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1004],0)))\r\n1\t |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))\r\n10000<\/span>\t |--Table Scan(OBJECT:([master].[dbo].[t]), WHERE:([master].[dbo].[t].[n1]=[master].[dbo].[t].[n2]))<\/code><\/pre>\nSQL Server makes the assumption that the filtered rowset will contain 10% rows of the table.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"This blog post is a short observation on assumptions that Oracle and SQL server optimizers make for estimating cardinality of a rowset obtained after applying an equality predicate which contains different columns of the same table. Continue Reading