The select * from view(union all of some tables) a where a.date_column = functiongetdate takes less than 0,01 seconds against the 14s using column 2014-07-10 14:36 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx>: > Thank you. > If you are interested. > > I don't have too much time and I still couldn't get work the Partitioned > UNION-ALL views > meanwhile I found a work around to solved in 11.2.0.3 patch 15, meanwhile > I try the other solution. > > The workaround is replacing int the where with a function instead of a > column > I used another table for the date, and the problem persists, so the > problem is not the table itself but the optimizer. I gather full statitics, > etc. > > select * from view(union all of some tables) a, one_row_date_table b > where a.date_column=b.date_column > takes 14s > > select * from view(union all of some tables) a > where a.date_column = (SELECT b.date_column FROM one_row_date_table b) > takes 14s > > select * from view(union all of some tables) a > where a.date_column = functiongetdate > > > functiongetdate > is > begin > SELECT b.date_column FROM one_row_date_table b > return b.date_column > end; > > :) > > If you are interested I put both execution plans. > > DATE COLUMN=DATE COLUMN > > ------------------------------------------------------------ > > Statement Id=15 Type=UNION-ALL > Cost=0 TimeStamp=10-07-14::14::25:00 > > (1) SELECT STATEMENT ALL_ROWS > Est. Rows: 5.314.311 Cost: 30.052 > (18) SORT GROUP BY > Est. Rows: 5.314.311 Cost: 30.052 > (17) HASH JOIN > Est. Rows: 5.314.311 Cost: 29.282 > (9) NESTED LOOPS > Est. Rows: 34 Cost: 33 > (7) NESTED LOOPS > Est. Rows: 1 Cost: 2 > (5) NESTED LOOPS > Est. Rows: 1 Cost: 2 > (3) TABLE TABLE ACCESS BY INDEX ROWID > DAZ.UTL_MULTIEMPRESA_EMPRESA [Analyzed] > (3) Blocks: 5 Est. Rows: 1 of 1 Cost: 1 > Tablespace: TBL_USERS > (2) INDEX (UNIQUE) INDEX UNIQUE SCAN > DAZ.CST_USR_SIGLA [Analyzed] > Est. Rows: 1 > (4) INDEX (UNIQUE) INDEX RANGE SCAN > DAZ.IDX_MEM_FECHA_HOY [Analyzed] > Est. Rows: 1 Cost: 1 > (6) INDEX (UNIQUE) INDEX UNIQUE SCAN > DAZ.CST_IDI_CODIGO_45 [Analyzed] > Est. Rows: 1 > (8) TABLE TABLE ACCESS FULL SOA.CLIENTES_ME > [Analyzed] > (8) Blocks: 110 Est. Rows: 34 of 1.971 Cost: 31 > Tablespace: TBL_USERS > (16) VIEW VIEW SOA.CARTERA_TOTAL_RAW > Est. Rows: 1.848.456 Cost: 29.172 > (15) UNION-ALL > (10) INDEX INDEX RANGE SCAN > SOA.CARTERA_IDX$$_03C10050 [Analyzed] > Est. Rows: 15 Cost: 4 > (14) VIEW VIEW SOA.HICARTERA > Est. Rows: 1.848.441 Cost: 29.168 > (13) UNION-ALL > (11) TABLE TABLE ACCESS FULL > SOA.HICARTERA_RW [Analyzed] > (11) Blocks: 88.881 Est. Rows: 1.729.179 > of 2.183.791 Cost: 24.025 > Tablespace: TBL_USERS > (12) TABLE TABLE ACCESS FULL > SOA.HICARTERA_RO [Analyzed] > (12) Blocks: 19.105 Est. Rows: 119.262 of > 536.364 Cost: 5.143 > Tablespace: TBL_USERS > > > DATE COLUMN=FUNCTION RETURNING DATE > > ------------------------------------------------------------ > > Statement Id=15 Type=INDEX > Cost=4 TimeStamp=10-07-14::14::24:26 > > (1) SELECT STATEMENT ALL_ROWS > Est. Rows: 27.669 Cost: 3.278 > (22) SORT GROUP BY > Est. Rows: 27.669 Cost: 3.278 > (21) MERGE JOIN CARTESIAN > Est. Rows: 27.669 Cost: 3.274 > (9) NESTED LOOPS > Est. Rows: 34 Cost: 32 > (7) NESTED LOOPS > Est. Rows: 1 Cost: 1 > (5) NESTED LOOPS > Est. Rows: 1 Cost: 1 > (3) TABLE TABLE ACCESS BY INDEX ROWID > DAZ.UTL_MULTIEMPRESA_EMPRESA [Analyzed] > (3) Blocks: 5 Est. Rows: 1 of 1 Cost: 1 > Tablespace: TBL_USERS > (2) INDEX (UNIQUE) INDEX UNIQUE SCAN > DAZ.CST_USR_SIGLA [Analyzed] > Est. Rows: 1 > (4) INDEX (UNIQUE) INDEX UNIQUE SCAN > DAZ.CST_MEM_CODIGO [Analyzed] > Est. Rows: 1 > (6) INDEX (UNIQUE) INDEX UNIQUE SCAN > DAZ.CST_IDI_CODIGO_45 [Analyzed] > Est. Rows: 1 > (8) TABLE TABLE ACCESS FULL SOA.CLIENTES_ME > [Analyzed] > (8) Blocks: 110 Est. Rows: 34 of 1.971 Cost: 31 > Tablespace: TBL_USERS > (20) BUFFER SORT > Est. Rows: 802 Cost: 3.246 > (19) VIEW VIEW SOA.CARTERA_TOTAL_RAW > Est. Rows: 802 Cost: 95 > (18) UNION-ALL > (11) FILTER > (10) INDEX INDEX RANGE SCAN > SOA.CARTERA_IDX$$_03C10050 [Analyzed] > Est. Rows: 15 Cost: 4 > (17) VIEW VIEW SOA.HICARTERA > Est. Rows: 787 Cost: 91 > (16) UNION-ALL > (13) TABLE TABLE ACCESS BY INDEX ROWID > SOA.HICARTERA_RW [Analyzed] > (13) Blocks: 88.881 Est. Rows: 427 of > 2.183.791 Cost: 31 > Tablespace: TBL_USERS > (12) INDEX INDEX RANGE SCAN > SOA.IDX_HCA_LIQ_RO [Analyzed] > Est. Rows: 544 Cost: 4 > (15) TABLE TABLE ACCESS BY INDEX ROWID > SOA.HICARTERA_RO [Analyzed] > (15) Blocks: 19.105 Est. Rows: 360 of > 536.364 Cost: 60 > Tablespace: TBL_USERS > (14) INDEX INDEX RANGE SCAN > SOA.IDX_HCA_LIQ [Analyzed] > Est. Rows: 618 Cost: 6 > >