RE: Woraround A: for performance problem on view joining several tables
Date: Fri, 11 Jul 2014 15:48:48 -0700
Message-ID: <BLU179-W834ACB043046042D487887EB090_at_phx.gbl>
+1
Partition elimination in partitioned views is a working feature but Carlos has not given us enough information (full view definition, full SQL query, dbms_xplan.display_cursor listing) for us to figure out whether it is working out for him. Iggy
From: jonathan_at_jlcomp.demon.co.uk
To: jcdrpllist_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: Woraround A: for performance problem on view joining several tables
Date: Thu, 10 Jul 2014 23:25:14 +0000
It's a little hard to tell from your choice of display tool, but it looks like your view is actually: select from table1 union all ( select from table2 union all select from table3 ) where the second union all is a stored view rather than in-line view. Who knows what other variations on the basic theme you've included without giving us clues. You may find that if you ensure that Oracle KNOWS that your driver table will return one row then you can avoid the function - the simplest test would be to add "where rownum = 1" to the subquery.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Juan Carlos Reyes Pacheco [jcdrpllist_at_gmail.com]
Sent: 10 July 2014 19:36
To: ORACLE-L
Subject: Woraround A: for performance problem on view joining several tables
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 Sat Jul 12 2014 - 00:48:48 CEST