Tom,
We ended up with views having 300 columns after
doing all of the GENERIC USEFUL JOINS we need.

While this reduces are views, and denormalizes
the "view" of the data for us, ther performance STINKS.

So, based on your continuous comment
(Views are just stored queries), I was thinking we should
create SVs, that do the JOINS, and return the 80% hard
columns the joins produce (Company_ID -> Company_Name),
and then join that view to the table we needed the
boatload of data from, but usually less than the total
200 fields it contains.

Currently we use:
Select * from VW_Inventory_Vendor_ControlInfo
-- where date ranges, etc.

-- The VW tells me it returns 200 columns from Inventory
-- and the required columns from Vendor and ControlInfo

We are wondering if we use:
Select x,y,z...
from SV_Inventory_Vendor_ControlInfo SV, TBL_Inventory T
where t.ID = SV.ID
-- and in the date ranges, etc.

We are trying to get CLOSER to the best of both worlds.

1) Views that contain the complexities of the joins to get
to the real data the user wants to see without having it
spread through our software.

2) Performance MUCH closer to a custom query.

In summary does a well-optimized Structural View
make sense if you are expecting to consistently join
it against the primary table to get the fields you need
on a query by query basis (programmers choose), versus
just having 10 or so similar views for each view, and
having to maintain all of them?

Tom,
Things seem to make sense to 1/2 the audience :-)

Anyways, we CURRENTLY use the views to PROTECT the programmers from having to do the complex joins everywhere,
and so that the data they TEND to need is readily at hand.

This CAUSES a problem... Too many columns in the views,
resulting in POOR Perforamnce... Made worse by programmers
who use Select * vs. Select x1..x192 when they don't need
all 300 fields.

So, the thought of Structural Views was to have the
RELATIONSHIPS between tables in the view, but the field
set to be THRUST back onto the developer to specify the
actualy fields they need.

The finaly question becomes what is worse for perforamnce,
1) Having views with so many columns than ANY select from
them pays a performance (DISK writing) penalty
2) Having the JOIN ONLY portion of the view, such that
you join back to the MAIN Table for the NUMEROUS Fields,
making the View fast, but COUNTING on the fact that
the view JOINED with one of the original tables again,
would be handled MORE EFFICIENTLY (Maybe?) than it
handling all of that data.

Problem: We updated our standard views based on MONTHS
of user requests for additional information to be there.
Now, we CREATED a performance penalty.

Solutions:
1) Create a veiw for EACH "UI View" of the data
(5-20 times more views)
2) Have the progrmmers code the VIEW Logic (Joins) and
not use the views except for reference on what tables
to join, or when that smaller view makes sense.
(More maintenence at the programmers level)
3) Combine the two (Structural Views) Where the join
logic is in the view, but then in 50% of the cases
when the information is not there, JOIN that table
back in and get the columns.
(Is this the Best or worse of 2 worlds???)

Question:
How much of a performance penalty would one pay to:

Select x1..x99
from View_Customer_Invoice v, Customer c
where c.rowid = v.cust_rowid
and c.ControllerID = :ControllerID -- From app

when View_Customer_InvoiceTotals =
Select c1..c3,c.rowid as cust_rowid, s.Person, I1..I5
From Customer c, SalesPerson s, Invoice I
where I.CustID = c.ID
and c.SalesPersonID = s.ID
and c.IsActive = 1
and c.IsSomething = 1
and I.IsValid = 1

---
Would it make more sense to join back by ROWID or
by ID (Primary Key), I would assume the former.

But my expectation would be that when Oracle sees
this selection from the view, it will "REASSEMBLE IT",
and do the right thing? (ROOT OF MY QUESTION).
(Right thing is FAST=TRUE<g>)

And EVEN IF IT DID NOT reassemble it, the mere fact
that we do not have so many columns being handled,
would cut down on the (SOMETIMES) 3 second parsing...

I admit, WE WERE BAD, we went to the extreme to get
ONE VIEW for Customer that had all of the "HANDY"
information. We have sinned. I now know that we
should have choosen our views more wisely, and NOT
abused ONE view so badly.

But the questions are:
1) Would we pay a price for REJOINING and forcing the
columns to be specified then?
2) Are we being wrong-headed AGAIN, and should we just
have 5-10 views according to the VIEW that is needed
by the UI/report, etc.?
3) Are we being LAZY and we just need to Join our tables
as the view does not have what we need?

I have asked the same 3 questions in different ways, in
hopes that you will be able to INTERPRET them to clarify
what is happening, and where we are tring to GROW.

I feel our views have become DUMPING grounds for columns
from every possible table so as to make it trivial for
the programmers to get what they need. And views that
query these views (and carry forth their HUNDREDS of columns
become slower and slower).

We were wrong, and want to learn the RIGHT way.

Please Help.

Kirk out!

I had an experience in performance analysis a few months ago that I think is relevant here. I was asked to look for ways to bring down LIO and PIO in a third-party application. I was surprised to discover that a number of the application's views had hints, including the ORDERED hint, built right into them. One view, used for somewhat adhoc reporting, joined approximately 7 tables and the optimizer was forced to join the tables in the same order every time, no matter what filtering conditions were applied by the query on the view. We ended up reducing LIO and PIO by other means, but we came close to intervening in the third-party application by disabling the hints in this view. If anyone has any doubt about the matter, please know that after that experience I come down on the side of keeping hints out of views and confining them to queries.

I agree with Tom, programmers don't need protection and should be able to read a data / schema design entity diagram to do their job, esp. if there Oracle programmers!

However, if you do have inexperienced (in oracle) client side programmers on your project, then I found that rather that developing a generic "meta layer" of views to try to cover all potential queries, I would design (or oversee the design and development) of PLSQL helper packages for the main functions / table / entities, including functions that return cursor refs, add, update and delete functions. This also prevents the programmers using CURSOR FOR loops and SELECT SYSTIME FROM DUAL with abandon!

This gives client side programmers the tools do their jobs (create click buttons and forms and stuff) with you (DBA and/or Oracle PLSQL developer) able to control the inner workings.

Just my 20/20 vision...

Tom and Everyone Else...

Great answers... Even beyond "most useful"!
This is something I can explain to the developers,
and everyone else... Furthermore, it makes good
sense.

Of course, now we will have to revamp our view
naming process...

But this puts us on the right track! And I SORT feel
STUPID for asking the question. But WHAT a revelation
to think of Views as "like" Sub-routines... It even
EXPAINS why you would not create ONE BIG ONE that does
everything!

That comment is WORTH the subscription fee for this
site a thousand times over :-))))))

Thanks so much!

Kirk out!

Hi Tom,

on the OTN i found the following Oracle Tuning Book:
"Oracle High Performance Tuning for 9i and 10g"
(</code> http://otn.oracle.com/books/pdfs/powell_ch.pdf <code>This should be only a sample chapter, but it seems the pdf contains the whole book!)

I read only chapter "6.8 Using views". Here the author states: "Views are not performance friendly! For the sake of performance do not use views."

The "proofs" he provides look a little bit suspicious to me.

So, is Mr. Powell (the author) right (i don't think so)?
What's your opinion (= truth ;-)?

At a quick glance - unless I missed something:
1. he is using elapsed time and comparing small
differences - they could go either way in a different
run (basically that is what Tom is saying - tkprof based
comparison of LIOs for example, makes much more sense.)
2. he is also comparing costs across different query
plans - I dont think you can compare costs like this.

But at least he is trying to be scientific in trying
to prove his statements (albeit his methods seem
to be flawed) - many times people just claim!

Hi Tom, interesting topic. May I also add this (knowing that where I err you will correct). A simple example: a view that joins say 10 tables and gets data from all of them does 9 joins (let say a starting table joined to 10 others). There will be data returned from all 10 tables. A query written against this view that returns say only 1 column (and hence only data from one table) will still translate into a query that does all the joins in the view. This is because the view defines a rowset by the sematics of its joins and without doing all joins in the view, the meaning of the rowset is different. Thus although it is true that selecting only the columns you want is more efficient than taking select *... it is also true that a generic view may exhibit performance issues because the large numbers of joins it does regardless of your needs. Of course there is the flip side too: if the joins in a view define part of the meaning of a rowset, then a query written by a programmer that is way faster because is only selects the columns it needs and only does the joins it needs is technically not a sematically equivelant query because its rowset is potentially different. Hmm.. now we have an interesting question beyond just performance: which query is correct, the short one of the long one. Its something to think about I guess.

Dear Tom,

Can you tell me that how the sort area size to be calculated. How I can determine my requirement for sort area size.

Thanks

Yes, Menon, there are many more errors in this paper.

For example, he writes about sequences:

"Sequences can be cached. Twenty sequences are cached by default. If a system failure occurs then all cached sequences are lost. Only sequence numbers are lost if a database server crashes, not log file recoverable rows in tables, or any other operations using sequence values.
If required by local law that all sequence numbers added to something like invoices must exist then always create sequences with the NOCACHE option." (p. 463)

It doesn't seem that he considered that a simple rollback of a transaction (with seq.nextval) can cause "lost" sequences numbers!!

Also "interesting" is his optinion about PL/SQL:

"In short PL/SQL is not a fully capable programming language. PL/SQL has programming language capabilities and a few bells and whistles, even some object and abstraction capability.
From my experience my recommendation is this. PL/SQL can be
used to store database SQL code in one place. This has the effect of only allowing people with PL/SQL and Oracle database administration skills to build and change that code. However, since PL/SQL is essentially primitive as a programming language and developers make better programmers than database administrators, there is a strong case for minimizing use of PL/SQL.
Do not write your entire system using PL/SQL." (p. 58)

Quite a difference to Toms approach ...

All i can do is :) The comments about pl/sql
are really interesting:)

Anyways, only the other day, I was discussing this
with a friend of mine (I work for Oracle, btw)
Why is it that there are so many experts on Oracle
who are *so* wrong? I think we Oracle folks must
be doing something terribly wrong!
For example, there was a comment in asktom
elsewhere on wrong information on OCP exams etc.

I am curious, if the same is true for say Microsoft or
IBM tecnhologies...I think Oracle loses big time due
to these myths as they result in people using our database
suboptimally big time!

Well, there are many "experts" out there, which should read the concepts guide before they publish an Oracle Tuning book...

And: Yes, the same is true for others. As an example: Informix (other database i have to deal with).
Many applications ignore the rules and features of this database.
For example most applications i've seen use the "max(id) + 1" approach to generate primary keys, and not the serial data type, which was designed exactly for this. Or nobody thought about the concurrency and locking behaviour (shared read locks!) and so on... :-(

So, Oracle is not alone. (But in the case of Oracle it's even more a pity, because the potential of the Oracle database is great!)

But, what i don't understand is: Why is the *OTN* promoting this Tuning book from Powell??? (see </code> http://otn.oracle.com/books/10g_books.html <code>.

"But, what i don't understand is: Why is the *OTN* promoting this Tuning book
from Powell??? (see </code> http://otn.oracle.com/books/10g_books.html <code>"

Exactly my point! Not only do we Oracle folks
promote such books (and many others)
written by "experts" but also many of our OCP
exam questions also promote incorrect thinking!
I guess it is not possible for otn folks to
review every book for accuracy - I am not sure
what should be done to stem this? May be a
committee of well known experts (such as Tom,
Cary etc) just glances through all books before we
promote them?

Glad that we are not alone though!;) and I agree
we lose even more than others because of our
potential..

Hi Tom,

Say if I have two similar views, one "fat" (contains more columns in the SELECT list) and one "lean". Now I want to fetch the values of some columns, which I can get from either view. Will there be any difference regarding performance?

Thanks.

I have read various threads on this.
I guess I understand that in most cases
the above is not an issue. However, in some cases
I have heard we can have an issue with optimimzaer not being
able to push the predicate to the inner level
and may give suboptimal plan.
1. Is this true? How often have you come across
this? If this is true do you have an example
that demos this problem?
2. If the above is true - can we solve the problem
by using "parameterized" views?

Thanx!

1) it is almost always because doing so would *change the query results* and most people don't realize that.

Here is an example I use:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
 
ops$tkyte@ORA9IR2> create index job_idx on emp(job);
Index created.
 
ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select ename, sal, job,
  4         sum(sal) over (partition by job) sal_by_job,
  5             sum(sal) over (partition by deptno) sal_by_deptno
  6    from emp
  7  /
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> select *
  2    from v
  3   where job = 'CLERK'
  4  /
 
ENAME             SAL JOB       SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
MILLER           1300 CLERK           4150          8750
JAMES             950 CLERK           4150          9400
SMITH             800 CLERK           4150         10875
ADAMS            1100 CLERK           4150         10875
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   VIEW OF 'V'
   2    1     WINDOW (SORT)
   3    2       WINDOW (SORT)
   4    3         TABLE ACCESS (FULL) OF 'EMP'
 
 
<b>at this point, people say "stupid optimizer", didn't use the index.  How silly is that. We can see it could using this:</b>

 
ops$tkyte@ORA9IR2> select ename, sal, job,
  2         sum(sal) over (partition by job) sal_by_job,
  3             sum(sal) over (partition by deptno) sal_by_deptno
  4    from emp
  5   where job = 'CLERK'
  6  /
 
ENAME             SAL JOB       SAL_BY_JOB SAL_BY_DEPTNO
---------- ---------- --------- ---------- -------------
MILLER           1300 CLERK           4150          1300
SMITH             800 CLERK           4150          1900
ADAMS            1100 CLERK           4150          1900
JAMES             950 CLERK           4150           950
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   WINDOW (SORT)
   2    1     WINDOW (SORT)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3         INDEX (RANGE SCAN) OF 'JOB_IDX' (NON-UNIQUE)
 
<b>Problem is, the results are *not* the same.  Even if they were, if I were given the queries -- I could generate data that would make them *not* return the same sets.  The two queries are not semantically equivalent...

When possible, we do push down as much as we can:</b>

 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename, sal, sal_by_job
  2    from v
  3   where job = 'CLERK'
  4  /
 
ENAME             SAL SAL_BY_JOB
---------- ---------- ----------
SMITH             800       4150
ADAMS            1100       4150
JAMES             950       4150
MILLER           1300       4150
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   VIEW OF 'V'
   2    1     WINDOW (BUFFER)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   4    3         INDEX (RANGE SCAN) OF 'JOB_IDX' (NON-UNIQUE)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off

<b>there is used the index because the answer would not be changed by doing so..</b>


anytime we cannot push, it is pretty much because doing so would return the *wrong* answer.

2) but then you are just moving the issue.  parameterized views say "I want to change this result based on this inputs".  that is all well and fine. if a query against the above view V should ONLY consider the JOB='CLERK' role - so be it, the only way to achieve that via a view would be to parameterize it.  Else, you get the wrong answer in that case :) 

What is the parameterized views mentioned above ?

All I have learned is you can't create view with any parameters or variable in it.

If there is the way to do that, please advise. I want to use it also

"1) it is almost always because doing so would *change the query results* and most people don't realize that."

The "almost" always means there are cases where
CBO works suboptimally - do you have any examples
handy?

Also, in the example, it is no wonder people get confused
because it seems that these two queries should be "equivalent". Could you exaplin why they are not?
Is it something to do with the way and the stage at
which analytic functions are evauated?
It also means that

select ename, sal, job,
sum(sal) over (partition by job) sal_by_job,
sum(sal) over (partition by deptno) sal_by_deptno
from emp
where job = 'CLERK'

is different from
select * from
( select ename, sal, job,
sum(sal) over (partition by job) sal_by_job,
sum(sal) over (partition by deptno) sal_by_deptno
from emp
)
where job = 'CLERK'

OK let me take a guess. In the first case you evaluate
anlaytics over the set of records on which the
where clause has been applied. In the second case
you apply the where clause afterwards? So what data set
would clarify the difference and give different
results?

Thank you so much!!!!

Sorry I missed that the data is showing the
differences!

One last q - I guess the above scenario happens when analytics are used? Do you have any example where analytics are not used and we run into the same issue
(people think by mistake that CBO is doing the wrong
thing but actually the underlying queries are different)?
Thanx!

Hi Tom
This is in response to your comment above
"set operations (intersect, union, etc), aggregates, outer joins - they can all
do it."

consider a query
select c1, c2
from t1, t2
where t1.a=t2.a
and t1.d1=:param1
union
select d1, d2
from t1, t3
where t1.a=t2.a
and t1.d1=:param1;

In general if I convert this into a parameterized
view and use sys+context to get the param values
- it should work without problems/confision.
Alternatively, if I just create a view

create or replace view v1 as
select c1 as col1, c2 as col2, t1.d1 as param
from t1, t2
where t1.a=t2.a
union
select d1, d2
from t1, t3
where t1.a=t2.a;

and select from it as
select col1, col2
from v1
where t1.d1 = :param1;

Can you see any problems/issues?

Thanx!

I have 3 tables, EMP, ACCOUNT and LOV. Each of them has a numeric ID as the PK (surrogate key). I have a "intersection" (fact table in DW lingo) that has its (composite) PK these 3 PKs. Basically, a many-many relationship between these 3 tables.

Of course, users are interested in all the real data in the tables, not the surrogate ids. So I create a view which joins all the 3 tables on the ids. Say I expose only a few fields that users need today in the view.

create view v as
select a.c1,a.c2,b.c1,b.c2,c.c1,c.c2
from a,b,c
where a.id=b.id and a.id=c.id;

If users need some other fields, they would join the view back to the underlying tables.

select a.c3,b.c3.c.c3
from v,a,b,c
where v.id=a.id and v.id=b.id and v.id=c.id;

This has *got* to hurt performance, right?

Instead, since I am joining the 3 tables anyway, it would make sense to expose *all* the columns in the tables, right?

Something like

create view v as
select a.*,b.*,c.* from a b c
where a.id=b.id and a.id=c.id;

Your opinion? Thanks

Right, thats what I thought too, but in general, if I have a view that does some joins and a query joins that view again to the underlying tables like in my example above

select a.c3,b.c3,c.c3
from v,a,b,c
where v.id=a.id and v.id=b.id and v.id=c.id;

Does the CBO do some optimization and eliminate the redundant joins?

Thanks

hi tom
if i have a view v1 with columns col1, col2,...col10
(some of these columns may have some pl/sql functions
working on them after they are selected)
and I select something like:
selct col1, col2
from v1.

Will the optimizer be smart enough and not run the
function on "col3..col10" since they dont get selected
anyways?

Oracle version 9.0.1.4

Thank you..

<b>In general yes, is it a "promise" -- no, but I'm not able to come up with a case where it does select "c3" when you don't ultimately select or use c3</b>



ops$tkyte@ORA9IR2> create or replace function f return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          return 42;
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select username c1, 5 c2, f() c3
  4    from all_users;
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select c1, c2 from v where rownum < 6;
 
C1                                     C2
------------------------------ ----------
SYS                                     5
SYSTEM                                  5
OUTLN                                   5
DBSNMP                                  5
WMSYS                                   5
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

<b>function was not run...</b>
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select c1, c2, c3 from v where rownum < 6;
 
C1                                     C2         C3
------------------------------ ---------- ----------
SYS                                     5         42
SYSTEM                                  5         42
OUTLN                                   5         42
DBSNMP                                  5         42
WMSYS                                   5         42
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
5
 
<b>function was run 5 times... BUT you might consider using scalar subqueries to take advantage of their caching capabilities:</b>


ops$tkyte@ORA9IR2> create or replace view v
  2  as
  3  select username c1, 5 c2, (select f() from dual) c3
  4    from all_users;
 
View created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select c1, c2, c3 from v where rownum < 6;
 
C1                                     C2         C3
------------------------------ ---------- ----------
SYS                                     5         42
SYSTEM                                  5         42
OUTLN                                   5         42
DBSNMP                                  5         42
WMSYS                                   5         42
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1
 


<b>Oracle will cache the last couple of scalar subquery executions and reuse their results given the same inputs</b>


 

that also tells me how I should test such hypothesis in
future!

Tom...

Is it possible that the reason for the discrepant execution plans is not due to allegedly poor performance of views in general but due to the fact that the view Mr. Powell uses in his first example cannot answer the question he asks using the base tables in the second example in the same fashion.

In the first example, he selects from a view which itself SELECTs _coa.coa#_ and then queries on that field:

CREATE VIEW glv AS
SELECT gl.generalledger_id, coa.coa#, t.text AS type,
st.text AS subtype, coa.text as coa, gl.dr, gl.cr, gl.dte
FROM type t JOIN coa USING(type)
JOIN subtype st USING(subtype)
JOIN generalledger gl ON(gl.coa# = coa.coa#);

.
.
.

EXPLAIN PLAN SET statement_id='TEST' FOR
SELECT * FROM glv WHERE coa# = '40003';

... but in the second example, the query on the base tables has a predicate on _g1.coa_:

EXPLAIN PLAN SET statement_id='TEST' FOR
SELECT gl.generalledger_id, coa.coa#, t.text AS type,
st.text AS subtype, coa.text as coa, gl.dr,
gl.cr, gl.dte
FROM type t JOIN coa USING(type)
JOIN subtype st USING(subtype)
JOIN generalledger gl ON(gl.coa# = coa.coa#)
WHERE gl.coa# = '40003';

Is it fair to expect that the optimizer would create/use the same plan for both queries?

Wow... surprised at some of the other claims made in that paper. Glad to see it wasn't published by Oracle Press or O'Reilly & Assoc.

Anyway... one of the other advantageous uses of views that we've discovered is to empower our stakeholders when troubleshooting the applications we develop for them.

Previously, we would scatter SQL across multiple database packages, sometimes involving predicates referencing specific codes (e.g., person_type_code = 'X'). Not only was this bad practice from a maintenance perspective, like if that code needed to be changed, but the burden was on us developers to troubleshoot problems such as, "Why is so-and-so not appearing in such-and-such list in screen X?" _We_ would have to track down the database procedure that returned that result set and figure out what was happening.

Now that we are more heavily using views, we have not only centralized business logic into each view instead of across multiple packages, we can now deliver them to our stakeholders so that they can troubleshoot them themselves. Now they have a means of seeing exactly what should expect to see in the application. That person isn't in the view? Well... now you know it's either a data problem or a problem with the view itself, and you can rule out a problem with the Java/application layer.

And since Oracle's optimizer has evolved tremendously since that of version 7.3.4, performance is not really an issue anymore. Of course, our views are perfectly written.

I have a view (very simplified) like

create or replace view v as
select
...
a+b a_plus_b,
a+b+c,
a+b-c+d
...

Notice how the a+b part repeats in there. For complex views, is there any way to reuse the computation done in the earlier column and use that columns's alias in the next column?

something like

create or replace view v as
select
...
a+b a_plus_b,
a_plus_b+c
a_plus_b-c+d
...

This would make the maintenance and readability of view immensely easier?

Thanks

Right, but that gets very cumbersome when I have 10 columns that is each built on the previous 9!

Any other way? Thanks

Tom,

following up on view performance I have observed the following behaviour:

I have a simple view:

SQL> CREATE OR REPLACE VIEW JP_V
  2  AS
  3  select * from JOURNALS
  4  UNION ALL
  5  select * from POSITIONS
  6  /

View created.


explain plan for
select  * from
feed_control fc, journals_positions jp
where
fc.feedsyscd=jp.feedsyscd and
fc.feedfilid = jp.feedfilid
and fc.func_area='NOSY'
/

-----------------------------------------------------------------------------
| Operation                    |  Name               | Rows  | Bytes | Cost (
-----------------------------------------------------------------------------
| SELECT STATEMENT             |                     | 16676 |    10M|   204K
|  HASH JOIN                   |                     | 16676 |    10M|   204K
|   TABLE ACCESS BY INDEX ROWID| FEED_CONTROL        |   200 | 33000 |     2 
|    INDEX RANGE SCAN          | FEED_CONTROL_PK     |   200 |       |     2 
|   VIEW                       | JOURNALS_POSITIONS  |   227M|   106G|            
|    UNION-ALL                 |                     |       |       |            
|     PARTITION RANGE ALL      |                     |       |       |            
|      PARTITION LIST ALL      |                     |       |       |            
|       TABLE ACCESS FULL      | JOURNALS            |   131M|    32G| 94586 
|     PARTITION RANGE ALL      |                     |       |       |            
|      PARTITION LIST ALL      |                     |       |       |            
|       TABLE ACCESS FULL      | POSITIONS           |    96M|    25G| 76040 
-----------------------------------------------------------------------------

It appears that CBO is not using indexes on the tables inside the view accessing them in NL. 

Here is the indexes available:

JOURNALS_PK                    FEEDSYSCD
                               FEEDFILID
                               RECSEQNUM
                               COMP_CODE
                               BATCH_NO
                               LINE_NO
                               STATUS_F
                               POSTDATE
                               FUNC_AREA

POSITIONS_PK                   FEEDSYSCD
                               FEEDFILID
                               RECSEQNUM
                               COMP_CODE
                               BATCH_NO
                               LINE_NO
                               STATUS_F
                               POSTDATE
                               FUNC_AREA

I used various hints like FIRST_ROWS, INDEX, USE_NL - nothing helped.

As soon as I use individual table in place of view it starts using PK index in nested loops and returns results immediatelly:

explain plan for
select  * from
feed_control fc, journals j
where
fc.feedsyscd=j.feedsyscd and
fc.feedfilid = j.feedfilid
and fc.func_area='NOSY'
/

-------------------------------------------------------------------------------
| Operation                         |  Name            | Rows  | Bytes | Cost (
-------------------------------------------------------------------------------
| SELECT STATEMENT                  |                  |  9257 |  3878K|    88 
|  TABLE ACCESS BY LOCAL INDEX ROWID| JOURNALS         |    46 | 12144 |     2 
|   NESTED LOOPS                    |                  |  9257 |  3878K|    88 
|    TABLE ACCESS BY INDEX ROWID    | FEED_CONTROL     |   200 | 33000 |     2 
|     INDEX RANGE SCAN              | FEED_CONTROL_PK  |   200 |       |     2 
|    PARTITION RANGE ALL            |                  |       |       |          
|     PARTITION LIST ALL            |                  |       |       |          
|      INDEX RANGE SCAN             | JOURNALS_PK      |    46 |       |    41 
-------------------------------------------------------------------------------


explain plan for
select  * from
feed_control fc, positions p
where
fc.feedsyscd=p.feedsyscd and
fc.feedfilid = p.feedfilid
and fc.func_area='NOSY'
/


------------------------------------------------------------------------------
| Operation                         |  Name            | Rows  | Bytes | Cost 
------------------------------------------------------------------------------
| SELECT STATEMENT                  |                  | 57050 |    24M|   112
|  TABLE ACCESS BY LOCAL INDEX ROWID| POSITIONS        |   285 | 81795 |     2
|   NESTED LOOPS                    |                  | 57050 |    24M|   112
|    TABLE ACCESS BY INDEX ROWID    | FEED_CONTROL     |   200 | 33000 |     2
|     INDEX RANGE SCAN              | FEED_CONTROL_PK  |   200 |       |     2
|    PARTITION RANGE ALL            |                  |       |       |          
|     PARTITION LIST ALL            |                  |       |       |          
|      INDEX RANGE SCAN             | POSITIONS_PK     |   432 |       |    31
------------------------------------------------------------------------------


What am I missing here ? Is it known limitation or kind of a bug ?

Thanks in advance,

Vadim
 

hi,

i have tables t1 and t2 as
create table t1
(
id number,
img blob
);

create table t2
(
id number,
str varchar2(255),
img blob
);

when I do
create or replace view v1 as
select img from t1
union
select img from t2;

I am getting error ORA-00932: inconsistent datatypes: expected - got BLOB.

Thanks.

Using Views
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.
That darn 10g performance tuning guide again:

"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."

Isn't this a gross generalization that is quite often untrue? Should the docs being saying this kind of thing perpetuating the idea that views are performance problems waiting to happen?

maybe you should take some time off from writing books, and write the docs instead. :)

Can you explain this behavior?

I have a complex view, V_CRYSTAL_DETAIL, that accesses 21 tables and returns a few dozen columns.

When I execute SELECT COUNT(*) FROM V_CRYSTAL_DETAIL WHERE PARENT_COMPANY_ID = :V1 AND GROUP_ID = :V2

the response time is under two minutes, returns 605,000+

When I extract the SELECT... statement from the view and execute and filter in the same manner, the response time doubles and is over 4 minutes, and returns the exact same number (that is good).

How is the SELECT statement slower in executing with the exact same filtering applied to it?

It is almost like the view performs better than the actual select statement, and according to Oracle Theory they should not.

Any ideas?

Re: How is the SELECT statement slower in executing with the exact same filtering applied to it?

Perhaps the view is materialized? :)

How i know the what all are table got structural changes in databse using oracle 9i (ie Using ALTER cluase)

while shutdown the system(computer) the the DATABASE also shutdown. while booting the system, the database also get open(if the windows services is AUTOMATIC).In this process without giving the STARTUP command also database is open .how it is?

operating system :WINDOWS

xxmod_int_fp_overtime_v is a local view based on:
per_all_people_f - remote table
per_all_assignments_f - remote table
pay_element_entries_f - remote table
xxmod_int_ee_overtime_v - local view based on remote tables,
includes MAX/GROUP BY to pivot rows into columns
xxmod_int_per_cust_id_v - local view based on remote tables

Running the query :
'SELECT * FROM xxmod_int_fp_overtime_v
WHERE po_cust_id = '123' AND po_person_id = 'AA1234A'

runs in < 1 second.

The synonym person_overtime refers to the view xxmod_int_tp_overtime_v which is defined as:

SELECT <columns>
FROM xxmod_int_fp_overtime_v
UNION ALL
SELECT <columns>
FROM xxmod_int_per_overtime_mv

where xxmod_int_per_overtime_mv is a table physically located on the local database.

Running the query

'SELECT * FROM person_overtime
WHERE po_cust_id = '123' AND po_person_id = 'AA1234A'

runs in approx 40 seconds, the tkprof showing that querying xxmod_int_fp_overtime_v takes most of the time.

I am only expecting any query from person_overtime to return between 0 and approx 30 rows.

Please can you provide an explaination as to why the 'union all' adversely affects the query on the
remote database and what steps I can take to influence the plan used.

I can provide tkprof outputs for both queries if required.

we have to migrate from Oracle8 (8.1.7.4.1) to Oracle9 (9.2.0.8.0)
In our application we have selects on views similar to the select below.
In Oracle8 the performances are good In Oracle9 no
If we use hint "rule" the performances are good also in Oracle9, but we prefere don't use this hint.
If we use the bind variable :kre also in the select inside where clause, the performances are good also in Oracle9,
but we are not able to find all the select like this.
if we set the parameter _UNNEST_SUBQUERY = false the performance are good also in Oracle9
Have I to set this parameter at db level, or are there problems in my select/view?
many many thanks
drop table PAR_PAR_REL_butta;
create table PAR_PAR_REL_butta as select*from all_objects;
insert into PAR_PAR_REL_butta select*from PAR_PAR_REL_butta;
insert into PAR_PAR_REL_butta select*from PAR_PAR_REL_butta;
insert into PAR_PAR_REL_butta select*from PAR_PAR_REL_butta;
insert into PAR_PAR_REL_butta select*from PAR_PAR_REL_butta;
alter table par_par_rel_butta add name_name_id varchar2(30);
update par_par_rel_butta set name_name_id=object_name;
update PAR_PAR_REL_butta set name_name_id=name_name_id||'jjjj' where rownum<10;
select count(*) from PAR_PAR_REL_butta;

COUNT(*)
---------
358080
create index PAR_PAR_REL_butta_i1 on PAR_PAR_REL_butta (object_name);
create index PAR_PAR_REL_butta_i2 on PAR_PAR_REL_butta (object_id);
create index PAR_PAR_REL_butta_i3 on PAR_PAR_REL_butta (name_name_id);

drop table kre_nehmer_butta;
create table kre_nehmer_butta as select*from all_objects;
insert into kre_nehmer_butta select*from kre_nehmer_butta;
insert into kre_nehmer_butta select*from kre_nehmer_butta;
insert into kre_nehmer_butta select*from kre_nehmer_butta;
insert into kre_nehmer_butta select*from kre_nehmer_butta;
select count(*) from kre_nehmer_butta;

COUNT(*)
---------
358096
create index kre_nehmer_butta_i1 on kre_nehmer_butta (object_name);
create index kre_nehmerL_butta_i2 on kre_nehmer_butta (object_id);

drop table FIN_EINNAHME_REL_butta;
create table FIN_EINNAHME_REL_butta as select*from all_objects;
create index FIN_EINNAHME_REL_butta_i1 on FIN_EINNAHME_REL_butta (object_name);
create index FIN_EINNAHME_REL_butta_i2 on FIN_EINNAHME_REL_butta (object_id);

CREATE OR REPLACE VIEW KRE_NEHMER_ALL_butta
AS
select KN.owner,kn.object_name, kn.object_id
from KRE_NEHMER_butta KN
union
select ppr.owner,ppr.name_name_id object_name, kn.object_id
from PAR_PAR_REL_butta PPR,KRE_NEHMER_butta KN
where kn.object_name=ppr.object_name
and not exists (select nn.object_id
from kre_nehmer_butta nn
where nn.object_id = kn.object_id and
nn.object_name=ppr.name_name_id);
begin
DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SYSTEM',
tabname =>'PAR_PAR_REL_BUTTA',
method_opt => 'FOR ALL INDEXED COLUMNS',cascade =>TRUE);
DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SYSTEM',
tabname =>'KRE_NEHMER_BUTTA',
method_opt => 'FOR ALL INDEXED COLUMNS',cascade =>TRUE);
DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'SYSTEM',
tabname =>'FIN_EINNAHME_REL_BUTTA',method_opt => 'FOR ALL INDEXED COLUMNS',cascade =>TRUE);
end;
/

variable kre number;
begin
select object_id into :kre from all_objects
where rownum=1;
end;
/
prompt this is the default in O9
alter session set "_UNNEST_SUBQUERY" = true;
set time on timing on
set autotrace on
select /*+ choose */ count(*) as CNT
from FIN_EINNAHME_REL_butta R
where R.object_ID = :KRE
and not exists(select KNA.object_ID
from KRE_NEHMER_ALL_butta KNA
where KNA.object_ID = R.object_ID);

CNT
---------
0
Elapsed: 00:01:119.38

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=38046 Card=1 Bytes=18)
1 0 SORT (AGGREGATE)
2 1 MERGE JOIN (ANTI) (Cost=38046 Card=1 Bytes=18)
3 2 INDEX (RANGE SCAN) OF 'FIN_EINNAHME_REL_BUTTA_I2' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
4 2 SORT (UNIQUE) (Cost=38045 Card=760011 Bytes=9880143)
5 4 VIEW OF 'KRE_NEHMER_ALL_BUTTA' (Cost=2843305 Card=760011 Bytes=9880143)
6 5 SORT (UNIQUE) (Cost=2843305
Card=760011 Bytes=43305539)
7 6 UNION-ALL
8 7 TABLE ACCESS (FULL) OF 'KRE_NEHMER_BUTTA' (Cost=427 Card=358096 Bytes=13965744)
9 7 FILTER
10 9 HASH JOIN (Cost=2307 Card=401915 Bytes=29339795)
11 10 TABLE ACCESS (FULL) OF 'KRE_NEHMER_BUTTA' (Cost=427 Card=358096 Bytes=7878112)
12 10 TABLE ACCESS (FULL) OF 'PAR_PAR_REL_BUTTA' (Cost=474 Card=358080 Bytes=18262080)
13 9 AND-EQUAL
14 13 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE)
15 13 INDEX (RANGE SCAN) OF 'KRE_NEHMER_BUTTA_I1' (NON-UNIQUE) (Cost=3 Card=26)

-- with 2 times the bind variable
select /*+ choose */ count(*) as CNT
from FIN_EINNAHME_REL_butta R
where R.object_ID = :KRE
and not exists(select KNA.object_ID
from KRE_NEHMER_ALL_butta KNA
where KNA.object_ID = :kre);

CNT
---------
0
Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 INDEX (RANGE SCAN) OF 'FIN_EINNAHME_REL_BUTTA_I2' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
4 2 VIEW OF 'KRE_NEHMER_ALL_BUTTA' (Cost=536 Card=34 Bytes=442)
5 4 SORT (UNIQUE) (Cost=536 Card=34 Bytes=1938)
6 5 UNION-ALL
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=624)
8 7 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
9 6 FILTER
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'PAR_PAR_REL_BUTTA' (Cost=23 Card=1 Bytes=51)
11 10 NESTED LOOPS (Cost=387 Card=18 Bytes=1314)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=352)
13 12 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
14 11 INDEX (RANGE SCAN) OF 'PAR_PAR_REL_BUTTA_I1' (NON-UNIQUE) (Cost=2 Card=22)
15 9 AND-EQUAL
16 15 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE)
17 15 INDEX (RANGE SCAN) OF 'KRE_NEHMER_BUTTA_I1' (NON-UNIQUE) (Cost=3 Card=26)

alter session set "_UNNEST_SUBQUERY" = false;
select /*+ choose */ count(*) as CNT
from FIN_EINNAHME_REL_butta R
where R.object_ID = :KRE
and not exists(select KNA.object_ID
from KRE_NEHMER_ALL_butta KNA
where KNA.object_ID = R.object_ID);

CNT
---------
0
Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FIN_EINNAHME_REL_BUTTA_I2' (NON-UNIQUE) (Cost=1 Card=1 Bytes=5)
3 2 VIEW OF 'KRE_NEHMER_ALL_BUTTA' (Cost=536 Card=34 Bytes=442)
4 3 SORT (UNIQUE) (Cost=536 Card=34 Bytes=1938)
5 4 UNION-ALL
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=624)
7 6 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
8 5 FILTER
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'PAR_PAR_REL_BUTTA' (Cost=23 Card=1 Bytes=51)
10 9 NESTED LOOPS (Cost=387 Card=18 Bytes=1314)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=352)
12 11 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
13 10 INDEX (RANGE SCAN) OF 'PAR_PAR_REL_BUTTA_I1' (NON-UNIQUE) (Cost=2 Card=22)
14 8 AND-EQUAL
15 14 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE)
16 14 INDEX (RANGE SCAN) OF 'KRE_NEHMER_BUTTA_I1' (NON-UNIQUE) (Cost=3 Card=26)
in Oracle8 is there a little problem on Card=299376.
but the performance is OK
select /*+ choose */ count(*) as CNT
from FIN_EINNAHME_REL_butta R
where R.object_ID = :KRE
and not exists(select KNA.object_ID
from KRE_NEHMER_ALL_butta KNA
where KNA.object_ID = R.object_ID);

CNT
---------
0
Elapsed: 00:00:00.94

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=3 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FIN_EINNAHME_REL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=1 Bytes=4)
3 2 VIEW OF 'KRE_NEHMER_ALL_BUTTA' (Cost=462 Card=34 Bytes=442)
4 3 SORT (UNIQUE) (Cost=462 Card=34 Bytes=1834)
5 4 UNION-ALL
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=592)
7 6 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
8 5 FILTER
9 8 NESTED LOOPS (Cost=419 Card=18 Bytes=1242)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'KRE_NEHMER_BUTTA' (Cost=19 Card=16 Bytes=320)
11 10 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=16)
12 9 TABLE ACCESS (BY INDEX ROWID) OF 'PAR_PAR_REL_BUTTA' (Cost=25 Card=299376 Bytes=14669424)
13 12 INDEX (RANGE SCAN) OF 'PAR_PAR_REL_BUTTA_I1' (NON-UNIQUE) (Cost=3 Card=299376)
14 8 AND-EQUAL
15 14 INDEX (RANGE SCAN) OF 'KRE_NEHMERL_BUTTA_I2' (NON-UNIQUE) (Cost=3 Card=1)
16 14 INDEX (RANGE SCAN) OF 'KRE_NEHMER_BUTTA_I1' (NON-UNIQUE) (Cost=4 Card=1)

I want to create a view as follows to be used on various reports:

create or replace view vw_mhemvt as (
select h.usrnam,
       h.rdtsts,
       h.hstdat,
       h.intpal,
       nvl(r.raktyp, 10) raktyp,
       nvl(r.rakusr,'NULL') rakusr,
       lag(h.whscde) over (partition by intpal
                           order by hstdat ) lstwhs,
       lag(h.rakblk) over (partition by intpal
                           order by hstdat ) lstblk,
       lag(h.rakasl) over (partition by intpal
                           order by hstdat ) lstasl,
       lag(r.raktyp) over (partition by intpal
                           order by hstdat ) lsttyp
 from mhehis h, mhedat m, rakloc r
 where h.trkcde = m.trkcde
   and h.whscde = r.whscde (+)
   and h.rakblk = r.rakblk (+)
   and h.rakasl = r.rakasl (+)
   and h.rakcol = r.rakcol (+)
   and h.raklvl = r.raklvl (+)
   and h.raksid = r.raksid (+)
   and h.rdtsts in ( 50, -- RDTSTS-FULL-PICK
                     100)  -- RDTSTS-COMPLETE
   and h.intpal is not null
)


However, whilst doing tests I found that I would get a performance problem as per the following example where my query would not use the appropriate index (mhehis_1).

Is there a way I can avoid this problem.

Ignore no of rows returned as I ran the queries at different times.

SQL> set autotrace on;
SQL> set timing on;
  1     select h.usrnam,
  2            h.rdtsts,
  3            h.hstdat,
  4            h.intpal,
  5            nvl(r.raktyp, 10) raktyp,
  6            nvl(r.rakusr,'NULL') rakusr,
  7            lag(h.whscde) over (partition by intpal
  8                              order by hstdat ) lstwhs,
  9            lag(h.rakblk) over (partition by intpal
 10                              order by hstdat ) lstblk,
 11            lag(h.rakasl) over (partition by intpal
 12                              order by hstdat ) lstasl,
 13            lag(r.raktyp) over (partition by intpal
 14                              order by hstdat ) lsttyp
 15       from mhehis h, mhedat m, rakloc r
 16       where h.trkcde = m.trkcde
 17         and h.whscde = r.whscde (+)
 18         and h.rakblk = r.rakblk (+)
 19         and h.rakasl = r.rakasl (+)
 20         and h.rakcol = r.rakcol (+)
 21         and h.raklvl = r.raklvl (+)
 22         and h.raksid = r.raksid (+)
 23         and h.rdtsts in ( 50,100)
 24         and h.intpal is not null
 25         and h.hstdat > sysdate - 101
 26*        and h.hstdat < sysdate - 100
SQL> /

3543 rows selected.

Elapsed: 00:00:01.74

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=103)
   1    0   WINDOW (SORT) (Cost=6 Card=1 Bytes=103)
   2    1     FILTER
   3    2       NESTED LOOPS (Cost=5 Card=1 Bytes=103)
   4    3         NESTED LOOPS (OUTER) (Cost=3 Card=1 Bytes=100)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'MHEHIS' (Cost=2
          Card=1 Bytes=62)

   6    5             INDEX (RANGE SCAN) OF 'MHEHIS_1' (NON-UNIQUE) (C
          ost=2 Card=3)

   7    4           TABLE ACCESS (BY INDEX ROWID) OF 'RAKLOC' (Cost=2
          Card=1 Bytes=38)

   8    7             INDEX (UNIQUE SCAN) OF 'RAKLOC_1' (UNIQUE)
   9    3         TABLE ACCESS (FULL) OF 'MHEDAT' (Cost=2 Card=1 Bytes
          =3)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      17894  consistent gets
          0  physical reads
          0  redo size
     222333  bytes sent via SQL*Net to client
       3248  bytes received via SQL*Net from client
        238  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       3543  rows processed




  1  select * from vw_mhemvt
  2   where hstdat > sysdate-101
  3*    and hstdat < sysdate - 100
SQL> /



3345 rows selected.

Elapsed: 00:01:29.99

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=126)
   1    0   FILTER
   2    1     VIEW OF 'VW_MHEMVT' (Cost=10 Card=1 Bytes=126)
   3    2       WINDOW (SORT) (Cost=10 Card=1 Bytes=103)
   4    3         NESTED LOOPS (OUTER) (Cost=9 Card=1 Bytes=103)
   5    4           NESTED LOOPS (Cost=7 Card=1 Bytes=65)
   6    5             TABLE ACCESS (FULL) OF 'MHEHIS' (Cost=5 Card=1 B
          ytes=62)

   7    5             TABLE ACCESS (FULL) OF 'MHEDAT' (Cost=2 Card=1 B
          ytes=3)

   8    4           TABLE ACCESS (BY INDEX ROWID) OF 'RAKLOC' (Cost=2
          Card=1 Bytes=38)

   9    8             INDEX (UNIQUE SCAN) OF 'RAKLOC_1' (UNIQUE)




Statistics
----------------------------------------------------------
          7  recursive calls
        272  db block gets
    3201517  consistent gets
     112454  physical reads
          0  redo size
     208222  bytes sent via SQL*Net to client
       3094  bytes received via SQL*Net from client
        224  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
       3345  rows processed

My appologies. I have been rude and not told you anything about my platform:

It is Oracle v9.2.0.2 on HP-UX IA64 11.23

The only difference between the 2 queries, subtle as it is , is the date range. If the date range is inside the view the performance is fine. However, I would like to create the view without the date range becuase it will be common for other reports. Could I use a parameterized view?

The reason why they show different results is because I used the date range (sysdate -111 to sysdate -110) and ran the query at different times. If I use a fixed date range as below it gives me the same answer. I believe the second query has failed to used one of the indexes (MHEHIS_1) as shown in the explain plan.

select h.usrnam,
h.rdtsts,
h.hstdat,
h.intpal,
nvl(r.raktyp, 10) raktyp,
nvl(r.rakusr,'NULL') rakusr,
lag(h.whscde) over (partition by intpal
order by hstdat ) lstwhs,
lag(h.rakblk) over (partition by intpal
order by hstdat ) lstblk,
lag(h.rakasl) over (partition by intpal
order by hstdat ) lstasl,
lag(r.raktyp) over (partition by intpal
order by hstdat ) lsttyp
from mhehis h, mhedat m, rakloc r
where h.trkcde = m.trkcde
and h.whscde = r.whscde (+)
and h.rakblk = r.rakblk (+)
and h.rakasl = r.rakasl (+)
and h.rakcol = r.rakcol (+)
and h.raklvl = r.raklvl (+)
and h.raksid = r.raksid (+)
and h.rdtsts in ( 50, -- RDTSTS-FULL-PICK
100) -- RDTSTS-COMPLETE
and h.intpal is not null
and h.hstdat > '30-OCT-2007 00:00:00'
and h.hstdat < '30-OCT-2007 23:59:00'
/

10249 rows selected.

Elapsed: 00:00:20.57

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=327 Card=2331 Bytes=
249417)

1 0 WINDOW (SORT) (Cost=327 Card=2331 Bytes=249417)
2 1 NESTED LOOPS (OUTER) (Cost=280 Card=2331 Bytes=249417)
3 2 HASH JOIN (Cost=47 Card=2331 Bytes=158508)
4 3 TABLE ACCESS (FULL) OF 'MHEDAT' (Cost=2 Card=6 Bytes
=78)

5 3 TABLE ACCESS (BY INDEX ROWID) OF 'MHEHIS' (Cost=44 C
ard=2331 Bytes=128205)

6 5 INDEX (RANGE SCAN) OF 'MHEHIS_1' (NON-UNIQUE) (Cos
t=65 Card=23807)

7 2 TABLE ACCESS (BY INDEX ROWID) OF 'RAKLOC' (Cost=2 Card
=1 Bytes=39)

8 7 INDEX (UNIQUE SCAN) OF 'RAKLOC_1' (UNIQUE)




select *
from vw_mhemvt
where hstdat > '30-OCT-2007 00:00:00'
and hstdat < '30-OCT-2007 23:59:00'
/


10249 rows selected.

Elapsed: 00:02:12.63

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10503 Card=144856 By
tes=18251856)

1 0 VIEW OF 'VW_MHEMVT' (Cost=10503 Card=144856 Bytes=18251856
)

2 1 WINDOW (SORT) (Cost=10503 Card=144856 Bytes=15499592)
3 2 HASH JOIN (Cost=2553 Card=144856 Bytes=15499592)
4 3 TABLE ACCESS (FULL) OF 'MHEDAT' (Cost=2 Card=6 Bytes
=78)

5 3 HASH JOIN (OUTER) (Cost=2531 Card=144858 Bytes=13616
652)

6 5 TABLE ACCESS (FULL) OF 'MHEHIS' (Cost=1846 Card=14
4858 Bytes=7967190)

7 5 TABLE ACCESS (FULL) OF 'RAKLOC' (Cost=121 Card=827
74 Bytes=3228186)

ops$tkyte%ORA10GR2> select *
  2    from (
  3  select ename, hiredate,
  4         lag(ename) over (order by hiredate) prior_ename,
  5         lag(hiredate) over (order by hiredate) prior_hiredate,
  6             lead(ename) over (order by hiredate) next_ename,
  7             lead(hiredate) over (order by hiredate) next_hiredate
  8    from scott.emp
  9  )
 10  where hiredate between to_date( '02-apr-1981' ) and to_date( '03-dec-1981' )
 11  /

ENAME      HIREDATE  PRIOR_ENAM PRIOR_HIR NEXT_ENAME NEXT_HIRE
---------- --------- ---------- --------- ---------- ---------
JONES      02-APR-81 WARD       22-FEB-81 BLAKE      01-MAY-81
BLAKE      01-MAY-81 JONES      02-APR-81 CLARK      09-JUN-81
CLARK      09-JUN-81 BLAKE      01-MAY-81 TURNER     08-SEP-81
TURNER     08-SEP-81 CLARK      09-JUN-81 MARTIN     28-SEP-81
MARTIN     28-SEP-81 TURNER     08-SEP-81 KING       17-NOV-81
KING       17-NOV-81 MARTIN     28-SEP-81 FORD       03-DEC-81
FORD       03-DEC-81 KING       17-NOV-81 JAMES      03-DEC-81
JAMES      03-DEC-81 FORD       03-DEC-81 MILLER     23-JAN-82

8 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select ename, hiredate,
  2         lag(ename) over (order by hiredate) prior_ename,
  3         lag(hiredate) over (order by hiredate) prior_hiredate,
  4             lead(ename) over (order by hiredate) next_ename,
  5             lead(hiredate) over (order by hiredate) next_hiredate
  6    from scott.emp
  7   where hiredate between to_date( '02-apr-1981' ) and to_date( '03-dec-1981' )
  8  /

ENAME      HIREDATE  PRIOR_ENAM PRIOR_HIR NEXT_ENAME NEXT_HIRE
---------- --------- ---------- --------- ---------- ---------
JONES      02-APR-81                      BLAKE      01-MAY-81
BLAKE      01-MAY-81 JONES      02-APR-81 CLARK      09-JUN-81
CLARK      09-JUN-81 BLAKE      01-MAY-81 TURNER     08-SEP-81
TURNER     08-SEP-81 CLARK      09-JUN-81 MARTIN     28-SEP-81
MARTIN     28-SEP-81 TURNER     08-SEP-81 KING       17-NOV-81
KING       17-NOV-81 MARTIN     28-SEP-81 FORD       03-DEC-81
FORD       03-DEC-81 KING       17-NOV-81 JAMES      03-DEC-81
JAMES      03-DEC-81 FORD       03-DEC-81

8 rows selected.

Ok. The penny has dropped.
Your explanation and example is excellent.
Thanks very much.

Performance issues in progressive relaxation technique while using ?=fuzzy. Other options are working fine. Is there any way to tune fuzzy operator in progressive relaxation technique?.

Hi Tom,

Good day.

I have a performance issue on one of the view. Please find below the details.

<code>
CREATE OR REPLACE FORCE VIEW APPS.XXRMB_MILES_MASTER_MAPPING
(
   MAPPING_SOURCE,
   CDH_ID,
   GLOBALID,
   SUBSYSTEM_MILES,
   SUBSYSTEM,
   SUBSYSTEM_ID,
   SHORTNAME
)
AS
     SELECT Mapping_Source,
            CDH_ID,
            GlobalId,
            Subsystem_Miles,
            Subsystem,
            Subsystem_Id,
            ShortName
       FROM (SELECT 'CDH' Mapping_Source,
                    A.Party_number CDH_ID,
                    RPAD (a.Party_number, 6, '0') GlobalId,
                    CASE
                       WHEN F.ORIG_SYSTEM = 'FIN' THEN 'FNS'
                       ELSE F.ORIG_SYSTEM
                    END
                       Subsystem_Miles,
                    F.ORIG_SYSTEM Subsystem,
                    F.ORIG_SYSTEM_REFERENCE Subsystem_Id,
                    A.known_as5 ShortName
               FROM SD.pk_parties A
                    JOIN (SELECT A.*
                            FROM    SD.pk_organization_profiles A
                                 JOIN
                                    (  SELECT PARTY_ID,
                                              MAX (version_number) version_number
                                         FROM SD.pk_organization_profiles B
                                     GROUP BY PARTY_ID) B
                                 ON A.party_id = B.PARTY_ID
                                    AND A.version_number = B.version_number) B
                       ON A.PARTY_ID = B.PARTY_ID AND a.KNOWN_AS5 IS NOT NULL
                    LEFT JOIN SD.pk_ORIG_SYS_REFERENCES F
                       ON A.PARTY_ID = F.OWNER_TABLE_ID AND F.STATUS = 'A'
              WHERE A.STATUS = 'A'
             UNION ALL
             --Person
             SELECT 'CDH' Mapping_Source,
                    A.Party_number CDH_ID,
                    RPAD (a.Party_number, 6, '0') GlobalId,
                    CASE
                       WHEN F.ORIG_SYSTEM = 'FIN' THEN 'FNS'
                       ELSE F.ORIG_SYSTEM
                    END
                       Subsystem_Miles,
                    F.ORIG_SYSTEM Subsystem,
                    F.ORIG_SYSTEM_REFERENCE Subsystem_Id,
                    A.known_as5 ShortName
               FROM SD.pk_parties A
                    JOIN (SELECT A.*
                            FROM    SD.pk_person_profiles A
                                 JOIN
                                    (  SELECT PARTY_ID,
                                              MAX (version_number) version_number
                                         FROM SD.pk_person_profiles
                                     GROUP BY PARTY_ID) B
                                 ON A.party_id = B.PARTY_ID
                                    AND A.version_number = B.version_number) B
                       ON A.PARTY_ID = B.PARTY_ID AND a.KNOWN_AS5 IS NOT NULL
                    LEFT JOIN SD.pk_ORIG_SYS_REFERENCES F
                       ON A.PARTY_ID = F.OWNER_TABLE_ID AND F.STATUS = 'A'
              WHERE A.STATUS = 'A'
             UNION ALL
             SELECT 'DIDaily' Mapping_Source,
                    NULL CDH_ID,
                    NULL GlobalId,
                    --"Subsystem" Subsystem,
                    CASE
                       WHEN "Subsystem" = 'XXX' THEN 'FNX'
                       WHEN "Subsystem" = 'XXXX' THEN 'INT'
                       ELSE "Subsystem"
                    END
                       Subsystem_Miles,
                    "Subsystem" Subsystem,
                    "SubsystemCounterpartyShortName" Subsystem_ID,
                    "CounterpartyName" Shortname
               FROM "SubsystemCounterparty_New"@sybase A -- where A."CounterpartyName"  in (select Known_as5 from SD.pk_parties where status = 'A')
                                                        )   ORDER BY shortname, subsystem;

SQL> explain plan for
  2  SELECT trim(SHORTNAME) SHORTNAME FROM APPS.XXRMB_MILES_MASTER_MAPPING B WHERE B.SUBSYSTEM='FEN'
  3  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2965320687

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                           |  4713 |   639K|       | 62300   (2)| 00:12:28 |        |      |
|   1 |  SORT ORDER BY             |                           |  4713 |   639K|       | 62300   (2)| 00:12:28 |        |      |
|   2 |   VIEW                     |                           |  4713 |   639K|       | 62299   (2)| 00:12:28 |        |      |
|   3 |    UNION-ALL               |                           |       |       |       |            |          |        |      |
|*  4 |     HASH JOIN              |                           |   501 | 38076 |  2064K| 14422   (4)| 00:02:54 |        |      |
|*  5 |      HASH JOIN             |                           | 34039 |  1662K|       |  8383   (4)| 00:01:41 |        |      |
|*  6 |       HASH JOIN            |                           |  7955 |   287K|       |  3465   (3)| 00:00:42 |        |      |
|*  7 |        TABLE ACCESS FULL   | HZ_ORIG_SYS_REFERENCES    |  7955 |   124K|       |  1227   (1)| 00:00:15 |        |      |
|*  8 |        TABLE ACCESS FULL   | HZ_PARTIES                | 46310 |   949K|       |  2237   (3)| 00:00:27 |        |      |
|*  9 |       TABLE ACCESS FULL    | HZ_ORGANIZATION_PROFILES  |   339K|  4309K|       |  4913   (4)| 00:00:59 |        |      |
|  10 |      VIEW                  |                           | 79338 |  2014K|       |  5757   (4)| 00:01:10 |        |      |
|  11 |       SORT GROUP BY        |                           | 79338 |  1007K|  8000K|  5757   (4)| 00:01:10 |        |      |
|* 12 |        TABLE ACCESS FULL   | HZ_ORGANIZATION_PROFILES  |   339K|  4309K|       |  4913   (4)| 00:00:59 |        |      |
|* 13 |     HASH JOIN              |                           |  4192 |   204K|       | 47871   (2)| 00:09:35 |        |      |
|  14 |      JOIN FILTER CREATE    | :BF0000                   |  7955 |   287K|       |  3465   (3)| 00:00:42 |        |      |
|* 15 |       HASH JOIN            |                           |  7955 |   287K|       |  3465   (3)| 00:00:42 |        |      |
|* 16 |        TABLE ACCESS FULL   | HZ_ORIG_SYS_REFERENCES    |  7955 |   124K|       |  1227   (1)| 00:00:15 |        |      |
|* 17 |        TABLE ACCESS FULL   | HZ_PARTIES                | 46310 |   949K|       |  2237   (3)| 00:00:27 |        |      |
|  18 |      VIEW                  |                           | 24404 |   309K|       | 44405   (1)| 00:08:53 |        |      |
|* 19 |       FILTER               |                           |       |       |       |            |          |        |      |
|  20 |        SORT GROUP BY       |                           | 24404 |  6386K|   525M| 44405   (1)| 00:08:53 |        |      |
|  21 |         JOIN FILTER USE    | :BF0000                   |  1952K|   498M|       |  3669   (3)| 00:00:45 |        |      |
|* 22 |          HASH JOIN         |                           |  1952K|   498M|  2176K|  3669   (3)| 00:00:45 |        |      |
|* 23 |           TABLE ACCESS FULL| HZ_PERSON_PROFILES        | 88810 |  1127K|       |  1116   (3)| 00:00:14 |        |      |
|* 24 |           TABLE ACCESS FULL| HZ_PERSON_PROFILES        | 88810 |    21M|       |  1116   (3)| 00:00:14 |        |      |
|  25 |     REMOTE                 | SubsystemCounterparty_New |    20 |   580 |       |     5   (0)| 00:00:01 | SYBASE | R->S |
--------------------------------------------------------------------------------------------------------------------------------

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

   4 - access("PARTY_ID"="B"."PARTY_ID" AND "VERSION_NUMBER"="B"."VERSION_NUMBER")
   5 - access("A"."PARTY_ID"="PARTY_ID")
   6 - access("A"."PARTY_ID"="F"."OWNER_TABLE_ID")
   7 - filter("F"."ORIG_SYSTEM"='FEN' AND "F"."STATUS"='A')
   8 - filter("A"."STATUS"='A' AND "A"."KNOWN_AS5" IS NOT NULL)
   9 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  12 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  13 - access("A"."PARTY_ID"="B"."PARTY_ID")
  15 - access("A"."PARTY_ID"="F"."OWNER_TABLE_ID")
  16 - filter("F"."ORIG_SYSTEM"='FEN' AND "F"."STATUS"='A')
  17 - filter("A"."STATUS"='A' AND "A"."KNOWN_AS5" IS NOT NULL)
  19 - filter("VERSION_NUMBER"=MAX("VERSION_NUMBER"))
  22 - access("PARTY_ID"="PARTY_ID")
  23 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  24 - filter("ACTUAL_CONTENT_SOURCE"='SST')

Remote SQL Information (identified by operation id):
----------------------------------------------------

  25 - SELECT "Subsystem","CounterpartyName" FROM "SubsystemCounterparty_New" WHERE "Subsystem"='FEN' (accessing
        'SYBASE.RMB.CO.ZA' )


58 rows selected.


-- Code with a filter added . The new predicate is based on a hard coded column.

SQL> explain plan for
  2  SELECT trim(SHORTNAME) SHORTNAME FROM APPS.XXRMB_MILES_MASTER_MAPPING B WHERE B.SUBSYSTEM='FEN' AND MAPPING_SOURCE='DIDaily'
  3  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 355311649

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |    22 |  3168 |       |   352   (1)| 00:00:05 |        |      |
|   1 |  SORT ORDER BY                     |                             |    22 |  3168 |       |   352   (1)| 00:00:05 |        |      |
|   2 |   VIEW                             |                             |    22 |  3168 |       |   351   (1)| 00:00:05 |        |      |
|   3 |    UNION-ALL                       |                             |       |       |       |            |          |        |      |
|   4 |     NESTED LOOPS                   |                             |       |       |       |            |          |        |      |
|   5 |      NESTED LOOPS                  |                             |     1 |    76 |       |   176   (1)| 00:00:03 |        |      |
|   6 |       NESTED LOOPS                 |                             |     1 |    60 |       |     6   (0)| 00:00:01 |        |      |
|   7 |        NESTED LOOPS                |                             |     1 |    39 |       |     5   (0)| 00:00:01 |        |      |
|   8 |         VIEW                       |                             |     1 |    26 |       |     5 (100)| 00:00:01 |        |      |
|   9 |          SORT GROUP BY             |                             |     1 |    13 |  8000K|            |          |        |      |
|* 10 |           FILTER                   |                             |       |       |       |            |          |        |      |
|* 11 |            TABLE ACCESS FULL       | HZ_ORGANIZATION_PROFILES    |   339K|  4309K|       |  4913   (4)| 00:00:59 |        |      |
|* 12 |         TABLE ACCESS BY INDEX ROWID| HZ_ORGANIZATION_PROFILES    |     1 |    13 |       |     5   (0)| 00:00:01 |        |      |
|* 13 |          INDEX RANGE SCAN          | HZ_ORGANIZATION_PROFILES_N1 |     4 |       |       |     2   (0)| 00:00:01 |        |      |
|* 14 |        TABLE ACCESS BY INDEX ROWID | HZ_PARTIES                  |     1 |    21 |       |     1   (0)| 00:00:01 |        |      |
|* 15 |         INDEX UNIQUE SCAN          | HZ_PARTIES_U1               |     1 |       |       |     0   (0)| 00:00:01 |        |      |
|* 16 |       INDEX RANGE SCAN             | HZ_ORIG_SYS_REFERENCES_N1   |     1 |       |       |   169   (1)| 00:00:03 |        |      |
|* 17 |      TABLE ACCESS BY INDEX ROWID   | HZ_ORIG_SYS_REFERENCES      |     1 |    16 |       |   170   (1)| 00:00:03 |        |      |
|  18 |     NESTED LOOPS                   |                             |       |       |       |            |          |        |      |
|  19 |      NESTED LOOPS                  |                             |     1 |    50 |       |   171   (1)| 00:00:03 |        |      |
|  20 |       NESTED LOOPS                 |                             |     1 |    34 |       |     1   (0)| 00:00:01 |        |      |
|  21 |        VIEW                        |                             |     1 |    13 |       |     5 (100)| 00:00:01 |        |      |
|* 22 |         FILTER                     |                             |       |       |       |            |          |        |      |
|  23 |          SORT GROUP BY             |                             |     1 |   268 |   525M|            |          |        |      |
|* 24 |           FILTER                   |                             |       |       |       |            |          |        |      |
|* 25 |            HASH JOIN               |                             |  1952K|   498M|  2176K|  3669   (3)| 00:00:45 |        |      |
|* 26 |             TABLE ACCESS FULL      | HZ_PERSON_PROFILES          | 88810 |  1127K|       |  1116   (3)| 00:00:14 |        |      |
|* 27 |             TABLE ACCESS FULL      | HZ_PERSON_PROFILES          | 88810 |    21M|       |  1116   (3)| 00:00:14 |        |      |
|* 28 |        TABLE ACCESS BY INDEX ROWID | HZ_PARTIES                  |     1 |    21 |       |     1   (0)| 00:00:01 |        |      |
|* 29 |         INDEX UNIQUE SCAN          | HZ_PARTIES_U1               |     1 |       |       |     0   (0)| 00:00:01 |        |      |
|* 30 |       INDEX RANGE SCAN             | HZ_ORIG_SYS_REFERENCES_N1   |     1 |       |       |   169   (1)| 00:00:03 |        |      |
|* 31 |      TABLE ACCESS BY INDEX ROWID   | HZ_ORIG_SYS_REFERENCES      |     1 |    16 |       |   170   (1)| 00:00:03 |        |      |
|  32 |     REMOTE                         | SubsystemCounterparty_New   |    20 |   580 |       |     5   (0)| 00:00:01 | SYBASE | R->S |
------------------------------------------------------------------------------------------------------------------------------------------

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

  10 - filter(NULL IS NOT NULL)
  11 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  12 - filter("ACTUAL_CONTENT_SOURCE"='SST' AND "VERSION_NUMBER"="B"."VERSION_NUMBER")
  13 - access("PARTY_ID"="B"."PARTY_ID")
  14 - filter("A"."STATUS"='A' AND "A"."KNOWN_AS5" IS NOT NULL)
  15 - access("A"."PARTY_ID"="PARTY_ID")
  16 - access("F"."ORIG_SYSTEM"='FEN' AND "A"."PARTY_ID"="F"."OWNER_TABLE_ID")
       filter("A"."PARTY_ID"="F"."OWNER_TABLE_ID")
  17 - filter("F"."STATUS"='A')
  22 - filter("VERSION_NUMBER"=MAX("VERSION_NUMBER"))
  24 - filter(NULL IS NOT NULL)
  25 - access("PARTY_ID"="PARTY_ID")
  26 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  27 - filter("ACTUAL_CONTENT_SOURCE"='SST')
  28 - filter("A"."STATUS"='A' AND "A"."KNOWN_AS5" IS NOT NULL)
  29 - access("A"."PARTY_ID"="B"."PARTY_ID")
  30 - access("F"."ORIG_SYSTEM"='FEN' AND "A"."PARTY_ID"="F"."OWNER_TABLE_ID")
       filter("A"."PARTY_ID"="F"."OWNER_TABLE_ID")
  31 - filter("F"."STATUS"='A')

Remote SQL Information (identified by operation id):
----------------------------------------------------

  32 - SELECT "Subsystem","CounterpartyName" FROM "SubsystemCounterparty_New" WHERE "Subsystem"='FEN' (accessing
        'SYBASE.RMB.CO.ZA' )
SQL> spool off

When I add a filter on the hard coded column the plan changes from full scan to index scan. The hard coded column is added for each subquery of the Union All code.

I am not able to make out why this should at all change the plan. Is there any way to let the optimizer know that the filtering condition is a hard coded column and is not going to change much. This change in plan is causing huge performance issues in our database.

Please help.

Regards,
Snehasish Das</code>