Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: April 08, 2003 - 8:19 am UTC

Last updated: October 21, 2019 - 6:46 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I remember hearing some time ago that creating views based upon other existing views should be avoided as it can often confuse the optimiser and result in full table scans.

I expect that this is just another urban myth however I would be interested in your opinion on this issue. I think that using views within a view definition can aid readability however is there any performance impact as opposed to incuding the entire text within a single view definition?

and Tom said...

It'll never "confuse" the optimizer.

It often PRECLUDES the optimizer from doing something. If the views contain things like rownum, aggregates (group by), distincts, sorts, analytics, level, and other operations -- then it can become not possible to "push things down" into the view.

What happens (i'll use rownum cause it is easy to see the effect) is people assume a view like:

create view v as select rownum r, ename from emp;

with a query like:

select * from v where ename = 'SMITH';

should be exactly the same as:

select rownum r, ename from emp where ename = 'SMITH';

Well, quite simply -- it is not:


ops$tkyte@ORA920> create or replace view v as select rownum r, ename from emp;
View created.

ops$tkyte@ORA920> select * from v where ename = 'TURNER';

R ENAME
---------- ----------
10 TURNER

ops$tkyte@ORA920> select rownum r, ename from emp where ename = 'TURNER';

R ENAME
---------- ----------
1 TURNER


See, the view returned A DIFFERENT ANSWER here. But -- what people compare is the performance of


select * from v where ...
and
select * from emp where .....


apples and toaster ovens. Consider that a where against an outer join isn't the same as a outer join against a where and so on and you'll see where the issues come in.

It is not that the optimizer is 'confused'. It is that the queries are semantically different (as above).


So, you must be careful you are getting what you wanted when you join views -- just as you must be careful you are getting what you wanted when you skip the views. Tell me -- is 1 TURNER the right answer or is 10 TURNER?

So, don't be afraid of views of views (we just after all replace text in there and optimize). Just make sure views of views is the answer you want -- in the most optimal fashion you can get it.

read
</code> http://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1009807 <code>




Rating

  (40 ratings)

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

Comments

jens, April 08, 2003 - 3:52 pm UTC

very great rownum example there ...

here is one, where rownum causes a query to be slow:

-- this takes HOURS ...
select B.Col1
from ( -- ... although this returns only a FEW rows
-- within a tenth part of a SECOND
select A.Col1, RowNum
from <complex_view> A
where <some conditions>
) B

I took this statement from a more complex real case and couldn't figure out, what 's going on here.

and just to be honest: query doesn't only get faster, if rownum is removed - replacing "complex view" with a less complex one is another "solution".

but what is that rownum thing?

Tom Kyte
April 08, 2003 - 5:50 pm UTC

rownum is a psuedo column -- when it is in the inline view like that -- the outer parts of the query cannot be "pushed" into it -- rownum must be assigned AND THEN the outer part of the query can be done.

Don't view this as a performance nasty -- view it as a tuning tool. I use rownum in subqueries all of the time to make them go faster as well ;)

A reader, April 09, 2003 - 3:28 am UTC

yes, but I didn't get it, why it takes longer to "wrap" the results from the inner most query (taken VERY FAST) with an outer query simple as the above one. lets say: the inner query selects 3 rows, and they get their rownums assigned. what is left to be done after that for the outer query except of presenting the set, which was generated by the inner query?

tom, I don't understand, what you mean by "Don't view this as a performance nasty -- view it as a tuning tool."

Tom Kyte
April 09, 2003 - 9:47 am UTC

show us the plans - then we can comment.


as for the "as a tuning tool" -- I'm just saying for every cloud there is a silver lining. I use rownum as a tuning technique, to make queries go faster (as well as slower)

A reader, April 09, 2003 - 4:49 pm UTC

It looks horrible if I copy the output taken from SQLPLUS into this area here. I prefer other tools, but now I have to spool the explain plans. how can I get the explain plans formatted in a way that fits using SQLPLUS?

Tom Kyte
April 09, 2003 - 8:52 pm UTC

I do it all of the time? Most of my pages have it.

Just cut out a couple of blanks here and there.

A reader, April 23, 2003 - 9:57 am UTC

OK. let 's take this instead of the first sample:

create table test(a number, b varchar2(20), primary key(a))
/
insert into test(a, b) values(1, 'xxx')
/
insert into test(a, b) values(2, 'yyy')
/
insert into test(a, b) values(3, 'zzz')
/
commit
/
analyze table test compute statistics
/

set autotrace on explain statistics

this is the (fast) non rownum version:

select A.* from test

A B
---------- --------------------
1 xxx
2 yyy
3 zzz

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=3 Bytes=15)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=3 Bytes=15)

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


and here is the version

select A.*, RowNum from test A

A B ROWNUM
---------- -------------------- ----------
1 xxx 1
2 yyy 2
3 zzz 3

---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=3 Bytes=15)
1 0 COUNT
2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=3 Bytes=15)

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

after adding rownum an additional COUNT step appears.

such an additional COUNT step (in conjunction with an additional VIEW step above it) is the only difference between the explain plans (slow and fast version) of the "real world" queries (where COUNT is much more expensive!).
in general both explain plans for both related cases (slow and fast) look pretty much the same.

where does the COUNT come from?

Tom Kyte
April 23, 2003 - 7:10 pm UTC

the count step in this example doesn't do anything.

how about another example that actually makes it do something "bad"

ROWNUM is the count guy.


do this

select a.*, rownum from test a where rownum < 100;

look at that plan -- see the count STOPKEY

The COUNT in this example does nothing other then assign ROWNUM a value.

A reader, April 24, 2003 - 11:22 am UTC

so it must be the additional VIEW step, which makes the difference:

select V2.*
from (
select V1.*, RowNum
from Test V1
) V2
where V2.a < 3

leads to:

A B ROWNUM
---------- -------------------- ----------
1 xxx 1
2 yyy 2

----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=3 Bytes=114)
1 0 VIEW (Cost=1 Card=3 Bytes=114)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=3 Bytes=15)

----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
284 bytes sent via SQL*Net to client
311 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
phases COUNT and VIEW only appear in conjunction with adding the rownum column.

in the "real cases" (querying a complex view instead of a table) these two additional steps are the only differences between the execution plans for the slow and fast version - they cause a hugh difference in costs calculated there ...

but why?

Tom Kyte
April 24, 2003 - 12:29 pm UTC

rownum, aggregates, distincts, set operations (like intersect, minus) outer joins - many things can cause it to be "not possible" to push (merge) the outer query with the inner query.

For example:

select v2.*
  from ( select v1.*, rownum from test v1 ) v2
  where v2.a < 3

is very very very very totally different then

select v1.*, rownum from test v1 where v1.a < 3

So, Oracle must resolve (the view step) the inline view AND THEN apply the predicate to it.

The first query says "hey, please take the query select * from test, assign rownum to it.  Then, after you've done that -- find the A's < 3 and output those rows"

The second query says "Hey, please find the A's < 3, assign rownum and output -- you don't have to assign rownum to each and every row ok..."


ops$tkyte@ORA920> create table t ( x int, a int, data char(100) );

Table created.

ops$tkyte@ORA920> create index t_idx on t(a);

Index created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t select object_id, rownum, 'x' from all_objects;

30007 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> column data noprint
ops$tkyte@ORA920> set autotrace on
ops$tkyte@ORA920>
ops$tkyte@ORA920> select v2.*
  2    from (select v1.*, rownum from t v1 ) v2
  3   where v2.a < 3;

         X          A     ROWNUM
---------- ---------- ----------
     17602          1        127
      7516          2        128


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




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

ops$tkyte@ORA920>
ops$tkyte@ORA920> select v1.*, rownum
  2    from t v1
  3   where v1.a < 3;

         X          A     ROWNUM
---------- ---------- ----------
     17602          1          1
      7516          2          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)




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

ops$tkyte@ORA920> set autotrace off


See the difference -- the results are different but the plans are very much different.  The first has to asign rownum to each ROW AND THEN apply the predicate, the other can apply the predicate and then assign rownum to the rows.


You get what you ask for -- if you demand we "assign rownum then apply predicate", we oblige (just like if you ask us to aggregate, etc etc etc)


 

A reader, April 26, 2003 - 4:58 am UTC

my fault. sure - the predicate has to be part of the inline view, if one wants to have consecutive row numbers assigned to the result set (in my test case the SAME row numbers were generated - for SOME REASON?).

and in fact in the real world case both predicate and rownum pseudo column ARE part of the inline view (so my sample was not appropriate - sorry about that). the reason why the predicate could NOT be pushed INTO this view must be somewhere inside its body. for instance some sub-views contain OUTER joins (you mentioned above as possible cause). there are function calls in WHERE-clauses as well (does that matter?).

for rownum it is clear enough. could you please give some more details why/how/WHEN aggregates, distincts, set operations, outer joins within views prevent predicates from being pushed into them (or do they in general)?

thanks in advance.

Tom Kyte
April 26, 2003 - 8:34 am UTC

see chapter 2 of the Oracle9iR2 Performance and Tuning guide -- the section on "how the optimizer transforms sql"

might be different chapter numbers in early docs -- all docs available on otn.oracle.com under documentation.

But Oracle9i doc says that..

Tony, July 15, 2003 - 7:30 am UTC

You argue that views are not evil, but Oracle9i Database Performance Tuning Release 2,Chapter 1 says

"Views can speed up and simplify application design. A simple view definition can mask data model complexity from the programmers whose priorities are to retrieve, display, collect, and store data.

However, while views provide clean programming interfaces, they can cause sub-optimal, resource-intensive queries. The worst type of view use is when a view references other views, and when they are joined in queries. In many cases, developers can satisfy the query directly from the table without using a view. Usually, because of their inherent properties, views make it difficult for the optimizer to generate the optimal execution plan. "

This doc clearly says that views are not good. Your comment please?




Tom Kyte
July 15, 2003 - 9:59 am UTC

where did it say "views are not good"

It did what I do all of the time -- NOTHING is black, NOTHING is white. NOTHING is entirely good. NOTHING is entirely evil.

I would say the "usually" part in their statement is false.

Same strange issues with inline views

Alla, November 04, 2003 - 9:37 am UTC

Hi Tom;

I am having the same issue as Jens above.

I have a query:

select set1.supplier_number, set1.supplier_name,
set1.amount, set2.amount
from (select *
from (select supplier_number, supplier_name,
amount
from <very complex view>
where account_key = :acct_key
and time_key = :time_key_1
order by amount desc)
where rownum < 51) set1,
(select supplier_number, supplier_name,
sum(amount) as amount
from <same complex view>
where account_key = :acct_key
and time_key in (:time_key_2, :time_key_3)) set2
where set1.supplier_number = set2.supplier_number;

Each inline view executes very fast: first one in 0.1 sec, second one in 0.3 sec

I was always under the impression that Oracle would execute both inline views and then just merge them. Result sets are very small. First one is obviously 50 records, second one is about 150 records.

But when I try to run the whole query, it takes forever

Any ideas why?

Thanks in advance

Alla

P.S. I don't have an access to the server where Oracle is installed, so I can't use tkprof :-(

Tom Kyte
November 04, 2003 - 10:21 am UTC

need to see the plans (autotrace or explain plan) for

a) both inline views
b) the whole thing

lets see the results of

set timing on
set autotrace traceonly statistics
<inline view 1>
<inline view 2>
<whole thing>

Alla, November 04, 2003 - 11:09 am UTC

Is it possible to send this information directly to you, without it being published here? (I am a consultant and it does not feel right to expose all the tables etc)

Thanks a lot in advance

Tom Kyte
November 04, 2003 - 4:07 pm UTC

ask somebody if it is OK

you see, the reason this site exists is to collaborate. it is not very scalable for me to answer individual questions -- for that, you can try support and a tar. here, everything is on the out and out...

i think this would be instructive for everyone to work thru, i don't have the bandwidth really to work person by person.

Alla, November 05, 2003 - 9:14 am UTC

I dig further some more and found the following:

1. Explain plan for the whole query is not the same as explain plans for the 2 inline views, i.e. I would've assumed that the explain plan for the whole query would be something like this:
HASH JOIN
PLAN FOR THE INLINE VIEW 1
PLAN FOR THE INLINE VIEW 2

But it looked like

HASH JOIN
PLAN FOR THE INLINE VIEW THAT DOES NOT HAVE ROWNUM
(same as the explain plan for the inline view)
PLAN FOR THE INLINE VIEW WITH ROWNUM
(completely different from the explain plan)

So, it all boiled down to exactly the same problem as Jens was having.

As soon as I take the rownum out of there, it works great.

So, what I ended up doing was re-writing a query like this

select *
from (select ...
from (select ...
from ...
where ...) set1,
(select ...
from ...
where ...) set2
where set1.column1 = set2.column1
ORDER BY ...)
where rownum < 51.

I.e. instead of limiting first inline view to 50 rows and then join it to the second inline view, I let them both be as many rows as the DB returns, then join them, order by and only then limit the number of rows

Any clues????

Thanks

P.S. Sorry, I still can't send those explain plans, but I thought that at least the solution would be helpful for people even if we don't know the reason why it was not working as (at least) I expected it should

Problem in creating View

Venkat, May 04, 2004 - 12:14 pm UTC

Sir,

desc employees
empid number
ename varchar2(20)
salary number;

create or replace view v1 as select * from employees order by salary;
- the above query is working fine and a view is being created.

But

create or replace view v1 as (select * from employees order by salary);
-now this is not working. What is the error?? Why it is so??

Tom Kyte
May 04, 2004 - 2:02 pm UTC

It just is? You can do this if you must:

scott@ORA9IR2> create or replace view v1
2 as
3 select * from (select * from emp order by sal);

View created.


Enhancement of 9i

Venkatraghavan, May 18, 2004 - 9:17 am UTC

There is an enhancement made in 9i release of Oracle for
Naming a Index while creating the table.

Create table NEW_EMP
( employee_id NUMBER
PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON
NEW_EMP(employee_id)),
first_name VARCHAR2(20));

I want to know the usage of this option being enabled!!!

Tom Kyte
May 18, 2004 - 4:31 pm UTC

I don't understand the last statement?

Why might this happen?

Loz, June 28, 2004 - 5:49 am UTC

I've had similar problems with rownum. While I understand there my be reasons for things not being able to be pushed down in some queries, why would

select cv.*, rownum from complex_view cv
where
<criteria not involving rownum>

run fast, but the exact same SQL wrapped in the following inline view run like a dog?

select * from
(
select cv.*, rownum from complex_view cv
where
<criteria not involving rownum>
)

Could you explain why they are not semantically equal? The execution plans are wildly different, involving numerous table scans in the second while we have and efficient index use in the first.

Unfortunately I will be unable to post the actual code or plans, but an outline explanation of this case would be appreciated.
Thanks.


Tom Kyte
June 28, 2004 - 8:31 am UTC

they are equivalent, i'd have to see an example to see if I could explain "why"

Rownum and View

Lamya, August 05, 2004 - 3:49 pm UTC

Hi Tom,

I have a complex view

CREATE OR REPLACE VIEW VW_WEIGHT
(ID, PROJECT_ID, MOUSE_NUMBER, LABEL, GENOTYPE,
AGE, MOUSE_ID, DATASET_ID, WEIGHT, CREATED,
COMMENTS)
AS
( SELECT
ROWNUM as id,
PROJECT_ID,
MOUSE_NUMBER,
LABEL,
GENOTYPE,
AGE,
MOUSE_ID,
DATASET_ID,
WEIGHT ,
CREATED,
COMMENTS
FROM ( select
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(i.created) - trunc(mt.DOB) ) age,
i.mouse_id,
i.dataset_id,
i.weight ,
i.CREATED,
i.COMMENTS
from INVERTED_SCREEN I LEFT JOIN
MOUSE_TABLE MT ON (I.MOUSE_ID = MT.MOUSE_ID)
where i.weight is not null
union
SELECT
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(w.created) - trunc(mt.DOB) ) age,
w.mouse_id,
w.dataset_id,
w.weight ,
w.created,
w.comments
from WEIGHT W LEFT JOIN
MOUSE_TABLE MT ON (W.MOUSE_ID = MT.MOUSE_ID)
where w.weight is not null
UNION ALL
SELECT
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(d.created) - trunc(mt.DOB) ) age,
d.mouse_id,
d.dataset_id,
d.subweight as weight ,
d.CREATED,
d.comments
from DEXA d LEFT JOIN
MOUSE_TABLE MT ON (d.MOUSE_ID = MT.MOUSE_ID)
where d.subweight is not null
));

The rownum above acts as a primary key which is needed in the application . MY problem is that when I do a
desc vw_weight -

Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
PROJECT_ID NUMBER
MOUSE_NUMBER NUMBER
LABEL VARCHAR2(300)
GENOTYPE VARCHAR2(20)
AGE NUMBER
MOUSE_ID NUMBER
DATASET_ID NUMBER
WEIGHT NUMBER
CREATED DATE
COMMENTS VARCHAR2(2000)


I get weight as number while in the columns the weight is defined as number(12,5) . The reason this happens is - the extra select statement to select rownum somehow compresses the other datatypes. If I remove the select statement to select rownum then I get the weight datatype as number(12,5).
My problem is I need to have a primary key for this view and rownum acts as one so how do I get the weight datatype to be displayed as number(12,5) ? I require this as there is some software on the database which does not recognise the precision in the data as its displayed as number so the data is wrongly displayed as a whole number without the precision even though the precision is present in the database.


Tom Kyte
August 05, 2004 - 8:35 pm UTC

with views, we have little to no control over this. sorry -- it just isn't propagated up.

I can say your "rownum as a primary key" is a "horrible, misguided" idea. you really need to thing 42 times about that one.

Rownum and view

Lamya, August 05, 2004 - 10:51 pm UTC

I agree with you Tom , but I have just joined this company and am still trying to understand the application , they do not discuss the way it is coded to the DBAs all I know is that they want a PK in the view and so they decided to use rownum....the key dosent have to make sense all they need is a key...can u suggest any other way to get a PK ?

Tom Kyte
August 06, 2004 - 8:04 am UTC

umm, put a primary key into the data itself. the use of rownum here is not only meaningless, it kills performance and is just dead *wrong*

Rownum and Pk

Lamya, August 10, 2004 - 4:14 pm UTC

Tom,

We do have PKs on each table but in the view if we select PK for each table then we will have duplicate ids in the view as it is possible that id = 1 be present in all the 3 tables ie
inverted screen , weight and dexa .

CREATE OR REPLACE VIEW LVISION.VW_WEIGHT
(ID, PROJECT_ID, MOUSE_NUMBER, LABEL, GENOTYPE,
AGE, MOUSE_ID, DATASET_ID, WEIGHT, CREATED,
COMMENTS)
AS
( SELECT
ROWNUM as id,
PROJECT_ID,
MOUSE_NUMBER,
LABEL,
GENOTYPE,
AGE,
MOUSE_ID,
DATASET_ID,
WEIGHT ,
CREATED,
COMMENTS
FROM ( select
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(i.created) - trunc(mt.DOB) ) age,
i.mouse_id,
i.dataset_id,
i.weight ,
i.CREATED,
i.COMMENTS
from INVERTED_SCREEN I LEFT JOIN
MOUSE_TABLE MT ON (I.MOUSE_ID = MT.MOUSE_ID)
where i.weight is not null
union
SELECT
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(w.created) - trunc(mt.DOB) ) age,
w.mouse_id,
w.dataset_id,
w.weight ,
w.created,
w.comments
from WEIGHT W LEFT JOIN
MOUSE_TABLE MT ON (W.MOUSE_ID = MT.MOUSE_ID)
where w.weight is not null
UNION ALL
SELECT
mt.PROJ_ID as PROJECT_ID,
mt.ID as MOUSE_NUMBER,
mt.LABEL,
mt.GENOTYPE,
(trunc(d.created) - trunc(mt.DOB) ) age,
d.mouse_id,
d.dataset_id,
d.subweight as weight ,
d.CREATED,
d.comments
from DEXA d LEFT JOIN
MOUSE_TABLE MT ON (d.MOUSE_ID = MT.MOUSE_ID)
where d.subweight is not null
));







Tom Kyte
August 10, 2004 - 4:25 pm UTC

rownum is an arbitrary number and can and will change with each "select" -- sorry, but it doesn't "do anything primary key" wise.

I don't get it, it cannot be doing a thing for you, don't even understand what possible use you could be finding for it at all. It cannnot be used to update or find those rows again, it might as well be a random number.

Rownum and PK

Lamya, August 10, 2004 - 5:21 pm UTC

Hi Tom,

You are right , rownum is not used , the developers have a certain software which requires all tables and views to have a PK , it does not necessarily mean to use them.

So i just need a random number but it must be unique ...I understand its confusing ..its the same to me ...the developers are 'god' here

Tom Kyte
August 10, 2004 - 7:34 pm UTC

"all tables and views must have a pk"
"we don't use them"

nice....

rownum is dandy, but has certain "attributes" that make it not "so good" all of the time.

if they don't use them -- why do they care if it is a constant "0" or NULL or whatever???

tuning views on views

A reader, August 16, 2004 - 1:01 pm UTC

have you ever found it a pain to tune views on views
since you need to expand them to selects from tables
to tune them? what are your thoughts on this?

thank you!

Tom Kyte
August 16, 2004 - 7:42 pm UTC

i tend to keep my views one level deep. I tend not to create views of views. it can be hard to tune views of views of views of views and so on just because it hurts our brains to reconstruct everything - not because they are slow -- just because we have a hard time dereferencing everything as humans.

group by?

dxl, August 17, 2004 - 8:56 am UTC

Tom

Please could you give an example of how query plans can change (or even actual results if possible) when you have a view of views that uses group bys with eg aggregates. So i can get a better idea of what to watch out for.

(We are on 8.1.7.4 and do seem to have some instances where several "layers" of view of views are used)

Many thanks


Tom Kyte
August 17, 2004 - 9:53 am UTC

there is no difference between views of views and queries really.

It is that the "semantics" change (and many people don't realize that).

a where of a group by -- is not necessarily the same as the group by of a where.

I did give a brief example in the original answer above. unless rownum is assigned AND THEN the predicate is applied -- the answers would be different then if you apply the predicate and then assign the rownum.



Query with inline view with rownum

Mohan, October 11, 2004 - 9:14 am UTC

I have a complex query with 6 table joins. It also selects rownum as one of the field. It returns 12 rows and comes out in less than a second. Now when I use this query as an in-line view, the query does not complete and the temporary tablespace keeps growing. Is there any BUG/Patch for this error. I tested this against 8.1.7 and 9iR2 and both has the same behaviour. Please Help!!.

Tom Kyte
October 11, 2004 - 9:48 am UTC

there is no "bug" here. It is the expected behaviour with rownum.


select *
from ( select .... from ..... where ... )
where <condition>


allows for the query to be rewritten as

select .... from ..... where ... where <condition>

(view merging, the predicate and such can get pushed into the inline view)

But, as soon as you add something "set oriented" -- like rownum, aggregates, order by, intersect, minus, union and so on -- view merging cannot take place. So the inline view is "resolved" (materialized in effect) and then the rest of the query takes place on that result.


Sooooo, so us the queries and I'll explain to you why it is doing exactly what it is doing (but it is *to be expected* -- if you assign rownum, it must assign rownum). Perhaps your use of rownum is inappropriate, show us the queries.



Row Num-Problem Query.

Mohan and Pradeep, October 11, 2004 - 10:19 am UTC

Thank you Much Tom For your Help.
The follwing is the query showing probelm. It runs very fine with out outer query.( that is with out Select A.* from (....) where A.r>=2 and A.r<=8.


select A.* from
(
SELECT
A.BA_ID,
A.UTIL_CD,
A.FIRST_NAME,
A.LAST_NAME,
A.EMPLOYEE_ID,
A.SUPERVISOR,
A.DEPT_NAME,
A.ACCT_STATUS,
A.TEMP_BA_ID,
A.ACCT_TYPE,
A.SHORT_NAME,
ACCT_NBR,rownum r
FROM
INTF_BA_REF IBR,
(SELECT DISTINCT
TB.BA_ID,
TB.UTIL_CD,
B.FIRST_NAME,
B.LAST_NAME,
B.EMPLOYEE_ID,
B.SUPERVISOR,
B.DEPT_NAME,
C.ACCT_STATUS,
C.BA_ID TEMP_BA_ID,
C.ACCT_TYPE,
E.SHORT_NAME
FROM
TB_EMP_ACCOUNTS TB,
INTF_HR_EMPLOYEE B,
INTF_BA_DETAIL C,
INTF_BA_REF D,
TB_LEGAL_ENTITY_DETAIL E,
TB_CONTRACT F,
TB_CONTRACT_BA G
WHERE
TB.EMPLOYEE_ID = B.EMPLOYEE_ID(+)
AND TB.BA_ID = D.BA_ID(+)
AND TB.BA_ID = G.BA_ID(+)
AND G.CONTR_ID = F.CONTR_ID(+)
AND F.LEG_ENT_ID = E.LEG_ENT_ID(+)
AND (B.TERMINATION_DT IS NULL
OR MONTHS_BETWEEN(B.TERMINATION_DT, SYSDATE) <=12)) A
WHERE
A.BA_ID=IBR.BA_ID(+) AND
(NOT EXISTS (SELECT 1 FROM INTF_BA_REF WHERE BA_ID = A.BA_ID)
OR UPD_TIMESTAMP = (SELECT MIN(UPD_TIMESTAMP ) FROM INTF_BA_REF WHERE BA_ID(+) = A.BA_ID) )
AND A.BA_ID = A.TEMP_BA_ID
AND A.EMPLOYEE_ID LIKE ('%1%')
)A where A.r>=2 and A.r<=8
/

Number of rows in each table in the query is as follows
"TB_EMP_ACCOUNTS" 3001
"INTF_HR_EMPLOYEE" 3663
"INTF_BA_DETAIL" 9179
"INTF_BA_REF" 7052
"TB_LEGAL_ENTITY_DETAIL" 86409
"TB_CONTRACT" 23025
"TB_CONTRACT_BA" 14460


Thank you very much you in advance ....

Pradeep George.



Tom Kyte
October 11, 2004 - 10:27 am UTC

when you say "it runs fine without the select a.*" -- did you actually run the entire query (or are you using that 'awesome' tool 'toad' that just fetched the first page of results.....


for 'pagination' queries -- you always want to:

select /*+ first_rows */ a.*
from ( select x.*, rownum r
from (YOUR_QUERY_HERE_WITH_FIRST_ROWS_MOST_LIKELY_AS_WELL)
where rownum <= :MAX_ROW_TO_FETCH )
where r >= :MIN_ROW_TO_FETCH;

rather than

a) get all rows
b) assign rownum as "r" to it
c) keep just 2..8

you want to

a) get the first 8 rows
b) assigning rownum to them
c) keeping just 2 and above


which is what my query will do.

Row Num-Problem Query.Modified Still No Improvement.

Mohan &amp; Pradeep george, October 11, 2004 - 10:46 am UTC

Thanks Tom.
I modified the query as suggested by you.(it is given below). But there is no difference. I use SQL*Plus to run the qureis. And the inner query which 'Runs fine' Show me the entire result. It would say "18 rows Selected".


select /*+ first_rows */ a.* from
(
SELECT
A.BA_ID,
A.UTIL_CD,
A.FIRST_NAME,
A.LAST_NAME,
A.EMPLOYEE_ID,
A.SUPERVISOR,
A.DEPT_NAME,
A.ACCT_STATUS,
A.TEMP_BA_ID,
A.ACCT_TYPE,
A.SHORT_NAME,
ACCT_NBR,rownum r
FROM
INTF_BA_REF IBR,
(SELECT DISTINCT
TB.BA_ID,
TB.UTIL_CD,
B.FIRST_NAME,
B.LAST_NAME,
B.EMPLOYEE_ID,
B.SUPERVISOR,
B.DEPT_NAME,
C.ACCT_STATUS,
C.BA_ID TEMP_BA_ID,
C.ACCT_TYPE,
E.SHORT_NAME
FROM
TB_EMP_ACCOUNTS TB,
INTF_HR_EMPLOYEE B,
INTF_BA_DETAIL C,
INTF_BA_REF D,
TB_LEGAL_ENTITY_DETAIL E,
TB_CONTRACT F,
TB_CONTRACT_BA G
WHERE
TB.EMPLOYEE_ID = B.EMPLOYEE_ID(+)
AND TB.BA_ID = D.BA_ID(+)
AND TB.BA_ID = G.BA_ID(+)
AND G.CONTR_ID = F.CONTR_ID(+)
AND F.LEG_ENT_ID = E.LEG_ENT_ID(+)
AND (B.TERMINATION_DT IS NULL
OR MONTHS_BETWEEN(B.TERMINATION_DT, SYSDATE) <=12)) A
WHERE
A.BA_ID=IBR.BA_ID(+) AND
(NOT EXISTS (SELECT 1 FROM INTF_BA_REF WHERE BA_ID = A.BA_ID)
OR UPD_TIMESTAMP = (SELECT MIN(UPD_TIMESTAMP ) FROM INTF_BA_REF WHERE BA_ID(+) = A.BA_ID) )
AND A.BA_ID = A.TEMP_BA_ID
AND A.EMPLOYEE_ID LIKE ('%1%') and rownum<=8
)A where r>=2

Thanks for your wonderful support to Oracle Community.! !



Tom Kyte
October 11, 2004 - 10:50 am UTC

compare the plans -- do this, run BOTH queries with sql_trace=true on (and time statistics)

then, post the results of each (NOT the entire tkprof -- just the relevant portions).

You can even remove the list of columns to cut down on the size of the post

Row Num-Problem Query. TKPROF..OUT PUTS

PRADEEP and MOHAN, October 11, 2004 - 11:17 am UTC

Thanks again TOM for your support.

Query with out ROWNUM
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 144
********************************************************************************

select sumobj#, inline#, textspos, textlen, instance#
from
suminline$ where hashval=:1 order by inline#


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 1 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=1 r=1 w=0 time=146 us)
0 TABLE ACCESS BY INDEX ROWID SUMINLINE$ (cr=1 r=1 w=0 time=102 us)
0 INDEX RANGE SCAN I_SUMINLINE$_3 (cr=1 r=1 w=0 time=98 us)(object id 395)

********************************************************************************

select distinct obj#,containerobj#,pflags,xpflags,mflags
from
sum$, suminline$ where sumobj#=obj# and inline#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 1 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 SORT UNIQUE (cr=1 r=1 w=0 time=122 us)
0 NESTED LOOPS (cr=1 r=1 w=0 time=90 us)
0 TABLE ACCESS BY INDEX ROWID SUMINLINE$ (cr=1 r=1 w=0 time=88 us)
0 INDEX RANGE SCAN I_SUMINLINE$_2 (cr=1 r=1 w=0 time=83 us)(object id 394)
0 TABLE ACCESS BY INDEX ROWID SUM$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_SUM$_1 (cr=0 r=0 w=0 time=0 us)(object id 388)

********************************************************************************

SELECT
A.BA_ID,

(Query truncated).


AND A.EMPLOYEE_ID LIKE ('%1%')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.32 0.31 576 19559 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.35 0.34 576 19559 0 18

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 144



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.02 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.32 0.31 576 19559 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.35 0.34 576 19559 0 18

Misses in library cache during parse: 2


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 2 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 2 2 0 0

Misses in library cache during parse: 0

3 user SQL statements in session.
2 internal SQL statements in session.
5 SQL statements in session.
********************************************************************************
Trace file: dev90_ora_27921.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
2 internal SQL statements in trace file.
5 SQL statements in trace file.
4 unique SQL statements in trace file.
110 lines in trace file.

QUERY WITH ROW NUM


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 144
********************************************************************************

select /*+ first_rows */ a.* from
(
SELECT
A.BA_ID,
(
QUERY TRUNCATED)..

AND A.EMPLOYEE_ID LIKE ('%1%') and rownum<=8
)A where r>=2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 40.18 41.18 1197 1705119 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 40.18 41.18 1197 1705119 0 0

Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user id: 144

Rows Row Source Operation
------- ---------------------------------------------------
0 VIEW (cr=0 r=0 w=0 time=0 us)
0 COUNT STOPKEY (cr=0 r=0 w=0 time=0 us)
0 FILTER (cr=0 r=0 w=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
0 VIEW (cr=0 r=0 w=0 time=0 us)
0 SORT UNIQUE (cr=0 r=0 w=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 r=0 w=0 time=0 us)
0 HASH JOIN OUTER (cr=0 r=0 w=0 time=0 us)
920519 FILTER (cr=1705119 r=1197 w=0 time=35573812 us)
920519 NESTED LOOPS OUTER (cr=1705119 r=1197 w=0 time=33654146 us)
920519 NESTED LOOPS OUTER (cr=773831 r=495 w=0 time=18276029 us)
770970 MERGE JOIN CARTESIAN (cr=32 r=63 w=0 time=3081376 us)
257 TABLE ACCESS FULL OBJ#(40879) (cr=10 r=43 w=0 time=5087 us)
770970 BUFFER SORT (cr=22 r=20 w=0 time=1565759 us)
3001 TABLE ACCESS FULL OBJ#(40909) (cr=22 r=20 w=0 time=5208 us)
837776 INDEX RANGE SCAN OBJ#(43758) (cr=773799 r=432 w=0 time=9601461 us)(object id 43758)
10767 TABLE ACCESS BY INDEX ROWID OBJ#(42444) (cr=931288 r=702 w=0 time=9597739 us)
10767 INDEX RANGE SCAN OBJ#(43790) (cr=920521 r=284 w=0 time=7095193 us)(object id 43790)
0 TABLE ACCESS FULL OBJ#(40890) (cr=0 r=0 w=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(40941) (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN OBJ#(40942) (cr=0 r=0 w=0 time=0 us)(object id 40942)
0 TABLE ACCESS BY INDEX ROWID OBJ#(40932) (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN OBJ#(41068) (cr=0 r=0 w=0 time=0 us)(object id 41068)
0 TABLE ACCESS BY INDEX ROWID OBJ#(40870) (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN OBJ#(43758) (cr=0 r=0 w=0 time=0 us)(object id 43758)
0 INDEX RANGE SCAN OBJ#(43758) (cr=0 r=0 w=0 time=0 us)(object id 43758)
0 SORT AGGREGATE (cr=0 r=0 w=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID OBJ#(40870) (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN OBJ#(43758) (cr=0 r=0 w=0 time=0 us)(object id 43758)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 1 40.18 41.18 1197 1705119 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 40.18 41.18 1197 1705119 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0

2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: dev90_ora_28208.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
113 lines in trace file.






Tom Kyte
October 11, 2004 - 1:38 pm UTC

need plans for BOTH and why doesn't the rownum query return *any rows*. are you in the same database?? what is up with that?

ROW NUM. TKPROF..OUT PUTS SORRY

Pradeep George, October 11, 2004 - 11:38 am UTC


Extremely sorry Tom. I posted the full TKPORF output in the exitement of getting quick replies from you ! !.

Hope You would forgive ! !.

Thanks
Pradeep George.

Rownum is a bad idea

John Antony, October 12, 2004 - 12:39 am UTC

Using rownum is a bad idea. It will downgrade the performance of your query.

Is the primary key would serve your purpose?


Tom Kyte
October 12, 2004 - 7:59 am UTC

umm, if their goal is to get rows "n-m" of a result set, rownum is not only "a really good idea", it will be very fast as compared to pretty much every other method.

rownum is not slow, you are severly mistaken on that point.

in-line view performance when using rownum

Mohan &amp; Pradeep, October 12, 2004 - 1:34 am UTC

Tom,

Sorry for the confusion. The plan for both are

1) When running the inner query independently. The query returns only 18 rows.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.03 0.02 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.32 0.31 576 19559 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.35 0.34 576 19559 0 18


2)When using in-line view for pagination purpose

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 40.18 41.18 1197 1705119 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 40.18 41.18 1197 1705119 0 0


The in-line view query was running for ever and the sesssion was killed after 30 mins.
So the ROWS column is returning 0.

Still cannot understand why value for query is very very high (1705119) compared to 19559 for the first plan. Seems it is going in an infinite LOOP.

Tom Kyte
October 12, 2004 - 8:01 am UTC

those are not plans.



EXECUTION PLANS FOR ROWNUM QUERY

MOHAN and PRADEEP, October 12, 2004 - 10:32 am UTC

Tom,
Thank you for your valueable time. Posting EXECUTION PLANS.

1) EXECUTION PLAN FOR THE QUERY WITH OUT WRAPPING SELECT STATEMENT.

Rows Execution Plan(QUERY WITH OUT ROW NUM).
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 COUNT
0 FILTER
0 NESTED LOOPS (OUTER)
0 VIEW
0 SORT (UNIQUE)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 HASH JOIN (OUTER)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'INTF_BA_DETAIL'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'TB_EMP_ACCOUNTS'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_EMPACCOUNT_BA_ID' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'INTF_HR_EMPLOYEE'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_INTF_HR_EMP1' (NON-UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_NTF_BA_REF' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TB_CONTRACT_BA'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TB_CONTRACT'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'IDX_CONTRACT' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TB_LEGAL_ENTITY_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'INDX_TB_LEGAL_ENTITY_DETAIL' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INTF_BA_REF'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_NTF_BA_REF'
(NON-UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_NTF_BA_REF'
(NON-UNIQUE)
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INTF_BA_REF'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_NTF_BA_REF'
(NON-UNIQUE)



2) EXECUTION PLAN FOR QUERY USING WRAPPING SELECT STATEMENT


Rows Execution Plan(QUERY WITH ROW NUM)
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 VIEW
0 COUNT
0 FILTER
0 NESTED LOOPS (OUTER)
0 VIEW
0 SORT (UNIQUE)
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS (OUTER)
0 HASH JOIN (OUTER)
2146730 NESTED LOOPS (OUTER)
1798115 FILTER
1798115 NESTED LOOPS (OUTER)
1798115 MERGE JOIN (CARTESIAN)
599 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'INTF_BA_DETAIL'
1798115 BUFFER (SORT)
3002 TABLE ACCESS GOAL: ANALYZED (FULL)
OF 'TB_EMP_ACCOUNTS'
1783763 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'INTF_HR_EMPLOYEE'
1783763 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_INTF_HR_EMP1' (NON-UNIQUE)
2133559 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_NTF_BA_REF' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'TB_CONTRACT_BA'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'TB_CONTRACT'
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'IDX_CONTRACT' (UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'TB_LEGAL_ENTITY_DETAIL'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'INDX_TB_LEGAL_ENTITY_DETAIL' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INTF_BA_REF'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_NTF_BA_REF' (NON-UNIQUE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'IDX_NTF_BA_REF'
(NON-UNIQUE)
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'INTF_BA_REF'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'IDX_NTF_BA_REF' (NON-UNIQUE)


Tom Kyte
October 12, 2004 - 10:40 am UTC

autotrace traceonly explain -- the one with CARD= values.



autotrace traceonly explain -- the one with CARD= values.

Pradeep and Mohan, October 12, 2004 - 11:18 am UTC

Tom, Thanks again for Help. Posting Plans again

1) explain plan for query with out wrapping select


----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=183)
1 0 COUNT
2 1 FILTER
3 2 NESTED LOOPS (OUTER) (Cost=20 Card=1 Bytes=183)
4 3 VIEW (Cost=20 Card=1 Bytes=148)
5 4 SORT (UNIQUE) (Cost=20 Card=1 Bytes=172)
6 5 NESTED LOOPS (OUTER) (Cost=18 Card=1 Bytes=172)
7 6 NESTED LOOPS (OUTER) (Cost=16 Card=1 Bytes=159 )
8 7 HASH JOIN (OUTER) (Cost=15 Card=1 Bytes=151)
9 8 NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes= 141)
10 9 NESTED LOOPS (Cost=3 Card=1 Bytes=128)
11 10 NESTED LOOPS (Cost=3 Card=1 Bytes=34)
12 11 TABLE ACCESS (FULL) OF 'INTF_BA_DETA IL' (Cost=2 Card=1 Bytes=20)
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'TB _EMP_ACCOUNTS' (Cost=1 Card=1 Bytes=14)
14 13 INDEX (RANGE SCAN) OF 'IDX_EMPACCO UNT_BA_ID' (NON-UNIQUE) (Cost=1 Card=1)
15 10 TABLE ACCESS (BY INDEX ROWID) OF 'INTF _HR_EMPLOYEE'
16 15 INDEX (RANGE SCAN) OF 'IDX_INTF_HR_E MP1' (NON-UNIQUE)
17 9 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' ( NON-UNIQUE)
18 8 TABLE ACCESS (FULL) OF 'TB_CONTRACT_BA' (C ost=11 Card=13927 Bytes=139270)
19 7 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CONTRAC T' (Cost=1 Card=1 Bytes=8)
20 19 INDEX (UNIQUE SCAN) OF 'IDX_CONTRACT' (UNI QUE)
21 6 TABLE ACCESS (BY INDEX ROWID) OF 'TB_LEGAL_ENT ITY_DETAIL' (Cost=2 Card=1 Bytes=13)
22 21 INDEX (RANGE SCAN) OF 'INDX_TB_LEGAL_ENTITY_ DETAIL' (NON-UNIQUE) (Cost=1 Card=1)
23 3 TABLE ACCESS (BY INDEX ROWID) OF 'INTF_BA_REF'
24 23 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQUE )
25 2 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQUE) (C ost=1 Card=1 Bytes=13)
26 2 SORT (AGGREGATE)
27 26 TABLE ACCESS (BY INDEX ROWID) OF 'INTF_BA_REF' (Cost =1 Card=1 Bytes=22)
28 27 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQUE ) (Cost=1 Card=1)



2)PLAN FOR QUERY WITH WRAPPING SELECT
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=174)
1 0 VIEW (Cost=22 Card=1 Bytes=174)
2 1 COUNT
3 2 FILTER
4 3 NESTED LOOPS (OUTER) (Cost=22 Card=1 Bytes=183)
5 4 VIEW (Cost=22 Card=1 Bytes=148)
6 5 SORT (UNIQUE) (Cost=22 Card=1 Bytes=172)
7 6 NESTED LOOPS (OUTER) (Cost=20 Card=1 Bytes=172 )
8 7 NESTED LOOPS (OUTER) (Cost=18 Card=1 Bytes=1 59)
9 8 HASH JOIN (OUTER) (Cost=17 Card=1 Bytes=15 1)
10 9 NESTED LOOPS (OUTER) (Cost=5 Card=1 Byte s=141)
11 10 FILTER
12 11 NESTED LOOPS (OUTER)
13 12 MERGE JOIN (CARTESIAN) (Cost=5 Car d=1 Bytes=34)
14 13 TABLE ACCESS (FULL) OF 'INTF_BA_ DETAIL' (Cost=2 Card=1 Bytes=20)
15 13 BUFFER (SORT) (Cost=3 Card=2974 Bytes=41636)
16 15 TABLE ACCESS (FULL) OF 'TB_EMP _ACCOUNTS' (Cost=3 Card=2974 Bytes=41636)
17 12 TABLE ACCESS (BY INDEX ROWID) OF ' INTF_HR_EMPLOYEE'
18 17 INDEX (RANGE SCAN) OF 'IDX_INTF_ HR_EMP1' (NON-UNIQUE)
19 10 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQUE)
20 9 TABLE ACCESS (FULL) OF 'TB_CONTRACT_BA' (Cost=11 Card=13927 Bytes=139270)
21 8 TABLE ACCESS (BY INDEX ROWID) OF 'TB_CONTR ACT' (Cost=1 Card=1 Bytes=8)
22 21 INDEX (UNIQUE SCAN) OF 'IDX_CONTRACT' (U NIQUE)
23 7 TABLE ACCESS (BY INDEX ROWID) OF 'TB_LEGAL_E NTITY_DETAIL' (Cost=2 Card=1 Bytes=13)
24 23 INDEX (RANGE SCAN) OF 'INDX_TB_LEGAL_ENTIT Y_DETAIL' (NON-UNIQUE) (Cost=1 Card=1)
25 4 TABLE ACCESS (BY INDEX ROWID) OF 'INTF_BA_REF'
26 25 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQ UE)
27 3 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQUE) (Cost=1 Card=1 Bytes=13)
28 3 SORT (AGGREGATE)
29 28 TABLE ACCESS (BY INDEX ROWID) OF 'INTF_BA_REF' (Co st=1 Card=1 Bytes=22)
30 29 INDEX (RANGE SCAN) OF 'IDX_NTF_BA_REF' (NON-UNIQ UE) (Cost=1 Card=1)


Tom Kyte
October 12, 2004 - 11:28 am UTC

1798115 MERGE JOIN (CARTESIAN)
599 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'INTF_BA_DETAIL'
1798115 BUFFER (SORT)
3002 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TB_EMP_ACCOUNTS'

is what actually happened....


13 12 MERGE JOIN (CARTESIAN) (Cost=5 Car d=1 Bytes=34)
14 13 TABLE ACCESS (FULL) OF 'INTF_BA_DETAIL' (Cost=2 Card=1 Bytes=20)
15 13 BUFFER (SORT) (Cost=3 Card=2974 Bytes=41636)
16 15 TABLE ACCESS (FULL) OF 'TB_EMP _ACCOUNTS' (Cost=3 Card=2974 Bytes=41636)

is what the optimizer thought would happen (so that is "what happened")


Is it me or are you missing a join?

FROM
TB_EMP_ACCOUNTS TB,
INTF_HR_EMPLOYEE B,
INTF_BA_DETAIL C,
INTF_BA_REF D,
TB_LEGAL_ENTITY_DETAIL E,
TB_CONTRACT F,
TB_CONTRACT_BA G
WHERE
TB.EMPLOYEE_ID = B.EMPLOYEE_ID(+)
AND TB.BA_ID = D.BA_ID(+)
AND TB.BA_ID = G.BA_ID(+)
AND G.CONTR_ID = F.CONTR_ID(+)
AND F.LEG_ENT_ID = E.LEG_ENT_ID(+)
AND (B.TERMINATION_DT IS NULL
OR MONTHS_BETWEEN(B.TERMINATION_DT, SYSDATE) <=12)) A

I see tb->b
tb->d
tb->g->f->e

but what about C?


and -- are stats upto date here? I don't see how they could be...

ROW NUM FOR PAGINATION

Pradeep George and Mohan, October 13, 2004 - 7:48 am UTC

Tom,

Thank you very much for the time you spent for helping us.
Yes , We missed a join condition. Once it is added, query with ROWNUM is working fine. Since our inner query returned some rows we did n't look into it before you poiniting out the error!

Thanks once again for the wonderful support you provide for Oracle community.


Tom Kyte
October 13, 2004 - 8:37 am UTC

hopefully you saw what I was doing.....


a) get the tkprof (shows reality, shows the rows flowing through steps in the plan)

b) get the autotrace traceonly explai (shows the guess -- how many rows it THINKS will flow)

c) try to figure out where it went wrong and why


Still -- your stats are miserable aren't they??? we got *lucky* here -- the optimizer actually debugged your erroneous query by having bad stats and thinking "1 row" when there were hundreds..... so, you'll want to look into that as well.

Venkatraghavan, January 21, 2005 - 8:32 am UTC

Sir,
I have connected as sysdba. Iam creating a stand-alone procedure with 'DISABLE'. But it is not being listed in user_procedures. Whereas it does the same when i have connected as normal user.
Is a packaged procedure an object ?. if not why it is being listed out in USER_PROCEDURES.


Tom Kyte
January 21, 2005 - 9:55 am UTC

don't do that. use sysdba to shutdown, to startup, to perform maintanence


NOT to write code in.


I don't know what "disable" means in the context of a procedure.
This procedure would be owned by SYS, it would and should show up in SYS's schema.


DO NOT do this as sysdba -- sysdba is special, magic. It is not for developing code in. Not really sure what your ultimate goal is -- but it is not a good idea to modify the sys schema by adding objects to it.

all_objects view, weird missing records!

philip winfield, June 22, 2005 - 3:14 pm UTC

Tom

Congratulations on your award. I am not at all surprised you picked it up.

I have a real strange problem...

SELECT distinct(owner) FROM all_objects in one schema gives me 20 and in another 49. Any ideas what on earth this could be due to?

Thanks

Phil.

Tom Kyte
June 23, 2005 - 1:45 pm UTC

because all_objects shows you the objects YOU (currently logged in user) have access to

one of the schemas you logged into could see a different set of objects than the other.

all_objects has "security"

Why can't a predicate be pushed when using union ?

Rob van Wijk, September 30, 2005 - 10:14 am UTC

Tom,

You mentioned:

<QUOTE>
select *
from ( select .... from ..... where ... )
where <condition>


allows for the query to be rewritten as

select .... from ..... where ... where <condition>

(view merging, the predicate and such can get pushed into the inline view)

But, as soon as you add something "set oriented" -- like rownum, aggregates,
order by, intersect, minus, union and so on -- view merging cannot take place.
So the inline view is "resolved" (materialized in effect) and then the rest of
the query takes place on that result.
</QUOTE>

I understand the difference in semantics when using rownum, aggregates and so, but i have a hard time seeing the difference when applying the union operator.

Could you please show an example when

select * from
( select * from A
UNION
select * from B
) where column in (select column from C)

is different from

select * from
( select * from A where column in (select column from C)
UNION
select * from B where column in (select column from C)
) ?

Thanks in advance,
Rob.


Tom Kyte
September 30, 2005 - 11:22 am UTC

that might not view merge, but it could predicate push.


new 1: explain plan for select * from (select ename, job from emp union select dname, loc from dept ) where job in ('X')

Explained.


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2022655914

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 51 | 8 (25)| 00:00:01 |
| 1 | VIEW | | 3 | 51 | 8 (25)| 00:00:01 |
| 2 | SORT UNIQUE | | 3 | 46 | 8 (63)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL| EMP | 2 | 28 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| DEPT | 1 | 18 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

4 - filter("JOB"='X')
5 - filter("LOC"='X')

18 rows selected.


predicate was pushed.

but that is different from total view merging. Some things like rownum, analytics and the like will obviate predicate pushing

Why can't a view be merged when using a union

Rob van Wijk, October 03, 2005 - 4:23 am UTC

Tom,

You are right, the right term is view merging instead of predicate pushing.

I created a little test case to simulate our situation:

rwk@MVS_O> create table a as select object_id id,object_name name from all_objects;
rwk@MVS_O> create table b as select object_id+1 id,object_name name from all_objects;
rwk@MVS_O>
rwk@MVS_O> alter table a add primary key (id);
rwk@MVS_O> alter table b add primary key (id);
rwk@MVS_O> create index a_idx on a(name);
rwk@MVS_O> create index b_idx on b(name);
rwk@MVS_O>
rwk@MVS_O> exec dbms_stats.gather_table_stats(user,'A',method_opt=>'FOR ALL INDEXED COLUMNS');
rwk@MVS_O> exec dbms_stats.gather_table_stats(user,'B',method_opt=>'FOR ALL INDEXED COLUMNS');
rwk@MVS_O>
rwk@MVS_O> explain plan
2 for
3 select *
4 from ( select * from a
5 union
6 select * from b
7 )
8 where name in ('DUAL')
9 /
rwk@MVS_O>
rwk@MVS_O> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 240 | 10 |
| 1 | VIEW | | 8 | 240 | 10 |
| 2 | SORT UNIQUE | | 8 | 208 | 10 |
| 3 | UNION-ALL | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| A | 4 | 104 | 2 |
|* 5 | INDEX RANGE SCAN | A_IDX | 4 | | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID| B | 4 | 104 | 2 |
|* 7 | INDEX RANGE SCAN | B_IDX | 4 | | 1 |
------------------------------------------------------------------------------

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

5 - access("A"."NAME"='DUAL')
7 - access("B"."NAME"='DUAL')

Note: cpu costing is off
rwk@MVS_O>
rwk@MVS_O> delete plan_table;
rwk@MVS_O>
rwk@MVS_O> explain plan
2 for
3 select *
4 from ( select * from a
5 union
6 select * from b
7 )
8 where name in (select /*+ cardinality (dual 1) */ 'DUAL' from dual)
9 /
rwk@MVS_O>
rwk@MVS_O> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1161 | 41796 | | 655 |
|* 1 | HASH JOIN | | 1161 | 41796 | | 655 |
| 2 | VIEW | VW_NSO_1 | 1 | 6 | | 14 |
| 3 | SORT UNIQUE | | 1 | | | 14 |
| 4 | TABLE ACCESS FULL | DUAL | 1 | | | 11 |
| 5 | VIEW | | 116K| 3402K| | 640 |
| 6 | SORT UNIQUE | | 116K| 2948K| 8240K| 640 |
| 7 | UNION-ALL | | | | | |
| 8 | TABLE ACCESS FULL| A | 58062 | 1474K| | 26 |
| 9 | TABLE ACCESS FULL| B | 58063 | 1474K| | 26 |
-----------------------------------------------------------------------------

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

1 - access("from$_subquery$_001"."NAME"="VW_NSO_1"."$nso_col_1")

Note: cpu costing is off

As you can see, the predicate is being pushed but the view is not getting merged.
Any ideas why this happens ?
And do you know a way to circumvent this behaviour ?
Database version is 9.2.0.7.0

Thanks in advance,
Rob.

Tom Kyte
October 03, 2005 - 7:35 am UTC

because of the distinct implied by the union set operation, it has to be done before the outer layers are done (in general)


select *
from ( select * from A
union
select * from B ) X,
C
where x.key = c.key;


is not the same as

select * from a, c where a.key = c.key
union
select * from b, c where b.key = c.key


we need to distinct before doing other stuff.

View

Vipul Jain, October 06, 2005 - 3:26 am UTC

Hi Tom,

I am not able to create a view which shows the current members also from the table "memberstaticcout". The Table stores the clubmembers details in repect to their districts and zone and i want a view which shows the static members and also current members for a year. But there is an error while creating a view. Please help me Tom. I will appreciate your Help.

Create or Replace View MemeberCount

(StaticYear,RegionId,ZoneID,DistrictID,ClubID,StaticMemberCnt,CurrentMemberCnt)

as

SELECT memstatic.staticyear 'StaticYear'
, memcurr.REGIONID 'RegionId'
, memstatic.rizoneid 'ZoneID'
, memstatic.districtid 'DistrictID'
, memstatic.ID 'ClubID'
, nvl(memstatic.memstaticcnt,0) 'StaticMemberCnt'
, NVL (DECODE (memcurr.REGIONID, 6, memCurr.nummbrs, memCurr.actnummbrs)
, 0
) AS 'CurrentMemberCnt'
FROM
-- read data from the static table
(SELECT staticyear, rizoneid, districtid, ID, memstaticcnt
FROM memberstaticcount msc
WHERE districtid IN (9999)
) memstatic
FULL OUTER JOIN
-- data for current members
(SELECT z.regionid, ad.DISTRICTID, z.cntryid, z.rizoneid, c.ID
, c.NUMMBRS, c.actnummbrs
FROM CLUB c, ACTIVECLUBDIST ad, rizone z
WHERE c.ID = ad.ID
AND c.IDTYPE = ad.IDTYPE
AND c.cntryid = z.cntryid
AND ad.districtid = z.districtid
AND c.NUMMBRS > 0
AND c.CLUBSTATCD = 0
AND ad.districtid IN (9999)
) memcurr ON memstatic.ID = memcurr.ID


Thanks and Regards,
Vipul Jain

Views on Views and Union All

Michael, December 12, 2006 - 9:04 pm UTC

I have used the recent version sql developer and tried to create a view on 2 views using the union all operator. It came back with an error saying minus, intersect, and union was not possible in a dynamic query. Every week a report in APEX 2.2.1 would cause an udump, and would have to restart the 10.2.0.3 Database and for a week would run fine. (this precludes the first sentence in order of events). Today I thought of made one of the views a materialized view, the other tommorrow and will create a view on those. A sample of the dump of the view on views union all query is below. The work history view is

create or replace view work_history as
select * from view1
union all
select * from view2

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_opidsa+360] [PC:0x2080540] [ADDR:0x0] [UNABLE_TO_READ] []
Current SQL statement for this session:
select * from work_history where report_date between

to_number(to_char(:P4_MONTH_FROM,'YYYYMM')) and

to_number(to_char(:P4_MONTH_TO,'YYYYMM')) and

acctno = to_number(:F10312_MKEY)
----- PL/SQL Call Stack -----
object line object
handle number name
539406BC 1685 package body SYS.DBMS_SYS_SQL
50E5EFA8 4161 package body FLOWS_020200.WWV_RENDER_REPORT3
5391BE40 1369 package body FLOWS_020200.WWV_FLOW_DISP_PAGE_PLUGS
5391BE40 360 package body FLOWS_020200.WWV_FLOW_DISP_PAGE_PLUGS
53A009D0 9924 package body FLOWS_020200.WWV_FLOW
53A0F07C 255 procedure FLOWS_020200.F
41E75B30 22 anonymous block

itar has been filed, but I think what SQL Developer is teeling me it is not recommended. My question is does Oracle recommend not doing union all on views and if not why?

Tom Kyte
December 12, 2006 - 10:11 pm UTC

sure we "recommend it", in that it is entirely supported and should work without any issues.

Simple View not picking up Base table changes??

Maverick, April 02, 2008 - 9:39 am UTC

Tom, I am testing a scenario with Views and was wondering why View doesn't reflect changes from base table structure changes?

Example:

SQL> CREATE OR REPLACE vw_test1
  2  AS
  3     SELECT *
  4       FROM test1;
CREATE OR REPLACE vw_test1
                  *
ERROR at line 1:
ORA-00922: missing or invalid option 


SQL> c/vw_test1/view vw_test1
  1* CREATE OR REPLACE view vw_test1
SQL> /

View created.

SQL> select * from test1;

        ID NAME                      VALUE                                      
---------- -------------------- ----------                                      
         1                                                                      
         2                                                                      
         3                                                                      

SQL> select * from vw_test1;

        ID NAME                      VALUE                                      
---------- -------------------- ----------                                      
         1                                                                      
         2                                                                      
         3                                                                      

SQL> alter table test1 add (sal number(10,2));

Table altered.

SQL> select * from vw_test1;

        ID NAME                      VALUE                                      
---------- -------------------- ----------                                      
         1                                                                      
         2                                                                      
         3                                                                      

SQL> desc vw_test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)
 VALUE                                              NUMBER(10)

SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               VARCHAR2(20)
 VALUE                                              NUMBER(10)
 SAL                                                NUMBER(10,2)

SQL> spool off


If you see from above example, i have added an extra column and view doesn't pick up [even though the view script says select * from test1]??

I'm missing some point which is bothering me..can you clarify?

Tom Kyte
April 02, 2008 - 10:15 am UTC

because when you create a view, it is compiled. Part of that process involves taking your "*" and expanding it out.

select the text out of user views and you'll see the list of columns.


Views are simply STORED QUERIES. The query is fixed "as of the time you compiled it". It is working correctly. Views are not supposed to change because of structure changes to the base tables.


Simple View not picking up Base table changes??

Maverick, April 02, 2008 - 3:45 pm UTC

Thanks for your response Tom. But I was wondering , since I am using "Select *" in the View Script, when ever I do select from View, it runs the script and get the data from underlying tables. so shouldn't it fail when I alter base table?
Like select * will now bring all columns including new column and it will not be in View structure. So, either it has to fail or should recompile view..both are not happening though.
Tom Kyte
April 02, 2008 - 8:04 pm UTC

it does not "run a script"

a view is a compiled stored object. It is like a table in that it has a set of columns with specific datatypes.

Unless and until you MODIFY THE VIEW, the columns it accesses DO NOT CHANGE (just like a table)

It should not fail, when you compile the view "select * from t", the "*" is simply expanded as of that point in time.

Look in user views, you'll see it.


ops$tkyte%ORA11GR1> create or replace view v as select * from all_users;

View created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select text from user_views where view_name = 'V';

TEXT
-------------------------------------------------------------------------------
select "USERNAME","USER_ID","CREATED" from all_users



Neither 'failure' nor 'recompile' should or do happen - it is the very definition of a view - you create it - and it STAYS THE SAME.

ORA-7445

A, December 21, 2012 - 1:41 am UTC

Hello,
Though not sure whether the information provided below is enough for you to tell the reason for the error (ORA-7445).

We rolled back the changes and it started working without an error

We are Oracle 10.2.0.3

Trans_Details table is millions of rows and one point of time 5 million rows gets deleted / process

This was the original query in an package ...

declare

arr_ProcDataID tbl_Number := tbl_Number();

begin
If (arr_ProcDataID.count() > 0) Then
FORALL int_LoopCounter IN 1 .. arr_ProcDataID.count()
Delete From Trans_Details A
Where A.Data_Id = arr_ProcDataID(int_LoopCounter);
commit;
End If;


end;

We changed it to ...

declare

arr_ProcDataID tbl_Number := tbl_Number();

cursor cur_pdd (vSystemDeal_ID number,
vReportingDate_ID number) is
Select B.Processdata_Id Bulk collect Into arr_ProcDataID
From Trans_Details B
Where B.SystemDeal_ID = vSystemDeal_ID
AND B.ReportingDate_ID = vReportingDate_ID;

begin

<<some other lines >>

open cur_pdd (vSystemDeal_ID,vReportingDate_ID);
loop
fetch cur_pdd bulk collect into arr_ProcDataID
LIMIT 20000;
EXIT WHEN cur_pdd%NOTFOUND;
for i in 1 .. arr_ProcDataID.count
loop
Delete From Trans_Details A Where A.Data_Id =
arr_ProcDataID(i);
end loop;
commit write batch nowait;
end loop;
close cur_pdd;
end;


Details of trace files is ....

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [pfr_v3_tab_handler+293] [PC:0x6390329] [ADDR:0xFFFFFFFFFFFFFFFF] [UNABLE_TO_READ] []


begin PKG_trans.proc_pd(VDEAL_ASSETCLASS_TYPE=>:VDEAL_ASSETCLASS_TYPE, VSYSTEMDEAL_ID=>:VSYSTEMDEAL_ID, VUSER_ID=>:VUSER_ID, VROLE_ID=>:VROLE_ID, VCALLING_BUTTON_TYPE=>:VCALLING_BUTTON_TYPE, VREPORTINGDATE_ID=>:VREPORTINGDATE_ID, CUR_OUT=>:CUR_OUT); end;
----- PL/SQL Call Stack -----
object line object
handle number name
0000000890F4DAC0 3762 package body PKG_trans.proc_pd
0000000890F4DAC0 4080 package body PKG_trans.proc_pd

0000000890ED8CC0 1 anonymous block
check trace file c:\oracle\product\10.2.0\db_1\rdbms\trace\cdosadb_ora_0.trc for preloading .sym file messages



Thanks
Tom Kyte
January 02, 2013 - 12:13 pm UTC

ora-7445
ora-600
ora-3113

support issues, something has happened that should not have happened.



if you are saying:

Trans_Details table is millions of rows and one point of time 5 million rows
gets deleted / process


and mean that in your plsql collection you have 5,000,000 items - you are doing it wrong, that much I feel strongly about. A few thousand at most - millions of things in an array - not sensible.


In looking at your code:




declare

  arr_ProcDataID tbl_Number := tbl_Number();

  cursor cur_pdd (vSystemDeal_ID number, 
                 vReportingDate_ID number) is 
  Select B.Processdata_Id Bulk collect Into arr_ProcDataID
  From Trans_Details B
  Where B.SystemDeal_ID = vSystemDeal_ID
  AND B.ReportingDate_ID = vReportingDate_ID;

begin

<<some other lines >>

 open cur_pdd (vSystemDeal_ID,vReportingDate_ID);
 loop
    fetch cur_pdd bulk collect into arr_ProcDataID 
     LIMIT 20000;
     EXIT WHEN cur_pdd%NOTFOUND; 
     for i in 1 .. arr_ProcDataID.count
     loop
         Delete From Trans_Details A Where A.Data_Id =  
        arr_ProcDataID(i);
     end loop;
     commit write batch nowait;                    
 end loop;
 close cur_pdd;      
end;



I cannot fathom why there is any procedural code here at all??? why would you query data to just turn around and send it back to be deleted?

just delete


delete from trans_details b
where b.systemdeal = VsystemDeal_id
and b.reportingdate_id = vReportingDate_id;


Also, your procedural code has a *serious* bug in it. You code loops like that in this fashion:

loop
   fetch C bulk collect into array;
   forall i in 1 .. array.count 
       delete ....;
   exit when c%notfound;
end loop;


see where my exit is - see where yours is... what happens in your code when there are 19,999 rows left in the cursor (or less)... you forget to process them!!!




So, remove your buggy procedural code and go with the single delete statement.




Also, if there are not many more than 5,000,000 rows in this table, consider (absolutely consider) not using delete at all - use a CREATE TABLE as select to save the rows you want to keep instead of using the inefficient delete statement for so much data...

Oracle views

Lise, January 11, 2013 - 5:54 am UTC

Hi Tom,
I am using 11.1.0.7, and I believe that quering the DBA_ Oracle views is faster than selecting from ALL_ views. I am using Oracle advanced queuing you see and I need to gather information from the DBA_QUEUES view for all the queues that are ENABLED, of type NORMAL, and owned by the AQ schema. It then joins on to DBA_TAB_PRIVS to determine whether the user has the privs to see any of the above queues.
These DML statements will live in a PL/SQL package.
I am happy for the GRANTs to DBA views to be issued, but I want to make sure the overall solution is performant and secure.

Any thoughts much appreciated.

Tom Kyte
January 15, 2013 - 9:03 am UTC

as far as performant goes - you'd be able to evaluate that yourself, benchmark it.

(make sure your dictionary stats are representative - but yes - the ALL_* views do different work - lots more security checking going on in there)


as for security - that is also something you'll have to decide. Is it OK in your site for this user to have select on these tables? If you wanted to lock it down a little bit more you could

o create a new user with create session, create view, grant on these views
o create a view of the DBA views that selects out *just* the relevant items
o grant on these views
o lock the account

that way you've isolated the "highly privileged access" and you know the development account cannot abuse the access to the DBA views.

You don't have to do this - it would be something you could do if you want to lock it down a bit more (and would help document what you are doing... why you have this privilege)

Jess, October 11, 2019 - 3:28 pm UTC

Hello Oracle Masters,

> But, as soon as you add something "set oriented" ... union and so on -- view merging cannot take place. So the inline view is "resolved" (materialized in effect) and then the rest of the query takes place on that result.

We have a view that's quadruple digits of lines long (with a plan that's over 300 lines long) that essentially looks like so:

select a, b, c
from big-bad-view1, tab1
where v1.x = t1.x
UNION
.....

There is a 100+ of these individual selects union'd together. There are 5 big bad views, so a number of unions go against each of those. The tables these are join to are pretty miscellaneous and not too bad. The big bad views are themselves views on top of view on top of views 5-6-7 levels deep. There are a couple of distincts in some of the unions, but not many.

99% of queries against this view are for 1 record (i.e., "select from this-view where colA = 'ABC'", which returns on average about 50 records.

Selects take a long time, and we are seeing locks on underlying tables...

This is a critical system, and there is no appetite to have the logic rewritten from scratch (for this view or the underlying big-bad ones).

I am trying to work out how (and if!) the performance can be improved given the restrictions. This thread is relevant, but not sure what to do, as can't get away from unions... I have refactored the big bad views (so that there's a 'with' clause for them, and that's what's referenced in the unions instead of each union having a direct reference), and I've been trying to work out if creating this view as "with read only" could help.

Is there anything else that can be done to improve it?
Connor McDonald
October 21, 2019 - 6:46 am UTC

Selects take a long time, and we are seeing locks on underlying tables...


A query is not going to *lock* a table, what is perhaps more likely is that the time taken to *parse* the query is block other queries from being able to parse as well.

Is there scope for replacing the union with union all? Does each query return a mutually set of data?

"union all" queries support predicates being pushed down into them, but even that may not help if your "big bad" views themselves are a mish-mash of unions etc.

First I'd be drilling down into *where* the performance problems occur. Try

- Running "set serveroutput off"
- Adding the /*+ gather_plan_statistics */ hint to your query
- Run the query
- Get the *true* plan by running:

select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

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