I was searching for perfomance between using views over tables. But, this thread says more or less the same which does not satisfy to my question. Can u explain me in depth y we have to go for updateable views rather than a table ?

Tom

I find the understanding of where clause merging into the view where clause to be useful. Not understanding this and it's limitations (views containing group functions, distinct etc.) could lead to over zealous use of views.

Maybe a word or two from you on this??

I am still very confused by the performance of views compared to tables. Your answer said, "A view is nothing more then a stored query. It will run no slower nor faster then a query directly against the base tables."

I did this on one of my views:

select count(*)
from view_x
where pa_date between '01-dec-01' and 01-jan-02';

and got an elapsed time of over 8 minutes.

Then I did the same query on the base table:

select count(*)
from table_x
where pa_date between '01-dec-01' and 01-jan-02';

and the count came back in 56 seconds.

The table has 36 columns and the view looks at 34 columns.

The query on the base table is clearly faster. What am I missing here?

Lets see the view text please, and a describe of the table, and the results of:

SQL> set autotrace traceonly explain
SQL> select count(*) from view_x where pa_date between ....
SQL> select count(*) from table_x where pa_date between ....


And also -- please (I would have thought we would have all learned this lesson) DON'T USE 2 DIGIT DATES.  and further, don't compare dates to strings and strings to dates.  Use this:

where pa_date between to_date( '01-dec-2001', 'dd-mon-yyyy' ) and 
                      to_date( '01-jan-2002', 'dd-mon-yyyy' )

in all real code (eg: everything beyond an example like this) 

Hi tom

Your Answers are justified my claims on Views in terms of Performance with my collegue. First of all thanks for that. I have one more doubt. We are having interface with outside source and there is no guarantee that a particular column will come as a Charecter or Number, But the Width is rigid. It can range from 1 to 2000. so I have created a Table with 40 columns all are of V2(2000). I have created a view like the below
Create View View1 as
Select substr(Colnum001,1,10) Alias1, Substr(Colnum002,1,3) Alias2......
From Table 1

Is there any performance overhead on giving the Substr while selecting a Column. I hope it wont since most of the oracle Internal views of V$ tables are having lot of functions associated still works fast.

I am correct in my conclusion.

Please suggest me

Thanks in advance

Here is the information you requested; the view text, the table description, and the autotraces. The reason the timings are faster than in my original question is that this database was moved to a different machine over the weekend.

SQL> l
  1  select text from user_views
  2* where view_name = 'PA_COST_DISTRIBUTION_LINES'
SQL> /

TEXT
------------------------------------
SELECT
       EXPENDITURE_ITEM_ID
     , LINE_NUM
     , CREATION_DATE
     , CREATED_BY
     , TRANSFER_STATUS_CODE
     , AMOUNT
     , QUANTITY
     , BILLABLE_FLAG
     , REQUEST_ID
     , PROGRAM_APPLICATION_ID
     , RESOURCE_ACCUMULATED_FLAG
     , PROGRAM_ID
     , PROGRAM_UPDATE_DATE
     , PA_DATE
     , DR_CODE_COMBINATION_ID
     , GL_DATE
     , TRANSFERRED_DATE
     , TRANSFER_REJECTION_REASON
     , BATCH_NAME
     , ACCUMULATED_FLAG
     , REVERSED_FLAG
     , LINE_NUM_REVERSED
     , SYSTEM_REFERENCE1
     , SYSTEM_REFERENCE2
     , SYSTEM_REFERENCE3
     , CR_CODE_COMBINATION_ID
     , IND_COMPILED_SET_ID
     , LINE_TYPE
     , BURDENED_COST
     , ORG_ID
     , BURDEN_SUM_SOURCE_RUN_ID
     , BURDEN_SUM_REJECTION_CODE
     , PROJECT_ID
     , TASK_ID
 FROM PA_COST_DISTRIBUTION_LINES_ALL
  WHERE
    NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)) =
   NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
SQL> 

SQL> desc PA_COST_DISTRIBUTION_LINES_ALL
 Name                            Null?    Type
 ------------------------------- -------- ----
 EXPENDITURE_ITEM_ID             NOT NULL NUMBER(15)
 LINE_NUM                        NOT NULL NUMBER(15)
 CREATION_DATE                   NOT NULL DATE
 CREATED_BY                      NOT NULL NUMBER(15)
 TRANSFER_STATUS_CODE            NOT NULL VARCHAR2(1)
 AMOUNT                          NOT NULL NUMBER(22,5)
 QUANTITY                        NOT NULL NUMBER(22,5)
 BILLABLE_FLAG                   NOT NULL VARCHAR2(1)
 REQUEST_ID                               NUMBER(15)
 PROGRAM_APPLICATION_ID                   NUMBER(15)
 RESOURCE_ACCUMULATED_FLAG                VARCHAR2(1)
 PROGRAM_ID                               NUMBER(15)
 PROGRAM_UPDATE_DATE                      DATE
 FUNCTION_TRANSACTION_CODE                VARCHAR2(30)
 PA_DATE                                  DATE
 DR_CODE_COMBINATION_ID                   NUMBER(15)
 GL_DATE                                  DATE
 TRANSFERRED_DATE                         DATE
 TRANSFER_REJECTION_REASON                VARCHAR2(250)
 BATCH_NAME                               VARCHAR2(30)
 ACCUMULATED_FLAG                         VARCHAR2(1)
 REVERSED_FLAG                            VARCHAR2(1)
 LINE_NUM_REVERSED                        NUMBER(15)
 SYSTEM_REFERENCE1                        VARCHAR2(30)
 SYSTEM_REFERENCE2                        VARCHAR2(30)
 SYSTEM_REFERENCE3                        VARCHAR2(30)
 CR_CODE_COMBINATION_ID                   NUMBER(15)
 IND_COMPILED_SET_ID                      NUMBER(15)
 LINE_TYPE                       NOT NULL VARCHAR2(1)
 BURDENED_COST                            NUMBER(22,5)
 ORG_ID                                   NUMBER(15)
 CODE_COMBINATION_ID                      NUMBER(15)
 BURDEN_SUM_SOURCE_RUN_ID                 NUMBER(15)
 BURDEN_SUM_REJECTION_CODE                VARCHAR2(30)
 PROJECT_ID                               NUMBER(15)
 TASK_ID                                  NUMBER(15)

SQL>

SQL> l
  1  select count(*)
  2  from pa_cost_distribution_lines_all
  3* where pa_date between to_date('01-dec-2001', 'dd-mon-yyyy') and to_date('01-jan-2002')
SQL> /
Elapsed: 00:00:10.14

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'PA_COST_DISTRIBUTION_LINES_N7' (N
          ON-UNIQUE)


SQL>

SQL> l
  1  select count(*)
  2  from pa_cost_distribution_lines
  3* where pa_date between to_date('01-dec-2001', 'dd-mon-yyyy') and to_date('01-jan-2002')
SQL> /
Elapsed: 00:03:59.89

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PA_COST_DISTRIBUTION_L
          INES_ALL'

   3    2       INDEX (RANGE SCAN) OF 'PA_COST_DISTRIBUTION_LINES_N7'
          (NON-UNIQUE)


SQL> 

Tom,

I am a bit confused about Constraints on Views. As per the documentation, they are not enforced. So what is the use for constraints on views ?

As per "SQL Reference" documentation :
<QUOTE>
View constraints (at the view and at the column or attribute level) are declarative only. That is, Oracle does not enforce them. However, operations on views are subject to the integrity constraints defined on the underlying base tables, so you can enforce constraints on views through constraints on base tables.
</QUOTE>

Thanks for your time.

Hi, Tom,

View is as good as base table, i agree with that.

Our senario is:

We have a transactional DB, and we are developing forcast&analysis system based on these tables at the same time with lots of transactions happened.
In the newly developed system, we normally use forms as interface to provide various demand forcast to the end user.
Problem is: we created lots of views which involved lots of analytical functions, and the forms are based on these views. By this way it results in VERY slow performance even for a sigle query.
Do you have any suggestions about it? We don't have money to build a data warehouse BTW.

Thanks

We have a slighly different scenario. We have two schemas with exactly same tables but the data is different. We need to present a read only user a unified view of the whole database. My approach is to create a third schema containing views on the two schemas to be used for queries. For example, a view THIRDSCHEMA.DEPT is a SELECT * FROM FIRSTSCHEMA.DEPT UNION ALL SECONDSCHEMA.DEPT. Read only users will query the THIRDSCHEMA.DEPT. The problem is most of the queries are fairly complicated with GROUP BY, EXISTS, etc on these views and I am wondering if performance will ever be hit on those views. The tables are fairly huge, 1 -15 GB each.

If performance may pose a problem, how would you approach this? We considered replication/materialized views from the first two schemas to the third one, but dropped it due to lack of space. Thanks a lot in advance.

Hello Tom

Thanks a lot for your valuable suggestion.

Regarding View Performance a have a bit doubt. Suppose I have two tables Account_Master and Invoice_Details. Basically Invoice table is dynamic in nature and it’s volume more than one million at a time. I have created a view with hints on those and join condition is account no. Now I am retrieving data from the view, passing Account Number as a parameter.

Following performance problem I have noticed.

1. When I am retrieving data from tables, passing
Account Number as a parameter and scanning index it
takes very less time rather than from view that takes a
lot.

2. In case of optimization when I retrieve data from view
it takes lot of resource rather than simple table
joining.

Now

1. Can I create view dynamically by passing parameters as
on required basis?

2. When I retrieve data from view on the basis of
parameters is it loading all the data i.e. the result
set of Create View in memory?

3. What is the best way to handle this type of view on
which where clause will be parameterized.

Please suggest.

Thanks in advance.

Regards
JP
USHACOMM INDIA

RE - Comment above by Jyotiprakash Nandi

Why don't you post you view definition
and test results ?

I have completed my own testing on views and results
were as per Tom has stated.

I am using a Oracle Transparent Gateway. I have statement wherein a local table and a remote database table are joined. The statement is like...

Select l.nme NAME, r.adrs_text ADDRESS
from cstmr_nms l, cstmr_adrs@rmt_db r
where r.adrs_id=l.adrs_id
r.STATE='CA'
and l.nme like 'JOHN%';

Will rewriting the statement as

Select l.nme NAME, r.adrs_text ADDRESS
from cstmr_nms l, (SELECT adrs_id, adrs_text from cstmr_adrs@rmt_db where STATE='CA') r
where r.adrs_id=l.adrs_id
and and l.nme like 'JOHN%';


provide any performance improvement...

If it does, does it have to explicitly written like this or the Oracle Optimizer may rewrite the first statment in the form of the second statement...

Hi Tom,
Do the columns of a view get stored in the data dictionary the way the columns of a table are stored in all_tab_columns?
My requirement is to find out the columns of a view in a PRO*C code.

Kapil

Thanks, Tom
That was helpful

>>> And also -- please (I would have thought we would have all learned this lesson)
>> DON'T USE 2 DIGIT DATES...Use this:
>>where pa_date between to_date( '01-dec-2001', 'dd-mon-yyyy' )

Tom, is there anything bad about doing just
to_date( '01-dec-2001') w/o the 'dd-mon-yyyy' ?

Thanks

I am not really sure if this is relevant to the original post. If it’s not, please ignore it. Sorry about that.
We have some set of tables that hold Quotation data for individuals as well as group schemes. The group id would be null in case of non-group scheme related data. Now for group scheme quote sets we created a view from all relevant tables but did not include 'where group id is not null'. I don’t like it because its called group scheme quote sets but includes all quote sets. The person who created the view tells me that it doesn't make a difference since the view would always be accessed with the group scheme id and the performance would also be same. I feel he is right about performance but don’t like the view definition without the group id not null check. Would like to know your view on this.

Thanks.

well, it can affect the plans -- consider the difference in query plans between v1 and v2:

ops$tkyte@ORA920.LOCALHOST> create table t1 ( x int primary key );
Table created.

ops$tkyte@ORA920.LOCALHOST> create table t2 ( x int );
Table created.

ops$tkyte@ORA920.LOCALHOST> exec dbms_stats.set_table_stats( user, 'T1', numrows => 10000, numblks => 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.LOCALHOST> exec dbms_stats.set_table_stats( user, 'T2', numrows => 10000, numblks => 10000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.LOCALHOST> create or replace view v1
  2  as
  3  select * from t2;

View created.

ops$tkyte@ORA920.LOCALHOST> create or replace view v2
  2  as
  3  select * from t2 where x is not null;

View created.

ops$tkyte@ORA920.LOCALHOST> set autotrace traceonly explain

ops$tkyte@ORA920.LOCALHOST> select * from t1 where x not in ( select x from v1 );

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=481504 Card=500 Bytes=6500)
   1    0   FILTER
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C003648' (UNIQUE) (Cost=4 Card=500 Bytes=6500
          )

   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=963 Card=500 Bytes=6500)


<b>that'll full scan T1 (via the primary key in this example) and then for EVERY ROW in T1, full scan T2</b>

ops$tkyte@ORA920.LOCALHOST> select * from t1 where x not in ( select x from v2 );

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=974 Card=9500 Bytes=247000)
   1    0   HASH JOIN (ANTI) (Cost=974 Card=9500 Bytes=247000)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C003648' (UNIQUE) (Cost=4 Card=10000 Bytes=13
          0000)

   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=963 Card=500 Bytes=6500)

<b>that will outer join T1 to T2 and only keep the rows in T1 that don't have a mate in T2.  In general, this is infinitely prefered.  But it is only semantically possible if it is known that X is not null!</b>



So, if X is not null -- it wouldn't hurt to state that in the view...
 

Thanks for you quick reply Tom.

I have an app that uses a view. I want to experiment with replacing that view with a Materialized view but don't have access to the original sql in the app. Will the query rewrite option allow me to achieve it ? I can ofcourse change the view code.

The view does a comparrison using sysdate like:

and (x is null or ( x is not null and sysdate >= x ))

When I create the MV using the select in the view it rejects that statement!! I also could not create a refresh on commit for a MV based on multiple tables, is that a restriction ? I am using 8.1.7

So I created the MV without the above "and" and instead used it in a sql statement. Then it does not use the MV and instead reverts back to the base tables. If I query the original view it does not use the MV either. I also created another view without the sysdate comparrison and it still did not use the MV.

What are my choices ? Response with an example would be greatly appreciated.

Thanks in advance for your assistance.

I purchased your book (worth it's weight in gold) and read Ch 13, but it seems you did not touch on that specific issue. I have also searched this site and did not seem to find any question along this line.
I apologize in advance if you feel this is not that correct thread to post this question to.

Thanks

Hi Tom:
Absolutely agreed, that the MV is stale as soon as it is created if I use the sysdate (I guess that's why it is a restriction).
I was trying to find if there is a work around for that.

See, I can't change the application code, in which the queries access the view. The view contains the "and with the sysdate". So I was thinking of creating a MV, minus the and condition with the sysdate. That is possible and I have done that. So I have a MV which is identical to the view without the "sysdate".

What I was hoping for is that when the app accesses the view, the oracle optimizer will see a MV with the same sql syntax (minus the and with the sysdate) and rewrite the query to use the MV to generate a partial result set and then apply the "and with the sysdate" conditon against that result set.

Would that work, or am I missing something ?

Tom
We are using 9iR2 with a db link to 8i(8.1.7.4) database. On the 9i server, I created a view as:

create view v_local as select * from remote_schema.t_remote@db_link;

When I checked the user_tab_columns on 9i server, I found that all the column definitions of remote table t_remote were present in the local data dictionary view as columns of view v_local. Even if I dropped the link, my stored procedures referencing v_local would still compile, though they won't run.

The problem happened when someone changed a column name in remote table t_remote. Now when I do a select against the view v_local, it gave <column name> invalid identifier...I did

create or replace view v_local as select * from remote_schema.t_remote@db_link;

This didn't solve the problem. I had to drop view and recreate and grant permissions.

My question is if view is just a stored query, why the remote table column definitions are being stored in the local database? Why Oracle doesn't just execute the stored statement select * from remote_schema.t_remote@db_link?

Thanks

Tom
I tried again today morning and create or replace view thing worked. The reason it didn't work before was that when I was playing with views on the local database, the remote database DBA was dropping the underlying tables and changing column names, almost during the same time. That is why I was getting weird results.

Actually storing the remote table meta data locally is a blessing. In case the remote database is unavailable because of network or maintenance, local developers can still at least compile their packages.

Thanks for your prompt response and perfectly clear examples as ever...

Tom 

What is the difference between inserting a date like

Create table t(x date);
SQL> Insert into t values('2003-01-01');

1 row created.

SQL> commit;

Commit complete.

and like below, i.e. after applying a date format....


SQL> ed
Wrote file afiedt.buf

  1* Insert into t values(to_date('2003-01-01','yyyy-mm-dd'))
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

X
----------
2003-02-08
2003-02-08
2003-02-07
2003-02-08
2003-01-01
2003-01-01

The output(last 2 rows ) as you see is the same), if I'm importing data into a different database, I can do to_char and to_date into appropriate format , Isnt it.. plsease discuss..
 

I have 3 tables in Oracle

1. Students
----------------
-RegisterNumber
-Name


2. Admission_Docs
-----------------
-RegisterNumber
-DocumentID
-Date



3.DocumentMaster
-----------------
-DocumentID
-Description


'Students' and 'DocumentMaster' are Master Tables while Admission_Docs is a transaction table. Students those who submit a particular document only will be appended to 'Admission_Docs'


Sample Data:

STUDENTS
------------
RegisterNumber Name
123 Moosa Davood
342 usman
987 John


ADMISSION_DOCS
----------------
RegisterNumber DocumentID Date
342 1 12/12/2002
342 2 13/12/2002
987 2 11/11/2002


DOCUMENT_MASTER
--------------------
DocumentID Description
1 Transfer Certificate
2 Nativity Certificate
3 Characer Certificate





For a particular student with RegisterNumber 342, I need the output like this



RegisterNumber Name Document Submitted Date
----------------------------------------------------------------------------------
342 Usman Transfer Certificate Yes 12/12/2002
342 Usman Nativity Certificate Yes 13/12/2002
342 Usman Character Certificate No



1. Is it possible to create an updatable view for this?
2. Else which is the best query to display this?


Thaha Hussain

anything is possible.

The real question is "is it in any way sensible".


I would say it is not sensible to create or update STUDENT records here.  Why?  they are not key preserved.  The student record will appear as many times as there are documents.  So, no, I would not allow modifications to the STUDENT object here.

Would it be sensible to update the DOCUMENT_MASTER here?  I don't think so.  It doesn't to me make sense to allow updates to the document master table semantically speaking in the context of a student.  Also -- each document will appear in this view for every student -- doesn't make sense.

So, that'll leave ADMISSION_DOCS.  This is one that might make sense HOWEVER, since we'll have to outer join to it -- it won't be POSSIBLE to modify it through the view UNLESS we trained the view to do that via instead of triggers.  So while it might make sense for this one, it isn't something that will happen naturally and I would advise against it.


So, I would probably not use a view, but rather query:


ps$tkyte@ORA920> variable bv number;
ops$tkyte@ORA920> exec :bv := 456;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from students;

REGISTERNUMBER NAME
-------------- -----
           123 tom
           456 bill
           789 mary

ops$tkyte@ORA920> select * from document_master;

DOCUMENTID DESCRI
---------- ------
         1 t cert
         2 n cert
         3 c cert

ops$tkyte@ORA920> select * from admission_docs;

REGISTERNUMBER DOCUMENTID DT
-------------- ---------- ---------
           456          1 09-MAR-03
           456          2 09-MAR-03
           789          2 09-MAR-03

ops$tkyte@ORA920> select a.* , decode(b.dt,null,'No','Yes') submitted, b.dt
  2   from (
  3  select *
  4    from students, document_master
  5   where students.registerNumber = :bv
  6        ) a, admission_docs b
  7   where a.registerNumber = b.registerNumber(+)
  8     and a.documentId = b.documentId (+)
  9  /

REGISTERNUMBER NAME  DOCUMENTID DESCRI SUB DT
-------------- ----- ---------- ------ --- ---------
           456 bill           1 t cert Yes 09-MAR-03
           456 bill           2 n cert Yes 09-MAR-03
           456 bill           3 c cert No

ops$tkyte@ORA920>


And directly modify the 3 objects as needed -- nothing fancy, don't get carried away.   

Tom,

This has nothing to do with the thread...but this is taken from one of your answers.

You were demonstrating the difference 'IS NOT NULL' clause can make.

<<
ops$tkyte@ORA920.LOCALHOST> select * from t1 where x not in ( select x from v1
);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=481504 Card=500 Bytes=6500)
1 0 FILTER
2 1 INDEX (FAST FULL SCAN) OF 'SYS_C003648' (UNIQUE) (Cost=4 Card=500
Bytes=6500
)

3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=963 Card=500 Bytes=6500)


that'll full scan T1 (via the primary key in this example) and then for EVERY
ROW in T1, full scan T2
>>

My question is on your sentence here. I thought only nested loops did this. I mean for every row in the driving table it looks for the matching record in the second table.

How did you tell from the plan that it gets the records this way?

Any info would be greatly appreciated.

regards

I.

I understood the view stuff and your query is simply great! I doubt you misunderstood me.

I'm using Delphi with Oracle.

Actually what I meant was - if there is some technique to get the output of your query as an object, it is easy for me to upgrade the Admission_docs through a Grid in the Front End connected to this view.

For example by Double clicking on a particular row of the grid, It should come as 'Yes' another click 'No' and a provision to supply the date if it is 'Yes'. Those work should immediately append a row in 'admission_docs' Or should remove the curresponding row from 'admission_docs' according to the status.



II.
If the 'admission_docs' transaction table design is wrong, please suggest the best practice. Please... I like you very much..


'Having little knowledge of what is available is a bad thing'. So these questions ;-)

Thanx in advance,
Thaha Hussain

Just be careful -- this sort of stuff can get confusing.  In this example, I'll permit the modification of submitted and dt but not any other columns - if you want to support that -- you'll have to write the code (but I don't recommend it as the records are "repeated" in the set -- gets ugly and ambigous).

admission_docs is the only table that it makes set to use in there.

admission_docs is a traditional association object, it is not a bad design -- it is very standard.  

ops$tkyte@ORA920> create or replace view V
  2  as
  3  select a.* , decode(b.dt,null,'No','Yes') submitted, b.dt
  4   from (
  5  select *
  6    from students, document_master
  7        ) a, admission_docs b
  8   where a.registerNumber = b.registerNumber(+)
  9     and a.documentId = b.documentId (+)
 10  /

View created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace trigger v_trigger
  2  instead of update on v
  3  begin
  4      delete from admission_docs
  5       where registerNumber = :old.registerNumber
  6         and documentid = :old.documentId;
  7
  8      if ( :new.submitted = 'Yes' )
  9      then
 10          insert into admission_docs
 11          (registerNumber,documentId,dt)
 12          values
 13          (:new.registerNumber,:new.documentId,:new.dt);
 14      end if;
 15  end;
 16  /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> update v
  2     set submitted = 'Yes', dt = sysdate
  3   where registerNumber = 456 and documentid = 3;

1 row updated.

ops$tkyte@ORA920>
ops$tkyte@ORA920> update v
  2      set submitted = 'No'
  3   where registerNumber = 456 and documentid = 1;

1 row updated.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from v where registerNumber = :bv;

REGISTERNUMBER NAME  DOCUMENTID DESCRIPTION          SUB DT
-------------- ----- ---------- -------------------- --- ---------
           456 bill           1 t cert               No
           456 bill           2 n cert               Yes 10-MAR-03
           456 bill           3 c cert               Yes 10-MAR-03

ops$tkyte@ORA920> 

Thank you very much! Yesterday night, me and my wife talked a lot about such a wonderful person like you.

I believe, that simplicity is your style!

Let Allah help you!

Thaha Hussain

Which one you like - 'Document_Master' or 'Document_Masters'?

Hi Tom,

I firmly believed that
1. View is a stored query
2. The performance impact of view is subtle
until I did the following simple test about it...

Senario:
1. table1 with 1.5M records
2. v_table1 is a view of table1 ( create view v_table1 as select * from table1; )
3. To query 10,000 times from table1 and v_table1 using index keys, and it took 1.75s and 3.14s respectively.

Do you think the 80% overhead is reasonable? Please correct me if I am wrong.

P.S. SQLs
select count(*) from table1 where <using index key>;
select count(*) from v_table1 where <using index key>;
and I confirmed the SQLs is ok by using tkprof.

TIA,
Eric

Hi Tom,

Thanks very much, you answered all my questions (actually my second question is the statistics of tkprok).

Regards,
Eric

Without using a view, is it possible to decribe the structure of a table alphabetically?

tom,

is this the url you post your articles - other than asktom.oracle.com, i see only article 1,2 . how to go to the index page for your articles - cause its a wealth for all of us.

Please provide me with the link if one exists.

thank you

Tom,

I am querying over a db link, a view exists on the remote server, my query takes so much time just for count(*) and i have provided the autotrace, with this information, will you be able to help me in understanding why its taking so much time to execute the query....

thanks for all your help

CREATE OR REPLACE VIEW OMDATAPULL AS
SELECT
GEIORDERS.CUSTNUM,
GEIORDERS.PONUM,
GEIORDERS.REQNUM,
GEIORDERS.SYSTEMORIGIN,
GEIORDERS.DATELASTUPDATED,
GEIORDERLINES.REQUESTDATE,
GEIORDERLINES.LINENUM,
--GEIORDERLINES.REQNUM,
GEIORDERLINES.SEQNUM,
--GEIORDERLINES.SYSTEMORIGIN,
GEIORDERLINES.CURRENCYTYPE,
GEIORDERLINES.CATALOGNUM,
--GEIORDERLINES.PONUM,
GEIORDERLINES.STATUS,
GEIORDERLINES.NOTES,
GEIORDERLINES.INVOICENUM,
GEIORDERLINES.CARRIER,
GEIORDERLINES.BILLOFLADING,
GEIORDERLINES.CARRIERPHONE,
GEIORDERLINES.SHIPMETHOD,
GEIORDERLINES.EXTPRICE,
GEIORDERLINES.MARKS,
GEIORDERLINES.CUSTREQUESTDATE,
GEIORDERLINES.ACTUALSHIPDATE,
GEIORDERLINES.SCHSHIPDATE,
GEIORDERLINES.DELIVERYDATE,
--GEIORDERLINES.DATELASTUPDATED,
GEIORDERLINES.SHIPPINGLOCATION,
GEIORDERLINEDATES.ACTUALDFARETURNDATE,
GEIORDERLINEDATES.ACTUALAPPRDRAWISSUEDATE,
GEIORDERLINEDATES.ACTUALAPPRCONSTRUCTDATE,
GEIORDERLINEDATES.ACTUALEQUIPMANUFDATE,
GEIORDERLINEDATES.ACTUALEQUIPTESTDATE,
GEIORDERLINEDATES.BASEDFARETURNDATE,
GEIORDERLINEDATES.BASEEQUIPTESTDATE,
GEIORDERLINEDATES.BASEAPPRCONSTRUCTDATE,
GEIORDERLINEDATES.SCHAPPRCONSTRUCTDATE,
GEIORDERLINEDATES.SCHAPPRDRAWISSUEDATE,
GEIORDERLINEDATES.SCHEQUIPTESTDATE,
GEIORDERLINETRACKING.TRACKINGNUM,
GEIORDERLINETRACKING.CARRIERSTATUS
--GEIORDERLINETRACKING.DELIVERYDATE,
--GEIORDERLINETRACKING.DATELASTUPDATED
FROM
GEIORDERS,GEIORDERLINES,GEIORDERLINEDATES,GEIORDERLINETRACKING
WHERE
GEIORDERS.REQNUM=GEIORDERLINES.REQNUM AND GEIORDERS.REQNUM=GEIORDERLINES.REQNUM AND
GEIORDERLINES.REQNUM= GEIORDERLINEDATES.REQNUM    AND
GEIORDERLINES.CUSTNUM= GEIORDERLINEDATES.CUSTNUM    AND
GEIORDERLINES.SYSTEMORIGIN= GEIORDERLINEDATES.SYSTEMORIGIN AND
GEIORDERLINES.LINENUM= GEIORDERLINEDATES.LINENUM AND
GEIORDERLINES.SEQNUM= GEIORDERLINEDATES.SEQNUM AND
GEIORDERLINES.REQUESTDATE= GEIORDERLINEDATES.REQUESTDATE AND
GEIORDERLINES.REQNUM= GEIORDERLINETRACKING.REQNUM AND
GEIORDERLINES.CUSTNUM= GEIORDERLINETRACKING.CUSTNUM AND
GEIORDERLINES.SYSTEMORIGIN= GEIORDERLINETRACKING.SYSTEMORIGIN AND
GEIORDERLINES.LINENUM= GEIORDERLINETRACKING.LINENUM AND
GEIORDERLINES.SEQNUM= GEIORDERLINETRACKING.SEQNUM AND
GEIORDERLINES.REQUESTDATE= GEIORDERLINETRACKING.REQUESTDATE

SQL>set autotrace on
SQL>set time on
17:29:51 SQL>select count(*) from OMDATAPULL@OMDATA;

  COUNT(*)
----------
       138

Elapsed: 00:12:744.40

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=14896 Card=
          1 Bytes=121)

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=14896 Card=1 Bytes=121)
   3    2       NESTED LOOPS (Cost=14884 Card=4 Bytes=336)
   4    3         MERGE JOIN (Cost=14884 Card=4 Bytes=184)
   5    4           SORT (JOIN) (Cost=1075 Card=125795 Bytes=1132155)
   6    5             INDEX (FAST FULL SCAN) OF 'XPKGEIORDERS' (UNIQUE DEVECOM.
          ) (Cost=96 Card=125795 Bytes=1132155)                        US.ORACL
                                                                       E.COM

   7    4           SORT (JOIN) (Cost=13809 Card=473406 Bytes=17516022
          )

   8    7             INDEX (FAST FULL SCAN) OF 'XPKGEIORDERLINES' (UN DEVECOM.
          IQUE) (Cost=505 Card=473406 Bytes=17516022)                  US.ORACL
                                                                       E.COM

   9    3         INDEX (UNIQUE SCAN) OF 'XPKGEIORDERLINEDATES' (UNIQU DEVECOM.
          E)                                                           US.ORACL
                                                                       E.COM

  10    2       INDEX (RANGE SCAN) OF 'XPKGEIORDERLINETRACKING' (UNIQU DEVECOM.
          E) (Cost=3 Card=340573 Bytes=12601201)                       US.ORACL
                                                                       E.COM





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

17:42:33 SQL>
 

Tom,
The version is 9iR2 and OS is Windows 2000.

thanks

tom,

please see below for the  trace...
i am sorry, i did not get you .. what do you mean by "(hint try driving site hint")

19:15:27 SQL>conn bomdata/bomdatadv@om
Connected.
19:15:42 SQL>set autotrace on
19:15:54 SQL>set time on
19:15:57 SQL>select count(*) from OMDATAPULL@OMDATA;
select count(*) from OMDATAPULL@OMDATA
                                *
ERROR at line 1:
ORA-02019: connection description for remote database not found


Elapsed: 00:00:00.91
19:16:02 SQL>select count(*) from OMDATAPULL;

  COUNT(*)
----------
       138

Elapsed: 00:05:305.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14896 Card=1 Bytes=1
          21)

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=14896 Card=1 Bytes=121)
   3    2       NESTED LOOPS (Cost=14884 Card=4 Bytes=336)
   4    3         MERGE JOIN (Cost=14884 Card=4 Bytes=184)
   5    4           SORT (JOIN) (Cost=1075 Card=125795 Bytes=1132155)
   6    5             INDEX (FAST FULL SCAN) OF 'XPKGEIORDERS' (UNIQUE
          ) (Cost=96 Card=125795 Bytes=1132155)

   7    4           SORT (JOIN) (Cost=13809 Card=473406 Bytes=17516022
          )

   8    7             INDEX (FAST FULL SCAN) OF 'XPKGEIORDERLINES' (UN
          IQUE) (Cost=505 Card=473406 Bytes=17516022)

   9    3         INDEX (UNIQUE SCAN) OF 'XPKGEIORDERLINEDATES' (UNIQU
          E)

  10    2       INDEX (RANGE SCAN) OF 'XPKGEIORDERLINETRACKING' (UNIQU
          E) (Cost=3 Card=340573 Bytes=12601201)





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

19:21:13 SQL>
19:21:13 SQL>
19:22:11 SQL> 

Tom,
hash join is enabled...

please see..

SQL>sho parameter

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean TRUE
active_instance_count                integer
always_anti_join                     string  NESTED_LOOPS
always_semi_join                     string  STANDARD
aq_tm_processes                      integer 0
audit_trail                          string  NONE
background_core_dump                 string  partial
background_dump_dest                 string  e:\oracle\admin\DEVECOM\bdump
backup_tape_io_slaves                boolean FALSE
bitmap_merge_area_size               integer 1048576
blank_trimming                       boolean FALSE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
commit_point_strength                integer 1
compatible                           string  8.1.0
control_file_record_keep_time        integer 7
control_files                        string  e:\oracle\oradata\DEVECOM\cont
                                             rol01.ctl, e:\oracle\oradata\D
                                             EVECOM\control02.ctl, e:\oracl
                                             e\oradata\DEVECOM\control03.ct
                                             l
core_dump_dest                       string  %ORACLE_HOME%\RDBMS\TRACE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
cpu_count                            integer 1
create_bitmap_area_size              integer 8388608
cursor_sharing                       string  EXACT
cursor_space_for_time                boolean FALSE
db_block_buffers                     integer 6225
db_block_checking                    boolean FALSE
db_block_checksum                    boolean FALSE
db_block_lru_latches                 integer 1
db_block_max_dirty_target            integer 6225
db_block_size                        integer 8192
db_domain                            string

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
db_file_direct_io_count              integer 64
db_file_multiblock_read_count        integer 8
db_file_name_convert                 string
db_files                             integer 1024
db_name                              string  DEVECOM
db_writer_processes                  integer 1
dblink_encrypt_login                 boolean FALSE
dbwr_io_slaves                       integer 0
disk_asynch_io                       boolean TRUE
distributed_transactions             integer 500
dml_locks                            integer 748

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
enqueue_resources                    integer 1792
event                                string
fast_start_io_target                 integer 6225
fast_start_parallel_rollback         string  LOW
fixed_date                           string
gc_defer_time                        integer 10
gc_files_to_locks                    string
gc_releasable_locks                  integer 0
gc_rollback_locks                    string  0-128=32!8REACH
global_names                         boolean FALSE
hash_area_size                       integer 131072

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
hash_join_enabled                    boolean TRUE
hash_multiblock_io_count             integer 0
hi_shared_memory_address             integer 0
hs_autoregister                      boolean TRUE
ifile                                file
instance_groups                      string
instance_name                        string  DEVECOM
instance_number                      integer 0
java_max_sessionspace_size           integer 0
java_pool_size                       string  20971520
java_soft_sessionspace_limit         integer 0

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
job_queue_interval                   integer 10
job_queue_processes                  integer 4
large_pool_size                      string  614400
license_max_sessions                 integer 0
license_max_users                    integer 0
license_sessions_warning             integer 0
lm_locks                             integer 12000
lm_ress                              integer 6000
local_listener                       string
lock_name_space                      string
lock_sga                             boolean FALSE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_state_1             string  enable
log_archive_dest_state_2             string  enable
log_archive_dest_state_3             string  enable
log_archive_dest_state_4             string  enable
log_archive_dest_state_5             string  enable

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
log_archive_duplex_dest              string
log_archive_format                   string  ARC%S.%T
log_archive_max_processes            integer 1
log_archive_min_succeed_dest         integer 1
log_archive_start                    boolean FALSE
log_archive_trace                    integer 0
log_buffer                           integer 32768
log_checkpoint_interval              integer 10000
log_checkpoint_timeout               integer 1800
log_checkpoints_to_alert             boolean FALSE
log_file_name_convert                string

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
max_commit_propagation_delay         integer 700
max_dump_file_size                   string  10240
max_enabled_roles                    integer 30
max_rollback_segments                integer 37
mts_circuits                         integer 170
mts_dispatchers                      string  (PROTOCOL=TCP)(PRE=oracle.auro
                                             ra.server.SGiopServer)
mts_listener_address                 string
mts_max_dispatchers                  integer 5
mts_max_servers                      integer 20
mts_multiple_listeners               boolean FALSE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
mts_servers                          integer 1
mts_service                          string  DEVECOM
mts_sessions                         integer 165
nls_calendar                         string
nls_comp                             string
nls_currency                         string
nls_date_format                      string
nls_date_language                    string
nls_dual_currency                    string
nls_iso_currency                     string
nls_language                         string  AMERICAN

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
nls_numeric_characters               string
nls_sort                             string
nls_territory                        string  AMERICA
nls_time_format                      string
nls_time_tz_format                   string
nls_timestamp_format                 string
nls_timestamp_tz_format              string
object_cache_max_size_percent        integer 10
object_cache_optimal_size            integer 102400
open_cursors                         integer 300
open_links                           integer 4

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
open_links_per_instance              integer 4
ops_interconnects                    string
optimizer_features_enable            string  8.1.7
optimizer_index_caching              integer 0
optimizer_index_cost_adj             integer 100
optimizer_max_permutations           integer 80000
optimizer_mode                       string  CHOOSE
optimizer_percent_parallel           integer 0
oracle_trace_collection_name         string
oracle_trace_collection_path         string  %ORACLE_HOME%\OTRACE\ADMIN\CDF
                                             \

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
oracle_trace_collection_size         integer 5242880
oracle_trace_enable                  boolean FALSE
oracle_trace_facility_name           string  oracled
oracle_trace_facility_path           string  %ORACLE_HOME%\OTRACE\ADMIN\FDF
                                             \
os_authent_prefix                    string
os_roles                             boolean FALSE
parallel_adaptive_multi_user         boolean FALSE
parallel_automatic_tuning            boolean FALSE
parallel_broadcast_enabled           boolean FALSE
parallel_execution_message_size      integer 2148

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
parallel_instance_group              string
parallel_max_servers                 integer 5
parallel_min_percent                 integer 0
parallel_min_servers                 integer 0
parallel_server                      boolean FALSE
parallel_server_instances            integer 1
parallel_threads_per_cpu             integer 2
partition_view_enabled               boolean FALSE
plsql_v2_compatibility               boolean FALSE
pre_page_sga                         boolean FALSE
processes                            integer 150

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
query_rewrite_enabled                boolean FALSE
query_rewrite_integrity              string  enforced
rdbms_server_dn                      string
read_only_open_delayed               boolean FALSE
recovery_parallelism                 integer 0
remote_dependencies_mode             string  TIMESTAMP
remote_login_passwordfile            string  EXCLUSIVE
remote_os_authent                    boolean FALSE
remote_os_roles                      boolean FALSE
replication_dependency_tracking      boolean TRUE
resource_limit                       boolean FALSE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
resource_manager_plan                string
rollback_segments                    string
row_locking                          string  always
serial_reuse                         string  DISABLE
serializable                         boolean FALSE
service_names                        string  DEVECOM
session_cached_cursors               integer 0
session_max_open_files               integer 10
sessions                             integer 170
shadow_core_dump                     string  partial
shared_memory_address                integer 0

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
shared_pool_reserved_size            string  1572864
shared_pool_size                     string  31457280
sort_area_retained_size              integer 65536
sort_area_size                       integer 65536
sort_multiblock_read_count           integer 2
sql92_security                       boolean FALSE
sql_trace                            boolean FALSE
sql_version                          string  NATIVE
standby_archive_dest                 string  %ORACLE_HOME%\RDBMS
star_transformation_enabled          string  FALSE
tape_asynch_io                       boolean TRUE

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
text_enable                          boolean FALSE
thread                               integer 0
timed_os_statistics                  integer 0
timed_statistics                     boolean FALSE
tracefile_identifier                 string
transaction_auditing                 boolean TRUE
transactions                         integer 187
transactions_per_rollback_segment    integer 5
use_indirect_data_buffers            boolean FALSE
user_dump_dest                       string  e:\oracle\admin\DEVECOM\udump
utl_file_dir                         string
SQL>

you have any comments on this?

Thanks as always 

how did you make it our from the above paramters that the db is - " I see lots of swapping to temp in your life. "

Thanks Tom!

Hi Tom,

If I create a view on a remote database entirely (Option 1), and then use that view to get my data to the local database, how is that different performance-wise from creating a local view using dblinks to the remote database and its tables (Option 2)? Setting aside for a moment the advantages of using Option 2 such as metadata imported into local database, dependent objects compile even if remote db is unavailable etc. Wouldn't the Option 1 view be optimized far better than the Option 2 view?

Option 1:
REMOTE_DB> create view v1 as select * from t1, t2
where t1.id = t2.id;
...
cursor c1 is select * from v1@rdb;
...

Option 2:
LOCAL_DB> create view v1 as select * from t1@rdb, t2@rdb
where t1.id = t2.id;
...
cursor c1 is select * from v1;
...

Thanks in advance.

Kashif

Tom

As you say Views are nothing more than stored queries.
I've created a View for users to access columns from 3
separate tables. They are now wanting to be able to pass
values to the View for querying.

example:
"select * from payack_view
where batchno between &batch1 and &batch2"

within the Payack_View itself how would this be accomplished?

The view itself is as follows:

CREATE OR REPLACE VIEW payack_view AS
SELECT a1,b2,c3,......etc
from a,b,c
where batchno ????? and b1=a1 and c1=b1.

would this require use of a function(I was hoping not)

thanks for any tips.

didn't realize it was that simple....I always tend to see
things as being more complicated than they really are...

as always..thanks for all you do here.
Denise

Hi Tom,

I have a doubt. While going through the SQL reference i read that we can create unique and primary key constraints on views using NOVALIDATE DISABLE command. But what i was thinking if we cannot enable those constraints then whats the use of having disabled constraints on a view.

As far as i understand, we cannot enable them because enabling the PK or UK (if on a table) will create an index if its not associated with any index. But in views this is not possible. So why is this clause available. Im not able to figure out the purpose of having a disabled constraint on a view.

Thanks and Regards

Thanks for answering Tom, but i have few more doubts..

o it gives the database (optimizer) more information.

-- more information about what? i mean can we have a test case where this is useful.

o They will also be used in ad-hoc query tools (if the tools choose to) to help you build queries.

-- How do we know when to create such constraints. I have doubt on who decides when to have such a constraint or is it something the tool does internally.

Thanks and Regards

Tom, does it mean that view constraints are useful for MVs only? thanks.

thnx sir
but if u plz could u tell me
if i can pass a parameter to a view
if i could how to be created(syntax)

Hi

we have some problems with our database. The problem is when we enable query rewrite a query against a specific view runs slow slow. I thought Query Rewrite only works with MVs...?

How come?

SELECT fill.bo_id_contact, fill.role_type_id_cntct, fill.bo_id_cust,
fill.role_type_id_cust, fill.rbtacctid, fill.rbtbillid, fill.rbtbillno,
fill.rbtbillstatus, fill.currency_cd, fill.rbttotaldue,
TO_CHAR (fill.rbtbilldate, 'YYYY-MM-DD'),
TO_CHAR (fill.rbtbillduedate, 'YYYY-MM-DD')
FROM sysadm.ps_rbt_c_bill_vw fill
WHERE bo_id_cust = 1000000000000002483252
AND bo_id_contact = 0
AND rbtacctid = '4295845'

WITH QUERY REWRITE
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 76 | 13 |
| 1 | HASH JOIN | | 1 | 76 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID | PS_RBT_ACCOUNT | 1 | 30 | 3 |
| 3 | INDEX UNIQUE SCAN | PS_RBT_ACCOUNT | 2454K| | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID | PS_RBT_BILL | 1 | 46 | 8 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | |
| 6 | BITMAP MINUS | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 8 | INDEX RANGE SCAN | PSARBT_BILL | 18 | | 3 |
| 9 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 10 | SORT ORDER BY | | | | |
| 11 | INDEX RANGE SCAN | PS3RBT_BILL | 18 | | 56902 |
-------------------------------------------------------------------------------------

WITHOUT QUERY REWRITE
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 567 | 17 |
| 1 | NESTED LOOPS | | 7 | 567 | 17 |
|* 2 | TABLE ACCESS BY INDEX ROWID| PS_RBT_ACCOUNT | 1 | 32 | 3 |
|* 3 | INDEX UNIQUE SCAN | PS_RBT_ACCOUNT | 2397K| | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| PS_RBT_BILL | 7 | 343 | 14 |
|* 5 | INDEX RANGE SCAN | PS2RBT_BILL | 11 | | 2 |
--------------------------------------------------------------------------------


I dont get why query rewrite is firing...

ttd@TTD>SELECT fill.bo_id_contact, fill.role_type_id_cntct, fill.bo_id_cust,
2 fill.role_type_id_cust, fill.rbtacctid, fill.rbtbillid, fill.rbtbillno,
3 fill.rbtbillstatus, fill.currency_cd, fill.rbttotaldue,
4 TO_CHAR (fill.rbtbilldate, 'YYYY-MM-DD'),
5 TO_CHAR (fill.rbtbillduedate, 'YYYY-MM-DD')
6 FROM sysadm.ps_rbt_c_bill_vw fill
7 WHERE bo_id_cust = 1000000000000002483252
8 AND bo_id_contact = 0
9 AND rbtacctid = '4295845' ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=76)
1 0 HASH JOIN (Cost=13 Card=1 Bytes=76)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RBT_ACCOUNT' (Cost=3 Card=1 Bytes=30)
3 2 INDEX (UNIQUE SCAN) OF 'PS_RBT_ACCOUNT' (UNIQUE) (Cost=2 Card=2454650)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RBT_BILL' (Cost=8 Card=1 Bytes=46)
5 4 BITMAP CONVERSION (TO ROWIDS)
6 5 BITMAP MINUS
7 6 BITMAP CONVERSION (FROM ROWIDS)
8 7 INDEX (RANGE SCAN) OF 'PSARBT_BILL' (NON-UNIQUE) (Cost=3 Card=18)
9 6 BITMAP CONVERSION (FROM ROWIDS)
10 9 SORT (ORDER BY)
11 10 INDEX (RANGE SCAN) OF 'PS3RBT_BILL' (NON-UNIQUE) (Cost=56902 Card=18)



ttd@TTD>alter session set query_rewrite_enabled = false;

Session altered.

ttd@TTD>SELECT fill.bo_id_contact, fill.role_type_id_cntct, fill.bo_id_cust,
2 fill.role_type_id_cust, fill.rbtacctid, fill.rbtbillid, fill.rbtbillno,
3 fill.rbtbillstatus, fill.currency_cd, fill.rbttotaldue,
4 TO_CHAR (fill.rbtbilldate, 'YYYY-MM-DD'),
5 TO_CHAR (fill.rbtbillduedate, 'YYYY-MM-DD')
6 FROM sysadm.ps_rbt_c_bill_vw fill
7 WHERE bo_id_cust = 1000000000000002483252
8 AND bo_id_contact = 0
9 AND rbtacctid = '4295845' ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=1 Bytes=76)
1 0 NESTED LOOPS (Cost=24 Card=1 Bytes=76)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RBT_ACCOUNT' (Cost=3 Card=1 Bytes=30)
3 2 INDEX (UNIQUE SCAN) OF 'PS_RBT_ACCOUNT' (UNIQUE) (Cost=2 Card=2454650)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'PS_RBT_BILL' (Cost=21 Card=1 Bytes=46)
5 4 INDEX (RANGE SCAN) OF 'PS2RBT_BILL' (NON-UNIQUE) (Cost=2 Card=18)

Hi

yes we have function based indexes. However as you can see it´s no good, making the query very slow... How can this be?!

Tom

Correct me if i am wrong but when you join a view to a view it should be the same as joining thme if the views were in fact tables??

If that is the case then please can you explain the following results.
I have 2 views (haven't posted their code here):

12:41:28 OPT1@OP5>desc sbe_table9a
Name Null? Type
----------------------------------------------------------------------- -------- ------------------
ORG_ID NOT NULL NUMBER(38)
SECTION NUMBER
VOUCHER_CODE VARCHAR2(6)
ONE_LENS NUMBER
BOTH_LENSES NUMBER

12:45:29 OPT1@OP5>desc sbe_table9b
Name Null? Type
----------------------------------------------------------------------- -------- ------------------
ORG_ID NUMBER(38)
REPAIR_CODE VARCHAR2(5)
BASIC_COUNT NUMBER
SMALL_COUNT NUMBER


Now their selects look like:

12:54:20 OPT1@OP5>select a.*
12:54:20 2 from sbe_table9a a;

ORG_ID SECTION VOUCHE ONE_LENS BOTH_LENSES
---------- ---------- ------ ---------- -----------
142 1 A 0 0
142 1 B 0 0
142 1 C 0 0
142 1 D 0 0
142 1 E 0 0
142 1 F 0 0
142 1 G 0 0
142 1 H 0 0
142 1 TOTAL 0 0
142 2 Prisms 0 0
142 2 Tints 0 0
142 2 TOTAL 0 0
213 1 A 8 54
213 1 B 0 13
213 1 C 0 0
213 1 D 0 0
213 1 E 0 0
213 1 F 0 0
213 1 G 0 0
213 1 H 0 0
213 1 TOTAL 8 67
213 2 Prisms 0 0
213 2 Tints 0 0
213 2 TOTAL 0 0
214 1 A 11 120
214 1 B 0 18
214 1 C 0 1
214 1 D 0 1
214 1 E 0 2
214 1 F 0 0
214 1 G 0 0
214 1 H 0 0
214 1 TOTAL 11 142
214 2 Prisms 0 0
214 2 Tints 0 0
214 2 TOTAL 0 0
220 1 A 0 0
220 1 B 0 0
220 1 C 0 0
220 1 D 0 0
220 1 E 0 0
220 1 F 0 0
220 1 G 0 0
220 1 H 0 0
220 1 TOTAL 0 0
220 2 Prisms 0 0
220 2 Tints 0 0
220 2 TOTAL 0 0

48 rows selected.

Elapsed: 00:00:00.09
12:54:21 OPT1@OP5>
12:54:21 OPT1@OP5>select b.*
12:54:21 2 from sbe_table9b b;

ORG_ID REPAI BASIC_COUNT SMALL_COUNT
---------- ----- ----------- -----------
142 Front 0 0
213 Front 2 0
214 Front 9 0
220 Front 0 0

4 rows selected.


If I join them i get:

12:54:22 OPT1@OP5>select a.*,b.*
12:54:52 2 from sbe_table9a a,
12:54:52 3 sbe_table9b b
12:54:52 4 where a.org_id = b.org_id;

ORG_ID SECTION VOUCHE ONE_LENS BOTH_LENSES ORG_ID REPAI BASIC_COUNT SMALL_COUNT
---------- ---------- ------ ---------- ----------- ---------- ----- ----------- -----------
142 1 A 0 0 142 Front 0 0

1 row selected.

Elapsed: 00:00:00.05

Which is NOT what I was expecting.

However if i create the views as tables and join them i get:

12:55:51 OPT1@OP5>create table t9a as
12:55:56 2 select a.*
12:55:56 3 from sbe_table9a a;

Table created.

Elapsed: 00:00:00.06
12:55:56 OPT1@OP5>
12:55:56 OPT1@OP5>
12:55:57 OPT1@OP5>create table t9b as
12:55:57 2 select b.*
12:55:57 3 from sbe_table9b b;

Table created.

Elapsed: 00:00:00.02
12:55:57 OPT1@OP5>select a.*,b.*
12:56:02 2 from t9a a,
12:56:02 3 t9b b
12:56:02 4 where a.org_id = b.org_id;

ORG_ID SECTION VOUCHE ONE_LENS BOTH_LENSES ORG_ID REPAI BASIC_COUNT SMALL_COUNT
---------- ---------- ------ ---------- ----------- ---------- ----- ----------- -----------
142 1 A 0 0 142 Front 0 0
142 1 B 0 0 142 Front 0 0
142 1 C 0 0 142 Front 0 0
142 1 D 0 0 142 Front 0 0
142 1 E 0 0 142 Front 0 0
142 1 F 0 0 142 Front 0 0
142 1 G 0 0 142 Front 0 0
142 1 H 0 0 142 Front 0 0
142 1 TOTAL 0 0 142 Front 0 0
142 2 Prisms 0 0 142 Front 0 0
142 2 Tints 0 0 142 Front 0 0
142 2 TOTAL 0 0 142 Front 0 0
213 1 A 8 54 213 Front 2 0
213 1 B 0 13 213 Front 2 0
213 1 C 0 0 213 Front 2 0
213 1 D 0 0 213 Front 2 0
213 1 E 0 0 213 Front 2 0
213 1 F 0 0 213 Front 2 0
213 1 G 0 0 213 Front 2 0
213 1 H 0 0 213 Front 2 0
213 1 TOTAL 8 67 213 Front 2 0
213 2 Prisms 0 0 213 Front 2 0
213 2 Tints 0 0 213 Front 2 0
213 2 TOTAL 0 0 213 Front 2 0
214 1 A 11 120 214 Front 9 0
214 1 B 0 18 214 Front 9 0
214 1 C 0 1 214 Front 9 0
214 1 D 0 1 214 Front 9 0
214 1 E 0 2 214 Front 9 0
214 1 F 0 0 214 Front 9 0
214 1 G 0 0 214 Front 9 0
214 1 H 0 0 214 Front 9 0
214 1 TOTAL 11 142 214 Front 9 0
214 2 Prisms 0 0 214 Front 9 0
214 2 Tints 0 0 214 Front 9 0
214 2 TOTAL 0 0 214 Front 9 0
220 1 A 0 0 220 Front 0 0
220 1 B 0 0 220 Front 0 0
220 1 C 0 0 220 Front 0 0
220 1 D 0 0 220 Front 0 0
220 1 E 0 0 220 Front 0 0
220 1 F 0 0 220 Front 0 0
220 1 G 0 0 220 Front 0 0
220 1 H 0 0 220 Front 0 0
220 1 TOTAL 0 0 220 Front 0 0
220 2 Prisms 0 0 220 Front 0 0
220 2 Tints 0 0 220 Front 0 0
220 2 TOTAL 0 0 220 Front 0 0

48 rows selected.


Which is what I expected when i joined the views.
Please tell me what is going on here???

(If necessary i can post the view code too)

Tom, please read the following example:

create table t1 (t1_id number, t1_date date, t1_split number, t1_calls number,
t1_avg_talktime number, t1_avg_acw number, t1_avg_hold number, t1_goal number);
create table t2 (t2_split number, t2_splitname varchar2(100), t2_start_date date, t2_end_date date);
create index t1_idx1 on t1 (t1_id, t1_date, t1_split);
create index t1_idx2 on t1 (t1_id, t1_date);
create index t1_idx3 on t1 (t1_id);
create or replace view v1_t1 as select
t1_id, t1_date,
t2_splitname,
extract(year from t1_date) t1year,
extract(month from t1_date) t1month,
sum(t1_calls)over(partition by t1_id, t1_date) sum_callss1,
t1_calls sum_callss2,
sum(t1_calls)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date)) sum_callss3,
sum(t1_calls)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date), t1_split) sum_callss4,
sum(t1_calls)over(partition by t1_id, extract(year from t1_date)) sum_callss5,
sum(t1_calls)over(partition by t1_id, extract(year from t1_date), t1_split) sum_callss6,
avg(t1_avg_talktime)over(partition by t1_id, t1_date) avg_tt1,
t1_avg_talktime avg_tt2,
avg(t1_avg_talktime)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date)) avg_tt3,
avg(t1_avg_talktime)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date), t1_split) avg_tt4,
avg(t1_avg_talktime)over(partition by t1_id, extract(year from t1_date)) avg_tt5,
avg(t1_avg_talktime)over(partition by t1_id, extract(year from t1_date), t1_split) avg_tt6,
avg(t1_avg_acw)over(partition by t1_id, t1_date) avg_acw1,
t1_avg_acw avg_acw2,
avg(t1_avg_acw)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date)) avg_acw3,
avg(t1_avg_acw)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date), t1_split) avg_acw4,
avg(t1_avg_acw)over(partition by t1_id, extract(year from t1_date)) avg_acw5,
avg(t1_avg_acw)over(partition by t1_id, extract(year from t1_date), t1_split) avg_acw6,
avg(t1_avg_hold)over(partition by t1_id, t1_date) avg_hold1,
t1_avg_hold avg_hold2,
avg(t1_avg_hold)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date)) avg_hold3,
avg(t1_avg_hold)over(partition by t1_id, extract(year from t1_date), extract(month from t1_date), t1_split) avg_hold4,
avg(t1_avg_hold)over(partition by t1_id, extract(year from t1_date)) avg_hold5,
avg(t1_avg_hold)over(partition by t1_id, extract(year from t1_date), t1_split) avg_hold6
from t1, t2 where t1_split=t2_split and t1_date between t2_start_date and nvl(t2_end_date, t1_date);

If I execute, for example the above select (without creationg the view) and adding
"... and t1_id=1291" in the where clause, the explain plan i get is:
Description
SELECT STATEMENT, GOAL = CHOOSE
WINDOW SORT
WINDOW SORT
WINDOW SORT
NESTED LOOPS
TABLE ACCESS FULL
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN

So far so good but as i'm intended to retrieve this same information in the web i use view v1_t1 in order to wrapp the complexity of the query.

When i execute:
select * from v1_t1 where t1_id=1291;
The explain plan is:
SELECT STATEMENT, GOAL = CHOOSE
VIEW
WINDOW SORT
WINDOW SORT
WINDOW SORT
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL
FILTER
SORT JOIN
TABLE ACCESS FULL

Of course my real world table has 35132 rows and it seems that the access full i get when i use the view prevent my users from retrieving the information. i'm working in 8.1.7 and global hints seems to no aply at all and i cannot make the view access the index (which is the best way because the first example take less than 10 seconds to finish and the view seems to run forever).
As always, any help will be appreciate

ops$tkyte@ORA817DEV> select * from v1_t1 where t1_id = 1234;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW OF 'V1_T1'
   2    1     WINDOW (SORT)
   3    2       WINDOW (SORT)
   4    3         WINDOW (SORT)
   5    4           NESTED LOOPS
   6    5             TABLE ACCESS (FULL) OF 'T2'
   7    5             TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   8    7               INDEX (RANGE SCAN) OF 'T1_IDX3' (NON-UNIQUE)
 

ops$tkyte@ORA817DEV>  select * from v1_t1 where t1_id = 1234;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=412)
   1    0   VIEW OF 'V1_T1' (Cost=6 Card=1 Bytes=412)
   2    1     WINDOW (SORT)
   3    2       WINDOW (SORT)
   4    3         WINDOW (SORT)
   5    4           NESTED LOOPS (Cost=1 Card=1 Bytes=170)
   6    5             TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=1 Bytes=83)
   7    5             TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   8    7               INDEX (RANGE SCAN) OF 'T1_IDX1' (NON-UNIQUE)
 

does not reproduce for me using either rbo or cbo.

How's about you run the test case and cut and paste the plans straight out of sqlplus.

(and i seriously doubt it is "preventing" your users from doing anything.  35k rows is tiny) 

Oracle 9i -
I have 2 tables. Table 1 say has col1, col2, col3 and table2 has col4, col5, col1.

I want to match col1 from table 1 to col1 from table2. I cannot create a view with join which I thought was the best thing to do because of the other software that will read these tables. In other words they want duplictae columns in 2 tables but no view. Other thing I thought was to have trigger on this, but don't know if thats a good idea.

Can I use function based indexes to populate the other column ? The column being copied is varchar2 and the join is on integer column. I do not have real column names as yet.. but they have asked if its feasible to do this way.

These 2 tables have one on one join.

Or with your expertise is there any other way to do this ??

Thanks so much

I was told that we cannot create a view because if we do that they have to define it in their software as new object and make patch or something.. don't know how their software works. But they said they cannot do it in this release..This table will not have many rows. Is there 2nd best way to do this ?

Hi Tom,

I am in problem how to create a view (e.g. create or replace view vTest as select * from Table1, like this type view at database level)
from forms block records selecting (all / partial records) by checkbox and later I will print out a report (*.rdf) from that view under the trigger when_button_press.

My system: Oracle8i, Developer 6i & Windows 2000.

Dear Tom,
I have a table and data like this:
yearmonth 200407.............
customer c1 c1 c2....
item t1 t2 t1...
rank r1 r3 r2...
The maximum number of items and ranks are known, and are 10.
User wants to see the o/p as below (example for a specific month)
yearmonth customer item1 item1_rank item2 item2_rank item3 item3_rank......
200407 c1 t1 r1 t2 r3

c2 t1 r2
The table is quite huge (around 15 million records).
Initially, I was thinking of creating a view like this:
SELECT customer, MAX(DECODE(item,'t1',item,null)) item1, MAX(DECODE(rank,'r1',rank,null)) item1_rank......
GROUP BY customer;
It is DW query, and the load to this table is monthly, and table is partitioned on yearmonth.
Because of performance reasons, and monthly load, I am thinking of creating a Materiliazed View on the above query.
Any suggestion will be quite helpful.
Thanks

Hi Tom,
Thanks for help.
Is the query approach correct?
Regards

Hi Tom,
Is there any way to cache a view?
Do you have any trick to do that?


SQL> alter view emp_view cache
  2  /
alter view emp_view cache
                    *
ERROR at line 1:
ORA-00922: missing or invalid option
 

Tom,
I'm working with a third party tool. The tool can display the result of a view if the view's name is passed. Now I want to display only part of the view depends on user's input. For example, if a user input case_id = 100, I only want to display information related to case_id = 100. I can only pass a view/table name to the third party tool. How can I accomplish the requirement?

Thank you very much.

Hi tom
while dropping a view is there an option that
allows us to not have an error generated if the
view did not exist? (kinda like "rm -rf" in unix)..

thanx!

  1  create or replace procedure drop_view( p_viewname in varchar2 )
  2  authid current_user
  3  as
  4  begin
  5    for x in ( select view_name from user_views
  6             where view_name = upper(p_viewname) )
  7    loop
  8       execute immediate 'drop view ' || x.view_name;
  9      dbms_output.put_line( 'Dropped view ' || x.view_name );
 10    end loop;
 11* end;
ops$tkyte@ORA9IR2> /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create view v as select * from dual;
 
View created.
 
ops$tkyte@ORA9IR2> exec drop_view('v');
Dropped view V
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec drop_view('v');
 
PL/SQL procedure successfully completed. 

was lookin for an sql based option which i take it
does not exist...

Thanx for your promptness as always!

Here's a variant I use often - do you see any lurking problem ?

SQL> create or replace procedure drop_view( p_viewname in varchar2 )
  2  authid current_user
  3  as
  4     view_does_not_exists exception;
  5     pragma exception_init (view_does_not_exists, -00942);
  6  begin
  7     execute immediate 'drop view ' || p_viewname;
  8     dbms_output.put_line( 'Dropped view ' ||  p_viewname);
  9  exception
 10     when view_does_not_exists then
 11       null;
 12  end;
 13  /

SQL> set serveroutput on
SQL> create view v as select * from dual;

View created.

SQL> exec drop_view('v');
Dropped view v

PL/SQL procedure successfully completed.

SQL> exec drop_view('v');

PL/SQL procedure successfully completed. 

hi tom
I am getting ora-600 when compiling a package
that uses a view which has a with clause and has a union in it. I get the same error when I compile the select
in a separate anonymous block.
When I create a simple with clause similar to the one
using selects from dual, it does not reproduce.
Are you aware of any bugs that may be related to this
problem? This happens in 9.1.0.5.

Thanx!
Errors for PACKAGE BODY DUMMY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
21/5 PL/SQL: SQL Statement ignored
222/10 PL/SQL: ORA-00600: internal error code, arguments: [kqludp2], [],
[], [], [], [], [], []

Hi Tom
I have a situation where I depend on some tables and
their data in my queries. These tables are in the hands of
an external module and so we need to define an interface so
that my code does not break if there is a change in the
table.

1. One way is to have views maintained by the other module
team.For some reason (that I would rather not go
into here) this solution does not work for us though
I prefer this solution.
2. Another way is to define a package and methods in it
that give me well defined set of data through functions
and procedures. The problem here is that this
forces me not use joins in my queries (currently I am
using joins with views that depend on the above tables.)
I am thinking may be I create package and procedures.
In this procedures I can use pipe lined functions
to return my data. This way I can join with the results
of the procedure/function.
What are yout thoughts on this approach? Will this
scale well if you are returning large data?
Any issues?

Thanx!

Thank you for the lightning fast response - can you
post the brand name of your coffee please?;)

"I'm inclined to believe that #1 sounds right if you need read only access.

a combination of #1 and #2 is right if you need read (views) and write (API)
access.".

I just need read access from these tables. External
modules are responsible to write to them.

#1 does work for you actually, any technical reasons behind the decision?

Actually, no ..but unfortately due to some non technical
reasons it is not an option.

So I take it that the following should be ok:
1. create a package
2. In the package create pipelined functions that
pipe the results of the qureies from these views
3. In my code, I join as if I would have with views,
except that I join with these pipelined functions
instead. This should also scale well with large
data set since piping does not "store" the intermediate
data anywhere.

Also, if I use these pipelined functions in some other views
are there any issues that I should be aware of. I have
heard there were some bugs when we use pipelined functions in a view?

Many thanx!:)

hmmmm.. that is a good point - very good point -
completely forgot those critical things called indexes.

OK...how about comparing the following approaches:

1. using packaged procedures for getting the data
and changing my code to make more procedural
( since I cant use some of the joins in the
current code.)
2. using pipeline function method
3. a third option could be to do this:
a. ask them to create procedures in the package
that give back "cursors" to the selects
b. Insert the data from the cursor directly into
a temporary table.
c. Executing my selects using current views - except
they will be selecting from the temporary tables
now.
d. delete from the temporary table once you are done
(since we use connection pooling.)
c. note you can create appropriate indexes on the temp
table.

I do agree that views are the correct way to go (said
that in the very beginning.) But what is the second
best approach?

Your comments?

thank you for the most valuable service to Oracle community!

thanx!:)

I have two set of views - the first set look like:
select ...
from t1
where t1.name = ...

The second set is where the above has to be slightly
modified as:

select ...
from t1
where t1.name in( select from t2_that_has_t1_names)
...

One approach is to just create two sets of views
(there will be around 15 views each - total 30 views.)

Another approach is to create only the second set
of views as follows:
select ...
from t1
where t1.name in(
select from gtt_with_appropriate_t1_names)

The "gtt_with_appropriate_t1_names" is a GTT which
is populated with appropriate sets of data
before each query (you delete and insert into this
before each query runs on the view.) The first set
of views is "subsumed" by this set of views since
it is a special case.

Now the data in the GTT could go up to say 15K rows
or so - the data would be just a bunch of strings of varchar2(256).
Delete/inserts on GTTs would be relatively
low overhead; so is it worth maintaining two
separate sets of views instead of trying to generalize
into one set?
Maintaining 2 sets reduces maintainability of views
but perhaps it is worth the trade off in terms of performance.

Assume that the query that populates the GTT is relatively
fast.

The number of times the queries on the views is run
is not very high. (It is used to display data from
repository in real time.) The slowness of the query
is mitigated by ui constructs such as progress bar etc...
I am aware of the background approach in which you
submit a job but that does not meet our requirements.

What do you think?

Thanx!

I am familiar with FGAC though I did not think
of it as a solution here.

Here the query is executed by a PL/SQL procedure(psuedo)
as follows:
proc( info_type, t1_name)
begin
if info_type = 1
open cursor for
select ..
from t1
where t1.name = 't1_name';
else
select ...
from t1
where t1.name in( select from t2_that_has_t1_names
where info_type = :info_type)
end if;
end;

Since each of these selects are based on views,
I will end up creating 2 sets of views or
I have to use GTT with just one set of view
that essentially uses the second set of views
with the first set as a special case.

Does FGAC make sense here?I guees here you
would set the "info_type" info in a context
variable and in your "security" function
you would use that to generate the predicate
dynamically?
Interesting.

In terms of performance, have you noticed a "significant" overhead in the intermediate step FGAC introduces to change the submitted query dynamically?
Which approach (GTT vs FGAC) would you choose given
the info?

Hi Tom,

I have a table named Table1 with two columns.

create table Table1 (
C1 Varchar2(10),
C2 Number(2)):

Data in Table1 like as
C1 C2
--- ---
A 2
B 1
C 3
D 5

and now I wish to get out put like as
C1 C2
--- ---
A 1
A 1
B 1
C 1
C 1
C 1
D 1
D 1
D 1
D 1
D 1

How it is possible?
Kindly help me.

Thanks a lot Tom.
My problem solved.

Hi Tom,

I understand that we should avoid using " select * " when creating view and newly added columns will not show up in the view if we use " select * ".  But I don't know why the following view will not recompile.  Thanks so much for your help.  (9204 on Solaris 5.9)

SQL> create table t1(c1 number, c2 number);

Table created.

SQL> 
SQL> create table t2(c4 number, c5 number);

Table created.

SQL> create or replace view vw as select t1.*, t2.c4
  2  from t1, t2
  3  where t1.c1=t2.c4;

View created.

SQL> desc vw
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 C1                                                 NUMBER
 C2                                                 NUMBER
 C4                                                 NUMBER

SQL> alter table t1
  2  add(c3 number);

Table altered.

SQL> desc vw
ERROR:
ORA-24372: invalid object for describe


SQL> alter view vw compile;

Warning: View altered with compilation errors.

SQL> show error
No errors.
SQL>  

ops$tkyte@ORA9IR2> show errors view vw;
Errors for VIEW VW:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-01730: invalid number of column names specified



the view text in this case was stored with "*", it is NOT expanded out:

ops$tkyte@ORA9IR2> select text from user_views where view_name = 'VW';
 
TEXT
-------------------------------------------------------------------------------
select t1.*, t2.c4
 from t1, t2
 where t1.c1=t2.c4



that view needs to be "created or replaced" (but fix it, don't use *) 

I have an strange problem where joining a table with a view, made out of a complex select statement, would make the optimizer full scan every underlaying table of the view, but if I hard code the values, it would pick up the indexes. I have set up an small table of even 5 values, and even thought the optimizer would then hit the wrong plan even with those values. Our tables have up to date statistics, as well as the other table we are testing it against. I am trying to follow your example on "do everything in sql" and thus I rewrote what was a long procedure into a complex view, but it is not helping me much since it takes a long of time, and wont return any rows. The tables underlying are only 12, and two of them have 1 million + rows, but the others have less than 500k.

I just would like to know if you have been under any similar situations, and if so, any insights or suggestion I could follow to make the optimizer choose the perfect plan for this. I know you would wish me to reproduce you with some example code, but I have set up example with smaller tables and the optimizer do its job -- choosing the perfect and appropiate plan, but it is just for these long tables that it is not doing so.

Thank you for reading, anyhow.

Sorry Tom for not being that clear,

For example, the two views I have are of the kind:

select indexed_column_one,
indexed_column_two,
..................
from (
select indexed_column_one,
indexed_column_two,
..................
from table_with_1_million_plus_rows,
other_tables_with less rows,
...............................
where some_predicates_here = values
...............
)

Both views can be joined together, in a bill master to bill details relationship. When I say "if I hard code the values" is that when I do, for example, this..

select *
from v_bills v1, v_bills_details v2
where v1.index_col = v2.v1_index_col
..............................
and v1.index_col = the_hard_code_value_here

.. there, the optimizer will pick up the correct plan, choosing all of the indexes on the underlying tables. But the problem raises when I try to do the following:

select *
from v_bills v1, v_bills_details v2, another_table_t t
where v1.index_col = v2.v1_index_col
..............................
and v1.index_col = t.index_col

.. there, the optimizer will make, for each value on t, a full scan on each of the underlying tables of both views, causing this to take a lot of resources.

The values on which I join the views on, on table another_table_t, are of the same datat type of those on indexed values on the views.

I have now determine it is something strange.

I told you I have views like..

select ....
from (
select ...,
( select ...
from ...
where ... ),
....
from ......,
.....
where ......
and .....
... .....
)

As you can see, I have scalar co-related subqueries there. In almost all the cases, I have to get aggregates, like count( * ) and max( * ) for a, so what I did to avoid hitting the table twice ( and therefore having two separate co-related subqueries for that ), was to "concatenate" this values.. as of select count( * ) || '|' || max( value ) on the inner query, and then on the inline from view, use subroutines of substr() and instr() to get those values separate. But here comes the problem, if I try to use any of these functions on the inline from view, the plan would totally go crazy and do all full scans.. but If I let the values "as if" ( 12123123|3423423 for example ), it pick up the correct path of the plan.

So, basically.. I want to know why is doing that ( please, an explanation, not just how I will solve this -- because I want to learn why oracle is doing this and logically why it has choosen to do so ), and also if you could tell me another way around this ( considering that I dont always have numbers concatenation -- I know I can do math with that -- but I do also have string values as well, i.e.: whatever|whatever ).

Thank you!

SQL@8i> select *
2 from v_xxx v, tmp_xxx t
3 where v.xxx_xxx = t.xxx_xxx
4 and v.xxx_xxx = t.xxx_xxx
5 and t.sesion = 123456;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89 Card=1 Bytes=416)
1 0 NESTED LOOPS (OUTER) (Cost=89 Card=1 Bytes=416)
2 1 NESTED LOOPS (Cost=88 Card=1 Bytes=411)
3 2 NESTED LOOPS (OUTER) (Cost=87 Card=1 Bytes=406)
4 3 NESTED LOOPS (Cost=86 Card=1 Bytes=392)
5 4 NESTED LOOPS (Cost=82 Card=1 Bytes=329)
6 5 NESTED LOOPS (Cost=47 Card=1 Bytes=297)
7 6 NESTED LOOPS (Cost=12 Card=1 Bytes=265)
8 7 NESTED LOOPS (Cost=11 Card=1 Bytes=251)
9 8 NESTED LOOPS (Cost=10 Card=1 Bytes=211)
10 9 NESTED LOOPS (Cost=7 Card=1 Bytes=198)
11 10 NESTED LOOPS (Cost=6 Card=1 Bytes=189)
12 11 NESTED LOOPS (Cost=5 Card=1 Bytes=175)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'TMP_XXX' (Cost=1 Card=1 Bytes=48)
14 13 INDEX (RANGE SCAN) OF 'TMP_XXX_IDX' (NON-UNIQUE)
15 12 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXXX' (Cost=4 Card=5016124 Bytes=637047748)
16 15 INDEX (RANGE SCAN) OF 'XXXXXXXX_KEY' (NON-UNIQUE) (Cost=3 Card=5016124)
17 11 TABLE ACCESS (BY INDEX ROWID) OF 'XXXX' (Cost=1 Card=9 Bytes=126)
18 17 INDEX (UNIQUE SCAN) OF 'XXXX_PK' (UNIQUE)
19 10 TABLE ACCESS (FULL) OF 'XXXXX' (Cost=1 Card=124 Bytes=1116)
20 9 INLIST ITERATOR
21 20 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXX' (Cost=3 Card=62864 Bytes=817232)
22 21 INDEX (RANGE SCAN) OF 'XXXXXXX_IDX_001' (NON-UNIQUE) (Cost=2 Card=62864)
23 8 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXXX' (Cost=1 Card=268011 Bytes=10720440)
24 23 INDEX (UNIQUE SCAN) OF 'XXXXXXXX_PK' (UNIQUE)
25 7 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXXXX' (Cost=1 Card=25 Bytes=350)
26 25 INDEX (UNIQUE SCAN) OF 'XXXXXXXXX_PK' (UNIQUE)
27 6 INDEX (FAST FULL SCAN) OF 'XXXX_IDX' (NON-UNIQUE) (Cost=35 Card=86 Bytes=2752)
28 5 INDEX (FAST FULL SCAN) OF 'DIR0_IDX' (NON-UNIQUE) (Cost=35 Card=1047 Bytes=33504)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXX' (Cost=4 Card=10385007 Bytes=654255441)
30 29 INDEX (RANGE SCAN) OF 'XXXXXX_IND01' (NON-UNIQUE) (Cost=3 Card=10385007)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'XXXX' (Cost=1 Card=9 Bytes=126)
32 31 INDEX (UNIQUE SCAN) OF 'XXXX_PK' (UNIQUE)
33 2 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXXXXX' (Cost=1 Card=29 Bytes=145)
34 33 INDEX (UNIQUE SCAN) OF 'XXXXXXXXX_PK' (UNIQUE)
35 1 TABLE ACCESS (BY INDEX ROWID) OF 'XXXXXXXX' (Cost=1 Card=58 Bytes=290)
36 35 INDEX (UNIQUE SCAN) OF 'XXXXXXXX' (UNIQUE)

and...

SQL@8i> select to_number( substr( v.xxxxxx, 1, instr( v.xxxxx, '|' ) - 1 ) ) xxxxx
2 from v_xxx v, tmp_xxx t
3 where v.xxx_xxx = t.xxx_xxx
4 and v.xxx_xxx = t.xxx_xxx
5 and t.sesion = 123456;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27708 Card=1 Bytes=80)
1 0 NESTED LOOPS (Cost=27708 Card=1 Bytes=80)
2 1 VIEW OF 'V_XXX' (Cost=27708 Card=5221 Bytes=355028)
3 2 NESTED LOOPS (OUTER) (Cost=27708 Card=5221 Bytes=783150)
4 3 HASH JOIN (Cost=27708 Card=5221 Bytes=767487)
5 4 INDEX (FULL SCAN) OF 'ZON_PK' (UNIQUE) (Cost=1 Card=124 Bytes=496)
6 4 NESTED LOOPS (Cost=27706 Card=5221 Bytes=746603)
7 6 NESTED LOOPS (Cost=27706 Card=5221 Bytes=730940)
8 7 NESTED LOOPS (OUTER) (Cost=27706 Card=5224 Bytes=715688)
9 8 HASH JOIN (Cost=27706 Card=5224 Bytes=705240)
10 9 NESTED LOOPS (Cost=10999 Card=42803 Bytes=4879542)
11 10 HASH JOIN (Cost=10999 Card=42803 Bytes=4793936)
12 11 INDEX (FAST FULL SCAN) OF 'SERINMU_IDX_001' (NON-UNIQUE) (Cost=34 Card=62864 Bytes=565776)
13 11 HASH JOIN (Cost=10883 Card=87779 Bytes=9041237)
14 13 HASH JOIN (Cost=669 Card=2256 Bytes=155664)
15 14 INDEX (FAST FULL SCAN) OF 'DIR0_IDX' (NON-UNIQUE) (Cost=35 Card=1047 Bytes=27222)
16 14 HASH JOIN (Cost=633 Card=27102 Bytes=1165386)
17 16 INDEX (FAST FULL SCAN) OF 'DIR0_IDX' (NON-UNIQUE) (Cost=35 Card=86 Bytes=2236)
18 16 TABLE ACCESS (FULL) OF 'XXXXXXXXX' (Cost=597 Card=268011 Bytes=4556187)
19 13 TABLE ACCESS (FULL) OF 'XXXXXXXX' (Cost=9976 Card=5016124 Bytes=170548216)
20 10 INDEX (UNIQUE SCAN) OF 'USO_PK' (UNIQUE)
21 9 TABLE ACCESS (FULL) OF 'XXXXXX' (Cost=9018 Card=10385007 Bytes=218085147)
22 8 INDEX (UNIQUE SCAN) OF 'USO_PK' (UNIQUE)
23 7 INDEX (UNIQUE SCAN) OF 'MED_PK' (UNIQUE)
24 6 INDEX (UNIQUE SCAN) OF 'CON_PK' (UNIQUE)
25 3 INDEX (UNIQUE SCAN) OF 'OLC_PK' (UNIQUE)
26 1 INDEX (RANGE SCAN) OF 'TMP_XXX_IDX' (NON-UNIQUE)

..what do you think it makes the plans change so badly ( see the FULL TABLE SCANS on some tables in the second plan ) ... sorry for not sposing the proper names of the names and indexes, I will see if I can get to ask a question to actually post them to you.

Hallo Tom,

I interested read the sub-thread
"q on an approach involving views"
you were in here 3 months ago.

totally agree with your statements
<Quote1>
never shall you use the proper
set of indexes again in your life due to this kludge -- at least when they
realize the huge load you put on the server doing this -- you'll have pipelined
views they can replace with real, honest, practical one
</Quote1>,

<Quote2>
I hate it when political decisions, affecting
technical things, prevent you from following the best practice.
</Quote2>

I am currently involved in a similar issue like
"A reader"'s one, I have to implement views on
pipelined functions due to political decisions.

However, I don't worry much about setting
"slow=true" in init.ora, because


my objection that we might setting "slow=true"
has arrived at the politicians, and they accepted
that the concept at the moment has to change
as soon as "slow=true" will come in
(pipelined functions has to be substituted by
real ones)


My question is, if the following approach to have
a "parametrized view" is correct
[
I cannot do a
"select * from table(mypck.pemp(7369))",
I can do a
"select * from emp_v where mypck.set_empno(7369)=0"
]

Setup:

create table emp as select * from scott.emp;


create or replace package mypck as

type emp_tab is table of emp%rowtype;

function set_empno(vempno in emp.empno%type) return integer;

function pemp(vempno in emp.empno%type default null) return emp_tab pipelined;

end mypck;
/




create or replace package body mypck as

empno_ emp.empno%type := null;

function set_empno(vempno in emp.empno%type) return integer is
begin

empno_ := vempno;

return 0;

end set_empno;



function pemp(vempno in emp.empno%type) return emp_tab pipelined is
r emp_tab;
dummy integer;
begin

if vempno is not null then
dummy := set_empno(vempno => vempno);
end if;

for r in (
select *
from emp
where empno = empno_
or empno_ is null
) loop

pipe row(r);

end loop;

return;
end pemp;

end mypck;
/



create or replace view emp_v(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) as
select * from table(mypck.pemp);


Now,

select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
from table(mypck.pemp(7369));

(which I am not allowed to use)

gives the same result as

select * from emp_v where mypck.set_empno(7369)=0;

(which I might use)

is this trick to shift the parameter into the where-clause
of a view correct ?

Hi Tom

I came accross a create view command with parameters which I never saw it before and could'nt find any example anywhere.

CREATE OR REPLACE VIEW my_view(users_id, sys_id, sys_name )
AS SELECT u.users_id, sys.sys_id, sys.name sys_name
FROM users u,
subprog_pro_users su,
Systems sys
WHERE u.users_id = su.users_id
AND su.subprogram_id = sys.sys_id
AND u.user_status <> 'Inactive'
AND su.project_id IN (SELECT project_id
FROM project
WHERE project IN (SELECT value
FROM Env_Var
WHERE name = 'PROJECT'))

Is this some thing new?

If I remove parameters and create view even then it works fine so what's the purpose behind this.

Thanks for your help.

Hi Tom

Since all three cloumns have same name it seems extra to write then again and again.

Thanks for clarification.

As always you are a great help to all.

Regards.

Hi Tom
What would be your answer to the following question?
I am sure d and e are wrong..

Which two statements about views are true:
a. a view can be created as read only
b.a view can be created as a join on two or more
tables
c.a view cannot have an order by clause in the select
statement
d.a view cannot be created with the group by clause
in the select
statement
e.a view must have aliases defined for the column
names in the select
statement


Thanks

Regarding your response above in this thread: <quote>They are used for complex query rewrites with materialized views and such. It is more "meta data" -- it gives the optimizer more information, allows for a broader range of query rewriting to take place. <quote>

Following is extracted from:
</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/constrai.htm#556 <code>

<quote>View Constraints:

You can create constraints on views. The only type of constraint supported on a view is a RELY constraint.

This type of constraint is useful when queries typically access views instead of base tables, and the DBA thus needs to define the data relationships between views rather than tables. View constraints are particularly useful in OLAP environments, where they may enable more sophisticated rewrites for materialized views.<quote>

Tom, could you please explain the use of creating constraints on views. The above statements from the doc are not particularly useful. I could not find any practical examples anywhere in the doc when I searched. Could you provide an example or a link to it? Thanks.

How did Oracle behave in 8i? any example to show me the use of constraints on views to illustrate how optimizer behaves with and without view constraints and how it improves query performance in 9i? I understand that it is metadata etc. Thanks much for your time.

It is beginning to make sense Tom. Thanks.
<quote>So, an MV that aggregates the base tables using a join could be used in query rewrites against the emp view that has the same aggregates.<quote>

I was wondering what difference it makes? If I have MVs created, optimizer would do the rewrite against MVs anyway. Why would I create views (with constraints defined) on base tables with the same definition/aggregates that I have already done in MV and let optimizer rewrite against the view rather than MVs? Any performance reason? because query rewrite against a view still have to go to base tables to get the data as opposed to rewriting against Mv. right? maybe I am missing a point.

I myself was wondering regarding the above question from Robert. Anyone knows the answer?

Thanks Tom. Very helpful. I have your books. In ch 6 of your 'effective oracle by design', you discuss about query rewrite of Mvs. In which chapter, do you discuss about constraints on views? I am not able to find it! I will keep looking for it. Thanks again.

Just curious to know that if I don't create constraints on views and my sql refers views what does optimizer do? would it not use views and automagically (Tom, I am stealing your terminology!) use base tables on which views were created? Do I see in the explain plan that optimizer used base tables instead of views in that case? Thanks. You are the best.

Tom,

I have a master view, where I am using another child view.

When I tried to query the view, it's not giving the result. Instead, it's getting hanged.

Child View Script
-----------------
CREATE OR REPLACE VIEW EH_AF_MKTFR_VW AS
SELECT point_of_sale, origin, destination, category,
rbd_class, farebasis_code,
booking_from_date, booking_to_date,
travel_from_date, travel_to_date,
market_fare_currency, adult_fare, child_fare,
infant_fare, rul.market_fare_id, rul.rule_id,
active_flag
FROM eh_af_market_fare mkt, eh_af_market_fare_rule rul
WHERE mkt.market_fare_id = rul.market_fare_id
AND mkt.rule_id = rul.rule_id;


Master View
-----------

CREATE OR REPLACE VIEW EH_AF_MKTCSTFARE_VW AS
SELECT orgdst_type, nvl(mkt.point_of_sale,
cst.point_of_sale) point_of_sale,
cst.point_of_sale cost_point_of_sale,
cst.origin, cst.destination,cst.journey_flag,
cst.carrier_code, cst.category, mkt.rbd_class
mrbd_class, cst.rbd_class rbd_class, cost_currency,
mkt.farebasis_code,
mkp.booking_from_date, mkp.booking_to_date,
mkp.travel_from_date, mkp.travel_to_date,
adult_cost, adult_markup_amt,

decode( adult_markup_pct, 0, 0,
round((100 - adult_markup_pct)/100,2))
adult_markup_pct,
adult_msp_amt, adult_msp_pct, child_cost,
child_markup_amt,

decode(child_markup_pct, 0, 0,
round((100 - child_markup_pct)/100, 2))
child_markup_pct,
child_msp_amt, child_msp_pct, infant_cost,
infant_markup_amt,

decode(infant_markup_pct, 0, 0,
round((100 - infant_markup_pct)/100, 2))
infant_markup_pct,

infant_msp_amt, infant_msp_pct,
adult_fare,child_fare,infant_fare,
market_fare_currency,
cst.fare_id, cst.rule_id, mkt.market_fare_id,
mkt.rule_id market_rule_id,
cst.active_flag cst_active_flag,
nvl(mkt.active_flag, cst.active_flag)
mkt_active_flag,
mkt.booking_from_date mkt_booking_from_date,
mkt.booking_to_date mkt_booking_to_date,
mkt.travel_from_date mkt_travel_from_date,
mkt.travel_to_date mkt_travel_to_date
FROM eh_af_orgdst_cost cst, eh_af_markup mkp,
eh_af_mktfr_vw mkt
WHERE cst.fare_id = mkp.fare_id
AND cst.point_of_sale = decode
(cst.point_of_sale, '***', '***', mkt.point_of_sale(+))
AND cst.origin = mkt.origin(+)
AND cst.destination = mkt.destination(+)
AND cst.category = mkt.category(+)
AND cst.active_flag = mkt.active_flag(+)
AND
( (mkp.booking_from_date BETWEEN
mkt.booking_from_date AND mkt.booking_to_date)
OR
(mkp.booking_to_date BETWEEN mkt.booking_from_date
AND mkt.booking_to_date)
OR
(mkt.booking_from_date BETWEEN
mkp.booking_from_date AND mkp.booking_to_date)
OR
(mkt.booking_to_date BETWEEN mkp.booking_from_date
AND mkp.booking_to_date)
OR
(mkt.booking_from_date IS Null)
)
AND
( (mkp.travel_from_date BETWEEN mkt.travel_from_date
AND mkt.travel_to_date)
OR
(mkp.travel_to_date BETWEEN mkt.travel_from_date
AND mkt.travel_to_date)
OR
(mkt.travel_from_date BETWEEN mkp.travel_from_date
AND mkp.travel_to_date)
OR
(mkt.travel_to_date BETWEEN mkp.travel_from_date
AND mkp.travel_to_date)
OR
(mkt.travel_from_date IS Null)
)


Pls. advice as this query is not returning any results and getting hanged.

Rgrds

Hi Tom,

You mean, the query is taking long time due to couple of reasons.

1. Using DECODE function in the WHERE clause.
2. Using alternatives with BETWEEN.


Pls. advice, how to tune the above query.

Rgrds

hi Tom,

I have the Explan Plan Statistics, Pls. advice how to avoid the 3 full table scans.

SELECT STATEMENT, GOAL = CHOOSE 789 1 196
SORT ORDER BY 789 1 196
TABLE ACCESS BY INDEX ROWID EH_AF_OWNR EH_AF_MARKUP 2 1 68
NESTED LOOPS 787 1 196
HASH JOIN OUTER 785 1 128
TABLE ACCESS FULL EH_AF_OWNR EH_AF_ORGDST_COST 34 1 50
VIEW EH_AF_OWNR EH_AF_MKTFR_VW 750 1 78
MERGE JOIN 750 1 88
SORT JOIN 343 40174 1848004
TABLE ACCESS FULL EH_AF_OWNR EH_AF_MARKET_FARE 31 40174 1848004
SORT JOIN 407 50504 2121168
TABLE ACCESS FULL EH_AF_OWNR EH_AF_MARKET_FARE_RULE 41 50504 2121168
INDEX RANGE SCAN EH_AF_OWNR EH_AF_MARKUP_UK1 1 1

Suppose a user user1 creates a view on a base table.
If user2 wants to access this view, user1 must give
specific access to user2 (select, update ....). What is
the minimum access rights that user2 must have, so that
user2 will have full access to the view even if
user1 does not grant (select, update ....) for the view

Is the view access similar to access of PL/SQL
procedure/function What are the similarities and
differences if any as far access management

"how can user2 get access to
user1.view without user1 or anyone else granting them access?"

Yes this is the question

Also

Is the access management for views, similar to access
management of PL/SQL procedure/function (athid).
What are the similarities and differences if any as far
security management goes between Views and Packages

SQL> connect test/test3
Connected.
SQL> drop view v1;
 
View dropped.
 
SQL> create view v1 as select * from t1;
 
View created.
 
SQL> connect / as sysdba
Connected.
SQL> drop user test5;
 
User dropped.
 
SQL> create user test5 identified by test5;
 
User created.
 
SQL> grant create session to test5;
 
Grant succeeded.
 
SQL> grant all on test.t1 to test5;
 
Grant succeeded.
 
SQL> select grantee, owner, table_name, privilege from dba_tab_privs where grantee = 'TEST5'
 

 
GRANTEE  OWNER     TABL PRIVILEGE
-------- --------- ---- -------------------
TEST5    TEST      T1   ALTER
TEST5    TEST      T1   DELETE
TEST5    TEST      T1   INDEX
TEST5    TEST      T1   INSERT
TEST5    TEST      T1   SELECT
TEST5    TEST      T1   UPDATE
TEST5    TEST      T1   REFERENCES
TEST5    TEST      T1   ON COMMIT REFRESH
TEST5    TEST      T1   QUERY REWRITE
TEST5    TEST      T1   DEBUG
TEST5    TEST      T1   FLASHBACK
 
11 rows selected.
 
SQL> connect test5/test5
Connected.
SQL> select * from test.v1;
select * from test.v1
                   *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
SQL> 
 

versions here?

b@ORA9IR2> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production




ops$tkyte@ORA9IR2> drop user a cascade;
 
User dropped.
 
ops$tkyte@ORA9IR2> drop user b cascade;
 
User dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a quota unlimited on users default tablespace users;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session, create table, create view to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session to b;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create table t1 ( x int );
 
Table created.
 
a@ORA9IR2> create view v1 as select * from t1;
 
View created.
 
a@ORA9IR2>
a@ORA9IR2> @connect "/ as sysdba"
a@ORA9IR2> set termout off
sys@ORA9IR2> set termout on
sys@ORA9IR2> grant all on a.v1 to b;
 
Grant succeeded.
 
sys@ORA9IR2>
sys@ORA9IR2> @connect b/b
sys@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2>
b@ORA9IR2> select * from a.v1;
 
no rows selected
 

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


I granted all on table instead of grant all view.
I thought grant all on table might work
 

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
 
SQL> drop user a cascade;
drop user a cascade
          *
ERROR at line 1:
ORA-01918: user 'A' does not exist
 
 
SQL> drop user b cascade;
drop user b cascade
          *
ERROR at line 1:
ORA-01918: user 'B' does not exist
 
 
SQL> create user a identified by a quota unlimited on users 
default tablespace users;  2  
create user a identified by a quota unlimited on users
                            *
ERROR at line 1:
ORA-01031: insufficient privileges
 
 
SQL> connect / as sysdba
Connected.
SQL> create user a identified by a quota unlimited on users 
default tablespace users;  2  
 
User created.
 
SQL> grant create session, create table, create view to a;
 
Grant succeeded.
 
SQL> create user b identified by b;
 
User created.
 
SQL> grant create session to b;
 
Grant succeeded.
 
SQL> connect a/a
Connected.
SQL> create table t1 ( x int );
 
Table created.
 
SQL> create view v1 as select * from t1;
 
View created.
 
SQL> connect "/ as sysdba"
Enter password: 
 
SQL> connect / as sysdba
Connected.
SQL> grant all on a.v1 to b;
 
Grant succeeded.
 
SQL> connect b/b
Connected.
SQL> select * from a.v1;
 
no rows selected
 
SQL> connect / as sysdba
Connected.
SQL> revoke all on a.v1 from b;
 
Revoke succeeded.
 
SQL> grant all on a.t1 to b;
 
Grant succeeded.
 
SQL> connect b/b
Connected.
SQL> select * from a.v1;
select * from a.v1
                *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 

so it is working???????

when you grant all on the view, the view is accessible.  when you revoke all on the view, it goes away.

you grant on the thing that you want to have the user use.

I see the error in your original script now, you granted on the table:

SQL> grant all on test.t1 to test5;
 
Grant succeeded.

and they queried the view.



Remember now, you asked:

"how can user2 get access to user1.view without user1 or anyone else granting them access?"

You did not grant them what I said -- and I hope you don't.  SELECT ANY TABLE is an overly powerful privilege and not the one you used

 

Hi Tom,

I understand that views is just similar to stored query. In that sense, does it means that changes in the base table will be reflected in the view?

Thank in advance.

Tom , Thanks for your great support. 
I am Sorry to ask additional question here.  
Is there any way to identify the dependency information of a Local View which refers remote table. Synonym and View for Remote Table is not listing in user_dependencies. 
Remote dependency information of Synonym is available in user_synonyms through DB_LINK column, but for Views there is no column to directly point the remote dependency. Without go through the TEXT column of user_views, is there any way to identify the remote reference of a View?
Can you please provide the asktom link if the answer is already in this site.
Sample:-
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Personal Oracle9i Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------------------
RTDB.US.ORACLE.COM

SQL> select * from global_name@mydb ;

GLOBAL_NAME
--------------------------------------------------------------------------------
MYDB.US.ORACLE.COM

SQL> select object_name, object_type from user_objects ;
OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------
MYDB.US.ORACLE.COM             DATABASE LINK

1 row selected.

SQL> create view remote_v1
  2    as
  3    select * from scott.dept@mydb ;

View created.

SQL>   select * from scott.dept@mydb ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL>   select * from remote_v1 ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL> select name,type,referenced_owner,referenced_type,referenced_link_name
  2    from user_dependencies ;

no rows selected

SQL> create synonym remote_s1 for scott.dept@mydb ;

Synonym created.

SQL> select * from remote_s1 ;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL> select name,type,referenced_owner,referenced_type,referenced_link_name
  2    from user_dependencies ;

no rows selected

SQL> select * from user_synonyms ;

SYNONYM_NAME    TABLE_OWNER     TABLE_NAME      DB_LINK
--------------- --------------- --------------- ---------------
REMOTE_S1       SCOTT           DEPT            MYDB.US.ORACLE.
                                                COM


1 row selected.

SQL> desc user_synonyms
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 SYNONYM_NAME                              NOT NULL VARCHAR2(30)
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 DB_LINK                                            VARCHAR2(128)

SQL> desc user_views ;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)

SQL> select text from user_views ;

TEXT
--------------------------------------------------
select "DEPTNO","DNAME","LOC" from scott.dept@mydb

1 row selected.

SQL>
 

ops$tkyte@ORA9IR2> create or replace view v as
  2  select * from dual@ora9ir2@loopback;
 
View created.
 
ops$tkyte@ORA9IR2> desc v
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 DUMMY                                             VARCHAR2(1)
 
ops$tkyte@ORA9IR2> select * from user_dependencies;
 
no rows selected
 
ops$tkyte@ORA9IR2> @invalid
 
no rows selected
 
ops$tkyte@ORA9IR2> drop database link ora9ir2@loopback;
 
Database link dropped.
 
ops$tkyte@ORA9IR2> @invalid
 
no rows selected
 
ops$tkyte@ORA9IR2> select * from v;
select * from v
              *
ERROR at line 1:
ORA-02019: connection description for remote database not found


It would appear they purposely made views "not dependent" on a database link, the view did not go invalid, it is not dependent on it.

I would have thought differently myself. 

Great.I was highly confused and now I am clear. Thank you very much Tom. Thanks for your quick response.

Dear Tom!

consider the example:

SQL> ed
Wrote file afiedt.buf

  1  create or replace view vw_test
  2  as  select my_pkg.get_booking(agent) agent_name,  j_ref  
3* from jh 
SQL> /

View created.

SQL> desc vw_test

 Name                    Null?    Type
 ----------------------- -------- ----------------
 AGENT_NAME                       VARCHAR2(4000)
 J_REF                   NOT NULL VARCHAR2(20)

SQL> desc my_pkg
FUNCTION GET_BOOKING RETURNS VARCHAR2(35)
Argument Name      Type                   In/Out Default?
-----------------  ----------------------- --- 
P_AGENT            VARCHAR2                IN

my Question:

Why the datatype of column agent_name is Varchar2(4000) ? Shouldn't it be Varchar2(35) (since the function is returning varchar2(35))?  What if i wanted it to be Varchar2(35) instead of Varchar2(4000)?

  1* select * From v$version
SQL> /

BANNER
-----------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> select name, value from v$parameter where name='cursor_sharing';

NAME                       VALUE
-------------------------- -----
cursor_sharing             EXACT

Thanks in Advance 

<<I understand that views is just similar to stored query. In that sense, does it means that changes in the base table will be reflected in the view? >>

If you created a view like "CREATE VIEW v AS SELECT * FROM t1" and then if you add more columns to the base table, they are not going to be reflected in your view definition if that's what you wanted to ask.

Hi Tom I have to tables,
one with data with date before x date
and the other with data after x date.

In every query I do, both tables are accesed,

I will ilke something like
SELECT iva_fecha, iva_codcli, iva_ctacorr, iva_monto,
iva_cuotas, iva_dsc, iva_dsm
FROM fon.iva_RW
where iva_fecha >=to_date('01jan2004')
UNION ALL
SELECT iva_fecha, iva_codcli, iva_ctacorr, iva_monto,
iva_cuotas, iva_dsc, iva_dsm
FROM fon.iva_RO
where iva_fecha <to_date('01jan2004')

And one table or their index don't be accessed if the date I'm quering is not in the range

select count(*)
from
iva where iva_fecha = to_date('02jan2004')


This is, in this situation I'll like instead of accessing both tables

1 SORT AGGREGATE (cr=7 r=0 w=0 time=91806 us)
2685 VIEW (cr=7 r=0 w=0 time=1301154147 us)
2685 UNION-ALL (cr=7 r=0 w=0 time=57807 us)
2685 INDEX RANGE SCAN OBJ#(28394) (cr=7 r=0 w=0 time=1301144754 us)(object id 28394)
0 FILTER (cr=0 r=0 w=0 time=5 us)
0 INDEX RANGE SCAN OBJ#(28390) (cr=0 r=0 w=0 time=0 us)(object id 28390)


I will like, only to access one, because I had said in the condition, one of the table don't have data in 01jan2004

1 SORT AGGREGATE (cr=7 r=0 w=0 time=91806 us)
2685 VIEW (cr=7 r=0 w=0 time=1301154147 us)
0 FILTER (cr=0 r=0 w=0 time=5 us)
0 INDEX RANGE SCAN OBJ#(28390) (cr=0 r=0 w=0 time=0 us)(object id 28390)

Is This possible?
Thank you Tom

Hi tom, what are you refering with partitoined views, please.

I found this
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5955697076876 <code>

and this hidden parameter

_partition_view_enabled

Or there is another way to do this, thank you Tom.

Thanks Tom, The problem is I use SE, so I can't use partitioned tables, and I would like that option.

I had found the example in partition views and tried, but it didn't work.
I created the constraint as in the example and the same condition in the view, I checked several times, and it is OK, and don't work. I checked the trace file, and the execution plan is the same in Oracle 9i.
I'll try in 10g the same example and I'll see if this is a limitation on 9i, if you could giveme an eaxmple would be really great, from dba objects.
I had been searching in your site and I can't find an example, maybe I'm using wrong keywords.

Thank Tom

Your response:
... <It would appear they purposely made views "not dependent" on a database link,
the view did not go invalid, it is not dependent on it.

I would have thought differently myself. >

Was helpful to acknowledge that the dependencies view will not supply this information and why that is so.
But the question remains: Is there a way to get dependency information in situations where a database link is used?

The performance is slow on the following view. This sql runs by any of 1500 agents any time in a day via web and it times out most of the time.

1. client and customer have 1 million rows
2. SELECT client_id, personnel_id, create_date from journal where is_phone_contact = 1 --> returns 1.6 million records
3. SELECT client_id, personnel_id, create_date from message where type = 4 --> returns 4.6 millions rows
4. Is there any way to expediate this view? Your advise will be highly appreciated. Thanks


SELECT COUNT(c.client_id)
FROM client c, customer cu
WHERE cu.customer_id = c.customer_id AND c.agent_id = :1
AND c.agent_change_date > c.first_assigned_date
AND c.client_id NOT IN (SELECT pcv.client_id
FROM personal_contact_view pcv
WHERE pcv.personnel_id = c.agent_id AND pcv.create_date > c.agent_change_date);

CREATE OR REPLACE VIEW PERSONAL_CONTACT_VIEW
(CLIENT_ID, PERSONNEL_ID, CREATE_DATE)
AS
SELECT client_id, personnel_id, create_date from journal where is_phone_contact = 1
UNION
SELECT client_id, personnel_id, create_date from message where type = 4
/

The performance is slow on the following view. This sql runs by any of 1500
agents any time in a day via web and it times out most of the time.

1. client and customer have 1 million rows
2. SELECT client_id, personnel_id, create_date from journal where
is_phone_contact = 1 --> returns 1.6 million records
3. SELECT client_id, personnel_id, create_date from message where type = 4 -->
returns 4.6 millions rows
4. Is there any way to expediate this view? Your advise will be highly
appreciated. Thanks


SELECT COUNT(c.client_id)
FROM client c, customer cu
WHERE cu.customer_id = c.customer_id AND c.agent_id = :1
AND c.agent_change_date > c.first_assigned_date
AND c.client_id NOT IN (SELECT pcv.client_id
FROM personal_contact_view pcv
WHERE pcv.personnel_id = c.agent_id AND pcv.create_date > c.agent_change_date);

CREATE OR REPLACE VIEW PERSONAL_CONTACT_VIEW
(CLIENT_ID, PERSONNEL_ID, CREATE_DATE)
AS
SELECT client_id, personnel_id, create_date from journal where is_phone_contact
= 1
UNION
SELECT client_id, personnel_id, create_date from message where type = 4
/

SELECT COUNT(c.client_id)
FROM client c, customer cu
WHERE cu.customer_id = c.customer_id AND c.agent_id = 603
AND c.agent_change_date > c.first_assigned_date
AND c.client_id NOT IN (SELECT pcv.client_id
FROM personal_contact_view pcv
WHERE pcv.personnel_id = c.agent_id AND pcv.create_date > c.agent_change_date)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.04 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.02 286.16 8927 19529 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.03 286.20 8927 19532 0 1

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

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
367 FILTER
849 NESTED LOOPS
849 TABLE ACCESS BY INDEX ROWID CLIENT
3350 INDEX RANGE SCAN CLIENT_I_INDX26 (object id 40917)
849 INDEX UNIQUE SCAN PK_CUSTOMER_ID_IDX (object id 41045)
482 VIEW
482 SORT UNIQUE
1190 UNION-ALL
461 TABLE ACCESS BY INDEX ROWID JOURNAL
10679 INDEX RANGE SCAN JOURNAL_CLIENT_ID_IDX (object id 35467)
729 TABLE ACCESS BY INDEX ROWID MESSAGE
1720 INDEX RANGE SCAN MESSAGE_VI_001 (object id 35482)

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

Tom,

Here's a quick question on views created with the FORCE option.  Is there any way to grant the correct privileges required on the view if it was created using FORCE and has compilation errors ?

SQL> create force view test_view as
  2  select * from table_not_existing
  3  /

Warning: View created with compilation errors

SQL> grant select on test_view to public
  2  /

grant select on test_view to public

ORA-04063: view "ORA817.TEST_VIEW" has errors

SQL> 

NB. Oracle v8.1.7

We do this a lot due to DB links - sometimes the tables don't exist on the remote DB at view creation time (eg. we're releasing code into each DB at a time) but they will exist at runtime.  We need to go back and run a separate "grant" script once all view/tables are created everywhere.  Much neater to GRANT the privs during the object creation.  Is there any way to do this other than having a catch-all "non-existing privs" script run separately ?

Thanks for any help! 

Tom,

Just to let you know, I figured out a way to do this by using a DUMMY view beforehand (which will always be created with no errors), and grant the privs on that.  The privs then stay when the view is re-created with errors.  A convoluted work-around but it does work.

Eg.

SQL> CREATE FORCE VIEW test_view AS
  2  SELECT * FROM DUAL;

View created

SQL> GRANT SELECT ON test_view TO PUBLIC;

Grant succeeded

SQL> CREATE OR REPLACE FORCE VIEW test_view AS
  2  SELECT * FROM table_not_existing;

Warning: View created with compilation errors

SQL> SELECT object_name, object_type, status FROM dba_objects
  2  WHERE object_name = 'TEST_VIEW';

OBJECT_NAME      OBJECT_TYPE        STATUS
---------------- ------------------ -------
TEST_VIEW        VIEW               INVALID

SQL> SELECT table_name, grantee, PRIVILEGE FROM dba_tab_privs
  2  WHERE table_name='TEST_VIEW';

TABLE_NAME          GRANTEE        PRIVILEGE
------------------- -------------- ----------------
TEST_VIEW           PUBLIC         SELECT

SQL> 


Et Voila! 

In one you say that it cannot issue the grant because it doesn't know if the grantor has permission to issue the grant. In the next statement, a grant is created validly on a view and then the view is changed to point to a different table where the grant is not permissible.

I played around with it a bit and there is indeed a record in ALL_TAB_PRIVS that shows the grant has been issued, but selecting from the view in question as the user with the invalid grant returns insufficient privileges. Since Oracle seems to check the validity of the grant each time, do you see any reason why a FORCE option for grants would be harmful?

Hallo Tom,

I always thought, that a view is nothing more
than a stored query 
(in the following sense:
if you
CREATE VIEW x as y
where y is a select-statement,
then
select * from x
is equivalent to
y
).

However, today I found out, that this is not true:
Consider

  1  CREATE OR REPLACE FORCE VIEW "RUSSELL" ("BERTRAND") AS
  2  select replace(replace(upper(dbms_lob.substr(dbms_metadata.get_ddl('VIEW', 'RUSSELL'), 1000, 61 + length(user))), chr(32)), chr(10))
  3  from dual
  4  minus
  5  select replace(replace(upper(sql_text), chr(32)), chr(10))
  6  from v$sqlarea
  7* where upper(sql_text) like '%RUSSELL%'
SQL> /

View created.

SQL> select * from russell;

BERTRAND
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECTREPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('VIEW','RUSSELL'),1000,61+LENGTH(USER))),CHR(32)),CHR(10))FROMDUALMINUSSELECTREPLACE(REPLACE(UPPER(SQL_TEXT),CHR(32)),CHR(10))FROMV$SQ
LAREAWHEREUPPER(SQL_TEXT)LIKE'%RUSSELL%'


1 row selected.

SQL> select bertrand from russell;

BERTRAND
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECTREPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('VIEW','RUSSELL'),1000,61+LENGTH(USER))),CHR(32)),CHR(10))FROMDUALMINUSSELECTREPLACE(REPLACE(UPPER(SQL_TEXT),CHR(32)),CHR(10))FROMV$SQ
LAREAWHEREUPPER(SQL_TEXT)LIKE'%RUSSELL%'


1 row selected.

SQL> /

BERTRAND
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECTREPLACE(REPLACE(UPPER(DBMS_LOB.SUBSTR(DBMS_METADATA.GET_DDL('VIEW','RUSSELL'),1000,61+LENGTH(USER))),CHR(32)),CHR(10))FROMDUALMINUSSELECTREPLACE(REPLACE(UPPER(SQL_TEXT),CHR(32)),CHR(10))FROMV$SQ
LAREAWHEREUPPER(SQL_TEXT)LIKE'%RUSSELL%'


1 row selected.

SQL> REM now we don't use the view, but the query directly - and will get another result !

SQL> select replace(replace(upper(dbms_lob.substr(dbms_metadata.get_ddl('VIEW', 'RUSSELL'), 1000, 61 + length(user))), chr(32)), chr(10))
  2  from dual
  3  minus
  4  select replace(replace(upper(sql_text), chr(32)), chr(10))
  5  from v$sqlarea
  6  where upper(sql_text) like '%RUSSELL%'
  7  /

no rows selected

SQL> /

no rows selected


So, a view seems to be a bit - just a bit -
different from a stored query 

consider

create or replace view a
as
select * from dual;

then
select * from a
gives the same resultset as (is equivalent to)
select * from dual

create or replace view a
as
select owner, object_name
from all_objects
where owner='SYS'
or object_name like '%ABC%';

then
select * from a
gives the same resultset as (is equivalent to)
select owner, object_name
from all_objects
where owner='SYS'
or object_name like '%ABC%'



......



this didn't happen in the example I gave

and not the point.

the conclusion remains different

Hi Tom,

I think I am seeing a phenomenon where the optimizer gets 'confused' and is not rewriting query even though it should. I am at a loss to figure out how to get around this. Please see below (Pardon the badly contrived test case; it's past 2030 hrs Local time and am still in the office):


create table t1 as select * from dba_tables

analyze table t1 compute statistics

create table t2 as select * from dba_users

analyze table t2 compute statistics

create materialized view mv1
enable query rewrite as
select t1.table_name, t2.username, t2.default_tablespace from t1, t2
where t1.owner = t2.username and t2.username != 'SYS'

analyze table mv1 compute statistics

<The above bits were taken from TOAD. Now to sqlplus>

u1@CJ9IDB1>alter session set query_rewrite_integrity=stale_tolerated;

Session altered.

Elapsed: 00:00:00.00
u1@CJ9IDB1>alter session set query_rewrite_enabled=force;

Session altered.

<The above two settings are just for the test case to save some time. Normally I wouldn't do that.>


Elapsed: 00:00:00.00
u1@CJ9IDB1>set autotrace traceonly


u1@CJ9IDB1>select count(*) from t1, t2, t1 dummy2
2 where t1.owner = t2.username and t2.username != 'SYS' and t2.default_tablespace = dummy2.tablespace_name
3 .
u1@CJ9IDB1>/

1 row selected.

Elapsed: 00:00:02.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=13)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=8 Card=245250 Bytes=3188250)
3 2 TABLE ACCESS (FULL) OF 'MV1' (Cost=2 Card=981 Bytes=6867)
4 2 TABLE ACCESS (FULL) OF 'T1' (Cost=5 Card=1250 Bytes=7500)

So far so good. The cbo does the rewrite correctly.

Now if I only add an alias to the first occurrence of 't1' in the query:


u1@CJ9IDB1>select count(*) from t1 dummy, t2, t1 dummy2
2 where dummy.owner = t2.username and t2.username != 'SYS' and t2.default_tablespace = dummy2.tablespace_name
3 /

1 row selected.

Elapsed: 00:00:02.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=14 Card=256518 Bytes=6156432)
3 2 TABLE ACCESS (FULL) OF 'T1' (Cost=5 Card=1250 Bytes=7500)
4 2 HASH JOIN (Cost=8 Card=1231 Bytes=22158)
5 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=18 Bytes=216)
6 4 TABLE ACCESS (FULL) OF 'T1' (Cost=5 Card=1231 Bytes=7386)

Now the optimizer is not rewriting the query, although it's effectively the same as the earlier one!! Why should that be so?

Trying to do explain_rewrite:

u1@CJ9IDB1>begin
2 dbms_mview.explain_rewrite('select count(*) from t1 dummy, t2, t1 dummy2
3 where dummy.owner = t2.username and t2.username != ''SYS''
4 and t2.default_tablespace = dummy2.tablespace_name','MV1', 'ST');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
u1@CJ9IDB1>select message from rewrite_table;

no rows selected

Elapsed: 00:00:00.00


Not sure why, but it's not returning any rows right now. Normally the message I get is 'QSM-01110: a lossy join in MV....'

Would really appreciate a workaround for this that is generic enough.

Thanks & regards,
Charu.

in 10gr1 I get:


MESSAGE
-------------------------------------------------------------------------------
QSM-01123: self-join algorithm could not resolve multiple instances
QSM-01110: a lossy join in MV, MV1, between tables, T2 and T1, not found in que
ry


but in 10gr2 I get:

ops$tkyte@ORA10GR2> select count(*) from t1 dummy, t2, t1 dummy2
  2  where dummy.owner = t2.username and t2.username != 'SYS' and t2.default_tablespace = dummy2.tablespace_name
  3  /


Execution Plan
----------------------------------------------------------
Plan hash value: 3178954463

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    12 |    21  (20)| 00:00:01 |
|   1 |  SORT AGGREGATE                |      |     1 |    12 |            |          |
|*  2 |   HASH JOIN                    |      |   239K|  2806K|    21  (20)| 00:00:01 |
|   3 |    MAT_VIEW REWRITE ACCESS FULL| MV1  |   887 |  5322 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL           | T1   |  1350 |  8100 |    14   (0)| 00:00:01 |
---------------------------------------------------------------------------------------


so, it might be a product issue in earlier releases - I'd suggest contacting support for this one. 

I now see that I have put my comment in wrong thread. Sorry about that. Should I copy it to a more suitable thread?

Thanks,
Charu

user@dev> create table t as select * from dba_objects;

Table created.

user@dev> create view v as select * from t;

View created.

user@dev> set autotrace traceonly explain

user@dev> select * from t;

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'



user@dev> select * from v;

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

What is the difference between these two things?

There must be a very simple lookup in the data dictionary to what the stored sql statement is, just as the table must be looked up.
I would just like to see your detailed wording of the difference(s).

Thanks very much

I did that but can't pick out the lookup of the view. The trace files look identical.

I'm convinced enough. Thanks!

Thanks for showing!

All this is stemming from the 30 character limit for object names. It's a longer story than I want to type...

Do you know if it is in the works to get rid of that limitation?

Thanks

When a view is defined with a column joined to a lookup table, I am seeing some strange output, is this a restriction on views.

Here Dept is joined to itself for demo purpose it actually joins to some other lookup table.

View Definition:
CREATE OR REPLACE VIEW deptview AS SELECT deptno,dname,(SELECT loc FROM dept d WHERE d.deptno=d1.deptno)loc FROM dept d1

When a select statement is executed as

SELECT * FROM deptview WHERE loc
NOT IN
(SELECT loc FROM deptview WHERE dname ='SALES')

The loc column shows up the same location for all the records.

Thanks in advance.

there is no join there that I see.  I see a scalar subquery, but not a single join anywhere?


but you would need to sort of supply more information - like versions, maybe a plan, the data, a cut and paste....


ops$tkyte@ORA10GR2> create table dept as select * from scott.dept;

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> CREATE OR REPLACE VIEW deptview AS SELECT deptno,dname,(SELECT loc FROM dept d
  2  WHERE d.deptno=d1.deptno)loc FROM dept d1
  3  /

View created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> SELECT * FROM deptview WHERE loc
  2  NOT IN
  3  (SELECT loc FROM deptview WHERE dname ='SALES')
  4  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 Accounting     NEW YORK
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON

 

Tom

This is what I see when I execute the query

SELECT * FROM deptview WHERE loc
NOT IN
(SELECT loc FROM deptview WHERE dname ='SALES')
/

1 10 ACCOUNTING NEW YORK
2 20 RESEARCH NEW YORK
3 30 SALES NEW YORK
4 40 OPERATIONS NEW YORK

The version of oracle is
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

SELECT STATEMENT, GOAL = CHOOSE
TABLE ACCESS BY INDEX ROWID SCOTT DEPT
INDEX UNIQUE SCAN SCOTT PK_DEPT
FILTER
TABLE ACCESS FULL SCOTT DEPT
FILTER
TABLE ACCESS FULL SCOTT DEPT
TABLE ACCESS BY INDEX ROWID SCOTT DEPT
INDEX UNIQUE SCAN SCOTT PK_DEPT
TABLE ACCESS BY INDEX ROWID SCOTT DEPT
INDEX UNIQUE SCAN SCOTT PK_DEPT

Thanks

you have a different schema than I do (pk_dept - for example)

so, going back to square one, full test case.

starting with create table
insert into
run query (with autotrace on)

cut and pasted

from sqlplus

just like I do ;)


need to reproduce....

ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> SELECT * FROM deptview WHERE loc NOT IN
  2    (SELECT loc FROM deptview WHERE dname ='SALES')
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        40 OPERATIONS     BOSTON


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   2    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
   3    0   FILTER
   4    3     TABLE ACCESS (FULL) OF 'DEPT'
   5    3     FILTER
   6    5       TABLE ACCESS (FULL) OF 'DEPT'
   7    5       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   8    7         INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
   9    5       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
  10    9         INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)


 

Tom,

Thanks for your replies.

Well this is happening after the upgrade to 9.2.0.7 from 9.2.0.5 with the patch p4163445_92070_WINNT.zip. It works correctly on another server running 9.2.0.5. Does this suffice or do you still insist a trace?

Thanks

Great explanation about what is exactly a view. get rid of all myth on views. It is simply a stored query.

Thanks for sharing your knowledge Tom. It is an inestimable value...

This is a bit off topic. Sorry, I was not sure where to put it. Been meaning to ask this for a while.

The documentation uses v$ view and v$ 'fixed table' interchangeably. I have actually been corrected by people when I say v$ view. However, when I look in the catalog script I see 'create or replace view'

How do the data structures that the v$ view read really work? Why do they cause latching issues if run too much? I think its because they basically contain metrics data and in order to get a consistent metric you need to hold what is storing the metric constant? So in this case a read is blocking a write in memory?

Hi Tom,
I am not a database expert.
as a part of an uptake project I have created a script which drops the database views and created the synonyms.
script drops the view one by one i.e. a PL/SQL API call is made , with the name of the view as a parameter and this PL/SQL API will drop the view. there are 30-40 such calls in the .sql script.
please Note that script is generated by the tool.

Now, as there are many calls, there is a contention! and
in the v$session view, when I query for for my machine
in the event column I see "latch: cache buffers chains".
can you please explain/more clear! would you please advice that how do I avoid the contention. is there any way to optimise or bulk drop!
thanks

Hi Tom,
thanks for your prompt reply.
but does it mean that I don't have any solution for this problem. can't we improve the performance further...
Thanks

Hi Tom,
I agree on what you say, but the problem is, if 10 teams extract such the SQL scripts generated by the tool and each script has 30-40 PL/SQL API calls( and this PL/SQL API drops the view passed as parameter to it).
Now if 1 script takes 20 minutes to complete, imagine the time for upgrade of all the application.
Do you think Oracle can improve on this side?
Thanks

sorry!!
the problem was with the schema object having VPD policy attached.
the issue is resolved now.
thanks

We have a view that a process applies a predicate and writes out a csv. Later the CSV is found to have missing
records. The following 2 queries encapsulate the issue.


QUERY 1:

SELECT * FROM
( SELECT * FROM
myView
WHERE Date = '11-DEC-2006' AND Rownum > 0)
WHERE
ETI = 'XXYYZZ'



QUERY 2:

SELECT * FROM
myView
WHERE Date = '11-DEC-2006' AND ETI = 'XXYYZZ'


The first query drops a row, the second one has all the required records. Obviously, the Rownum > 0 is meant to replicate the values being materialized.

The view joins two tables which are range partitioned on valuedate. when Identical but nonpartitioned tables are created from the 11Dec partition - both queries yield the same results.

1) Why would the order the predicates are applied to the view make a difference to the data returned, (2 equivalences with an AND between them WHERE A = 1 and B =1 should be the same as WHERE B = 1 and A = 1).

2) Why would partitioning have an effect?
Why would partitioning change what data I get back.

Yes, I know that there should be TO_DATEs included. They were ommitted for brevity. (Is there a convention that we can use to indicate that my 11-DEC-2006 isn't a string without TO_DATE('11-DEC-2006', 'DD-MON-YYYY') like [11-DEC-2006] or {11-DEC-2006})

I'm asking a more generic question. Is there any possible data or view definition that should cause the two queries (above) to yield different results.

If I ask, 'are these two queries (below) the same, you don't have to run them to know that they are.


SELECT * FROM table_a WHERE col1 = 'A' and col2 = 'B'

and

SELECT * FROM table_a WHERE col2 = 'B' and col1 = 'A'

Can I say the same thing about these two queries:



QUERY 1:

SELECT * FROM
( SELECT * FROM
myView
WHERE Col1 = 'A' AND Rownum > 0)
WHERE
Col2= 'Z'



QUERY 2:

SELECT * FROM
myView
WHERE Col1 = 'A' and Col2= 'Z'


I can give you cases where they are the same, use any table you want.

SELECT * FROM all_objects
WHERE owner = 'SYS' AND object_name = 'DUAL'


SELECT * FROM (
SELECT * FROM all_objects
WHERE owner = 'SYS' AND ROWNUM > 0 )
WHERE object_name = 'DUAL'

On my system with my view and my data and my queries in the same form as above yield different results. I *assert* that there is no view definition or data which could legitamately produce that difference and therefore there is a bug in Oracle. I'm open to entertain the fact that there is something about using a view or particular sets of data that would invalidate my assertion but I believe that the answer to whether query 1 and 2 are functionally equivalent can be known a priori.

In reality what is going on is more like this:

CREATE TABLE newTable
as
SELECT * FROM
myView
WHERE Col1 = 'A'

SELECT * FROM newtable WHERE col2 = 'Z'

and that yields a different result than


SELECT * FROM
myView
WHERE Col1 = 'A' and Col2= 'Z'

I use the rownum > 0 to simulate the same thing in a single query. It forces Oracle to apply the col2 = 'Z' predicate until after the col1 = 'A' predicate just like when I do the CTAS.

BTW, No the data is not changing. The data is the same before and after the CTAS and I can run it over and over and get the same wrong results.

I'm not sure how you can claim that a simple case can always be provided. I've built seemingly similar tables filled with seemingly similar data with a seemingly similar view and received the correct results 100%.

Ah, we just found the bug. Bug 4604970.

It would have been very difficult to build that case for you unless I already knew that it was THIS bug that caused the wrong answer. I could have built tons of test cases and unless and until the optimizer chose a HASH GROUP BY you wouldn't have seen the error. And you wouldn't have seen the error if you had enough memory for the operation because one of the fixes is "Increase the memory used by hash group by" and you'd have to be exactly at 10.2.0.2. And even then you're not guarenteed to see it the bug because the bug text says,
"Wrong results *possible* from the result of a non-distinct aggregation with a group by (such as sum(col)) when HASH GROUP BY is used."
-- emphasis mine.


To me this is the worst kind of bug. It totally eliminates any confidence in Oracle. If the optimizer chose the wrong plan, fine... we can find the slow query and fix it. If the query caused an exception to be raised, again fine. We'd just figure out a different way to get the right results. If the query brought down production, not so fine but still not as bad. At least you'd know that something was wrong. But you could go on for a long time reporting the wrong values, billing the wrong amounts, concluding the wrong conclusions when there are bugs like this.

Does Oracle announce these bugs differently than less critical bugs? With the stream of announcements that come out of large vendors like Oracle I can see how DBA's could miss such announcements -if- they look like every other one.

Anyway. I know you're like a doctor doing triage. You hear hoofbeats and think horses not zebras. This is certainly a zebra.

Hi Tom,

Not a problem or a query but an observation.

V$SESSION is a public synonyms for the view SYS.V$_SESSION. But the definition of V$_SESSION points back to V$SESSION as in the select below. I know dynamic performance view are different from our standard views and are constantly updated when the oracle instance is up and running.
But how does this work out?

/NCLDBA/NV02 > select object_name,object_type,owner
2 from dba_objects
3 where object_name = 'V$SESSION';

OBJECT_NAME OBJECT_TYPE OWNER
------------------------------ ------------------ ---------------
V$SESSION SYNONYM PUBLIC

1 row selected.

/NCLDBA/NV02 > select dbms_metadata.get_ddl('VIEW','V_$SESSION','SYS')
2 from dual;

DBMS_METADATA.GET_DDL('VIEW','
--------------------------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW "SYS"."V_$SESSION" ("SADDR", "SID", "SERIAL#", "A
UDSID", "PADDR", "USER#", "USERNAME", "COMMAND", "OWNERID", "TADDR", "LOCKWAIT",
"STATUS", "SERVER", "SCHEMA#", "SCHEMANAME", "OSUSER", "PROCESS", "MACHINE", "T
ERMINAL", "PROGRAM", "TYPE", "SQL_ADDRESS", "SQL_HASH_VALUE", "PREV_SQL_ADDR", "
PREV_HASH_VALUE", "MODULE", "MODULE_HASH", "ACTION", "ACTION_HASH", "CLIENT_INFO
", "FIXED_TABLE_SEQUENCE", "ROW_WAIT_OBJ#", "ROW_WAIT_FILE#", "ROW_WAIT_BLOCK#",
"ROW_WAIT_ROW#", "LOGON_TIME", "LAST_CALL_ET", "PDML_ENABLED", "FAILOVER_TYPE",
"FAILOVER_METHOD", "FAILED_OVER", "RESOURCE_CONSUMER_GROUP", "PDML_STATUS", "PD
DL_STATUS", "PQ_STATUS", "CURRENT_QUEUE_DURATION", "CLIENT_IDENTIFIER") AS
select "SADDR","SID","SERIAL#","AUDSID","PADDR","USER#","USERNAME","COMMAND","
OWNERID","TADDR","LOCKWAIT","STATUS","SERVER","SCHEMA#","SCHEMANAME","OSUSER","P
ROCESS","MACHINE","TERMINAL","PROGRAM","TYPE","SQL_ADDRESS","SQL_HASH_VALUE","PR
EV_SQL_ADDR","PREV_HASH_VALUE","MODULE","MODULE_HASH","ACTION","ACTION_HASH","CL
IENT_INFO","FIXED_TABLE_SEQUENCE","ROW_WAIT_OBJ#","ROW_WAIT_FILE#","ROW_WAIT_BLO
CK#","ROW_WAIT_ROW#","LOGON_TIME","LAST_CALL_ET","PDML_ENABLED","FAILOVER_TYPE",
"FAILOVER_METHOD","FAILED_OVER","RESOURCE_CONSUMER_GROUP","PDML_STATUS","PDDL_ST
ATUS","PQ_STATUS","CURRENT_QUEUE_DURATION","CLIENT_IDENTIFIER" from v$session



1 row selected.

/NCLDBA/NV02 >

However I do an autotrace for a query for V$SESSION, it shows it is referring X$KSUE

NV02 > connect / as sysdba
NV02 > set autotrace traceonly explain
NV02 > select * from v$session where rownum < 2;

Execution Plan
----------------------------------------------------------
0 NULL SELECT STATEMENT Optimizer=CHOOSE NULL
1 0 COUNT (STOPKEY) NULL
2 1 FIXED TABLE (FULL) OF 'X$KSUSE' NULL



NV02 >


Obviously this circular view definition won't work for me

/NCLDBA/NV02 > create synonym zxc for vzxc;

Synonym created.

/NCLDBA/NV02 > create view vzxc as select * from zxc;
create view vzxc as select * from zxc
*
ERROR at line 1:
ORA-01731: circular view definition encountered

/NCLDBA/NV02 > create force view vzxc as select * from zxc;

Warning: View created with compilation errors.

/NCLDBA/NV02 >

Regards,
Shivdeep Modi

Hi Tom,

I had a feeling that this would be the case, it is just that output of the sql which made me curious.
After all what you see may not be true all the times :)
Thanks anyway for your clarification.
By the way the spell checker on the right hand side of posting window does not catch the junk words after a full stop.

Regards,
Shivdeep Modi

Dear Tom,

I have small question on views.i am tuning one sql query and here is the query.

SELECT /*+ choose */ distinct a.document_date,a.document_number,a.UI_UPDATED_ON,a.grr_status,
a.grr_type,a.grr_line_status,
a.ship_to_organization_code, a.vendor_code,
processing_status_code
FROM interface.cil_grr_status_reports_v a
,apps.rcv_headers_interface b
WHERE a.document_number = b.receipt_num(+)
and a.grr_status <> 'CREATED'
and grr_type not in ('06','16','18','19')
and update_flag <> 'C'
AND NOT EXISTS (
SELECT 1
FROM po.rcv_shipment_headers x
WHERE a.ship_to_org_id = x.ship_to_org_id
AND TO_CHAR (a.document_number) = x.receipt_num)
ORDER BY a.ship_to_organization_code,a.grr_type,
a.document_number

This will internally access one views i.e interface.cil_grr_status_reports_v .

My question is
1. When ever we execute the above sql query, this will call view first it will extract all the rows i.e it will execute view then it will start meging based on the predicates is that right.

2. The above query will take minimum 9 minutes to retrive 70rows, this is very time consuming so how do i tune this stmt.

Here is sql_text for view

SELECT /*+ index(d CIL_RECEIPT_TAXES_DETAIL_U1) */
h.ship_to_org_id, h.grr_source, h.grr_type, h.grr_type_status, h.document_number, h.created_on,
h.grr_status, h.vendor_code,h.vendor_name, d.po_number, d.po_line_num,
d.po_release_number, h.challan_number, h.invoice_number, h.invoice_date,
d.grr_line_status, d.document_serial_number, d.item, d.inventory_item_id,
d.item_description,
h.document_date, h.assess_value_name, h.ship_to_organization_code,
h.dn_no,
h.challan_amount, h.misc_rcpt_tag,
d.inspection_required_flag, d.modvat_required_flag,
d.receipt_quantity, d.rate, d.assess_value,
d.subinventory,
d.po_shipment_number,
d.ui_location,
d.grr_cardtype, d.ui_subinventory, d.ui_locator_id,
d.received_quantity, d.short_excess_subinv,
d.insp_required_reason,
d.inspector, d.accepted_quantity,
d.rejected_quantity_total, d.used_by_ppi_quantity, d.acc_subinventory,
d.grr_item_status, d.ui_updated_by,
d.ui_updated_on,
d.insp_done_on,
d.rcv_trans_on,
d.mtl_trans_on,
d.modvat_flag, d.cenvat_flag, d.tariff,
s.ui_sub, s.acc_sub, nvl(update_flag,'N')
FROM interface.cil_receipt_setup s,interface.cil_receipt_taxes_header h, interface.cil_receipt_taxes_detail d
where (h.ship_to_org_id in (25,28,183,204) OR h.ship_to_organization_code = 'IMO')
and h.created_on > '01-Mar-2005'
and h.document_number = d.document_number
and h.ship_to_org_id = s.org_id
and h.grr_type = s.grr_type
and h.grr_type_status = s.grr_type_status

2.

I'm visiting this site after a long time. I like the new look of this.

LEt me come to the question.
Can I create views on a pipelined funciton like this?

create or replace view my_view_name as select * from TABLE(my_pipe_function());
Or what are the advantages of using meterialized views instead of normal view in this scnario?


Thanks

Dear Tom,

Expecting your reply positively.

I have small question on views.i am tuning one sql query and here is the query.

SELECT /*+ choose */ distinct a.document_date,a.document_number,a.UI_UPDATED_ON,a.grr_status,
a.grr_type,a.grr_line_status,
a.ship_to_organization_code, a.vendor_code,
processing_status_code
FROM interface.cil_grr_status_reports_v a
,apps.rcv_headers_interface b
WHERE a.document_number = b.receipt_num(+)
and a.grr_status <> 'CREATED'
and grr_type not in ('06','16','18','19')
and update_flag <> 'C'
AND NOT EXISTS (
SELECT 1
FROM po.rcv_shipment_headers x
WHERE a.ship_to_org_id = x.ship_to_org_id
AND TO_CHAR (a.document_number) = x.receipt_num)
ORDER BY a.ship_to_organization_code,a.grr_type,
a.document_number

This will internally access one views i.e interface.cil_grr_status_reports_v .

My question is
1. When ever we execute the above sql query, this will call view first it will extract all the rows i.e it will execute view then it will start meging based on the predicates is that right.

2. The above query will take minimum 9 minutes to retrive 70rows, this is very time consuming so how do i tune this stmt.

Here is sql_text for view

SELECT /*+ index(d CIL_RECEIPT_TAXES_DETAIL_U1) */
h.ship_to_org_id, h.grr_source, h.grr_type, h.grr_type_status, h.document_number, h.created_on,
h.grr_status, h.vendor_code,h.vendor_name, d.po_number, d.po_line_num,
d.po_release_number, h.challan_number, h.invoice_number, h.invoice_date,
d.grr_line_status, d.document_serial_number, d.item, d.inventory_item_id,
d.item_description,
h.document_date, h.assess_value_name, h.ship_to_organization_code,
h.dn_no,
h.challan_amount, h.misc_rcpt_tag,
d.inspection_required_flag, d.modvat_required_flag,
d.receipt_quantity, d.rate, d.assess_value,
d.subinventory,
d.po_shipment_number,
d.ui_location,
d.grr_cardtype, d.ui_subinventory, d.ui_locator_id,
d.received_quantity, d.short_excess_subinv,
d.insp_required_reason,
d.inspector, d.accepted_quantity,
d.rejected_quantity_total, d.used_by_ppi_quantity, d.acc_subinventory,
d.grr_item_status, d.ui_updated_by,
d.ui_updated_on,
d.insp_done_on,
d.rcv_trans_on,
d.mtl_trans_on,
d.modvat_flag, d.cenvat_flag, d.tariff,
s.ui_sub, s.acc_sub, nvl(update_flag,'N')
FROM interface.cil_receipt_setup s,interface.cil_receipt_taxes_header h, interface.cil_receipt_taxes_detail d
where (h.ship_to_org_id in (25,28,183,204) OR h.ship_to_organization_code = 'IMO')
and h.created_on > '01-Mar-2005'
and h.document_number = d.document_number
and h.ship_to_org_id = s.org_id
and h.grr_type = s.grr_type
and h.grr_type_status = s.grr_type_status

Tom,

If I could ask your opinion on something ??

We were looking into constraints on views a bit more lately, and according to the documentation, we can create a primary key constraint on a view (NOT materialized view .. just a plain, vanilla view).

However, it appears that one must create the view "DISABLE NOVALIDATE" in order to create the constraint.

gregs-DEV10 > ALTER VIEW v_junk ADD CONSTRAINT v_junk_pk PRIMARY KEY ( j1_id, j2_id );
ALTER VIEW v_junk ADD CONSTRAINT v_junk_pk PRIMARY KEY ( j1_id, j2_id )
*
ERROR at line 1:
ORA-00922: missing or invalid option


gregs-DEV10 > ALTER VIEW v_junk ADD CONSTRAINT v_junk_pk PRIMARY KEY ( j1_id, j2_id ) DISABLE;

View altered.

gregs-DEV10 >

My question is: What's the point of creating this constraint, if I cannot enable it?

We wanted to create a PK on the view, so we can could create a foreign key from another table to the view ..

ALTER TABLE junk ADD CONSTRAINT junk_fk FOREIGN KEY ( j1_id, j2_id ) REFERENCES v_junk ( j1_id, j2_id );

but of course, it won't allow us to do that.

Why does Oracle allow us to create the constraint, if we can't use it?

Isn't that kind of like saying: "Here's a computer with no ON switch, Enjoy" ... What good is it if I can't turn it on? (other than a glorified paper-weight ... )
:)

What are we missing?

Hello Tom,

Greetings, Need your help in solving my issue explained below.

I am having a problem in committing transactions at a time on both databases (remote and local).

A procedure does insert into views (These views are created from a remote database using db links)
After successful execution of this procedure, another procedure executes to update some table in local database.
When the local database transaction is successful the both transactions in remote and local should commit.

When doing this getting error ORA-02047: cannot join the distributed transaction in progress.

If I do a commit after successful transaction in remote database and then after local it is working fine.
It is not allowing a single commit for two transactions.

The process is running in local database.

I am not sure, can we use autonomous transaction feature here if posible help me explain how it can be else please suggest a solution to resolve the issue.

Hope I explained well.

Thanks
- ARC

Tom,
Thanks for your reply.
1. The views are created from remote database called postgre using hetrogenious conection.
2. These are 2 praviate procedures in a package.
3. procedure P1 inserts data into views which are created from postgre.
4. procedure p2 updates data in Oracle DB.

when I execute this package its failing while execution of these procedures.

Thanks
- ARC

Tom!
I am running the query like this (on 9.2.0.6):

select * from 
 TABLE(pipe_couple_rows(...))  pipe_output,
 VIEW1  vv
where vv.id = pipe_output.id and vv.id is not null  

Pipelined function pipe_couple_rows() usually returns 0..10 rows, and view vv yields about 100 000 rows when unrestricted.
Because we have no statistics for table functions output, ORACLE decides to materialize view vv (100K rows) and uses HASH JOIN to join pipe_output and vv - it is long and it is about 60000 consistent gets.

I would prefer that ORACLE chose the pseudocode like this:

For x in (select * from pipe_output)
Loop
   Index lookup for single row from vv;
   Output joined record;
End loop

I did achieved my goal with hint /*+ first_rows*/ - amount of consistent gets fell to 60 (0.1% from previous attempt) and I get the fruits in no time.

Still I consider this way as non-secure. I think that the plan could suddenly change in the future. For example, if we replace first_rows with first_rows(10), we'll receive 1000000 consistent gets (because vv is still being materialized, but NESTED LOOPS are in use instead of HASH JOINs).

Can you give robust method to compose similar queries?

TIA, Max

ops$tkyte%ORA10GR2> with T
  2  as
  3  ( select *
  4      from TABLE(cast( str2tbl( :in_list ) as str2tblType) ) t where rownum > 0
  5  )
  6  select *
  7    from emp
  8   where ename in ( select /*+ cardinality(10) */ * from t )
  9  /

Thank you very much, Tom!
Cardinality hint works! Still I am suspecting that ORACLE is too distrustful of index access, because I had to change /*+ cardinality(t 10)*/ with /*+cardinality(t 1)*/ to get optimal plan. In fact, threshold value turned out to be "4" (with larger numbers plan started deteriorating), while it should be up to 100..500.

Nevertheless, now I am more sure (in comparision with just FIRST_ROWS) that the plan with cardinality(1) will stay stable, so the goal has been achieved.

Tom,
We need to insert / update rows in a fact table and we do it as a Change Data Capture everyday morning. We build a stage table and we do partition exchange to build our actual prodution table.

I am in a dilemma as to which is better. Building an oracle view which uses a minus query to compare data across stage and production or running the SQL (Minus Query) everyday as an ETL for the warehouse. Performance wise which one you think is better.

Let me know...

True, but since Oracle already owns the query dont you think (just like bind variables) its already parsed and can retreive data back quicker?

Tom - What do you think is the best way to compare 2 tables of same structure (columns, datatypes..etc). Right now I am using a Minus query to retrieve my change rows.

But we are doing something similar for tables which has around 40 million rows in them and it takes close to an hour to come back with the result set.

Do you have any other suggestions?

Thanks

Looking at Greg's comments (above) about "view constraints":

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:636499119911#139376700346553151

and your followup, I'm inferring that this is very similar to the query/rewrite-enabled constraints we can declare for data warehousing when integrity,etc is ETL-enforced.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806498660292#514445900346820998

For example, with views, we can declare primary keys, foreign keys, etc:

10gR2> create table t ( t number primary key );

Table created.

10gR2> create table u ( u number primary key references t);

Table created.

10gR2> create view t_vw as select * from t;

View created.

10gR2> create view u_vw as select * from u;

View created.

10gR2> alter view t_vw add constraint t_vw_pk primary key (t) rely disable novalidate;

View altered.

10gR2> alter view u_vw add constraint u_vw_pk primary key (u) rely disable novalidate;

View altered.

10gR2> ed
Wrote file afiedt.buf

1 alter view u_vw
2 add constraint u_to_t_fk
3 foreign key (u) references t
4* rely disable novalidate
10gR2> /
add constraint u_to_t_fk
*
ERROR at line 2:
ORA-25158: Cannot specify RELY for foreign key if the associated primary key is
NORELY


10gR2> alter table t modify primary key RELY;

Table altered.

10gR2> alter view u_vw
2 add constraint u_to_t_fk
3 foreign key (u) references t
4 rely disable novalidate
5 /

View altered.

This is potentially useful for Oracle's optimizer, and for reporting/development tools if we're only allowing the privileges on "simplifying views".

Reading up further and further, "what you said" here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:636499119911#tom2695797455172
and here
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:636499119911#tom13041565979243
etc.

Tom:

Can't I create a view from one DB to another using a link if the table has a CLOB object? is there a way around this.


SQL> CREATE or REPLACE VIEW M_LOG as SELECT * from m_log@abc_link
  
ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables

ops$tkyte%ORA10GR2> select * from t@ora10gr2@loopback;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables



no rows selected

ops$tkyte%ORA10GR2> insert into t select * from t@ora10gr2@loopback;

0 rows created.

Hi Tom,

Apologies if my question is not related.
I was wondering if you can suggest whether it is possible to find out if CBO has rewritten certain query ? If yes, where will I be able to find out the rewritten query ?
For e.g. (Most probably) CBO will rewrite the following query

SELECT fname, lname, salary
FROM ( SELECT fname, lname, (sal * 1.01) salary FROM EMP)

to

SELECT fname, lname, (sal * 1.01) salary
FROM EMP

How can I determine that oracle has actually rewritten my query ?

<<LOB functionality over a database link is limited, in general, you'd copy over to a global temporary table what you wanted, work on it locally and then push it back - tyically using a stored procedure to do the copy/push back. >>

can you explain more on how you do that.

We have a PB application that mainly runs on instance/database A. Now it needs to send an email and attachment which is soted in BLOB_CONTENT in DOCUMENT_TABLE in instance B.

I tried creating a view/synonym in in in stance A for it to access and it wont work.

What do you suggest to do here. I assume the client code can just do a direct connection to instance B if we grant it access. what options would you consider.

Tom:

if you have any client like sql*plus, VB, PB, oracle forms etc.

can't you have a select from table statement that logs in to another database

for example, if i logged in to sqlplus instance A

A> cant i just create a select statement that logs into instance B and query table here using the userid/password@tns_name for instance B
without using any Database links.

that is how client code initally connects to any database, unless they establish a connection and then select from table and then have to close it and open another connection.

Tom:

The PB client does not need to store anything. An oracle web page save the BLOB into DOCUMENTS_TABLE in database "BBB".

All PB needs is to access that BLOB in that table from database "AAA".

I cant create a view in AAA for that table nor a select over a db link would work for the BLOB.

I was thinking of having PB do two connections: one to "AAA" and one to "BBB".

Is there a way in oracle to give access to that table for that client if it is only connected to AAA.

Tom:

The problem is that you cant bring the file locally because of the BLOB column type.

AAA> create table test as select * from documents_table@bbb;

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Hi Tom,

Where does oracle stores the infomation related to a columns orginal name ie name without alias in a view. If i query all_tab_columns i get the column name either of same as column or if alias is give then alias name. 

I want to know what is the orginal column name not alias. for eg.


SQL> set linesize 80
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 EMPNO                                         NUMBER
 ENAME                                         VARCHAR2(15)
 JOB                                           VARCHAR2(9)

SQL> create or replace view vmyemp as select empno,ename employee_name,job job_description from emp;

View created.

SQL> desc vmyemp
 Name                               Null?    Type
 ------------------------------ -------- -------------------
 EMPNO                                        NUMBER
 EMPLOYEE_NAME                                VARCHAR2(15)
 JOB_DESCRIPTION                              VARCHAR2(9)

SQL> select column_name from all_tab_columns where table_name = 'VMYEMP';

COLUMN_NAME
------------------------------
EMPNO
EMPLOYEE_NAME
JOB_DESCRIPTION 

Here i want to know what is the actual column name of the EMPLOYEE_NAME and  JOB_DESCRIPTION by a query and query should return ename and job.

Thanks

Vikas

Thanks for this.

Actually i needed it for one of my requirement and thought of using any oracle view if it exists.

Yes also feel why would oracle need it to store.

Thanks

Vikas sharma

Hi Tom,
Version 10gR2.

I have FEW views that have approximately the following structure :

CREATE OR REPLACE FORCE VIEW v_call (call_month,
area,
phone,
call_date)
AS
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0107
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0207
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0307
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0407
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0507
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0607
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0707
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0807
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0907
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1007
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1107
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1207
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0108
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0208
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0308
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0408
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0508
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0608
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0708
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0808
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0908
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1008
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1108
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1208
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0109
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0209
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0309
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0409;


The views are build on monthly tables and should contain only 18 mounth (6 quarters) of history details.
Once per quarter i need to remove the first quarter from the view and recreate it again.

For example In Apt 1st 2009 , the view should contain only the last 6 quarters(18 month) :

CREATE OR REPLACE FORCE VIEW v_call (call_month,
area,
phone,
call_date)
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1007
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1107
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1207
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0108
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0208
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0308
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0408
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0508
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0608
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0708
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0808
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0908
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1008
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1108
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call1208
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0109
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0209
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0309
UNION ALL
SELECT TO_CHAR (call_date, 'YYMM') call_month, area, phone, call_date
FROM tc_call0409;

Of course that using partition table insted those monthly tables , is the proper solution but currently
it not available.

Could you please suggest how to do it dynamically in PL/SQL ?

Thanks