Indexes in Oracle :Index Scan Methods :Part 2

[@sireeshaas7680]

I was asked about invisible index today in an interview. I was able to answer it confidently ☺️ Thanks much for making videos in an understandable way👍🙏

[@ashishmeher-tb8ji]

very detailed explanation

[@TechCoach]

I am glad I could help

[@prasadzagde8210]

Superb.. explained in a very neat manner.. Thanks for sharing knowledge.

[@gauravsinghal4948]

Very informatic session

[@TechCoach]

Thanks Gaurav for the kind words :) Please consider subscribing to my other channel as well. It will really help me. kzbin.info/www/bejne/bHiUqXqfYqlopdk

[@reverend900]

BEST VID ABOUT SCANNING METHODS.. Thanks

[@TechCoach]

reverend900 Thanks a lot for the wonderful comment, I have a small request please help me build a new channel by subscribing kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@reverend900]

+Tech Coach Done, I already watsaap to my group/learners. This vid has really high quality content. You can also upload on udemy.com

[@TechCoach]

Thanks a lot buddy for the help, I will surely think about it :)

[@TheSomnath2000]

Thanks Vivek for sharing the knowledge with us . These videos are awesome . Could you please create videos on the below mentioned topics . . 1. Oracle Wait events - Different types of wait events . 2. OEM -- How to use all the important functions of OEM . 3. AWR report - How to analyse an AWR report . 4. RMAN Backup 5. Explain Plan -- How to analyse an explain plan of a complex query It will be really helpful .. Thanks in advance .

[@mohitbhandari1106]

Please create viedo on multiset operators and how it uses as performance improvement

[@atulgupta9301]

Beautifully explained.

[@TechCoach]

Thanks Atul :)

[@sagarbodala9590]

awesome.. thanks for sharing

[@TechCoach]

Thanks a lot for the kind words:),Please help me in my new Initiative by subscribing sharing and watching my new youtube channel "An Indian Abroad" kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@sachin1234566]

Good explanation... You have a deep knowledge keep it up bro. Thanks

[@TechCoach]

Thanks a lot for the kind words :)

[@CrazyFunCricket]

Awesome Man!!!! you made learning easy. Please make some real live performance problem videos. till now no one has posted good perf vidoes.

[@ankushgondane8642]

Very much informative video It tells how deep understanding you are having. Thank you👍

[@TechCoach]

Ankush Gondane Thanks a lot Ankush for the kind words :)

[@JagadishVarodiya]

Great Thanks for explaing this in detail

[@TechCoach]

Thanks a lot for the kind words

[@PrateekMehtaABDFAN]

Thanks a lot for taking time to make such practical videos..

[@TechCoach]

Thanks a lot for the kind words Prateek, I have created 6 other videos on indexing that may interest you :)

[@smitaa2106]

You are like a google..full of knowledge..

[@TechCoach]

Thanks Smita for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@MK-kn9dl]

THANKS A LOT, IT WAS SO USEFULL FOR ME😍

[@TechCoach]

I am glad I could help

[@dollyphilip20]

Thank you Vivek. Very good explanation. You're the best.

[@TechCoach]

Thanks a lot for the kind words, they made my morning

[@sudhakar1dw]

Thanks for providing such a very informative content!!

[@TechCoach]

sudhakar1dw Thanks a lot for the kind words.I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@amitkumar-ev8im]

Really nice video,Once Again.Please upload more video related to PerformanceTuning.

[@Aditya6573]

This is a great series. Excellent information with precision! Thank you so much. One small doubt. Is the order of columns in composite index still relevant? I had heard that now the order in index doesnt matter... If its still relevant then my bad maybe its about order of indexed columns in predicate.

[@raghavmahajan3129]

very clear and precise explanation

[@TechCoach]

Thanks Raghav Mahajan for the kind words :)

[@prashantmhatre9225]

Awesome video!! Waiting for the next tutorial desperately :)

[@TechCoach]

Thanks Prashant, I will upload the next one soon.

[@TechCoach]

Please help me in my new Initiative by subscribing sharing and watching my new youtube channel "An Indian Abroad" kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@sagarjadhav299]

It's really helpful sir

[@TechCoach]

Thanks a lot for the kind words sagar, I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@sathurmukil6308]

Good explanation bro..your efforts are much appreciated!!

[@TechCoach]

Thanks Sathur for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@Aubd417]

Thanks a lot for your valuable contents, in the video @2.11 do you mean to create index on foreign key columns? Could you please clarify

[@TechCoach]

Yes,You should create indexes on columns that you going to use often in joins that includes the foreign key columns,I hope this clarifies :)

[@dineshattluri6254]

for index fast full scan, you have mentioned that there should not be any order by clause. but if we use any other indexed columns from a composite index except 1stcolumn, it is a fast full index, right? then why it will be a prerequisite of not having order by clause?

[@GauravKumar-ec8vd]

I have been studying index from many days but couldn't understand better. Thanks you so much sir for explaining in depth but in simple way. May I know which topics I must go through to better my basics?

[@AnIndianAbroadd]

Learn Indexing, Partitioning, PL/SQL ,procedures ,packages function, analytical functions,triggers, views and similar stuff you will do good..All the best :)

[@GauravKumar-ec8vd]

@@AnIndianAbroadd thank you so much

[@AnIndianAbroadd]

@@GauravKumar-ec8vd I have a small request, please watch and subscribe to my channel " An Indian Abroad"

[@grvmavi]

Thanks Brother

[@neerajshukla6907]

AMAZING EXPLANATION BRO

[@TechCoach]

Thanks Neeraj for the kind words, I have a small request. I am working on this new KZbin channel.I would really appreciate if you watch and subscribe to it. Your support is my motivation. kzbin.info/www/bejne/ZqSvhmuPlr6latk

[@anuragkumargupta7506]

IN WHICH CATAGOERY I SHOULD PLACED PRIMARY INDEXING ,SECONDARY INDEXING CLIUSTER INDEXING . ?

[@tapasgaan7371]

N what is the difference b/w multiblock read and sequential read...I mean in sequential read in index , there is multiblock read as well...please clarify my confusion

[@TechCoach]

Index usually have sequential read which is the slowest because it traverse the index in a particular order. Only in case of Index Fast full scan multiblock read is performed because the query doesn't care in which order the data is to be retrived. Also all the columns that are part of the query should be part of the index as well.

[@tapasgaan7371]

does multiblock read in index fast full scan means it will directly read bulk of data from all leaf node

[@TechCoach]

That's right Tapas. I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@ravikhanna29]

I have one question. index range scan does sequential read or multiblock read?

[@TechCoach]

It will use sequential read ( Single block read). I am working on this initiative, a new youtube channel named "an Indian Abroad" I would really appreciate if you watch, subscribe and share the video with your friends. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@pramod7191]

Good Explanation Sir.. can you please upload vedio of function based index.

[@TechCoach]

Thanks pramod for the kind words. Here is the video on functional index. kzbin.info/www/bejne/pXrOgaSeiK6WqqM

[@anuragkumargupta7506]

I CATAGORIES THE INDEX AS FOLLOW :-  INDEXING IS DEFINED BASED ON ITS INDEXING ATTRIBUTES. INDEXING CAN BE OF THE FOLLOWING TYPES - 1.PRIMARY INDEXING 2.SECONDARY INDEXING. 3.CLUSTER INDEXING.  BASICALLY INDICES ARE OF TWO TYPES 1. ORDERED INDICES. i. DENSE INDEXING ii. SPARSE INDEXING B+TRESS( BALANCED TREE INDEX) BIT MAP 2. HASH INDICES.  INDICES ARE AGAIN DEVIDED ON THE BASICS OF NO. OF ATTRIBUTE 1. SINGLE ATTRIBUTE KEY INDICES I. B+ TREE II. CLUSTER AND NON CLUSTER OR PRIMARY OR SECONDARY INDICES 2. MULTIPLE ATTRIBUTE KEY INDICES. I. BITMAP  INDICES ARE DIVIDED ON THE BASICS OF LEVEL 1. SINGLE LEVEL INDICES 2. MULTI LEVEL INDICES B+ TREE BIT MAP INDEXING.  INDEX ARE CATEGRRIES ON THE BASIS OF STRUCTURE 1. B TRESS INDEX 2. BIT MAP INDEX.

[@CtrlAltTravel]

Thanks TechCoach for the explanation. I wanted to know how Oracle internally executes the following query : select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal

[@TechCoach]

Hi Priyanka, sure I will take a look, Can you please tell me what exactly you are trying to achieve? May be there is a more efficient way to write the query? I have a small request, I am working on this new youtube channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@vinod901]

Priyanka Joshi, this link might help you, www.complexsql.com/query-to-find-second-highest-salary-of-employee/

[@vinod901]

Priyanka Joshi, this link might help you, www.complexsql.com/query-to-find-second-highest-salary-of-employee/

[@blazejason5551]

Sorry to be off topic but does anyone know a way to get back into an instagram account..? I somehow lost my login password. I would appreciate any assistance you can give me.

[@creeddarian108]

@Blaze Jason instablaster ;)

[@mohitbhandari1106]

Thanks Vivek it's really helpful :)

[@TechCoach]

Mohit Bhandari you are welcome :)

[@mohitbhandari1106]

can you reply to my question also which i asked on Local and Global index :)

[@TechCoach]

Mohit Bhandari sure I will do that, I will recommend watching the entire partitioning series first that will help you understand the concept better. As a. part of this Indexing series I will explain partitioned index again. I will try to explain with more examples

[@mohitbhandari1106]

Thanks :) Vivek for the quick response.. i will watch :)

[@TechCoach]

Please help me in my new Initiative by subscribing sharing and watching my new youtube channel "An Indian Abroad" kzbin.info/door/oLdaMxhfDgdgIAcRGlUvkg

[@mohamedhameed3867]

Hello @Tech Coach, The following query gives me an INDEX SKIP SCAN instead of TABLE ACCESS FULL. EXPLAIN PLAN FOR SELECT * FROM INDEX_DEMO WHERE OBJECT_NAME = 'DUAL'; My index looks like this - alter table index_demo add constraint PK_INDEX_DEMO primary key (OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID); OBJECT_NAME is in the second position but still i get an INDEX SKIP SCAN instead of TABLE ACCESS FULL. Could you kindly explain me why this is happening? Thanks in Advance

[@tusharshreshth]

Mohamed, You are absolutely correct. Oracle document URL which clearly specifies, leading column of a composite index is not specified in the query predicate then optimizer considers a skip scan. In this video scenario he might not gathered the statistics for the table due to which full table scan is happened. docs.oracle.com/database/121/TGSQL/tgsql_optop.htm#GUID-B7C62F0F-EB7C-422C-919D-D86456A74A60

[@amitkumar-ev8im]

Do you have any tutorial video for Oracle Hints.If yes Please share Link.

[@AnIndianAbroadd]

Not yet amit let me work on it soon, I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/Z2KnfGClrrekopI

[@shoumadipdas]

in your indexing tutorial 2,u have mentioned that adding a index will eliminate the chance of child table lock in case we delete/update/merge from parent table in a primary key - foreign key relationship... My question is what exactly will happen from index perspective after adding index in child table ...??

[@TechCoach]

Shoumadip, when we update delete a row based on the index it will have to lock only the impacted row( row level locking) while if the index is not present it will have to lock the entire table for updates/deletes ( table level locking). You should once check out Bitmap indexes and why they shouldn't be used in a system that can have parallel updates. That will clarify your doubts. I am sorry for not replying earlier, things were a little hectic this week.

[@shoumadipdas]

@@TechCoach i watched your bimap index video...i understood the concept...a big thanks to you...but i have below two doubts - 1) while creating bitmap join index on transaction & customers table exactly behind the scene how the index content will be like rowid--values...please provide a diagram to understand... 2) why adding a index in child table will prevent the locking on child table??please provide one real code example Regards, Shoumadip

[@shoumadipdas]

Hi tech coach...also please explain a bit more on bitmap join index please...

[@TechCoach]

Shoumadip, I will recommend reading a blog from asktom on the below topics they are excellent. Unfortunately I might take sometime to create content on your questions as I am working on some other videos right now

[@tapasgaan7371]

Hi Vivek, really nice explanation...but I have one doubt why in order by 1st column in composite index index full scan and why in order by other column in composite index index fast full scan will be used...I mean what is the exact difference b/w index full scan and index fast full scan . .? Thank u

[@TechCoach]

When doing Index fast full scan the query doesn't care in which order the data is stored.

[@balasahebangde112]

Excellent explanation with hands on !! Only suggestion would be speed should be bit low as sometimes getting difficulties to understand.

[@TechCoach]

Sure buddy I will work on it :)

[@tapasgaan7371]

One more doubt, in unique index even if not null constraint is not there , oracle should use unique index scan instead of full table scan since only one null value can exist in the indexed column...I hope u understand what I mean

[@TechCoach]

The assumption is incorrect, A unique index can have many null values. Create table Index_verify (A int, B Int) Create unique index A_Index_verify on Index_verify (A) Insert into index_verify Values (Null,2) Insert into index_verify Values (Null,2) Insert into index_verify Values (Null,2)

[@tapasgaan7371]

Thanks for clearing the confusion

[@tapasgaan7371]

One more doubt : why there should be no ordering clause in index fast full scan

[@tapasgaan7371]

I think order by asc will hold good for index fast full scan but not desc clause

[@deviprasad130]

I don't find part-1 of "Indexes in Oracle :Index Scan Methods :Part 1"...Could you please provide the link..

[@TechCoach]

Here you go buddy. Happy Learning :) Indexes in Oracle: kzbin.info/aero/PLUWdEQb_2yqVXnx-P1881VVtec8moW1sk

[@aniketvinchurkar295]

please create one video on B-tree vs bit - map like this one please thanks you :)

[@TechCoach]

Sure Aniket, I will work on it. I have a small request I am working on this new channel and will really appreciate if you watch and subscribe to it. kzbin.info/www/bejne/f4GVh6Fsn7Brfsk

[@bmushtaqahmed]

Good one, but I'm still confused about the index fast scan.

[@TechCoach]

Thanks for the kind words please let me know about your doubt. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. kzbin.info/www/bejne/fnO0eZihgpaIaMU

[@anuragkumargupta7506]

I M VERY CONFUSED ........😆😆😆😆😆