| Performance access direct table vs view [message #534856] |
Fri, 09 December 2011 13:20  |
eeriehunk
Messages: 16
Registered: May 2009
|
Junior Member |
|
|
Hello Folks,
If you have 3 tables (yr09, yr10,yr11) one with 2009 data, 2010 and 2011 data respectively. And a view (vw_yr091011) with a "union all" on all three.
Question: Will the performance be same for the following two queries ?
Question: Will Oracle read all 3 tables in the view when we search for only one year ?
select count(*) from yr09
where year = 2009;
-- vs
select count(*) from vw_yr091011
where year = 2009;
The following link says yes, the performance remains the same.
Link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21547067945222
Search for post : August 21, 2007 - 12pm
when I tried on a volume of 14000 records. The count came out same but the view took 50 more sec. And the explain plan shows it accessed all three tables. Did I miss something.
Kindly provide insight on this please.
Regards,
Aj
|
|
|
|
|
|
|
|
|
|
| Re: Performance access direct table vs view [message #534872 is a reply to message #534867] |
Fri, 09 December 2011 16:38  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
What did you execute first? View or table?
I'm agree with blackswan, though depending if the volumes need it (I assume noted volumes are test/dev and are significantly lower than production) partitioning the table (if you have the option) may be the way to go.
Nb: the only explain plan to trust is one sql*plus puts out. I've seen (for example) SQL developer give bad (i.e. inaccurate) plans for older DB versions. Certainly from the GUI option. Not on work rig atm but I assume dbms_xplan in raw code is ok.
|
|
|
|
|
|
| Re: Performance access direct table vs view [message #534913 is a reply to message #534887] |
Sat, 10 December 2011 02:49 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hello - I've just been doing a little experiment on this. Here is my test case:conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon
create table yr09(year number);
create table yr10(year number);
create table yr11(year number);
exec dbms_Stats.set_table_stats('JON','YR09',numrows=>1000000)
exec dbms_Stats.set_table_stats('JON','YR10',numrows=>1000000)
exec dbms_Stats.set_table_stats('JON','YR11',numrows=>1000000)
create view vw_yr091011 as
select * from yr09
union all
select * from yr10
union all
select * from yr11;
explain plan for
select count(*) from vw_yr091011
where year = 2009;
select * from table(dbms_xplan.display);
alter table yr09 add constraint ck09 check(year=2009);
alter table yr10 add constraint ck10 check(year=2010);
alter table yr11 add constraint ck11 check(year=2011);
explain plan for
select count(*) from vw_yr091011
where year = 2009;
select * from table(dbms_xplan.display);
Without the check constraints I get this:
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 1276644899
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 102 (19)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VW_YR091011 | 96 | 1248 | 102 (19)| 00:00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS STORAGE FULL| YR09 | 10000 | 126K| 35 (18)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL| YR10 | 10000 | 126K| 35 (18)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL| YR11 | 10000 | 126K| 35 (18)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("YEAR"=2009)
filter("YEAR"=2009)
5 - storage("YEAR"=2009)
filter("YEAR"=2009)
6 - storage("YEAR"=2009)
filter("YEAR"=2009)
No surprise there. With the constraints I get this:PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2941325854
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 102 (19)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | VIEW | VW_YR091011 | 96 | 1248 | 102 (19)| 00:00:02 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS STORAGE FULL | YR09 | 10000 | 126K| 35 (18)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS STORAGE FULL| YR10 | 10000 | 126K| 35 (18)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | TABLE ACCESS STORAGE FULL| YR11 | 10000 | 126K| 35 (18)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("YEAR"=2009)
filter("YEAR"=2009)
5 - filter(NULL IS NOT NULL)
6 - storage("YEAR"=2009)
filter("YEAR"=2009)
7 - filter(NULL IS NOT NULL)
8 - storage("YEAR"=2009)
filter("YEAR"=2009)
What is this telling me? The optimizer appears to be aware that the constraint means there can be no matching rows in two of the tables, because it is adding a filter NULL IS NOT NULL. Does this mean that two of the tables are not addressed at all? Or is it merely a fast way of throwing out all their rows?
[update: this is 11.2.0.3 EE, 32bit Vista Home Premium]
[Updated on: Sat, 10 December 2011 02:51] Report message to a moderator |
|
|
|
|
|
| Re: Performance access direct table vs view [message #534923 is a reply to message #534916] |
Sat, 10 December 2011 05:23 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Sussed.
So, Eeriehunk, to conclude: I believe your view can be as efficient as querying the table directly, if you have check constraints that tell the optimizer what is going on. I think you'll have to test a lot of queries to be sure they all work as expected.
In case anyone is interested, I modified my test case to have real data and to run the query:insert into yr09 select 2009 from dual connect by (rownum <=1000000);
insert into yr10 select 2010 from dual connect by (rownum <=1000000);
insert into yr11 select 2011 from dual connect by (rownum <=1000000);
exec dbms_stats.gather_table_stats('JON','YR09')
exec dbms_stats.gather_table_stats('JON','YR10')
exec dbms_stats.gather_table_stats('JON','YR11')
set autot on
select count(*) from vw_yr091011
where year = 2009;
alter table yr09 drop constraint ck09;
alter table yr10 drop constraint ck10;
alter table yr11 drop constraint ck11;
select count(*) from vw_yr091011
where year = 2009;Execution Plan
----------------------------------------------------------
Plan hash value: 2941325854
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1346 (2)| 00:00:17 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | VIEW | VW_YR091011 | 1000K| 3906K| 1346 (2)| 00:00:17 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS STORAGE FULL | YR09 | 999K| 3905K| 450 (2)| 00:00:06 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS STORAGE FULL| YR10 | 1 | 4 | 450 (2)| 00:00:06 |
|* 7 | FILTER | | | | | |
|* 8 | TABLE ACCESS STORAGE FULL| YR11 | 1 | 4 | 450 (2)| 00:00:06 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("YEAR"=2009)
filter("YEAR"=2009)
5 - filter(NULL IS NOT NULL)
6 - storage("YEAR"=2009)
filter("YEAR"=2009)
7 - filter(NULL IS NOT NULL)
8 - storage("YEAR"=2009)
filter("YEAR"=2009)
Statistics
----------------------------------------------------------
29 recursive calls
1 db block gets
1606 consistent gets
1568 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
and without,Execution Plan
----------------------------------------------------------
Plan hash value: 1276644899
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1346 (2)| 00:00:17
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | VIEW | VW_YR091011 | 1000K| 3906K| 1346 (2)| 00:00:17
| 3 | UNION-ALL | | | | |
|* 4 | TABLE ACCESS STORAGE FULL| YR09 | 999K| 3905K| 450 (2)| 00:00:06
|* 5 | TABLE ACCESS STORAGE FULL| YR10 | 1 | 4 | 450 (2)| 00:00:06
|* 6 | TABLE ACCESS STORAGE FULL| YR11 | 1 | 4 | 450 (2)| 00:00:06
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - storage("YEAR"=2009)
filter("YEAR"=2009)
5 - storage("YEAR"=2009)
filter("YEAR"=2009)
6 - storage("YEAR"=2009)
filter("YEAR"=2009)
Statistics
----------------------------------------------------------
37 recursive calls
3 db block gets
4858 consistent gets
4704 physical reads
0 redo size
422 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
1 rows processed
The consistent gets figures are conclusive.
|
|
|
|
| Re: Performance access direct table vs view [message #537351 is a reply to message #534923] |
Tue, 27 December 2011 20:23 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
In the old days we did what was called a manually partitioned view:
create or replace view vw_yr091011
as
select 2009 year,c2,c3... from yr09
union all
select 2010 year,c2,c3... from yr10
union all
select 2011 year,c2,c3... from yr11
/
select * from vw_yr091011 where year = 2009;
will now short circuit and only read the 2009 table at execution time. Only yr09 will be accessed.
You should easily be able to verify this with some simple timings. The manually partitioned view should be noticibly faster than the original view shown by someone above. Once you verify this, you can use the timing of this view to determine if other techniques (like the constraints method) are giving the same effect.
I have 11g 11.2.0.1 for Windows7 on my home computer. Consider the following query plan I get from autotrace when I try your situation using the manually partitioned view approach:
SQL> l
1* select * from vw_yr091011 where year = 2009
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1215290558
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 60 | 2 (0)| 00:00:01 |
| 1 | VIEW | VW_YR091011 | 3 | 60 | 2 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL | YR09 | 1 | 17 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS FULL| YR10 | 1 | 17 | 2 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS FULL| YR11 | 1 | 17 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(NULL IS NOT NULL)
6 - filter(NULL IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
398 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL>
Note in particular the two filter lines added to the query:
4 - filter(NULL IS NOT NULL)
6 - filter(NULL IS NOT NULL)
The Oracle parser added these lines to the orignal query as a cheap way to get execution to skip access of the corresponding tables. I would expect that yr10 and yr11 would not be accessed to answer the above query and that only yr09 would be touched. This will of course be relected in shorter runtimes. If other techinques achieve the same timings or show similar query predicate alterations, then you are good to go.
Quote:John Watson showed the above when he demonstrated in his posts to this thread, that constraints produce the same filter expressions. This was nice John, I am going to use this example in a class I am writing for my company, thanks man.
As to your original question, technically there will be fewer cpu cycles spent in parsing your query if you are accessing a table directly rather than using a view. But Oracle parser has gotten very good at query merging over the years so this cost gets cheaper with each release. The actual modified query will be just as fast regardless of if it came from tables or views. So overall, unless your query is a centisecond query, you will not be hindered by using views in any currently supported version of the database.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
