RE: Woraround A: for performance problem on view joining several tables
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "jcdrpllist@xxxxxxxxx" <jcdrpllist@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- 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
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Juan Carlos Reyes Pacheco [jcdrpllist@xxxxxxxxx]
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
Other related posts:
