@@@SivaAcademy Can you confirm suppose we opened and started working on cursor and in between some records were deleted by another user from the table then still are we able to see the selected all rows of that cursor ?
Below are some questions asked in an interview and need clarification. 1) Func and a Procedure with same in a package goes under overloading? 2) What all grants required to execute procedure with name schema.PROC1 having DML on schema1.tab1 from schema2.?
You can also manipulate a unique data for a colomn with a unique-function based index. CREATE TABLE my_table ( data_col VARCHAR2(100) NOT NULL, deleted VARCHAR2(1) DEFAULT 'N' NOT NULL ); ALTER TABLE my_table ADD( CHECK (deleted IN ('N', 'Y') ENABLE VALIDATE); CREATE UNIQUE INDEX TEST_UK ON my_table (CASE "DELETED" WHEN 'N' THEN "data_col" END); The values in the data_col have to been unique if the deleted-column is "N" if the column is "Y" then the index doesn't see the value for the uniqueness.
@Praveen, These index related videos focus the concept of index, and its usage in general from performance tuning aspect. I will be starting videos specific to performance tuning videos., there i will cover in detail related to tuning aspect. Please stay tune. Thanks,Siva
Hi Sir, I am learning a lot from your videos and they are very very helpful. Could you please explain y index will be used though we use sum function in the select and not used when Upper function is used?
Hello sir , i have a doubt regarding index, for ex we have a query. Select *from emp where emp_grade ='A' and class_section ='B'; And we have created index on emp_grade , will index get called if there are more than one where condition?
Yes praveen, we can use the user defined function to create the function based index as far as the function is DETERMINISTIC function. Will explain about DETERMINISTIC function in upcoming video please stay tuned.
Best 🎉
We are eagerly waiting sir for videos specific to performance tuning
Me too
@Venkat, sure,will start posting tuning videos soon, please stay tuned.
@Sagar, sure,will start posting tuning videos soon, please stay tuned.
Any videos related to packages and scenarios in real time
@@@SivaAcademy Can you confirm suppose we opened and started working on cursor and in between some records were deleted by another user from the table then still are we able to see the selected all rows of that cursor ?
First time seeing excellent tutorial
Welcome 🙏, its my pleasure
Excellent explaination of index concepts
🙏
Sir,
You explained it very nicely 👍👍👍😍😍😍❤️❤️
Thank you bro
Thanks on your detailed information about function based index
Welcome bro
@@SivaAcademy Can you please make a video on Namespaces ??
Yesterday I sent a mail requesting you to make a video on it ?
Sure bro, next upcoming video will be on namespace, kindly stay tuned
Good explanation on indexes
Thank you
Thanks sir for wonderful video
Cool stuff
Thank you
Very Helpful, Thank you
Welcome 🙏
What if there exists an index on of the columns of the expression used in where condition?
sir can you please detail about performence tunning for indexes...
Sure bro, please stay tuned
Plz give one demo of function based index created on user defined function/packaged function...I mean deterministic one
@Rabinatayan patri, sure, i will post on deterministic function soon. Please stay tuned.. Thanks, Siva
Does index refresh itself before dml operation? Or we need to create it every time?
Index is maintained by oracle, and it will get automatically updated whenever any DML operation happens on the tables in which index is created
Below are some questions asked in an interview and need clarification.
1) Func and a Procedure with same in a package goes under overloading?
2) What all grants required to execute procedure with name schema.PROC1 having DML on schema1.tab1
from schema2.?
@Vankata Maddina, Sure, will post these questions as part of upcoming videos. Please stay tuned...Thanks, Siva
You can also manipulate a unique data for a colomn with a unique-function based index.
CREATE TABLE my_table
(
data_col VARCHAR2(100) NOT NULL,
deleted VARCHAR2(1) DEFAULT 'N' NOT NULL
);
ALTER TABLE my_table ADD(
CHECK (deleted IN ('N', 'Y') ENABLE VALIDATE);
CREATE UNIQUE INDEX TEST_UK ON my_table
(CASE "DELETED" WHEN 'N' THEN "data_col" END);
The values in the data_col have to been unique if the deleted-column is "N" if the column is "Y" then the index doesn't see the value for the uniqueness.
Can you please elaborate in detail about index for performance tuning
@Praveen, These index related videos focus the concept of index, and its usage in general from performance tuning aspect.
I will be starting videos specific to performance tuning videos., there i will cover in detail related to tuning aspect. Please stay tune. Thanks,Siva
@@SivaAcademy thank you very much sir, we are eagerly waiting sir
Hi Sir, I am learning a lot from your videos and they are very very helpful. Could you please explain y index will be used though we use sum function in the select and not used when Upper function is used?
Thanks for your comments, sure will address the question in upcoming video, please stay tuned
Hello sir , i have a doubt regarding index, for ex we have a query.
Select *from emp where emp_grade ='A' and class_section ='B';
And we have created index on emp_grade , will index get called if there are more than one where condition?
Yes index will be used, however there are certain rules governing the same
Hello Sir, I have a question, can we create function based indexes using user defined functions?
Yes praveen, we can use the user defined function to create the function based index as far as the function is DETERMINISTIC function. Will explain about DETERMINISTIC function in upcoming video please stay tuned.
Can we create function based index on date column?
@Aishwarya, yes, you can create function based index on date column. eg create index test_idx on emp(trunc(hiredate));
What is the table name where the indexed data is stored ?
User_indexes,user_ind_columns,user_ind_statistics and user_ind_expression table
Need UNix questions