| Union all tables with View and using the Index [message #658691] |
Fri, 23 December 2016 03:45  |
preet_kumar
Messages: 204
Registered: March 2007
|
Senior Member |
|
|
I am not sure where to post this topic so i am sorry if its under wrong heading.
With Enterprise edition of Oracle one has the option of partitioning but unfortunately we cannot afford the cost and using Standard edition.
There are monthly data which we store in individual tables and also index on the date and later create a view with union all on these monthly table.Now if i search for any record with date range on this view it uses the Index whereas if i use an aggregate like avg,sum on this view etc then it takes long and does a full table scan.
Is there any solution for this with Standard edition ?
Thanks
Preet
|
|
|
|
| Re: Union all tables with View and using the Index [message #658693 is a reply to message #658691] |
Fri, 23 December 2016 04:36  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Are you sure you can't combine the monthly tables into a single one? How much data do they hold?
I got stuck with something similar a while back, 30 daily tables and a union all view. Queries against the view had awful performance. I combined the tables into a single one and all the performance problems went away.
|
|
|
|
| Re: Union all tables with View and using the Index [message #658695 is a reply to message #658691] |
Fri, 23 December 2016 05:18 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This simple example works for me no problem:create table jan(c1 number,c2 date not null);
alter table jan add constraint ck_jan check (c2>=to_date('01-01-2016','dd-mm-yyyy') and c2<to_date('01-02-2016','dd-mm-yyyy'));
create index jani on jan(c2);
create table feb(c1 number,c2 date not null);
alter table feb add constraint ck_feb check (c2>=to_date('01-02-2016','dd-mm-yyyy') and c2<to_date('01-03-2016','dd-mm-yyyy'));
create index febi on feb(c2);
insert into jan values(10,to_date('10-01-2016','dd-mm-yyyy'));
insert into feb values(10,to_date('10-02-2016','dd-mm-yyyy'));
create view jf as select * from jan union all select * from feb;
set autot trace exp
select * from jf where c2 between to_date('05-01-2016','dd-mm-yyyy') and to_date('15-01-2016','dd-mm-yyyy');
select sum(c1) from jf where c2 between to_date('05-01-2016','dd-mm-yyyy') and to_date('15-01-2016','dd-mm-yyyy');orclz> set autot trace exp
orclz> select * from jf where c2 between to_date('05-01-2016','dd-mm-yyyy') and to_date('15-01-2016','dd-mm-yyyy');
Execution Plan
----------------------------------------------------------
Plan hash value: 199891681
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 0 (0)| 00:00:01 |
| 1 | VIEW | JF | 1 | 22 | 0 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | JAN | 1 | 22 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | JANI | 1 | | 0 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| FEB | 1 | 22 | 0 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | FEBI | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C2">=TO_DATE(' 2016-01-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"C2"<=TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
5 - filter(NULL IS NOT NULL)
7 - access("C2">=TO_DATE(' 2016-01-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"C2"<=TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
orclz> select sum(c1) from jf where c2 between to_date('05-01-2016','dd-mm-yyyy') and to_date('15-01-2016','dd-mm-yyyy');
Execution Plan
----------------------------------------------------------
Plan hash value: 3688056951
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | VIEW | JF | 1 | 22 | 0 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | JAN | 1 | 22 | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | JANI | 1 | | 0 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| FEB | 1 | 22 | 0 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | FEBI | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C2">=TO_DATE(' 2016-01-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"C2"<=TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - filter(NULL IS NOT NULL)
8 - access("C2">=TO_DATE(' 2016-01-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"C2"<=TO_DATE(' 2016-01-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
orclz>
This is release 12.1.0.2, no statistics or dynamic sampling.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
