Skip to Main Content
  • Questions
  • Performance of a VIEW on multiple tables (historical plus current)

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Laura.

Asked: August 14, 2019 - 1:45 pm UTC

Answered by: Connor McDonald - Last updated: October 31, 2019 - 2:15 am UTC

Category: Database Administration - Version: 11.2.0.3

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Table Functions, Part 2: Returning complex (non-scalar) datasets

You Asked

Good afternoon,

I have a customer that manage millions of data with a lot of tables. The thing is that we have a big table from 2013 until now which we would like to divided in two, one for the last three months and one for the rest (which we call the historical). If customer want to consult the three last months it is easy and quick but if want to consult older we have created a view with a join of the historical and the original table (now just three months of data).
We are testing the performance and with views we have no really good one in our testing lab in comparation with doing the same sql with the base table. We expect a slightly impact in the performance but it is too much.

Is there any way to improve it when the sql in against a View??
We have standard license so we can not have the option to particion.

Thank you in advanced.


and we said...

With standard edition, I would look at using "partition views", which was the predecessor to partitioning. It takes more effort to setup and maintain, but the optimizer is smart enough to avoid tables it does not need to

SQL> create table t1 as select * from dba_objects where object_id between 0 and 20000;

Table created.

SQL> create table t2 as select * from dba_objects where object_id between 20001 and 40000;

Table created.

SQL> create table t3 as select * from dba_objects where object_id between 40001 and 60000;

Table created.

SQL>
SQL> alter table t1 add constraint ck1 check (object_id between 0 and 20000);

Table altered.

SQL> alter table t2 add constraint ck2 check (object_id between 20001 and 40000);

Table altered.

SQL> alter table t3 add constraint ck3 check (object_id between 40001 and 60000);

Table altered.

SQL>
SQL> create or replace view t as
  2    select * from t1
  3    union all
  4    select * from t2
  5    union all
  6    select * from t3
  7  ;

View created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where object_id < 30;

Execution Plan
----------------------------------------------------------
Plan hash value: 3867803903

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    28 |  7224 |   331   (1)| 00:00:01 |
|   1 |  VIEW                | T    |    30 |  3668 |   101   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | T1   |    28 |  3388 |   101   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T2   |     1 |   140 |   115   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|*  7 |     TABLE ACCESS FULL| T3   |     1 |   140 |   117   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("OBJECT_ID"<30)
   4 - filter(NULL IS NOT NULL)
   5 - filter("OBJECT_ID"<30)
   6 - filter(NULL IS NOT NULL)
   7 - filter("OBJECT_ID"<30)


You can see from the FILTER lines 4 and 6, then we never actually visit tables T2 and T3 because they cannot possibly contain rows for that object_id range.


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database