Home » RDBMS Server » Performance Tuning » Union all tables with View and using the Index (Oracle 11g)
Union all tables with View and using the Index [message #658691] Fri, 23 December 2016 03:45 Go to next message
preet_kumar
Messages: 202
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 Go to previous messageGo to next message
cookiemonster
Messages: 13292
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 Go to previous messageGo to next message
John Watson
Messages: 7623
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');
Here are the plans,
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>
sure, both plans show the query hitting both table, but they don't. Look at the filter for operation 5 in the first query and operation 6 in the asggregate query: the second table is not actually touched.
This is release 12.1.0.2, no statistics or dynamic sampling.
Re: Union all tables with View and using the Index [message #658831 is a reply to message #658695] Wed, 28 December 2016 03:52 Go to previous messageGo to next message
preet_kumar
Messages: 202
Registered: March 2007
Senior Member
Thanks for the detailed explanation.

How about the below query on the given view.In my case it does a full table scan.

select max(c1) from jf;
Re: Union all tables with View and using the Index [message #658832 is a reply to message #658831] Wed, 28 December 2016 03:58 Go to previous messageGo to next message
John Watson
Messages: 7623
Registered: January 2010
Location: Global Village
Senior Member
Come on, man. Are you trolling? Being deliberately stupid to make people angry? Or do you actually believe that anyone can assist with that amount of information?
Re: Union all tables with View and using the Index [message #658977 is a reply to message #658832] Tue, 03 January 2017 03:48 Go to previous message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unless you have an index on c1 on all the underlying tables how could oracle do anything other than full table scans?
Previous Topic: Identifying the parsing of the query (merged by MC)
Next Topic: db file sequential read causing Performance Issues
Goto Forum:
  


Current Time: Tue Oct 23 02:07:41 CDT 2018