Skip to Main Content
  • Questions

  • Query & application access through views vs tables - costs/benefits?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Richard.

Asked: January 03, 2002 - 12:36 pm UTC

Last updated: September 14, 2005 - 1:29 pm UTC

Version: 8.1.7.2

Viewed 1000+ times

You Asked

Tom,
I inherited a local standard for a home-grown data warehouse environment where "master" views are created for their underlying tables. They are exact replicas of the table definitions (i.e. create or replace view view_name as select * from table_name). The table name is identical to the corresponding view name except it has "_T" appended to the name to distinguish the table vs the view.

All query access for users is granted to the "master" views and not the tables if the user is referencing data within single tables. In the case of "modeled" views or "non-master" views (i.e. a view that is not an exact replica of a particular table; it may reference only certain columns within a single table or join several tables), users are granted access to the "modeled" views (and those "modeled" views reference the "master" views and not the tables directly).

The benefit is that when maintenance is performed on tables that require a drop, the grants are not affected (since the grants are on the views & not the tables).

Does this "master" view approach generally make sense? Maybe if there's only a lot of potential maintenance of the definitions? Are there implications in carrying this approach over to transactional environments as well whereby applications have grants on "master" views rather than tables directly?

Is a possible drawback performance degradation? Or is accessing the "master" view rather than its underlying table result in only a negligible performance hit? One cost is that it's another layer of definitions that needs to be maintained/compiled.

Thanks in advance for any feedback,
-Rick

and Tom said...

I actually quite like it. Think about things people seem to (for whatever reason) constantly want to do

o rename a column (trivial with a CREATE OR REPLACE VIEW)
o reorder the columns (trivial with a CREATE OR REPLACE VIEW)
o limit access to certain columns (trivial....)
o change the data you can access - filter it (trivial....)


the added one you have -- a persistent method to maintain grants is another great example.

Except for a very small hit on the parse to retrieve the view text (and remember for a common view, its a cache hit), there isn't that much of a difference between using the view and a table beyond all of the advantages you get from the view. Especially in a data warehouse where the parse time to execute time ratio is tiny (parse time is soooo small compared to the overall execution time).

I would leave the views as they are -- you'll be glad some day you did.

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why

Kulguru, January 03, 2002 - 2:11 pm UTC

". Especially in a data warehouse where the parse time to execute time ratio is tiny (parse time is soooo small compared to the overall execution time)."

Why is it so , can you expand on your statement. And is the converse true for oltp applications.

Tom Kyte
January 03, 2002 - 3:24 pm UTC

In a data warehouse, queries run in seconds.

In OLTP, queries run in hundredths or thousandths of seconds.

Parse time can take MORE time in OLTP then actual execution. Parse time could be a very large percentage of your time in OLTP if you

a) don't use bind variables
b) parse a statement more then once per session

They could in fact be as much as 90% of your execution time -- since in OLTP your actual SQL statements are fast (once compiled).


In a data warehouse you generally

a) run significantly LESS queries (you don't have 10,000 users running 1,000 TPS in a DSS system)

b) run queries that take significantly more then 1/100 of a second.



Thank-you for the validation

Bruce, January 03, 2002 - 3:54 pm UTC

Thank-you! I have trouble getting support from other DBAs when I explain to them this is why I want to create the extra views and do things this way.

I also make more differentiation in privileges in that role privileges are granted to the views and specific object privileges are granted to users (usually only object owners) at the base table level. This also helps me identify who has privileges granted directly and who has them granted via roles.


Pros of Views Outweight Cons...except maybe hiniting with views

MGP, January 04, 2002 - 10:31 am UTC

I think the benefits for view use are quite promising.. However I have experienced problems with views and optimizing complex SQL statements..

As we know Oracle does not always choose what we might consider the best way to get at the data..(Possibly due to incomplete statistics, disk/ram hardware effectiveness, or maybe they way the moon aligns with Uranus ;)) Anyway, I have found it difficult at times prodding the optimizer to use the right path to the data when views are used.... Generally I have not been able to force the use of an index on a views underlying tables and/or force a Merge Join or Nested Loop join when I feel that they will produce better runtime results. In those times I have resorted to redefining the underlying view with hints and/or at worst case resorting to using the base tables so that the hints are interpreted by the optimizer correctly..

This is at least been my experience with views in connection with complex SQL statements...


Tom Kyte
January 04, 2002 - 7:53 pm UTC

Apples and oranges I think here.

There are views against a single table -- such as the question is about.

There are complex views that ANSWER A SPECIFIC question (what you are referring to).

there can be issues when querying complex views that were not designed to answer the ultimate question. 99.999% of the time -- this is 100% unavoidable due to the semantics of the question. Sometimes, the optimizer could do better.

There is no difference between:

create view v as < complex query >;

select * from V, T1, T2, ... where ...;

and

select * from ( <complex query> ), T1, T2, ... where ... ;

all views are inlined -- if the inlined view can be MERGED, it will be as if you wrote the query straight against the base tables. If it cannot be (because it would change the semantics of the query itself), it won't be. This is where people start to say "hey, views are slow". No, the view isn't slow, its the question you asked that was slow. Many times I find that the "rewritten query against the base table" is semantically different then the query against the view (they return DIFFERENT results).

Joining to a group by is different then grouping by a join, outer joining to a join is differnt then joining to an outer join and so on. Many times, these rewritten queries are really different (meaning, if the rewritten query is CORRECT, the view was totally inappropriate to use in the first place)


Given the constraints above -- single table views -- there is nothing to be concerned about.

A pathological case?

Dany, August 13, 2002 - 2:02 am UTC

I have a specific instance where my view is most definitely slower

create view project_roles_view as
select projectid,
manager,
leader,
startdate,
lead (startdate)
over (partition by projectid, manager, leader
order by startdate) enddate
from (select distinct
projectid,
manager,
leader,
startdate
from project_status)
as read only;

Now if I issue

select * from project_roles_view where projectid = '1234';

it takes many times longer than selecting using the raw query.

Why?

Tom Kyte
August 13, 2002 - 10:22 am UTC

and what is the raw query.

I'll betcha your "raw" query is not:

select *
from (select projectid,
manager,
leader,
startdate,
lead (startdate)
over (partition by projectid, manager, leader
order by startdate) enddate
from (select distinct
projectid,
manager,
leader,
startdate
from project_status) )
where projectid = '1234'


if it isn't, we are comparing apples to toasters.

Re: A pathological case?

Dany, August 13, 2002 - 6:07 pm UTC

You are right ... my fault, I was incorrectly comparing
with

select projectid,
manager,
leader,
startdate,
lead (startdate)
over (partition by projectid, manager, leader
order by startdate) enddate
from (select distinct
projectid,
manager,
leader,
startdate
from project_status)
where projectid = '1234';

which is missing the parentheses and is therefore a toaster and not an apple.

So, is it possible to convice Oracle (8.1.7) to do the merge?

The MERGE hint? UNNEST_SUBQUERY or COMPLEX_VIEW_MERGING parameters?

Tom Kyte
August 13, 2002 - 7:31 pm UTC 

It can be hard to push into aggregates like that.  In many cases, it can alter the meanging of the query -- here I don't think it does, but the CBO seems to be doing it for me:

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into project_status select * from project_status;

102400 rows created.

(i generated 100 random rows and kept inserting them)


ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into project_status values ( '1234', 11, 11, sysdate );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index project_status_idx on project_status( projectid );

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table project_status
  2  compute statistics
  3  for table
  4  for all indexes;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select projectid,
  2         manager,
  3         leader,
  4         startdate,
  5        lead (startdate)
  6             over (partition by projectid, manager, leader
  7                   order by startdate) enddate
  8    from (select distinct
  9                 projectid,
 10                 manager,
 11                 leader,
 12                 startdate
 13            from project_status)
 14  where projectid = '1234';

PROJE    MANAGER     LEADER STARTDATE ENDDATE
----- ---------- ---------- --------- ---------
1234          11         11 13-AUG-02


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=55 Card=2049 Bytes=79911)
   1    0   WINDOW (BUFFER)
   2    1     VIEW (Cost=40 Card=2049 Bytes=79911)
   3    2       SORT (UNIQUE) (Cost=40 Card=2049 Bytes=63519)
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_STATUS' (Cost=27 Card=2049 Bytes=63519)
   5    4           INDEX (RANGE SCAN) OF 'PROJECT_STATUS_IDX' (NON-UNIQUE) (Cost=6 Card=2049)




Statistics
----------------------------------------------------------
         82  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        606  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed


and if you had an index on (projectid, manager, leader, startdate) -- it works really well.


do you have an example?

Re: A pathological case?

Dany, August 13, 2002 - 11:08 pm UTC

Thank you for your help, I do appreciate it.

If I understand correctly, then yes using the toaster the CBO does push the predicate into the view, but if you now use the apple and query with

select * from
(select projectid,
manager,
leader,
startdate,
lead (startdate)
over (partition by projectid, manager, leader
order by startdate) enddate
from (select distinct
projectid,
manager,
leader,
startdate
from project_status))
where projectid = '1234';

The you (well, I do) get a full table scan and sort.

My specific example is different to what I stated to protect the innocent, and harbour the ugly, but the essence of it remains the same.

1. There is a table that you want to extract the
distinct set of certain columns

2. There is an analytical function that you apply to
the resulting set from 1. In my case to determine
the enddate for timestamped rows by a particular
partitioning of the set.

At the very least there is some transformation of
the columns in the result set from 1 to produce
additional columns. This is an important reason
for the view.

3. You want the results of 2 to be available as a view
that you can issue arbitrary queries on, preferably
without loss of performance.

I am not forced to use a view, since I am also writing the application that issues the queries, but I agree with your sentiments about the advantages of views, in particular modularity, hence why I would like to use one here.

Thanks again for your help.

Tom Kyte
August 14, 2002 - 7:49 am UTC 

I do not get the full scan:


ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> set echo on
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from
  2  (select projectid,
  3         manager,
  4         leader,
  5         startdate,
  6        lead (startdate)
  7             over (partition by projectid, manager, leader
  8                   order by startdate) enddate
  9    from (select distinct
 10                 projectid,
 11                 manager,
 12                 leader,
 13                 startdate
 14            from project_status))
 15  where projectid = '1234';

PROJE    MANAGER     LEADER STARTDATE ENDDATE
----- ---------- ---------- --------- ---------
1234          11         11 13-AUG-02


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=55 Card=2049 Bytes=98352)
   1    0   VIEW (Cost=55 Card=2049 Bytes=98352)
   2    1     WINDOW (BUFFER)
   3    2       VIEW (Cost=40 Card=2049 Bytes=79911)
   4    3         SORT (UNIQUE) (Cost=40 Card=2049 Bytes=63519)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_STATUS' (Cost=27 Card=2049 Bytes=63519)
   6    5             INDEX (RANGE SCAN) OF 'PROJECT_STATUS_IDX' (NON-UNIQUE) (Cost=6 Card=2049)



It uses the index.


Lets see your autotrace.  Also, tell us more about the table -- what indexes are there, what is the cardinality of the various columns, what is the size of the table.

Example

Dany, August 14, 2002 - 7:14 pm UTC

I dropped all the indexes and issued the following query

08:47:38 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT v.PROJECTID,
2 v.MANAGER,
3 v.LEADER,
4 v.STARTDATE,
5 v.ENDDATE
6 FROM (SELECT PROJECTID,
7 MANAGER,
8 LEADER,
9 STARTDATE,
10 LEAD (STARTDATE - 1)
11 OVER (PARTITION BY PROJECTID, MANAGER, LEADER ORDER BY STARTDATE) ENDDATE
12 FROM PROJECT_STATUS) v
13* WHERE v.PROJECTID = '6203547821'
08:48:01 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'PROJECT_STATUS'




Statistics
----------------------------------------------------------
1671 recursive calls
298 db block gets
7471 consistent gets
20967 physical reads
31468 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1 rows processed

Next I created a primary key index with projectid as the first element ...

08:48:48 NPIVS@MDM.WORLD> ALTER TABLE PROJECT_STATUS ADD CONSTRAINT PROJECT_TABLE_PK PRIMARY KEY (PROJECTID, MANAGER, STARTDATE) ENABLE VALIDATE;

Table altered.

... then I reissued the query ...

08:51:11 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT v.PROJECTID,
2 v.MANAGER,
3 v.LEADER,
4 v.STARTDATE,
5 v.ENDDATE
6 FROM (SELECT PROJECTID,
7 MANAGER,
8 LEADER,
9 STARTDATE,
10 LEAD (STARTDATE - 1)
11 OVER (PARTITION BY PROJECTID, MANAGER, LEADER ORDER BY STARTDATE) ENDDATE
12 FROM PROJECT_STATUS) v
13* WHERE v.PROJECTID = '6203547821'
08:51:27 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'PROJECT_STATUS'




Statistics
----------------------------------------------------------
1788 recursive calls
299 db block gets
7492 consistent gets
20967 physical reads
31488 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
1 sorts (disk)
1 rows processed

... with little difference ...

Here's the query without the "view" ...

08:52:13 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT PROJECTID,
2 MANAGER,
3 LEADER,
4 STARTDATE,
5 LEAD (STARTDATE - 1)
6 OVER (PARTITION BY PROJECTID, MANAGER, LEADER ORDER BY STARTDATE) ENDDATE
7 FROM PROJECT_STATUS
8* WHERE PROJECTID = '6203547821'
08:54:39 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_STATUS'
3 2 INDEX (RANGE SCAN) OF 'PROJECT_TABLE_PK' (UNIQUE)




Statistics
----------------------------------------------------------
65 recursive calls
0 db block gets
32 consistent gets
3 physical reads
0 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

... it's significantly faster

The table is this size ...

08:54:42 NPIVS@MDM.WORLD> select count(*) from project_status;

COUNT(*)
----------
638148


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'PROJECT_STATUS'




Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
6943 consistent gets
6937 physical reads
0 redo size
369 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Okay, I have forgotten to analyze the table and indexes.

08:55:34 NPIVS@MDM.WORLD> analyze table project_status estimate statistics for table for all indexes;

Table analyzed.

... Now reissue the query on the "view"

08:57:00 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT v.PROJECTID,
2 v.MANAGER,
3 v.LEADER,
4 v.STARTDATE,
5 v.ENDDATE
6 FROM (SELECT PROJECTID,
7 MANAGER,
8 LEADER,
9 STARTDATE,
10 LEAD (STARTDATE - 1)
11 OVER (PARTITION BY PROJECTID, MANAGER, LEADER ORDER BY STARTDATE) ENDDATE
12 FROM PROJECT_STATUS) v
13* WHERE v.PROJECTID = '6203547821'
08:57:43 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9322 Card=638263 Byt
es=24892257)

1 0 VIEW (Cost=9322 Card=638263 Bytes=24892257)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'PROJECT_STATUS' (Cost=1054 Car
d=638263 Bytes=22339205)





Statistics
----------------------------------------------------------
1753 recursive calls
296 db block gets
7480 consistent gets
16766 physical reads
31528 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
1 rows processed

... nope, no difference from pre-analyze ...

Here's the query without the "view" again

08:58:24 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT PROJECTID,
2 MANAGER,
3 LEADER,
4 STARTDATE,
5 LEAD (STARTDATE - 1)
6 OVER (PARTITION BY PROJECTID, MANAGER, LEADER ORDER BY STARTDATE) ENDDATE
7 FROM PROJECT_STATUS
8* WHERE PROJECTID = '6203547821'
08:58:57 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1157 Card=6383 Bytes
=223405)

1 0 WINDOW (SORT)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_STATUS' (Cost=
1054 Card=6383 Bytes=223405)

3 2 INDEX (RANGE SCAN) OF 'PROJECT_TABLE_PK' (UNIQUE) (Cos
t=28 Card=6383)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed


... also no difference from pre-analyze

Thank you for your help.


Tom Kyte
August 14, 2002 - 8:11 pm UTC

The cardinalities look a bit off don't they --

3 2 INDEX (RANGE SCAN) OF 'PROJECT_TABLE_PK' (UNIQUE) (Cost=28 Card=6383)

it thinks there are 6300+ rows with that projectid, but there are obviously much less.

Try the analyze COMPUTE (its just 700k rows)

Also, play with the optimizer_index_cost_adj, try setting that to 35/25 (alter session)

Is it the analytical function?

Dany, August 14, 2002 - 10:46 pm UTC

Having "ANALYZE ... COMPUTE STATISTICS" and set OPTIMIZER_INDEX_COST_ADJ to 25 did not change the cardinality of either view.

It appears to be the analytical function though.

The second instance has the analytical function commented out and matches the statistics of the "non view" query in the previous review (see above).

Note that the cardinality for the query without the analytical function is still very high.

12:37:08 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT v.PROJECTID,
2 v.MANAGER,
3 v.LEADER,
4 v.STARTDATE,
5 v.ENDDATE
6 FROM (SELECT PROJECTID,
7 MANAGER,
8 LEADER,
9 STARTDATE,
10 LEAD (STARTDATE) OVER (PARTITION BY PROJECTID, MANAGER ORDER BY STARTDATE) ENDDATE
11 FROM PROJECT_STATUS) v
12* WHERE v.PROJECTID = '6203547821'
12:38:06 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE ENDDATE
---------- ---------- ---------- -------------------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9321 Card=638148 Byt
es=24887772)

1 0 VIEW (Cost=9321 Card=638148 Bytes=24887772)
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'PROJECT_STATUS' (Cost=1054 Car
d=638148 Bytes=22335180)





Statistics
----------------------------------------------------------
1671 recursive calls
296 db block gets
7472 consistent gets
14030 physical reads
31508 redo size
621 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
1 rows processed

12:38:38 NPIVS@MDM.WORLD> edit
Wrote file afiedt.sql

1 SELECT v.PROJECTID,
2 v.MANAGER,
3 v.LEADER,
4 v.STARTDATE /*,
5 v.ENDDATE */
6 FROM (SELECT PROJECTID,
7 MANAGER,
8 LEADER,
9 STARTDATE /*,
10 LEAD (STARTDATE) OVER (PARTITION BY PROJECTID, MANAGER ORDER BY STARTDATE) ENDDATE */
11 FROM PROJECT_STATUS) v
12* WHERE v.PROJECTID = '6203547821'
12:40:58 NPIVS@MDM.WORLD> /

PROJECTID MANAGER LEADER STARTDATE
---------- ---------- ---------- --------------------
6203547821 POWCP POWERCOR 01-JAN-2001 00:00:00


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=548 Card=6382 Bytes=
223370)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PROJECT_STATUS' (Cost=54
8 Card=6382 Bytes=223370)

2 1 INDEX (RANGE SCAN) OF 'PROJECT_TABLE_PK' (UNIQUE) (Cost=
28 Card=6382)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
565 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

12:41:01 NPIVS@MDM.WORLD>

Thank you for your help.

Tom Kyte
August 15, 2002 - 8:09 am UTC

No, not entirely (mine has the analytics)

go a step further and analyze compute for table, for all indexes, for all indexed columns. compute histograms.

Just to clarify...

Connor McDonald, August 15, 2002 - 5:10 am UTC

"Except for a very small hit on the parse to retrieve the view text (and remember for a common view, its a cache hit)"

I thought that the view text is not stored in the dictionary or library cache (of course the blocks from view$ would hopefully be in the buffer cache) - tkprof seems to show that every parse on a view results in a fresh query to view$.

(Not a problem in the case of the original poster)

Tom Kyte
August 15, 2002 - 8:14 am UTC

I meant buffer cache yes, it is not in the dc cache.

View spoiling my execution plan

Tracy Tupman, September 14, 2005 - 5:07 am UTC 

This is very annoying and I cannot get Oracle to do as I want. Quite a tricky one to explain but I will try:

I have a situation where a function on a column in a view is preventing the use of a primary key index on another column in the view.

The underlying table looks like this:

PAYMENTCARDS
(ID                                        NUMBER(12) NOT NULL
,ACCOUNTID                                 NUMBER(12) NOT NULL
,CARDNUMBER                                VARCHAR2(20) NOT NULL
,CARDNUMBERENCRYPTED                       VARCHAR2(32)
,S_RECORDCREATED                           DATE NOT NULL)

And there is a view on the table which was created like this: 

CREATE OR REPLACE VIEW VPAYMENTCARDS
(ID, ACCOUNTID, CARDNUMBER, S_RECORDCREATED)
AS 
SELECT ID
      ,ACCOUNTID
      ,SUBSTR(ENCRYPTION.DECRYPT(CARDNUMBERENCRYPTED),1,32) CARDNUMBER
      ,S_RECORDCREATED
FROM PAYMENTCARDS;

Basically the view returns all the same values except that it calls a decryption routine to decrypt the cardnumberencrypted column

If I run the following sql it uses the primary key index to access rows in the PaymentCards table:

SQL> l
  1  explain plan for select p.reference, c.cardnumber
  2  from paymentrecords p,
  3  paymentcards c
  4  where p.paymentCARDID = c.id (+)
  5* and p.s_recordcreated between :b1 and :b2
SQL> /

Explained.

SQL> @utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |    26 |  702 |
|  NESTED LOOPS OUTER           |                              |    26 |  702 |
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |    26 |  234 |
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|    26 |      |
|   TABLE ACCESS BY INDEX ROWID |PAYMENTCARDS                  |    10K|  175K|
|    INDEX UNIQUE SCAN          |PK_PAYMENTCARDS_ID            |    10K|      |
--------------------------------------------------------------------------------

9 rows selected.

However, if I use the view vpaymentcards instead of paymentcards it does a full table scan:

SQL> explain plan for select p.reference, c.cardnumber
  2  from paymentrecords p,
  3  vpaymentcards c
  4  where p.paymentCARDID = c.id (+)
  5  and p.s_recordcreated between :b1 and :b2
  6  .
SQL> /

Explained.

SQL> @utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |    26 |    1K|
|  HASH JOIN OUTER              |                              |    26 |    1K|
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |    26 |  234 |
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|    26 |      |
|   VIEW                        |VPAYMENTCARDS                 |    10K|  615K|
|    TABLE ACCESS FULL          |PAYMENTCARDS                  |    10K|  273K|
--------------------------------------------------------------------------------

9 rows selected.

I do not understand this behaviour. If I remove the outer join it is able to use the index to access the payment cards table:

SQL> explain plan for select p.reference, c.cardnumber
  2  from paymentrecords p,
  3  vpaymentcards c
  4  where p.paymentCARDID = c.id 
  5  and p.s_recordcreated between :b1 and :b2
  6  .
SQL> /

Explained.

SQL> @utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |     1 |   37 |
|  NESTED LOOPS                 |                              |     1 |   37 |
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |    26 |  234 |
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|    26 |      |
|   TABLE ACCESS BY INDEX ROWID |PAYMENTCARDS                  |    10K|  273K|
|    INDEX UNIQUE SCAN          |PK_PAYMENTCARDS_ID            |    10K|      |
--------------------------------------------------------------------------------

9 rows selected.

Similarly, if I remove c.cardnumber from the SELECT list it is also able to use the index:

SQL>  explain plan for select p.reference
  2  from paymentrecords p,
  3  vpaymentcards c
  4  where p.paymentCARDID = c.id (+)
  5  and p.s_recordcreated between :b1 and :b2
  6  .
SQL> /

Explained.

SQL> @utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |    26 |  338 |
|  NESTED LOOPS OUTER           |                              |    26 |  338 |
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |    26 |  234 |
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|    26 |      |
|   INDEX UNIQUE SCAN           |PK_PAYMENTCARDS_ID            |    10K|   39K|
--------------------------------------------------------------------------------

8 rows selected.

But I need the outer join for the query to be correct, and I need to SELECT c.cardnumber.
The reason for the view being there is to enable users to see the decrypted column, and I was hoping to implpement this by the use of a view
in order that the underlying application need not change any sql - the change of access is controlled by a new synonym pointing at the view rather than
the table.

To clarify, I was hoping to leave the sql completely unchanged, i.e.

select p.reference, c.cardnumber 
from paymentrecords p,
paymentcards c
where p.paymentCARDID = c.id (+)
and p.s_recordcreated between :b1 and :b2

but change the paymentcards synonym to point at vpaymentcards (in the examples above I have just used the viewname in the sql).

How can I get this sql to use the same plan whether looking at the view or the table. This is a production system, the paymentcards table is very big,
and a full table scan takes far too long to run.

It is obviously something to do with the encryption routine used by the view, however as I have proved I can get the sql to use the index on the primary 
key by modifying it slightly, but this defeats the object and gives incorrect results anyway. Why should something in the SELECT list affect the
plan in this way, and why should the removal of the outer join also allow the index to be used?

Any help with this would be enormously appreciated - I do not want to have to request an application change to get this to work. I just want the execution 
plan to stay the same.

Tom Kyte
September 14, 2005 - 8:35 am UTC

if you give me a complete example (create tables and such) I'll be glad to take a look

As it is - paymentcardid isn't in the view or the table, so I'm not sure WHAT that is.



Here are the create table statements etc

Tracy, September 14, 2005 - 10:28 am UTC 

Ok, here you are. There is no need for any data in these tables to achieve the same results, so there are no inserts here but when I have done this with tables with hundreds of thousand of rows the results are the same (whether the tables are analyzed or not).

SQL> CREATE OR REPLACE package Encryption AS
  2     FUNCTION Encrypt (input_string IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
  3     FUNCTION Decrypt (input_string IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
  4  END Encryption;
  5  /

Package created.

SQL> CREATE OR REPLACE package body Encryption AS
  2     key_string                  VARCHAR2(32)  := 'lfngjtuekwiakrltoykirntjynejwnth';
  3     encrypted_string            VARCHAR2(2048);
  4     decrypted_string            VARCHAR2(2048);
  5     padded_input                VARCHAR2(24);
  6     FUNCTION Encrypt (input_string IN VARCHAR2) RETURN VARCHAR2 IS
  7     BEGIN
  8        encrypted_string := NULL;
  9        padded_input     := RPAD(input_string,24,'~');
 10        dbms_obfuscation_toolkit.DES3Encrypt( input_string     => padded_input,
 11                                              key_string       => key_string,
 12                                              encrypted_string => encrypted_string );
 13        RETURN encrypted_string;
 14     END;
 15     FUNCTION Decrypt (input_string IN VARCHAR2) RETURN VARCHAR2 IS
 16     BEGIN
 17        decrypted_string := NULL;
 18        encrypted_string := NULL;
 19        dbms_obfuscation_toolkit.DES3Decrypt( input_string     => input_String,
 20                                              key_string       => key_string,
 21                                              decrypted_string => decrypted_string );
 22        decrypted_string := RTRIM(decrypted_string,'~');
 23        RETURN decrypted_string;
 24     END;
 25  END Encryption;
 26  /

Package body created.

SQL> 
SQL> CREATE TABLE PAYMENTCARDS
  2  (
  3    ID                   NUMBER(12)               NOT NULL,
  4    ACCOUNTID            NUMBER(12)               NOT NULL,
  5    CARDNUMBER           VARCHAR2(20)             NOT NULL,
  6    CARDNUMBERENCRYPTED  VARCHAR2(32),
  7    S_RECORDCREATED      DATE                     NOT NULL
  8  );

Table created.

SQL> ALTER TABLE PAYMENTCARDS ADD (
  2    CONSTRAINT PK_PAYMENTCARDS_ID PRIMARY KEY (ID));

Table altered.

SQL> CREATE TABLE PAYMENTRECORDS
  2  (
  3    ID               NUMBER(12)                   NOT NULL,
  4    PAYMENTCARDID    NUMBER(12),
  5    REFERENCE        VARCHAR2(15),
  6    S_RECORDCREATED  DATE                         NOT NULL
  7  );

Table created.

SQL> CREATE INDEX IX_PAYMENTRECS_S_RECORDCREATED ON PAYMENTRECORDS
  2  (S_RECORDCREATED);

Index created.

SQL> ALTER TABLE PAYMENTRECORDS ADD (
  2    CONSTRAINT PK_PAYMENTRECORDS_ID PRIMARY KEY (ID));

Table altered.

SQL> CREATE OR REPLACE VIEW VPAYMENTCARDS
  2  (ID, ACCOUNTID, CARDNUMBER, S_RECORDCREATED)
  3  AS
  4  SELECT ID
  5        ,ACCOUNTID
  6        ,SUBSTR(ENCRYPTION.DECRYPT(CARDNUMBERENCRYPTED),1,32) CARDNUMBER
  7        ,S_RECORDCREATED
  8  FROM PAYMENTCARDS;

View created.

SQL> 
SQL> ed
Wrote file afiedt.buf

  1  explain plan for select p.reference, c.cardnumber
  2      from paymentrecords p,
  3      paymentcards c
  4      where p.paymentCARDID = c.id (+)
  5*     and p.s_recordcreated between :b1 and :b2
SQL> /

Explained.

SQL> @utlxpls

Plan Table                                                                      
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |       |      | 
|  NESTED LOOPS OUTER           |                              |       |      | 
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |       |      | 
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|       |      | 
|   TABLE ACCESS BY INDEX ROWID |PAYMENTCARDS                  |       |      | 
|    INDEX UNIQUE SCAN          |PK_PAYMENTCARDS_ID            |       |      | 
--------------------------------------------------------------------------------

9 rows selected.

So a Primary key scan is used in the above, but as soon as we use the view instead it does a full table scan as below:

SQL> ed
Wrote file afiedt.buf

  1  explain plan for select p.reference, c.cardnumber
  2      from paymentrecords p,
  3      vpaymentcards c
  4      where p.paymentCARDID = c.id (+)
  5*     and p.s_recordcreated between :b1 and :b2
SQL> /

Explained.

SQL> @utlxpls

Plan Table                                                                      
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |       |      | 
|  MERGE JOIN OUTER             |                              |       |      | 
|   SORT JOIN                   |                              |       |      | 
|    TABLE ACCESS BY INDEX ROWID|PAYMENTRECORDS                |       |      | 
|     INDEX RANGE SCAN          |IX_PAYMENTRECS_S_RECORDCREATED|       |      | 
|   SORT JOIN                   |                              |       |      | 
|    VIEW                       |VPAYMENTCARDS                 |       |      | 
|     TABLE ACCESS FULL         |PAYMENTCARDS                  |       |      | 
--------------------------------------------------------------------------------

11 rows selected.

But then if I remove the outer join or remove the c.cardnumber from the SELECT list, as in the next two examples it is happy to use the Primary key index again. What I want is to keep the sql the same but still use the Primary Key to access PaymentCards. As I explained in my earlier email in reality the sql would remain identical to remove the need for application changes and I would simply use a change of synonym to point at the view rather than the table, but in my examples I am using the table or view name explicitly in the sql.

SQL> ed
Wrote file afiedt.buf

  1  explain plan for select p.reference, c.cardnumber
  2      from paymentrecords p,
  3      vpaymentcards c
  4      where p.paymentCARDID = c.id 
  5*     and p.s_recordcreated between :b1 and :b2
SQL> /

Explained.

SQL> @utlxpls

Plan Table                                                                      
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |       |      | 
|  NESTED LOOPS                 |                              |       |      | 
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |       |      | 
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|       |      | 
|   TABLE ACCESS BY INDEX ROWID |PAYMENTCARDS                  |       |      | 
|    INDEX UNIQUE SCAN          |PK_PAYMENTCARDS_ID            |       |      | 
--------------------------------------------------------------------------------

9 rows selected.

SQL> ed
Wrote file afiedt.buf

  1   explain plan for select p.reference
  2      from paymentrecords p,
  3      vpaymentcards c
  4      where p.paymentCARDID = c.id (+)
  5*     and p.s_recordcreated between :b1 and :b2
SQL> /

Explained.

SQL> @utlxpls

Plan Table                                                                      
--------------------------------------------------------------------------------
| Operation                      |  Name                        |  Rows | Bytes|
--------------------------------------------------------------------------------
| SELECT STATEMENT              |                              |       |      | 
|  NESTED LOOPS OUTER           |                              |       |      | 
|   TABLE ACCESS BY INDEX ROWID |PAYMENTRECORDS                |       |      | 
|    INDEX RANGE SCAN           |IX_PAYMENTRECS_S_RECORDCREATED|       |      | 
|   INDEX UNIQUE SCAN           |PK_PAYMENTCARDS_ID            |       |      | 
--------------------------------------------------------------------------------

8 rows selected.

SQL> spo off


I do not understand the behaviour - I do not see why a full table scan is ever necessary here. I have tried various hints too but to no avail.

Tom Kyte
September 14, 2005 - 11:11 am UTC 

you must be on 8i -- in 9i and 10g (where complex view merging is TRUE by default), this uses an index immediately.

Please contact support and ask them about the undocumented complex view merging setting, what it implies.  And remember, this'll affect all queries - not just this one so testing this is paramount.

ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV> alter session set "_complex_view_merging" = FALSE;
 
Session altered.
 
ops$tkyte@ORA817DEV> select p.reference, c.cardnumber
  2      from paymentrecords p,
  3      vpaymentcards c
  4      where p.paymentCARDID = c.id (+)
  5      and p.s_recordcreated between :b1 and :b2
  6  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN (OUTER)
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENTRECORDS'
   4    3         INDEX (RANGE SCAN) OF 'IX_PAYMENTRECS_S_RECORDCREATED' (NON-UNIQUE)
   5    1     SORT (JOIN)
   6    5       VIEW OF 'VPAYMENTCARDS'
   7    6         TABLE ACCESS (FULL) OF 'PAYMENTCARDS'
 
 
 
ops$tkyte@ORA817DEV> alter session set "_complex_view_merging" = TRUE;
 
Session altered.
 
ops$tkyte@ORA817DEV> select p.reference, c.cardnumber
  2      from paymentrecords p,
  3      vpaymentcards c
  4      where p.paymentCARDID = c.id (+)
  5      and p.s_recordcreated between :b1 and :b2
  6  /
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS (OUTER)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENTRECORDS'
   3    2       INDEX (RANGE SCAN) OF 'IX_PAYMENTRECS_S_RECORDCREATED' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENTCARDS'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_PAYMENTCARDS_ID' (UNIQUE)

Complex View Merging

Tracy, September 14, 2005 - 12:16 pm UTC

Thanks for the quick answer. Yes, we are on 8i. I have not come across this setting before, but after altering the session as you suggest it does indeed use the index in our database too. The vPaymentCards view is the only view used by the entire application (the only view belonging to the application schema anyway), so maybe it would be safe to use it at system level although I agree that testing would be essential. I have done some further reading about it (good old Google) and there seem to be many warnings about setting it unless told to do so by Oracle (they always try to make things sound scary, don't they). I reckon that if I put it in the init.ora and things went a bit askew I could easily enough take it back out again - nothing to lose except that we have had to do a couple of restarts...

Tom Kyte
September 14, 2005 - 1:29 pm UTC

please test it - you should consider working with support on this, ask least metalink search around on it.

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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library