Cartesian join with a hierarchical query (typically on DUAL) seems to be a common way of generating duplicate rows in a row set. For example, the following SQL produces 1000 rows for each row in the underlying table T1.<\/p>\n
select idx,n from t1 join \r\n (select level idx from dual connect by level <= 1000) on 1=1 \r\n order by idx,n ;<\/code><\/pre>\nFor instance, if we insert 5 integers between 1001 and 1005 into the table T1, the output of the query above will look like follows:<\/p>\n
create table t1 (n number) ;\r\ninsert into t1\r\n select level + 1000\r\n from dual connect by level < 5 ;\r\ncommit ;\r\nexec dbms_stats.gather_table_stats(NULL,'T1');\r\n\r\n IDX N\r\n---------- ----------\r\n 1 1001\r\n 1 1002\r\n 1 1003\r\n...\r\n 1000 1003\r\n 1000 1004\r\n 1000 1005\r\n\r\n5000 rows selected.<\/code><\/pre>\nObviously, it isn't that difficult to duplicate rows in a row set. Nevertheless, I'd like to highlight a little problem with this approach. What I mean by that is that the estimated cardinalities are completely wrong in such case:<\/p>\n
----------------------------------------------------------------------------------------------------------------------------- \r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | \r\n----------------------------------------------------------------------------------------------------------------------------- \r\n| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:00.01 | 3 | | | | \r\n| 1 | SORT ORDER BY | | 1 | 5 | 5000 |00:00:00.01 | 3 | 160K| 160K| 142K (0)| \r\n| 2 | MERGE JOIN CARTESIAN | | 1 | 5 | 5000<\/span> |00:00:00.01 | 3 | | | | \r\n| 3 | VIEW | | 1 | 1 | 1000<\/span> |00:00:00.01 | 0 | | | | \r\n| 4 | CONNECT BY WITHOUT FILTERING| | 1 | | 1000 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| \r\n| 5 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | | \r\n| 6 | BUFFER SORT | | 1000 | 5 | 5000 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)| \r\n| 7 | TABLE ACCESS FULL | T1 | 1 | 5 | 5 |00:00:00.01 | 3 | | | | \r\n-----------------------------------------------------------------------------------------------------------------------------<\/code><\/pre>\nAs you can see, the optimizer assumed that SELECT FROM DUAL CONNECT BY was going to return just a single row. As a consequence, the estimated cardinality for the whole SELECT is wrong by orders of magnitude. Moreover, if this join was embedded in a larger query it could even cause the butterfly-effect on the entire cardinality calculation.<\/p>\n
In the case that you just started thinking of adding the CARDINALITY(DUAL,1000) hint to the inline view, I could already tell you that this would work. Or should I better say - at least for this very simple example. Though, I don't recommend doing it because the hint might be \"lost\" during optimizer transformations which are being applied to more complex queries.<\/p>\n
So I'd like to suggest a more robust solution instead. First, you should create a table containing just the sequence of integer values up to the number of rows you'd need to duplicate, like for example:<\/p>\n
create table t_int (idx number) ;\r\ninsert into t_int\r\n select level \r\n from dual connect by level <= 1000 ;\r\ncommit ;\r\nexec dbms_stats.gather_table_stats(NULL,'T_INT');<\/code><\/pre>\nAnd then you can replace DUAL with the newly created T_INT in the join:<\/p>\n
select idx,n from t1 join t_int on 1=1 \r\n where idx <= 1000 \r\n order by idx,n ;<\/code><\/pre>\nThere you go. Since T_INT is a regular table with up-to-date statistics, the cardinalities are going to be correct now:<\/p>\n
-------------------------------------------------------------------------------------------------------------------- \r\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | \r\n-------------------------------------------------------------------------------------------------------------------- \r\n| 0 | SELECT STATEMENT | | 1 | | 5000 |00:00:00.01 | 7 | | | | \r\n| 1 | SORT ORDER BY | | 1 | 5000 | 5000 |00:00:00.01 | 7 | 232K| 232K| 206K (0)| \r\n| 2 | MERGE JOIN CARTESIAN| | 1 | 5000 | 5000<\/span> |00:00:00.01 | 7 | | | | \r\n| 3 | TABLE ACCESS FULL | T1 | 1 | 5 | 5 |00:00:00.01 | 3 | | | | \r\n| 4 | BUFFER SORT | | 5 | 1000 | 5000 |00:00:00.01 | 4 | 36864 | 36864 |32768 (0)| \r\n|* 5 | TABLE ACCESS FULL | T_INT | 1 | 1000 | 1000<\/span> |00:00:00.01 | 4 | | | | \r\n--------------------------------------------------------------------------------------------------------------------<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"How to avoid wrong cardinalities in cartesian joins Continue Reading