Woraround A: for performance problem on view joining several tables
Date: Thu, 10 Jul 2014 14:36:23 -0400
Message-ID: <CAGYrQys6tJPmQy88dTsxG=v-9Kv+aK2go=pN+Ens3P6aP5w8Gg_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 10 2014 - 20:36:23 CEST