In depth parse of queries in view and a table
Geetha Nadiger, February 01, 2002 - 5:40 am UTC
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 ?
February 01, 2002 - 9:23 am UTC
you don't HAVE TO do anything.
Views are a development tool, a thing of convienence. They provide a layer of abstraction.
I cannot tell you how many times people ask "how can I rename a column", "how can I change the order of the columns", "how can I add a new column in the 'middle' of a table".
If you EVER think you'll be asking those questions yourself someday, use views. Views let you do all of the above trivially.
If you want to put the complex SQL into views and have simple SQL in your apps -- use views (i use them ALOT in my apps).
A view is nothing more then a stored query. It will run no slower nor faster then a query directly against the base tables.
Caveat - understand merging of where clauses...
A reader, February 01, 2002 - 2:49 pm UTC
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??
February 01, 2002 - 3:42 pm UTC
search for
views of views
on this site to read about that. A look at a view like a function or procedure. It to me is a standalone thing. You must take care when querying from multiple views or joining views willy - nilly to other objects. In the end, there is NO difference between:
select * from v1, v2 where ....;
and
select *
from ( select ..... /* the TEXT of v1 */ ) v1,
( select ..... /* the TEXT of v2 */ ) v2
where ...
they will behave the same. Issues can arise when doing either of those queries is "non-performant" -- that is simply because the views were not designed to be used that way.
Here, in this question, with the simple views -- there aren't any concerns like that.
view performance
Peggy Morgan, February 01, 2002 - 6:46 pm UTC
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?
February 01, 2002 - 7:50 pm UTC
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)
Using Built in Functions in Views
Lakshmi Narasimhan R, February 02, 2002 - 5:21 am UTC
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
February 02, 2002 - 5:44 pm UTC
Well, it won't slow down the actual "fetching" too much HOWEVER, unless you use function based indexes:
</code>
http://asktom.oracle.com/~tkyte/article1/index.html <code>
searching on these columns could be very slow (full table scans). If you have a view like:
create view v as select substr( column1, 1, 10 ) c1 from t;
And you query:
select substr( column1, 1, 10 ) c1 from t where column1 = 'foo';
that can use an index on column1, whereas:
select c1 from v where c1 = 'foo';
will not unless the index was created on t(substr(column1,1,10))
If you never search on the columns, this is not a concern. If you do, learn about function based indexes.
view performance (part b)
Peggy Morgan, February 04, 2002 - 6:20 pm UTC
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>
February 05, 2002 - 8:05 am UTC
This is classic -- you are comparing APPLES to toaster ovens (and there is simply no way to compare them)
Look at your view text:
... 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)
Your two queries return DIFFERENT (totally different) answers. It is not that the view is SLOW, it is that the question the view answers is totally different from the query against the base tables.
These counts are different.
Look at the plan -- the view must access the table in order to evaluate the predicate "where nvl(......". The simple select count(*) against the base table does not need to do that. It is the cost of doing the table access by index rowid that makes the view run slower (but remember -- they are answering 100% different questions)
This is similar to comparing the performance of:
select count(*) from emp;
to
select count(*) from dept;
you cannot -- the view is not "evil" here -- you simply are comparing two things that are not comparable.
Compare the performance of:
select count(*)
from pa_cost_distribution_lines
where pa_date between to_date('01-dec-2001', 'dd-mon-yyyy')
and to_date('01-jan-2002')
to
select count(*)
FROM PA_COST_DISTRIBUTION_LINES_ALL
where pa_date between to_date('01-dec-2001', 'dd-mon-yyyy')
and to_date('01-jan-2002')
AND
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)
and then talk to us -- you'll find they perform the same.
Question on View Constraints
Ravi, February 05, 2002 - 11:37 am UTC
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.
February 05, 2002 - 11:55 am UTC
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.
Want solution
A reader, February 05, 2002 - 5:50 pm UTC
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 join two different schemas to create a view
Alex Norman, February 05, 2002 - 9:42 pm UTC
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.
February 06, 2002 - 8:02 am UTC
I would have tried as hard as possible to avoid the use of multiple tables in different schemas if I had a requirement to present the information as a single instance.
I would have most likely looked at using VPD (see
</code>
http://asktom.oracle.com/~tkyte/article2/index.html <code>
) and table partitioning. I would have added a column (say "schema_name") and the VPD would normally return the predicate "where schema_name = sys_context( 'my_ctx', 'schema_name' )". For the read only user it would return null (no predicate -- sees all data).
Physical partioning on the schema name would be very efficient (partition elimination would kick in based on the predicate) and each app would think they had their own schema and the read only users would have no issues with UNIONS getting in the way.
You may run into performance issues with UNION ALLS in the views and performing complex operations on this (not because they are VIEWS but because there is a UNION ALL that takes place before the aggregation can)
View Performance
Jyotiprakash Nandi, February 09, 2002 - 3:55 am UTC
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 its 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
February 09, 2002 - 11:54 am UTC
1) that does not make syntactic sense to me.
2) impossible. A VIEW is nothing more then a stored query (i'll say it again A VIEW is NOTHING more then a STORED QUERY). If you run:
select * from view;
select * from ( <text of view> );
they are the SAME. Much like the comment directly above -- I believe you are once again comparing apples to toaster ovens and your VIEW is nothing like your QUERY (else they would in fact be the SAME)
View Performance - Follow Up
Jim, February 10, 2002 - 7:00 pm UTC
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.
Inline Views as an optimization option
Subhro, July 15, 2002 - 5:34 am UTC
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...
July 15, 2002 - 8:48 am UTC
I've no idea.
given I have no idea what your structures, indexes, volume of data, existing performance, desired performance, etc etc etc etc.....
Question on columns of views
Kapil, August 08, 2002 - 10:43 am UTC
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
August 08, 2002 - 1:09 pm UTC
yes.
just query all_tab_columns
Kapil, September 12, 2002 - 11:40 pm UTC
Thanks, Tom
That was helpful
to_date( '15-Nov-2002') without 'dd-mon-yyyy'
Robert, November 15, 2002 - 11:18 am UTC
>>> 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
November 15, 2002 - 7:26 pm UTC
yes, big time.
Some day your application will be installed into a database where the default date mask isn't dd-mon-rr
some day your application will be run by a user that set the default date mask to something other then dd-mon-rr
never use two digit years.
always use formats on the way INTO the database and the way OUT of the database to avoid any chance for confusion/errors/bugs in that regards.
Your view on a view please :;-))
Kalita, December 11, 2002 - 6:43 am UTC
I am not really sure if this is relevant to the original post. If its 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 dont 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 dont like the view definition without the group id not null check. Would like to know your view on this.
Thanks.
December 11, 2002 - 7:02 am UTC
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...
Kalita, December 11, 2002 - 7:14 am UTC
Thanks for you quick reply Tom.
using a Materialized view instead of a view
Moh'd Saeed, December 27, 2002 - 6:17 pm UTC
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
December 28, 2002 - 9:32 am UTC
basically -- how could query rewrite work on that view? Think about it:
...
and (x is null or ( x is not null and sysdate >= x ))
.....
the instant after the MV is created -- it is STALE already. You can create it as a MV for sure, and you can query it directly but no query rewrite.
You need to make sure you really want to do this first however -- keeping in mind that the instant after the MV is created, it is out of date -- would return a different answer then the real view would since it is based on SYSDATE.
I'm at a loss as to give you an example since I have no real idea what your particular issue is. SYSDATE isn't going to work real well. Refresh on commit restrictions are documented here:
</code>
http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76994/mv.htm#721 <code>
using a Materialized view instead of a view
A reader, December 30, 2002 - 11:54 am UTC
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 ?
December 30, 2002 - 12:12 pm UTC
No workaround - the MV would have to be in a continous state of REFRESH!!! it could never refresh on commit -- because it would never finish refreshing.
now, if you drop the entire "x is null or ( x is not null and sysdate >= x )" from the MV, we would be able to use the MV -- sure -- but it really depends on the question being asked and the underlying complexity of the view itself as to whether this makes sense or not.
Insufficient data to make a response (and please don't post it here -- it you get all of the info together -- you can post it as a question when I'm taking them)
View on remote tables
Arun Gupta, January 15, 2003 - 8:40 am UTC
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
January 15, 2003 - 8:53 am UTC
Well, this is actually one of the nice features of a view with dblinks -- it imports the meta data OVER into your local database. Makes compiling faster (and more "robust"), makes query tools happy (they go after user_tab_columns and such)...
If you don't want that effect, use a synonym.
Now, I cannot reproduce your issue -- I did this:
scott@ORA920> create view emp_view as select * from emp@ora817dev.us.oracle.com;
View created.
scott@ORA920> select * from emp_view;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
then in the 817 instance I:
1* alter table emp add new_job varchar2(30)
scott@ORA817DEV> /
Table altered.
scott@ORA817DEV> update emp set new_job = job;
14 rows updated.
scott@ORA817DEV> alter table emp drop column job;
Table altered.
Now, in 9iR2 I found:
scott@ORA920> /
select * from emp_view
*
ERROR at line 1:
ORA-00904: "JOB": invalid identifier
but a simple:
scott@ORA920> create or replace view emp_view as select * from emp@ora817dev.us.oracle.com;
View created.
scott@ORA920> select * from emp_view;
EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO NEW_JOB
---------- ---------- ---------- --------- ---------- ---------- ---------- ------------------------------
7369 SMITH 7902 17-DEC-80 800 20 CLERK
7499 ALLEN 7698 20-FEB-81 1600 300 30 SALESMAN
7521 WARD 7698 22-FEB-81 1250 500 30 SALESMAN
7566 JONES 7839 02-APR-81 2975 20 MANAGER
7654 MARTIN 7698 28-SEP-81 1250 1400 30 SALESMAN
7698 BLAKE 7839 01-MAY-81 2850 30 MANAGER
7782 CLARK 7839 09-JUN-81 2450 10 MANAGER
7788 SCOTT 7566 09-DEC-82 3000 20 ANALYST
7839 KING 17-NOV-81 5000 10 PRESIDENT
7844 TURNER 7698 08-SEP-81 1500 0 30 SALESMAN
7876 ADAMS 7788 12-JAN-83 1100 20 CLERK
7900 JAMES 7698 03-DEC-81 950 30 CLERK
7902 FORD 7566 03-DEC-81 3000 20 ANALYST
7934 MILLER 7782 23-JAN-82 1300 10 CLERK
14 rows selected.
fixed it right up -- so, can you do a cut and paste in the manner I do - just like above (unedited, straight cut and pastes) and we'll see what might have gone wrong.
It works
Arun Gupta, January 15, 2003 - 11:02 am UTC
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...
What is the difference
Nasser, February 10, 2003 - 12:06 am UTC
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..
February 10, 2003 - 7:41 am UTC
the formats are just that -- FORMATS.
they tell the database what to expect.
Your DEFAULT format is apparently yyyy-mm-dd, hence
Insert into t values('2003-01-01');
Insert into t values(to_date('2003-01-01','yyyy-mm-dd'))
are exactly the same in your database -- but the better coding practice is the second one. I would encourage you not to rely on a default format mask (it can change) but rather use an explicit one on input and output.
At the end of the day in your example, the 7 bytes ultimately stored were identical.
Simple thing for you..Great thing for me..
Thaha Hussain, March 09, 2003 - 10:04 am UTC
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
March 09, 2003 - 11:24 am UTC
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.
-
A reader, March 09, 2003 - 1:12 pm UTC
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
March 09, 2003 - 1:14 pm UTC
thats what the filter is doing. filter is like "for each row"
Continuation of the discussion...
K.O. Thaha Hussain, March 10, 2003 - 3:40 am UTC
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
March 10, 2003 - 7:44 am UTC
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!
Thaha Hussain, March 11, 2003 - 1:01 am UTC
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
Master or Masters?
Thaha Hussain, March 11, 2003 - 1:20 am UTC
Which one you like - 'Document_Master' or 'Document_Masters'?
March 11, 2003 - 7:29 am UTC
masters would be in keeping with students
but master sounds better ;)
totally up to you.
Question on view performance
Eric, April 14, 2003 - 5:30 am UTC
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
April 14, 2003 - 7:48 am UTC
I see no wide disparity. A view is a stored query -- so in fact there is more code executed by the server when processing a view -- however the overhead is marginal when compared to the potential gains.
Measuring elapsed time isn't the best (well, it is one of the worst) ways to measure this. Run that test 100 times and you'll see what I mean. Here is one run I did (big_table has 4 million rows but the size isn't relevant here at all. The impact of a view is entirely during the PARSE phase - after that, it is just "not relevant")
big_table@ORA920> create or replace view v as select * from big_table;
View created.
big_table@ORA920>
big_table@ORA920> declare
2 l_start number;
3 l_run1 number;
4 l_run2 number;
5 l_rec big_table%rowtype;
6 begin
7 insert into run_stats
8 select 'before', stats.* from stats;
9
10 l_start := dbms_utility.get_time;
11 for i in 1 .. 10000
12 loop
13 select * into l_rec from big_table where id = i;
14 end loop;
15 l_run1 := (dbms_utility.get_time-l_start);
16 dbms_output.put_line( l_run1 || ' hsecs' );
17
18 insert into run_stats
19 select 'after 1', stats.* from stats;
20 l_start := dbms_utility.get_time;
21 for i in 1 .. 10000
22 loop
23 select * into l_rec from v where id = i;
24 end loop;
25 l_run2 := (dbms_utility.get_time-l_start);
26 dbms_output.put_line( l_run2 || ' hsecs' );
27 dbms_output.put_line
28 ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) ||
29 '% of the time' );
30
31 insert into run_stats
32 select 'after 2', stats.* from stats;
33 end;
34 /
191 hsecs
195 hsecs
run 1 ran in 97.95% of the time
PL/SQL procedure successfully completed.
And tkprof says:
SELECT * from big_table where id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 2.13 2.01 0 0 0 0
Fetch 10000 0.89 0.86 0 40000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 3.03 2.88 0 40000 0 10000
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=40000 r=0 w=0 time=651858 us)
10000 INDEX UNIQUE SCAN BIG_TABLE_PK (cr=30000 r=0 w=0 time=396834 us)(object id 31697)
********************************************************************************
SELECT * from v where id = :b1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10000 2.22 2.04 0 0 0 0
Fetch 10000 0.90 0.84 0 40000 0 10000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20001 3.13 2.89 0 40000 0 10000
Rows Row Source Operation
------- ---------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=40000 r=0 w=0 time=637516 us)
10000 INDEX UNIQUE SCAN BIG_TABLE_PK (cr=30000 r=0 w=0 time=382123 us)(object id 31697)
for all intents and purposes -- they are exactly the same
Thanks!
Eric, April 14, 2003 - 10:03 pm UTC
Hi Tom,
Thanks very much, you answered all my questions (actually my second question is the statistics of tkprok).
Regards,
Eric
Describing a table
TH, June 18, 2003 - 2:35 am UTC
Without using a view, is it possible to decribe the structure of a table alphabetically?
June 18, 2003 - 6:07 pm UTC
sure, you can query the base sys tables (but that wouldn't be a good idea).
describe returns the columns in the order they appear in the data dictionary only.
http://osi.oracle.com/~tkyte/article1/index.html
A reader, June 22, 2003 - 9:33 am UTC
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
view on remote server
A reader, June 27, 2003 - 8:22 am UTC
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>
June 27, 2003 - 9:18 am UTC
and if you run it at the remote site, what then. (hint try driving site hint)
view on remote server
A reader, June 27, 2003 - 8:23 am UTC
Tom,
The version is 9iR2 and OS is Windows 2000.
thanks
A reader, June 27, 2003 - 9:59 am UTC
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>
June 27, 2003 - 10:13 am UTC
driving site:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96533/hintsref.htm#5699 <code>
Looking at the query performance on the REMOTE site -- doesn't look like you are going to get it to go faster (eg: not a distributed database issue as much as "query slow in the first place").
Look at the underlying query behind this view, look at the settings on the server -- I see merge join, unusual -- do they have hash joins disabled?
view on remote
A reader, June 27, 2003 - 11:35 am UTC
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
June 27, 2003 - 12:17 pm UTC
wow, that sure makes this page large doesn't it.
the hash and sort areas are teeny tiny don't you think. I see lots of swapping to temp in your life.
lots of swapping to temp
A reader, June 28, 2003 - 9:12 am UTC
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!
June 28, 2003 - 8:02 pm UTC
because you have a teeny tiny sort area size of 64k and you are doing big sorts apparently (your query above was anyway)
Remote view vs. local view.
Kashif, August 11, 2003 - 12:28 pm UTC
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
August 11, 2003 - 2:02 pm UTC
in option 1 -- the query plan for the view is generated at the remote site, this in general is "better"
you can get the advantages of option 2 by simply creating a local view that refereces the remote one.
Creating Views with value passing
Denise, October 13, 2003 - 8:23 pm UTC
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.
October 14, 2003 - 6:25 am UTC
you have a view:
create view V as
SELECT a1,b2,c3,......etc
from a,b,c
where b1=a1 and c1=b1.
you want only certain batchnos, you would query:
select * from v where batchno between :1 and :2;
thanks Tom
Denise, October 14, 2003 - 8:34 am UTC
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
Constraints on views
A reader, October 15, 2003 - 2:29 am UTC
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
October 15, 2003 - 7:53 am UTC
it is called METADATA
it gives the database (optimizer) more information.
the optimizer can use this in materialized view rewrites.
They will also be used in ad-hoc query tools (if the tools choose to) to help you build queries.
Few more doubts
A reader, October 15, 2003 - 8:25 am UTC
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
October 15, 2003 - 6:19 pm UTC
if you have either of my two books -- expert one on one oracle or effective oracle by design -- look at my writings on MV's there.
it shows that if the optimizer doesn't understand the fkey relationships between objects, nullality of columns, keys and such -- it cannot rewrite queries to use MV's as often. That is what this is for. It is just more META DATA.
the owner (creator) of a view would be responsible for this - just as a table owner would
view constraints on MV?
reader, December 20, 2003 - 6:06 pm UTC
Tom, does it mean that view constraints are useful for MVs only? thanks.
December 20, 2003 - 6:48 pm UTC
well, they are metadata as well -- "more data about your data" so they are useful that way.
would it not be nice if --- the v$ tables had this defined on them so you would know what joins to what ..... (for example)
thnx
aladdin, January 14, 2004 - 3:24 am UTC
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)
January 14, 2004 - 3:36 pm UTC
search for
"parameterized view"
why query rewrite works with plain views?
A reader, January 20, 2004 - 9:10 am UTC
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?
January 20, 2004 - 9:27 am UTC
how's about you share the before/after plans -- did you even compare them to see what is happening? show us the autotrace traceonly explain output....
yes here they are
A reader, January 20, 2004 - 11:52 am UTC
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...
January 20, 2004 - 2:10 pm UTC
query rewrite isn't firing (i see no rewrite taking place).
can you show us:
a) log onto sqlplus
b) set autotrace traceonly explain
c) run the query
d) alter session set query_rewrite_enabled=true
e) run the query again
let me see the plan change due solely to query_rewrite_enabled being turned on. use CUT AND PASTE.
here
A reader, January 21, 2004 - 3:58 am UTC
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)
January 21, 2004 - 6:48 am UTC
so, do you have function based indexes. see:
</code>
http://asktom.oracle.com/~tkyte/article1/index.html <code>
and ctl-f for query_rewrite. they control the use of FBI's and I'm guessing that there is an FBI there that is used by the query with query rewrite enabled.
FBI
A reader, January 21, 2004 - 6:51 am UTC
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?!
January 21, 2004 - 7:42 am UTC
i cannot "see" a thing. I "see" no tkprofs. I "see" nothing.
joining views
Duncan, January 30, 2004 - 7:56 am UTC
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)
January 30, 2004 - 8:29 am UTC
please file a tar on this -- looks wrong, yes.
Access full in a view
Mariano, February 05, 2004 - 1:12 pm UTC
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
February 06, 2004 - 8:21 am UTC
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)
Table design question
Sonali, July 13, 2004 - 11:02 am UTC
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
July 13, 2004 - 12:26 pm UTC
why cannot you create a view? why is the best way outlawed?
we can't create a view
sonali, July 13, 2004 - 1:51 pm UTC
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 ?
July 13, 2004 - 1:54 pm UTC
hey, if there is a third party vendor involved here and these are their tables -- your best and only bet is to ask them "how do I do this with your software". i don't play games with 3rd party tables.
the only way I would know to do this is
a) rename the existing table to table_old
b) create a new view named after the old table
c) put instead of triggers on this view (so it works just like a table)
the 3rd party should not know the difference now. but they would have to bless this implementation.
Creating view from Forms block records
Dulal, August 18, 2004 - 5:35 am UTC
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.
Your opinon?
A reader, August 28, 2004 - 8:24 am UTC
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
August 28, 2004 - 10:03 am UTC
if people ask for that data frequently, a MV would be the appropriate action to take.
think of MV's as you do indexes. You add indexes in transaction systems to speed access to answers. You add MV's in DW's to speed access to answers...
Query Approach.
A reader, August 28, 2004 - 5:56 pm UTC
Hi Tom,
Thanks for help.
Is the query approach correct?
Regards
August 28, 2004 - 6:08 pm UTC
if you were trying to pivot, sure.
Nice
Catherine, August 31, 2004 - 6:00 am UTC
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
August 31, 2004 - 9:57 am UTC
doesn't even "make sense".
a view is just a stored query, nothing more, nothing less.
if you've:
create view emp_view as select * from emp;
then:
select * from emp_view;
is really:
select * from (select * from emp);
the cache already caches things for you -- if you use it, it'll be cached. If you don't use it, it won't be. 99.9999999999% of the time the cache does it's job without any outside influences at all (eg: setting db_cache_size is all you need to think about).
Beyond that, you can use the keep/recycle pools but now you are micro tuning, after you've tuned everything else and want that extra 3%.
view with parameter?
A reader, August 31, 2004 - 11:41 am UTC
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.
August 31, 2004 - 1:24 pm UTC
time to ask the 3rd party tool for some ideas. I've quite simply "no idea what you can in fact do via this 3rd party tool"
dropping view
A reader, August 31, 2004 - 5:07 pm UTC
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!
August 31, 2004 - 5:46 pm UTC
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.
thanx! that is what i thought..
A reader, August 31, 2004 - 6:03 pm UTC
was lookin for an sql based option which i take it
does not exist...
Thanx for your promptness as always!
catch the exception ?
Alberto Dell'Era, August 31, 2004 - 6:14 pm UTC
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.
ora-600 with views using with clause
A reader, September 02, 2004 - 7:06 pm UTC
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], [],
[], [], [], [], [], []
September 03, 2004 - 9:39 am UTC
there is exactly one thing to do once you get an ora-00600. contact support please. (well, after you get your version correct that is, 9105?
there was 9.0.1.y
there is 9.2.0.x
sorry that was 9.0.1.5
A reader, September 03, 2004 - 10:33 am UTC
q on an approach involving views
A reader, September 09, 2004 - 3:58 pm UTC
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!
September 09, 2004 - 4:17 pm UTC
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.
#1 does work for you actually, any technical reasons behind the decision?
thanx!
A reader, September 09, 2004 - 4:36 pm UTC
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!:)
September 09, 2004 - 5:49 pm UTC
that would be akin to setting "slow=true" in your init.ora, actually "slowness=maximum", but technically it would "work"
Other than the obvious performance implications (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), there are no "special" issues.
thanx!
A reader, September 09, 2004 - 6:29 pm UTC
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!
September 09, 2004 - 6:34 pm UTC
my comments were already made. I hate it when political decisions, affecting technical things, prevent you from following the best practice.
Everything else is less than desirable here. Prototype them all (including the real view approach) and present you findings to your coworkers -- tell them "ok guys, no views -- well, you better buy me, just me, 5 new CPU's please -- oh and when do I hire the N additional coders to design, build, debug and maintain all of this stuff?"
ok - got it...
A reader, September 09, 2004 - 6:39 pm UTC
thanx!:)
scenario for reusing views
A reader, September 22, 2004 - 8:51 pm UTC
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!
thanx!
A reader, September 23, 2004 - 4:42 pm UTC
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.
September 24, 2004 - 9:40 am UTC
<quote>
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?
</quote>
that is correct, info type is a context.
ok - last q (on this topic:))
A reader, September 24, 2004 - 9:48 am UTC
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?
September 24, 2004 - 11:27 am UTC
it happens at parse time only. You can measure it very precisely.
Here I would choose FGAC -- the users that go for all rows would be horribly penalized.
thanx!
A reader, September 24, 2004 - 11:41 am UTC
Help to write select statement
Dulal, November 01, 2004 - 1:40 am UTC
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.
November 01, 2004 - 3:43 am UTC
you need another table with at least (in this case) 5 rows. I'll use all_objects, you can use whatever you like (it needs at least select max(c2) from table1 rows)
select c1, 1
from table1, (select rownum r
from all_objects
where rownum <= (select max(c2) from table1 ) x
where table1.c2 <= x.r;
Dulal, November 01, 2004 - 11:42 pm UTC
Thanks a lot Tom.
My problem solved.
Invalid view with " select * " after adding columns
Sean, November 09, 2004 - 2:40 pm UTC
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>
November 09, 2004 - 3:28 pm UTC
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 *)
Index problems with complex Views
A reader, November 09, 2004 - 4:15 pm UTC
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.
November 09, 2004 - 7:21 pm UTC
give me at least a "For example" -- for example, I've no idea what you mean by "hard code values" with respect to a view?
Sorry
A reader, November 10, 2004 - 9:05 am UTC
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.
November 10, 2004 - 12:06 pm UTC
aggregates, outer joins -- whats in the view, I would guess you have something in there that prevents predicate pushing -- but only because pushing the predicate would materially change the answer.
Something strange..
A reader, November 10, 2004 - 1:24 pm UTC
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!
November 10, 2004 - 7:52 pm UTC
a WHERE on a VIEW does not necessarily givethe same results as a VIEW with a WHERE...
aggreates
set operations
rownum
many things
prevent a predicate from being "pushed" into a view for the reason that, well, it would change the answer potentially.
give me a concrete example to work with.
Like..
A reader, November 11, 2004 - 4:02 pm UTC
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.
November 11, 2004 - 4:28 pm UTC
uh-uh, NEED the example, the views.
shifting a parameter of a pipelined-function into the where clause of a view
Matthias Rogel, December 09, 2004 - 5:57 am UTC
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 ?
December 09, 2004 - 2:02 pm UTC
not sure I would trust that.
why not
mypkc.set_empno(2222);
select * from emp_v;
Create view
Laxman Kondal, December 10, 2004 - 7:39 am UTC
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.
December 10, 2004 - 10:58 am UTC
those are not parameters to the view.
they are the column names
create view v ( a, b, c )
as
select d, e, f from t;
is just like:
create view v
as
select d AS a, e AS b, f AS c from t;
Create view
Laxman Kondal, December 10, 2004 - 11:19 am UTC
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.
correct answer
Venkat, January 04, 2005 - 9:35 am UTC
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
January 05, 2005 - 8:00 am UTC
a) is true
b) is true
c) is false in 815 and up, true before 815. (so, ambigous -- insufficient data to answer properly)
d) is false
e) is false is semi sort of sometimes true. it must have proper column names -- so things without names must be aliased UNLESS you are doing a materialized view of course.
constarints on views
Robert, January 08, 2005 - 2:16 pm UTC
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.
January 08, 2005 - 4:59 pm UTC
think of it this way
if Oracle knows that a view named EMP (on a much bigger table without such constraints)
a) has an attribute DEPTNO
b) DEPTNO is NOT NULL in that view
c) DEPTNO is a foreign key to DEPT (a view or another table)...
then, the optimizer KNOWS that a MV that joins the base table of the EMP view to DEPT --
a) preserves all rows of the EMP view (the join cannot "lose" any emp rows -- since DEPTNO is NOT NULL and a fkey to DEPT)
b) does not multiply rows in EMP, since a fkey points to a PRIMARY KEY (unique)
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.
if the optimizer did not know the deptno was a fkey, or that it is NOT NULL -- well, it wouldn't be able to do that rewrite.
constraints on views cont
Robert, January 08, 2005 - 10:53 pm UTC
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.
January 09, 2005 - 11:39 am UTC
do you have Expert One on One Oracle or Effective Oracle by design? In there I walk through this -- why the constraints are NEEDED.
if you have a pair of tables:
emp( empno, ename, deptno, sal );
dept(deptno, dname );
and you create a mview EMP_DEPT as
select sum(e.sal), d.dname
from emp e, dept d
where e.deptno = d.deptno
group by d.dname;
Until the optimizer KNOWS
a) deptno is a primary key in dept
b) deptno is NOT NULL in emp
c) deptno is a FORIEGN KEY in EMP to DEPT(deptno)
until the optimizer knows for sure all three facts -- the optimizer CANNOT use the mview to answer the question:
select sum(sal) from emp;
because
a) if deptno was not the primary key in dept, then a single row in EMP might result in two rows in the MVIEW -- leading to double (or more) counting
b) if deptno was NULLable in EMP, then we would lose rows in the join of EMP to DEPT and we would miss salaries
c) if deptno was NOT a fkey from emp to dept, there could be deptnos in emp that have no match in DEPT hence b) would apply again.
Views would apply the same. As for "why would you do this" -- well, views are commonly used in reporting systems to make things "easier" for end users and if a view has constraints that apply to it that the base tables did not (eg: you make sure the emp view has only employees in departments -- it filters out contract workers that are not in departments for example) -- they should have some way of telling the optimizer this -- leting the optimzer have more ways to optimize the query.
It all comes down to "more information is better"
Good Question but no answer?
Jim, January 09, 2005 - 10:41 am UTC
I myself was wondering regarding the above question from Robert. Anyone knows the answer?
January 09, 2005 - 12:06 pm UTC
(i sleep sometimes, watch my kids in sports -- things like that. sorry about that. the dates where jan 8/9th...)
constraints on views
reader, January 09, 2005 - 12:08 pm UTC
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.
January 09, 2005 - 12:23 pm UTC
i didn't, i just expressed why the constraints are NECESSARY.
whether emp and dept in the above example are TABLES or VIEWS isn't relevant is it? It is only relevant that a, b, and c are met -- regardless of whether emp and dept are VIEWS or TABLES.
don't get lost in the woods due to all of the trees. Views are Tables in a sense. just think of it like that. They are more "restrictive" tables perhaps -- so that more constraints can apply to them than apply to the BASE TABLES.
very insteresting discussion above.....
reader, January 09, 2005 - 12:38 pm UTC
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.
January 09, 2005 - 1:23 pm UTC
the constraints on a view are so the optimizer can rewrite the view to query materialized views INSTEAD of the base tables.
If the optimizer cannot or chooses not to rewrite the query against some materialized view -- the view will be processed against the base tables as normal.
View getting hanged when queried
A reader, March 23, 2005 - 6:57 am UTC
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
March 23, 2005 - 9:03 am UTC
not getting hung, taking long time perhaps.
joining on functions. look at that decode, ouch -- that has to hurt.
and ( t1.x between t2.a and t2.b or ... )
and ( t2.a between t1.x and t1.y or ... )
ouch ouch OUCH.
cartesian be thy name -- basically take every row join to every other row and evaluate predicate big time. this could take a while if you have more than one or two rows.
A reader, March 25, 2005 - 9:47 am UTC
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
March 25, 2005 - 6:25 pm UTC
no, i'm saying "look at the question you are asking"
it is a cartesian join, with or's, with a decode of an outer joined to column and ......
look at it -- think about how much work you have to do in order to process that there query.
A reader, March 25, 2005 - 10:23 pm UTC
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
March 26, 2005 - 9:11 am UTC
why?
full scans are one of three things:
a) really really really good
b) not so good
c) neither good nor bad, but just the same as using a slow index
A plan by itself is fairly useless. especially when you let it wrap
reader
A reader, March 30, 2005 - 11:44 am UTC
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
March 30, 2005 - 12:39 pm UTC
not sure what you are asking? are you asking "how can user2 get access to user1.view without user1 or anyone else granting them access?"
reader
A reader, March 30, 2005 - 1:07 pm UTC
"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
March 30, 2005 - 1:20 pm UTC
the all and overly powerful "select any table".
In order to create a view, you need direct access (not via a role). In order to grant (convey access to someone else), you need with the grant option.
Thanks
A reader, March 30, 2005 - 2:10 pm UTC
ALL does not seem to work
A reader, April 01, 2005 - 8:36 am UTC
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>
April 01, 2005 - 8:52 am UTC
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
A reader, April 01, 2005 - 10:05 am UTC
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
April 01, 2005 - 11:16 am UTC
can you run my test case as is? I cannot reproduce in 9205
reader
A reader, April 01, 2005 - 12:48 pm UTC
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
April 01, 2005 - 1:08 pm UTC
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
Views
B, April 08, 2005 - 7:57 pm UTC
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.
April 09, 2005 - 7:31 am UTC
A view is just a stored query, if you:
create view v as select a, b, c from t where x > 5;
select * from V
it is the same as if you issued:
select * from (select a, b, c from t where x > 5 );
and in that simple case, the same if you issued:
select a, b, c from t where x > 5;
Local View for remote Table-reference Information
Sekar Sai, April 09, 2005 - 9:24 pm UTC
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>
April 11, 2005 - 9:59 am UTC
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.
Thanks Tom.
Sekar Sai, April 11, 2005 - 11:41 pm UTC
Great.I was highly confused and now I am clear. Thank you very much Tom. Thanks for your quick response.
Some Clarification
Muhammad Riaz Shahid, April 19, 2005 - 2:31 am UTC
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
April 19, 2005 - 7:33 am UTC
plsql functions are always considered to return varchar2 4000 in SQL, always.
If you want them to be constrained you would have to use substr (with cursor sharing exact set)
To B from AU
Vinayak, April 19, 2005 - 9:45 am UTC
<<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.
Is there a way to avoid to restrict the access to one table in the view?
A reader, May 19, 2005 - 4:08 pm UTC
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
May 19, 2005 - 4:28 pm UTC
it'll immediately find out it doesn't need to access it. and will do minimum work to find that out
but unless you use partitioning or partitioned views - the database doesn't know until it goes to look "no data can be there"
so, if you partition by that column and the partitioning is such a way that the table can be precluded from consideration, it'll happen.
Partitioned views
A reader, May 19, 2005 - 4:34 pm UTC
May 20, 2005 - 6:57 am UTC
that is how they are turned on, in 10g, they are on by default. the 7.3 tuning guide (it is on otn, you can get to it) has perhaps the most complete definition
Partition views example
A reader, May 20, 2005 - 8:45 am UTC
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
May 20, 2005 - 10:27 am UTC
you don't give me an example to work with, heck, it might not even apply here. but basically - why is a quick 3 IO index probe a big deal?
Any suggestions on how to find dependencies when a database link is used?
David Campbell, June 28, 2005 - 7:13 pm UTC
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?
June 28, 2005 - 8:43 pm UTC
well, I did say "i would have thought differently myself" which would indicate "i don't know why they did this"
it just isn't there. Short of parsing things yourself, I don't know of a way offhand, no.
view tuning -- Oracle 9.2.0.6
Baqir Hussain, June 29, 2005 - 11:20 pm UTC
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
/
view tuning
Baqir Hussain, June 30, 2005 - 12:46 am UTC
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)
********************************************************************************
Grant privs on a view with errors ?
David, August 17, 2005 - 5:04 am UTC
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!
August 17, 2005 - 1:26 pm UTC
not really -- it cannot check to see if you have the ABILITY to even give that grant yet!
that is why it won't grant, it cannot verify you can give the grant.
Grant privs on a view with errors ?
David, August 18, 2005 - 12:00 pm UTC
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!
August 18, 2005 - 4:38 pm UTC
very cool -- nicely done. thanks for the followup, yet another trick to put into the proverbial bag
(see, I learn something new every day myself ;) let's just hope I don't forget it!)
Something doesn't fly
Bob B, August 18, 2005 - 5:25 pm UTC
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?
August 18, 2005 - 7:02 pm UTC
Oracle won't let you GRANT if it cannot validate the grant.
However, it will let you CREATE OR REPLACE the view - keeping the grants there and upon recompilation it will validate the GRANTs.
There is no reason why a "force" option on an invalid view for grants "would be harmful" -- it just isn't supported or done. The code says "in order to issue a grant, we must be able to verify it is there". When the view goes invalid (due to the OR REPLACE), it lets the grant stay registered but won't let the view recompile unless and until the GRANT is "ok"
A VIEW is NOTHING more than a STORED QUERY ?
Matthias Rogel, October 12, 2005 - 5:33 am UTC
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
October 12, 2005 - 7:30 am UTC
how does that show anything?
I'm not getting it.
like that
Matthias Rogel, October 12, 2005 - 7:42 am UTC
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
October 12, 2005 - 7:47 am UTC
so what? that is because the contents of v$sql *changed* - nothing to do with the view here - the contents of a v$ table changed.
I would entirely expect the results to change when the data being queried changed
The view is a stored query. when you query:
select * from V;
oracle will rewrite that as
select * from (text of view goes here)
That rewrite is done internally - no need for you to see it, v$sql would not reflect it.
The view is just a stored query and nothing more.
the "why" was clear
Matthias Rogel, October 12, 2005 - 10:04 am UTC
and not the point.
the conclusion remains different
October 12, 2005 - 1:56 pm UTC
no, it doesn't.
you have different data in v$sqlarea, I have no idea why you think they would result in the same results - no idea.
a view is a stored query. internally, your query against a view is rewritten. You cannot see this. I fail to understand why this "shows" anything other than if you run an equivalent query against different sets of data - you get different results.
Can't understand this 'feature' about query rewrite
Charu Joshi, October 17, 2005 - 11:16 am UTC
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.
October 17, 2005 - 11:31 am UTC
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.
Grrr...
Charu, October 17, 2005 - 11:18 am UTC
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
What are the difference(s) though obviously minute?
A reader, November 01, 2005 - 12:43 pm UTC
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
November 02, 2005 - 4:51 am UTC
turn on sql_trace=true and run the tkprof - you'll "see"
there is a lookup during the hard parse to get the view text, but I would say "they are the same"
I did that, but didn't see the lookup
A reader, November 02, 2005 - 1:24 pm UTC
I did that but can't pick out the lookup of the view. The trace files look identical.
I'm convinced enough. Thanks!
November 03, 2005 - 6:27 am UTC
need a "hard parse", did you hard parse or soft parse?
if you do this:
drop table t;
create table t ( x int );
create view v as select * from t;
alter session set sql_trace=true;
select * from t;
select * from v;
and run tkprof (with sys=yes, to see sys sql), you should see:
select *
from
t
select text
from
view$ where rowid=:1
select *
from
v
in the tkprof.
It was soft
A reader, November 07, 2005 - 2:48 pm UTC
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
November 08, 2005 - 9:31 pm UTC
can only say it is being considered.
Select Problem with a join view
Murali, February 06, 2006 - 9:13 am UTC
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.
February 07, 2006 - 12:51 am UTC
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
Sorry
Murali, February 07, 2006 - 5:35 am UTC
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
February 07, 2006 - 5:52 am UTC
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)
Upgrade to 9.2.07
Murali, February 07, 2006 - 8:28 am UTC
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
February 08, 2006 - 1:01 am UTC
you have not given me what I asked for:
...
so, going back to square one, full test case.
starting with create table
insert into
run query (with autotrace on)
cut and pasted
......
sort of like my examples typically are....
CREATE table
INSERT INTO TABLE...
CREATE indexes/constraints/whatever
run query
see bad stuff
if it is working on one and not on the other - I would suspect you have some init.ora parameters relating to the optimizer set on one and not the other - I would suggest you look at that and post what init.ora parameters relating to the optimizer you have set on the one that is not working as well.
Questions on views
Boutkhil, May 22, 2006 - 3:10 am UTC
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...
v$ views or v$ tables
Ryan, June 09, 2006 - 5:41 pm UTC
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?
June 09, 2006 - 5:59 pm UTC
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1001.htm#i1398692 <code>
Actually, technically, the v$ objects are all synonyms.
the v_$ stuff - they are views.. to which the v$ things points...
But, they are "views" - they are mostly based on "fixed tables" (x$ tables) - tables that are not tables but memory structures. SHARED memory structures.
Which require latching to access safely (hate it when a linked list's next pointer is modified as we are walking the list...)
latches are lightweight serialization devices, locks - used to protect access to shared data structures in most cases.
question on views
Ejaz, August 14, 2006 - 10:41 am UTC
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
August 14, 2006 - 12:14 pm UTC
I think you have what is known as a red herring here.
cache buffers chains latches are taken when we read from the buffer cache.
unless your drop view is doing tons of logical IO's (could be) AND you are competing with dozens of other people for this latch, it is doubtful that the cache buffers chains has anything to do with this. (you need to get that latch to do the logical IO here)
It is more likely that it is in part due to the fact that each view you drop takes some number of sql statements to be processed.
I just sql_trace'd a drop view on 10gR2 and:
16 user SQL statements in session.
168 internal SQL statements in session.
184 SQL statements in session.
there were 184 sql statements needed to complete the task.
I doubt you have contention, you have "the price of admission".
question on views
Ejaz, August 16, 2006 - 3:01 am UTC
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
August 16, 2006 - 8:27 am UTC
this is a one time operation, why would you bother? You won't be doing this 1500 times a second after all.
questions on view
Ejaz, August 16, 2006 - 10:08 am UTC
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
August 16, 2006 - 10:40 am UTC
I cannot imagine it takes 20 minutes to complete dropping 20 views.
Have you actually traced this (like I demonstrated) to see what it is doing.
questions on view
Ejaz, November 24, 2006 - 7:49 am UTC
sorry!!
the problem was with the schema object having VPD policy attached.
the issue is resolved now.
thanks
Different Data depending on predicate order
Mark Brady, December 12, 2006 - 4:03 pm UTC
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.
December 12, 2006 - 10:07 pm UTC
nope, I cannot run those queries and in fact don't have the code that generates the csv.
if you can make it TINY, post it.
why are you using strings to store dates??????
Yes, of course
Mark Brady, December 13, 2006 - 5:49 pm UTC
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.
December 15, 2006 - 8:01 am UTC
if you say they return different results, you should be able to
a) provide create table
b) create views
c) some inserts
d) the queries showing different answers.
I'm not sure how you can claim that a ...
Mark Brady, December 19, 2006 - 12:06 pm UTC
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.
December 19, 2006 - 12:58 pm UTC
create the tables
using dbms_stats get the stats there so the plan is the same as your "real" one.
I do it all of the time.
About Dynamic Performance Views
Shivdeep Modi, December 20, 2006 - 11:01 am UTC
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
December 20, 2006 - 1:18 pm UTC
they are burned into the code, they know what to do.
About Dynamic Performance Views
Shivdeep Modi, December 21, 2006 - 11:45 am UTC
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
performance on views
Balu, January 19, 2007 - 5:43 am UTC
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.
Pipelined function
babu, January 31, 2007 - 12:53 pm UTC
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
January 31, 2007 - 4:49 pm UTC
yes, you can create a view.
The advantages of a materialized view over a normal view in this case are....
precisely the same as they would be for any materialized view against any data compared to a 'normal' view against any data - no different.
view execution internally
balu, February 08, 2007 - 8:53 am UTC
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
February 08, 2007 - 11:14 am UTC
1) maybe yes, maybe no. You cannot look at a query - and just the query - and say what the plan will be in general.
You can however use explain plan to see what the plan is, how the query will be executed.
Primary Keys on Views ??
Greg, February 27, 2007 - 1:13 pm UTC
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?
February 27, 2007 - 2:22 pm UTC
they are metadata that may be used by the optimizer during a query rewrite process - when you use rely - in order to better optimize a query, in light of the additional information.
2 Phase commit remote views
A RC, May 25, 2007 - 3:35 am UTC
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
May 26, 2007 - 11:57 am UTC
need more information.
if you just log into sqlplus and
a) run procedure1
b) run procedure2
c) commit
are you saying it fails? that does not sound correct, it doesn't work that way - that would succeed.
hence, there must be something else afoot here like you are using XA or something to manage transactions outside of our control.
2 Phase commit remote views
ARC, May 29, 2007 - 6:37 am UTC
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
How to push predicate into view in this simple case
Max, June 13, 2007 - 6:06 am UTC
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
Results of How to push predicate into view in this simple case
Max, June 14, 2007 - 10:10 pm UTC
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.
Views Vs Running same SQL as an ETL
Brat, September 03, 2007 - 2:25 pm UTC
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...
September 05, 2007 - 1:34 pm UTC
since a view is nothing more than a stored query - this is "six one way, 1/2 dozen the other" as far as I can tell.
you are running the same query either way?
Views Vs Running same SQL as an ETL
Brat, September 05, 2007 - 3:17 pm UTC
True, but since Oracle already owns the query dont you think (just like bind variables) its already parsed and can retreive data back quicker?
September 05, 2007 - 5:46 pm UTC
no, when you parse a query against a view, we have to run a query to retrieve the view text and rewrite the query, it is not stored compiled or anything.
Minus Query
Brat, September 10, 2007 - 11:42 am UTC
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
September 15, 2007 - 2:14 pm UTC
Constraints on views: primary key, foreign key, etc.
Duke Ganote, October 30, 2007 - 11:26 am UTC
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".
YEAH, WHAT YOU SAID! (on view constraints)
Duke Ganote, October 30, 2007 - 11:58 am UTC
view
A reader, March 18, 2008 - 4:45 pm UTC
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
March 24, 2008 - 9:27 am UTC
since the only way that remote lobs can be acted on would be to insert as select really (to copy them over)
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.
or to return a portion of the lob using dbms_lob at the remote site - it would not really make sense to have this view, there would be nothing you could do with it.
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.
how to find if cbo has rewritten the query?
Narendra, April 29, 2008 - 12:26 pm UTC
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 ?
April 29, 2008 - 6:32 pm UTC
that is not really a rewrite, that is a simple "merge"
A rewrite would turn a query against table T to a query against table T2 (materialized view) or rewrite a subquery as a join or vice versa
and the rewrites are done internally, against a parsed representation of the query - with a materialized view rewrite, you can actually "see" the rewrite using dbms_mview.explain_rewrite - but normally "no"
view
A reader, May 06, 2008 - 1:15 pm UTC
<<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.
May 07, 2008 - 1:00 am UTC
well, the PB application would create the blob locally:
insert into local_table (the_blob_data)
and when it was done creating the blob, it would
insert into remote_table@link select whatever from local_table;
view
A reader, May 06, 2008 - 3:58 pm UTC
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.
May 07, 2008 - 1:04 am UTC
no, you need a database link.
view
A reader, May 07, 2008 - 10:57 am UTC
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.
May 08, 2008 - 3:13 am UTC
now, we have database bbb. and aaa...
(clarity is a good thing...)
Your original comment:
... 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.
...
made it sound like "PB application is logged into A, but B sends the mail. The attachment needs to be on B"
Ok, using logic here....
If PB needs to access lob on database B from A, then PB app would:
a) insert into local_table select blob from remote_table@link where condition;
local_table now has blob, do whatever you like with it.
sort of the reverse of: insert into remote_table@link select whatever from local_table;
(and I see you took my email to heart, or not really...)
view
A reader, May 08, 2008 - 11:00 am UTC
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
May 12, 2008 - 9:36 am UTC
you do not have a blob column there, please read the error message, please describe your table, please - get the real details.
you do NOT have a blob there.
Orginal Column Name In View Not alias
Vikas Sharma, May 30, 2008 - 8:22 am UTC
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
May 30, 2008 - 3:04 pm UTC
... Where does oracle stores the infomation related to a columns orginal name ie
name without alias in a view. ...
no where, it is not needed by us, we have no use for it. It doesn't exist.
Orginal Column Name In View Not alias
Vikas Sharma, May 31, 2008 - 2:02 am UTC
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
Dynamically create view
yoav ben moha, April 19, 2009 - 11:32 am UTC
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
April 21, 2009 - 12:29 pm UTC
you would write procedural code, that procedural code would implement your logic to create the view in a string (to build a string that is the create view statement)
then you would "execute immediate" that string.
how to invalid a view ?
A reader, June 24, 2009 - 2:45 pm UTC
hi tom,
was reading the concept guide and came upon this
its on page 157 chapter 6-5
under
<Data Warehousing Considerations>
Some data warehouses drop indexes on tables at night to facilitate faster loads.
However, all views dependent on the table whose index is dropped get invalidated.
This means that subsequently running any package that reference these dropped
views will invalidate the package.
Remember that whenever you create a table, index, and view, and then drop the index,
all objects dependent on that table are invalidated, including views, packages, package
bodies, functions, and procedures. This protects updatable join views.
done a test but doesnt seems to be what is describe above.
SCOTT@orcl> create index testidx on dept(dname);
Index created.
SCOTT@orcl> create view testview as SELECT * FROM DEPT;
View created.
SCOTT@orcl> DROP INDEX testidx;
Index dropped.
SCOTT@orcl> select status from useR_objects where object_name = 'TESTVIEW';
STATUS
VALID
Well it did not get invalid
q1) did i miss up anything
q2) what is updatable join views. ?
Please advice
Regards,
Noob
June 26, 2009 - 10:09 am UTC
the documentation is out of date with regards to that, it doesn't happen that way in current releases.
an updatable join view is a view that includes a JOIN and has some columns that are updatable.
ops$tkyte%ORA9IR2> create or replace view v
2 as
3 select emp.ename, dept.dname
4 from emp, dept
5 where emp.deptno = dept.deptno
6 /
View created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select status from user_objects where object_name = 'V';
STATUS
-------
VALID
ops$tkyte%ORA9IR2> select column_name, updatable
2 from user_updatable_columns
3 where table_name = 'V'
4 /
COLUMN_NAME UPD
------------------------------ ---
ENAME NO
DNAME NO
ops$tkyte%ORA9IR2> create unique index dept_idx on dept(deptno)
2 /
Index created.
ops$tkyte%ORA9IR2> select status from user_objects where object_name = 'V';
STATUS
-------
VALID
ops$tkyte%ORA9IR2> select column_name, updatable
2 from user_updatable_columns
3 where table_name = 'V'
4 /
COLUMN_NAME UPD
------------------------------ ---
ENAME NO
DNAME NO
ops$tkyte%ORA9IR2> alter view v compile;
View altered.
ops$tkyte%ORA9IR2> select column_name, updatable
2 from user_updatable_columns
3 where table_name = 'V'
4 /
COLUMN_NAME UPD
------------------------------ ---
ENAME YES
DNAME NO
ops$tkyte%ORA9IR2> update v set ename = lower(ename);
14 rows updated.
ops$tkyte%ORA9IR2> update v set dname = lower(dname);
update v set dname = lower(dname)
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table
the addition of the unique index permits data from the EMP table to be safely modified via this view. The emp table is "key preserved" due to the unique index. That means we know that the rows in EMP that appear in the view appear AT MOST ONCE in the view.
The proper way to do this however would be via a constraint:
ops$tkyte%ORA9IR2> drop index dept_idx;
Index dropped.
ops$tkyte%ORA9IR2> alter view v compile;
View altered.
ops$tkyte%ORA9IR2> select column_name, updatable
2 from user_updatable_columns
3 where table_name = 'V'
4 /
COLUMN_NAME UPD
------------------------------ ---
ENAME NO
DNAME NO
ops$tkyte%ORA9IR2> alter table dept add constraint dept_pk primary key(deptno);
Table altered.
ops$tkyte%ORA9IR2> select status from user_objects where object_name = 'V';
STATUS
-------
INVALID
ops$tkyte%ORA9IR2> select count(*) from v;
COUNT(*)
----------
14
ops$tkyte%ORA9IR2> select column_name, updatable
2 from user_updatable_columns
3 where table_name = 'V'
4 /
COLUMN_NAME UPD
------------------------------ ---
ENAME YES
DNAME NO
that automagically maintains everything without having to recompile manually.
how to invalid a view ?
alan, July 04, 2009 - 8:36 am UTC
hi tom, with regards to your answers above.
would you mind to explain to me
q1) if i create a view , create an index on the underlying table, drop the index - the view is still valid
however if i create an UNIQUE index and drop it later, the view will be invalidated.
why the difference between UNIQUE and NONUNIQUE index ? (on view getting invalidated)
q2) i understand a keypreserved table is a table that still has its key preserved when it is join in a view. so that when we do a update on the view, we can safetly update that keypreserved table.
however i do not understand,
why creating an unique index or primary key on the (dept-deptno) table will enable EMP to be key-preserved table ?
how will that trigger ORACLE to know that EMP table is now key-preserved ?
Hope to hear from you soon
Best Regards,
Noob From Singapore.
July 06, 2009 - 8:05 pm UTC
having a unique index in place can change the status of columns from "not updatable to updatable".
create view v as select ename, dname from emp, dept where emp.deptno = dept.deptno;
if you uniquely index dept(deptno), we can update ename in that view. If you do not, we cannot. (well, you could use a non-unique index in conjunction with a UNIQUE constraint as well)
A key preserved table is a table in a join such that we know the rows in that table appear AT MOST ONCE in the resulting join. If you know that dept(deptno) is UNIQUE, then you know for a fact that if you join emp to dept by deptno, the rows in emp can appear AT MOST ONCE (they are key preserved) in the resulting join.
Declaring PL/SQL Variables based on View Columns
Rama Subramanian G, October 23, 2009 - 12:05 am UTC
Tom,
In a PL/SQL Procedure, if I declare a set of variables, basing them on columns of a view, at runtime, will the view get evaluated to instantiate those variables? I mean will there be a performance overhead in terms of instantiating those variables?
October 23, 2009 - 2:11 pm UTC
that is all done at compile time - the compiler needs to know the datatype.
can I use view
babloo, November 22, 2010 - 11:42 am UTC
Hi Tom,
we have a database which is read by many external systems.
Currently it contains only data specfic to USA. Ie country_code=USA.
Now we will load the worldwide data into it.Existing systems still need to see the USA data only. we will write new application for worldwide data.
How to load the data without rewriting all existing applications to have the filter(contry_code=USA).
I am thnking two options
1. Rename the table to data_global and create a view with the same name that is current table name used by applications . view will have the filter(contry_code=USA).
new application can use the data_global with the filter.
2. use the partinoing but We still need add a filter in existing queries for partition elimination
Please suggest
November 23, 2010 - 1:10 pm UTC
you don't give much information here - like a description of the existing application and how it accesses the database versus how the NEW application will.
for example - if you tell me "old application is a 3 tier application that uses the username 'foo' to connect to database - new application will be 3 tier too but will be using 'bar' to connect" - I could give you a good answer.
Tell me this - if you were looking at the database using sqlplus - could you tell the difference somehow between "version 1" and "version 2" of the application? Is there some obvious telltale sign there that a given session is "version 1" versus "version 2"?
view
sam, August 02, 2011 - 4:46 pm UTC
Tom:
1. If you are writing a pl/sql stored procedure
how do you normally decide whether to store the SQL in a VIEW and defined a CURSOR in pl/sql (i.e SELECT * from VIEW_EMP) to select from view versus writing the SQL in CURSOR in the pl/sql routine (i.e SELECT * from EMP where col1=.. and col2=..) and not selecting from a view?
2. Would your answer be different for the case where an SP is invoked in DB1 but all tables queried are in another database (using DB link). WOuld it make any performance difference if I create a LOCAL VIEW in DB2 and have SP1 call it instead.
August 02, 2011 - 4:51 pm UTC
1) are you going to use that view in multiple places? If so - sure, a view (a view is like a subroutine to me, you put reusable stuff in there)
If not - probably just code it right in there as a cursor.
2) Here I would want the view on database 2 like you suggest. The reason is that the query optimization of the view would take place on the remote site - and you have the best chance of getting the best plan. If it were optimized locally - at database 1 - the optimizer would not have all of the available statistics.
view
sam, August 02, 2011 - 5:56 pm UTC
Tom:
Great!
It is really only used once by one pl/sql routine but because of your #2 explanation concerning the query optimization I will go with the VIEW solution.
It is also a LONG query (50 lines) that has TWO subqueries UNION together. But that should not be a factor to use a view versus sql in pl/sql.
thank you,
Data Dictionary View
Vasanthan, April 03, 2012 - 4:55 am UTC
Hi Tom,
I have studies that Data dictionary tables and their views are owned by SYS user. If this is the case,
1)I was trying to query from a DBA_ (view) from SYSTEM user and I can get it. The surprise is that, in the from clause I did not use SYS.DBA_(...) while querying but still I get an another user's object!.
How this is possible?
2) I was trying to find the owner of DBA_SYS_PRIVS view but in vain. How to find that. Can you give the query?
3) I was trying to connect DBSNMP user to connect as sysdba and got connected, in 10g. The surprise is that the user does not have sysdba privilege. The situation is same for other users without Sysdba privilege as well. What may be the reason?
I was doing: Connect DBSNMP as sysdba
Password: whatever I give it accepts and connects as sysdba. Why so?
Thanks so much.
Ragards.
April 03, 2012 - 6:50 am UTC
1) read about public synonyms
http://docs.oracle.com/cd/E11882_01/server.112/e25789/datadict.htm#CNCPT1212 2)
ops$tkyte%ORA11GR2> select owner from dba_views where view_name = 'DBA_SYS_PRIVS';
OWNER
------------------------------
SYS
ops$tkyte%ORA11GR2>
3)
ops$tkyte%ORA11GR2> select * from all_users where username = 'SANTA';
no rows selected
ops$tkyte%ORA11GR2> connect santa/claus as sysdba;
Connected.
sys%ORA11GR2>
Unless you are connecting over the network,
"anything/you_want as sysdba"
is IDENTICAL to
"/ as sysdba"
a local sysdba connection always uses OS authentication, the user/pass supplied is not even looked at.
doubt in one of your view's output
Biswaranjan, April 15, 2012 - 1:49 am UTC
Hi Tom,
I was looking through this page and found something strange about one of you post.
please clarify my doubt.
below is one of you post to 'Dulal's request' in 2004.
###############
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.
Followup November 1, 2004 - 3am Central time zone:
you need another table with at least (in this case) 5 rows. I'll use all_objects, you can use
whatever you like (it needs at least select max(c2) from table1 rows)
select c1, 1
from table1, (select rownum r
from all_objects
where rownum <= (select max(c2) from table1 ) x
where table1.c2 <= x.r;
###############
select * from table1;
C1 C2
A 2
B 1
C 3
D 5
and the output of the below query :
select c1, 1
from table1, (select rownum r
from all_objects
where rownum <= (select max(c2) from table1 ) x;
is
C1 C2
A 1
A 1
A 1
A 1
B 1
B 1
B 1
B 1
B 1
C 1
C 1
C 1
D 1
which is not like the output desired by Dulal.
Please clarify if i missed somewhere.
thanks,
Biswaranjan.
April 15, 2012 - 7:52 am UTC
correct, had the predicate backwards. that is what happens when people do not give me a create/inserts to generate test data.
that is why I no longer look at questions without a create/insert. I have no way to test short of generating the create/inserts myself. the correct answer way back when would have been:
ops$tkyte%ORA11GR2> select c1, 1
2 from table1, (select rownum r
3 from all_objects
4 where rownum <= (select max(c2) from table1 )) x
5 where x.r <= table1.c2
6 order by c1;
C1 1
---------- ----------
A 1
A 1
B 1
C 1
C 1
C 1
D 1
D 1
D 1
D 1
D 1
11 rows selected.
I'm sure they figured out the predicate condition was reversed - pretty easy to see if you understand the technique used...
doubt in one of your view's output got cleared
Biswaranjan, April 15, 2012 - 8:39 am UTC
I have no words to say . But only to say LONG lIVE TOM ,Long Live oracle.
Thanks,
Biswaranjan.
doubt in one of your view's output got cleared
Biswaranjan, April 15, 2012 - 8:44 am UTC
Hi Tom,
If we will use the below query for the same result will it be any problem for performance.
select c1,1 from table1,(select rownum r from dual connect by level<6) x where x.r<=table1.c2 order by c1;
thanks and regards,
Biswaranjan.
April 15, 2012 - 9:29 am UTC
it would probably work even better - it just wasn't a generally/universally available approach in 2004.
today, you would probably:
ops$tkyte%ORA11GR2> with data(r,max_c2)
2 as
3 (select 1 r, max(c2) max_c2
4 from table1
5 union all
6 select r+1, max_c2
7 from data
8 where r < max_c2
9 )
10 select c1, 1 c2
11 from table1, data
12 where data.r <= table1.c2
13 order by c1;
C1 C2
---------- ----------
A 1
A 1
B 1
C 1
C 1
C 1
D 1
D 1
D 1
D 1
D 1
11 rows selected.
In 11gr2 and above...
things change over time :)
View invalidation wrong documentation .
Biswaranjan, May 22, 2012 - 1:00 am UTC
Hi Tom,
I was reading "Oracle® Database Concepts 10g Release 2) and
Under the chapter dependencies among schema objects "Data Warehousing Considerations" found these below comments and
tested accordingly but found no invalidation.
"Remember that whenever you create a table, index, and view, and then drop the index,
all objects dependent on that table are invalidated, including views, packages, package
bodies, functions, and procedures. This protects updatable join views."
I have done the same but found no invalidation for views.
I tested in "PL/SQL Release 10.2.0.1.0 - Production".
Can you please tell is it like they have relaxed this constraint in the above mentioned version.
thanks as always,
Biswaranjan.
May 22, 2012 - 8:12 am UTC
Yes, that is not true - I'm not sure it ever was. I tried back to 9i and it doesn't invalidate.
And I don't see any reason why it would - updateable join views are dependent on constraints more than an index.
could you please post a comment against that stating this? that way it'll get fixed in the documentation.
continuation to my last post.
Biswaranjan, May 22, 2012 - 1:59 pm UTC
Hi Tom,
Thank u for clarify.
I just clicked the "contact us" link and sent a mail to oracle
document support team as you stated.
I was not aware how to do it.
Regards,
Biswaranjan
Thank u for clarify.
droping index invalidates all objects
Biswaranja, June 03, 2012 - 8:30 am UTC
June 04, 2012 - 8:52 am UTC
umm, well, my response would be the same as above. could you do the same as you did before?
continuation to my last post.
Biswaranjan, June 04, 2012 - 9:10 am UTC
Sure I will do it.
Thanks,
Biswaranjan.
question about views with duplicated columns
Sokrates, October 25, 2012 - 2:00 pm UTC
sokrates@11.2 > create table t ( a varchar2(1) );
Table created.
sokrates@11.2 > create view v as select a as a, a as b from t;
View created.
sokrates@11.2 > insert into v (a) values('a');
1 row created.
sokrates@11.2 > insert into v (b) values('b');
1 row created.
sokrates@11.2 > select * from v;
A B
- -
a a
b b
so far expected behaviour
Now:
sokrates@11.2 > insert /* expecting exception */ into v( a, b) values ('c', 'd');
1 row created.
sokrates@11.2 > select * from v;
A B
- -
a a
b b
d d
shouldn't raise this an exception ?
inserted value 'c' is lost
sokrates@11.2 > update /* expecting exception */ v set a='e', b='f';
3 rows updated.
sokrates@11.2 > select * from v;
A B
- -
f f
f f
f f
where is my 'e' ?
October 28, 2012 - 10:39 pm UTC
ok, so that is a bug, but I'm not sure if it is
a) a documentation bug, this behavior isn't documented
b) an implementation bug, that isn't the expected behavior.
would you be able to file? if not, let me know.
thanks, filed a bug
Sokrates, October 29, 2012 - 5:45 am UTC
Nested Materialized view refresh PX Deq Credit wait events
Christophe, December 13, 2012 - 10:08 am UTC
Hello Tom,
I have an aggregate materialized view using a fact and a dimension table defined :
> CREATE MATERIALIZED VIEW "vm_clm_non_cob_grp_mth"
PARALLEL COMPRESS
PARTITION BY LIST (month_id)
(
PARTITION vm_non_cob_mbr_mth_200801 VALUES (200801)
, PARTITION vm_non_cob_mbr_mth_200802 VALUES (200802)
)
BUILD DEFERRED
USING NO INDEX
REFRESH FAST
ENABLE QUERY REWRITE AS
SELECT t.month_id, fct.dspnsing_fee_bket_id, fct.sponsor_grp_sk
, fct.age_grp_id, fct.region_id, fct.gender_id, fct.rltn_sk_id, fct.drg_pln_id
, fct.div_id, fct.pln_id, fct.dis_st_id, fct.prod_catg_id, fct.pharmy_typ_id
, fct.biol_id, fct.drg_id, fct.dis_crse_id, fct.thptc_clas_id
, SUM(fct.drg_cost) drg_cost
, SUM(fct.ingrd_cost) ingrd_cost
, SUM(fct.dspnsing_fee) dspnsing_fee
, SUM(fct.planpd) planpd
, SUM(fct.nbr_clms) nbr_clms
, COUNT(*) cnt
, COUNT(fct.drg_cost) cnt_drg_cost
, COUNT(fct.ingrd_cost) cnt_ingrd_cost
, COUNT(fct.dspnsing_fee) cnt_dspnsing_fee
, COUNT(fct.planpd) cnt_planpd
, COUNT(fct.nbr_clms) cnt_nbr_clms
FROM "clm_non_cob_fct" fct, time_dim t
WHERE fct.time_id = t.time_id
GROUP BY t.month_id, fct.dspnsing_fee_bket_id, fct.sponsor_grp_sk
, fct.age_grp_id, fct.region_id, fct.gender_id, fct.rltn_sk_id, fct.drg_pln_id
, fct.div_id, fct.pln_id, fct.dis_st_id, fct.prod_catg_id, fct.pharmy_typ_id
, fct.biol_id, fct.drg_id, fct.dis_crse_id, fct.thptc_clas_id;
The complete refresh takes under 40s and here is the execution plan for the join/grouping being performed:
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | TQ |IN-OUT|PQ Distrib|
------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 7517K| | | |
| 1| PX COORDINATOR | | | | | |
| 2| PX SEND QC (RANDOM) | :TQ10002 | 7517K| Q1,02| P->S | QC (RAND)|
| 3| HASH GROUP BY | | 7517K| Q1,02| PCWP | |
| 4| PX RECEIVE | | 7517K| Q1,02| PCWP | |
| 5| PX SEND HASH | :TQ10001 | 7517K| Q1,01| P->P | HASH |
|* 6| HASH JOIN | | 7517K| Q1,01| PCWP | |
| 7| BUFFER SORT | | | Q1,01| PCWC | |
| 8| PART JOIN FILTER CREATE| :BF0000 | 1200 | Q1,01| PCWP | |
| 9| PX RECEIVE | | 1200 | Q1,01| PCWP | |
| 10| PX SEND BROADCAST | :TQ10000 | 1200 | | S->P | BROADCAST|
| 11| TABLE ACCESS FULL | TIME_DIM | 1200 | | | |
| 12| PX BLOCK ITERATOR | | 7517K| Q1,01| PCWC | |
| 13| TABLE ACCESS FULL | clm_non_cob_fct| 7517K| Q1,01| PCWP | |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("FCT"."TIME_ID"="T"."TIME_ID")
So no problem there.
However, if I decide to use this MV as a nested MV and have this new MV fast refreshable, I would need to create an MV log on the nested MV
> DROP MATERIALIZED VIEW "vm_clm_non_cob_grp_mth";
> CREATE MATERIALIZED VIEW "vm_clm_non_cob_grp_mth" ... (same as before)
> CREATE MATERIALIZED VIEW LOG ON "vm_clm_non_cob_grp_mth" WITH COMMIT SCN, ROWID (
month_id, dspnsing_fee_bket_id, sponsor_grp_sk, age_grp_id, region_id
, gender_id, rltn_sk_id, drg_pln_id, div_id, pln_id, dis_st_id, prod_catg_id
, pharmy_typ_id, biol_id, drg_id, dis_crse_id, thptc_clas_id, drg_cost
, ingrd_cost, dspnsing_fee, planpd, nbr_clms, cnt, cnt_drg_cost, cnt_ingrd_cost
, cnt_dspnsing_fee, cnt_planpd, cnt_nbr_clms
) INCLUDING NEW VALUES;
Now if I perform a complete refresh after the DDL (it is a force refresh but it will choose a complete one the first time)
> exec DBMS_MVIEW.REFRESH('"vm_clm_non_cob_grp_mth"', method => '?');
It never finishes and I see a lot of Px Deq Credit wait events. By looking at the temp usage, I can see that it stopped growing meaning that the aggregates have all been stored in temp tables and are just waiting to be written to the MV.
This high number of Px wait events are also happening on aggregation MV without any MV logs on them.
Does it have something to do with my parallel configuration? What is rally happening in the background once the aggregation/joins are performed in the temp tablespace and are waiting to be written to the MV?
Thanks,
Christophe
Question on Create View
Sridhar, July 18, 2013 - 5:00 am UTC
Hi Tom,
I know that in an SQL statement, we are not supposed to select a data that is of BOOLEAN datatype, but wanted to experiment something on it, so i did the following:
1) Created a function that returns a BOOLEAN
2) Used that function in the CREATE VIEW AS SELECT that function from dual.
Some weird things happened:
1) View got created (is it not supposed to throw the error "expression is of wrong type"? Whereas it threw this error when selecting from the view)
2) When I did DESC that view, it showed the datatype as NUMBER.
Is this intended behavior or a bug?
scott@SRID> select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
scott@SRID> create or replace function f_check
2 return boolean
3 is
4 begin
5 return true;
6 end;
7 /
Function created.
scott@SRID> create or replace view fv_view as (select f_check COL1 from dual);
View created.
scott@SRID> desc fv_view
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
COL1 NUMBER
scott@SRID> select * from fv_view;
select * from fv_view
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
July 18, 2013 - 3:36 pm UTC
it is a bug that does not reproduce in the latest release:
ops$tkyte%ORA12CR1> create or replace view fv_view as (select f_check COL1 from dual);
create or replace view fv_view as (select f_check COL1 from dual)
*
ERROR at line 1:
ORA-00902: invalid datatype
Orginal Column Name In View Not alias revisited
hans wijte, February 17, 2015 - 4:37 pm UTC
In this thread in 2008 someone from India asked "Orginal Column Name In View Not alias, ie name without alias in a view. ...": you answered that Oracle doesn't need that info. That's correct: say you have a table T375 with 3 million incomprehensible column names, and a view HELPDESK with 300 understandable column names then you can find this relation as follows:
select x.column_name||' = '||y.column_name
from dba_tab_columns x
, dba_tab_columns y
where x.owner='XXXXX'
and y.owner=x.owner
and x.table_name='T375'
and y.table_name='HELPDESK'
and x.column_id=y.column_id
order by y.column_name
/
question on views
hans wijte, February 17, 2015 - 5:32 pm UTC
disregard my response above; it's total BS. I was a bit tired I suppose :)