What is the view in oracle? simple view? complex view?
Вставка
- Опубліковано 7 вер 2024
- What is view?
What is Simple View?
What is Complex View?
Are DML's allowed on view?
Difference between "Simple" & "Complex" view?
List of all the SQL and PLSQL interview questions @ easy-learning-...
Very Helpful.. having more than 10 years of career gap made me forget few things but these videos helped me a lot to brush up PLSQL skills.. Thank you Siva..
Thank you and welcome 💐
Man i like your videos..i started out as a just a functional consultant and credit to Siva Academy am both functional as well as a developer..
My Pleasure, thanks for your comments :-)
Super yaar,your video is very clearly understandable,tried reading many websites no one is clearly telling in website, you should have created a series to learn entire oracle na
Clearly explained sir. We expect more content like this. Why you have stopped posting new videos sir?
Thank you, please stay tuned, will start posting videos from this month.
@@SivaAcademy thank you sir
Good video. i have an query .if we update the base table and the column is consumed as part of view ,will it also be updated ? .or do we need to refresh the views to see the changes
Hi Siva bhai , i am wating for views other videos such as MV's refresh types.
@Achyut, sure bro, this topic is already in my pending list, will post it, Please stay tuned.
Can I apply DML on view which is created on 1 view and that view is created on 2 tables?
Thanks
Thanks for this , but i want to know about materialised view too
How dml operation can we perform in simple view ?
Thank you,
I follow your DB links regularly.
I have some questions I am facing in interview
What is force view?
What is table function? What is pipeline function?
How we can use those functions in real time?
How we can call them both?
Sure bro, i will add it to my upcoming list of videos, please stay tuned
Can you explain about complex view to be created for fact table to join dimension tables.
can we use case ,decode etc in views or DML statements ? Can you do video on it. ?
Thanks a lot for such great efforts...
my pleasure, thank you
Thx sir. It's very cool nd right to the point
@Rekha, Thanks for your comment :-)
Very useful vedio
Thank you
Thanks Siva, could you please create a full playlist ?
Thanks, your videos are very clear and easy to understand.
What happens to view if the table is deleted? What happens to the view which was created on another view when table is deleted?
Thanks in advance.
Welcome,
If you drop a table, the view will get invalid. You cannot execute the view, until the tables are created back, you can look into this video for some detail.
ua-cam.com/video/e4VbIwz3mps/v-deo.html
Thanks a lot the way you are explain , could you please provide the video on normalization topic.
Thank you bro, sure, will cover in upcoming videos, please stay tuned
Give an example of a function return collection
And
Collection as in parameter for function
Please provide bro
Thank you Sir..! The vedios are really helpful and we are learning a lot from these vedios....I request you to please share some script with vedios...... Thanks a lot ....!
Welcome Mohin, most of the scripts you can find under sivakacademy.blogspot.com/
crystal clear explanation bro..
Thank you 🙏 bro
Nice video brother
Thank you
Hi Siva, thanks for all the amazing videos, I wanted to know how to take an extract of all the column headers and table names with out the data in oracle sql. For example, There are 1000 tables in one schema and I want to copy all the table names and their respective column names without the data. So that next time if I want to search a particular column value, I can directly open that excel and search for the column name and the table names. In this way I will reduce the manual work of searching that column name in each and every table. Please suggest :)
Thanks for your comments bro,
yeah you can get all the tables, and column names of the user using this data dictionary table, just check this table once "user_tab_columns"
Hello sir is your course available on udemy please let me know i would love to buy it ,you explained everything with great detail. Thanks
Everything is here then why u paid from other flatform.. W u want to pay bhai. .
Great video
🙏
Sir what is the drawback of view
very helpful..!!
Welcome bro
Force view ke bare mein detail mein information ka ek video laiye
Thank you..awesome video!!
You are so welcome!
Nice explanation
🙏🙏👍
Pls explain why translate function returns null if it has null argument
Sir we can do dml operations on complex view for that we have check user_updatable_columns.
@karnaraj, yeah, you can do dml operation on complex view, with few restriction, i will post in a separate video in detail with examples , please stay tuned.
@@SivaAcademy you're lecture are best, I'm using for interviews and suggesting aslo .and one more thing I'm thinking for certification can you help me.
@Karnaraj, Please let me know what help you need, if possible, i will be happy to help :-)....
@@SivaAcademy how to prepare for Oracle certification. How to get dummies and other study materials
Sir i took one base table I have 5 columns but I took only 3 columns into view but I want to perform on this simple view,,I tried my self to insert 3 columns of values on simple views.when I queried base table three columns only inserted remains coulmns nulls
Kindly create one session on partition of table
Yes bro, please stay tuned
Sir plz do a practical video on when dmls allowed in simple view as well as complex plz sir a humble req , plz make it asap, thnks!!
@Shaheen, Sure, i will post a video on dml on views...please stay tuned.
@@SivaAcademy is this posted, I am adding one more question if not,
for eg : table t1 is created with single column c1 and view is created with v1, after that we add on two more column c2,c3, so if we query v1 what will be the output ?
@@mahendrasinghthakur9471 what ever column you created the view for the same column will be the result of the view.. addition of new columns in the table will not impact the views
Hi Siva
Wanted to know what’s the difference between a view and a temp table?
view is just a result of query ( on top of existing tables), however temp table is similar to table, but holds data for short duration based on definition.
@@SivaAcademyThanks a lot bro your content and explanation is crystal clear....:-)
Welcome 🙏💐
Thank you.Please confirm if we can have index in view.If yes how.
No, index cannot be created on view, however we can create on mview
Thank u, could u explain what is materialized view and compound view. Are they different or same???
Welcome, please look into this
ua-cam.com/video/GQxJli8YUN0/v-deo.html
Sir, why group functions are not allowed in Simple View? Please Explain, Sir.! What happen when we create it. Please write a query if possible. Please!
You can have group function, however it becomes complex view
tried group function in simple view, is working fine
Hi Siva,
Can we use instead of trigger for complex view update to do changes in base table?
Yes we can
Hi Siva and All,
Anyone knows, how can I alter column size of a view ?
Suppose I have a view with one column size is 30, I want this to be 40 varchar2, can you please tell me
I have alter the base table but it is not reflecting in view.
If you modify the datatype of the column of underlying base table, then it will automatically reflect in view, view refers to underlying tables columns datatype only, this is based on Oracle database
@@SivaAcademy yes Siva, I have tried what you have mention, but is not showing the same.
Is the column you are checking is actually from underlying base table, or is it a derived column from other columns?, Describe the view structure and tablets structure to cross verify the same, or cross check the datatype in user_tab_columnsdata dictionary
@@SivaAcademy below is the example:
Create table emp (id number, ename varchar2(20));
---insert some record in emp table;
Commit;
Desc emp;
Here column ename size is 20.
--creating view
Create view emp_v as select ename from emp;
--altering the emp table with ename size is 30 char.
Alter table emp modify ename varchar2(30);
---so now the ename column size is 30 of emp table.
Desc view emp_v;
Size of ename column is same as previous 20 char.
--hope you will understand, please let me know if any further information
Thank you Siva.
Sir i created the view on emp table but in user_tab_columns its not showing any data why sir like that but when i did user_views i can see that view in list
@Shaheen, It should display, can you check on ALL_TAB_COLUMNS as well
Can we drop a column from a view??
If any data is deleted from view, will it affect base table also, means data will be deleted from base table or not?
Yes
What is Difference between table and view?
View - retrieves data from base table only
Need more explanation on DML operation
sure, please stay tuned, will post as a seperate video
Hi,
Can you pl share a video on Oracle Temporary Tables?
Sure bro, please stay tuned
Thank you so much Siva. I'm impressed by your videos btw.
Welcome bro
Can v create index on a view
No, we cannot create index on view
Can we get Oracle SQL developer for free. From where can we install. Please share the link.
yes, you can download from oracle site
what is metadata bro?
information about the database objects like table, view, sequence etc
tell me we can update a view or can not?
Simple views can be updated, where as complex view cannot be updated... Watch instead of trigger to know how to update complex view
What is the main purpose of having a view?
Why can't we have a table instead of view?
What will happen when we query the view?
I'll raise few more questions after this
What is the main purpose of having a view?
To name a few,
1. Reusability
2. Display same data in multiple format without re-storing the same data again and again.
3. To hide complex SQL. Make easy for access.
4. To give read only access to other uses for selected set of records
5. Same SQL functionality re-usability, can be joined with other views or tables.
6. Display computed & expression based columns ( that are not part of base tables)
Why can't we have a table instead of view?
1. Display same data in multiple format without re-storing the same data again and again.
2. Incase if there a need to store the data, then M-VIEW (Materialized view) will serve the purpose. ( Though there are other uses of M-VIEWS)
What will happen when we query the view?
1. Since “VIEWS” don’t have data of its own, every time you query the view, it executes the underlying SQL, and fetched the data from base tables.
@@SivaAcademy
I agree with few points.
My understanding about a view is,
1) it will be used for security purpose
2) reducing the complexity
3) reusability
I would like to highlight a point here, when we say reusability means the complex query can be converted into a view to fetch the result. This is good. But can't we use RESULT_CACHE in the complex query instead of creating a view. I believe RESULT_CACHE will improve the query performance. First time when we query the complex sql as usual it will take more time but next time onwards, the result will be faster. When we use RESULT_CACHE the results will be stored in the shared pool of the SGA. Whenever we use the complex sale, the results will be fetched from here instead of going to the data blocks..
Correct me if I'm wrong?
You are rihgt, "RESULT_CACHE" is mainly from performance aspect only. It depends on the use case whether we need it or not.
having said that, there is no restriction to use result cache in view. If the view is going to executed frequently, then result cache would help.
@@SivaAcademy
I think you got confused.
I was telling that instead of creating a view, we can use result_cache hint directly in our base query to get the results faster.
View is for security purpose which means we can hide important columns from the unknown users. This can be done by using INVISIBLE column in Oracle 12c.
No No,
view can server multiple purpose , not just to hide columns.. You cannot completely replace with a query (with result cache).
For eg, View can be considered like named SQL, you can access the SQL by its name, rather then every one to retype the same SQL, again, for re-usability & security is the main purpose of view. Result cache can aid view in improving the performance of the query...
in some projects, data access from tables would be happen through view only....