How to Create Database Indexes: Databases for Developers: Performance #4

[@sebon11]

Woooooow, man! This was SO CLEAR explanation, nice! I'm impressed with the quality of the vid with everything - sound hq, switching between bricks, code and drawings where needed, highlighting the stuff and everything. Very good video!

[@TheMagicofSQL]

Thanks, glad you found this useful :)

[@SubhashPophale]

This won't be exaggeration if I say you read a whole lot pages and watch the this 13 min video. Excellent and lucid explaination of Indexes in oracle database.

[@TheMagicofSQL]

Thanks, glad you found this useful!

[@Sam-Land]

I wish I had watched this years ago to explain the ordering. I'm going to be breaching this video

[@eduardodelcorrallira529]

I was about to thumb it up and realized it already was. Plenty of Aha! Moments Very clear, and fun too :)

[@TheMagicofSQL]

Great to hear :)

[@amirthalakshmi7348]

Thank you very much... It's really really very very useful.,.. This series deserves more views..

[@TheMagicofSQL]

You're welcome, please share these with anyone you think they can help :)

[@hectorcortes92]

Amazing!! Thanks!

[@jayhaygh]

Brilliant! This deserves more views

[@TheMagicofSQL]

Thanks, glad you enjoyed this!

[@Nick71NA]

Wonderful explanation. Only a observation, complexity for the use of index in B-tree is O(log(N)) where the logarithm base is b.

[@TheMagicofSQL]

Thanks. Is your observation related something specific in the video?

[@Nick71NA]

@@TheMagicofSQL Only a clarification, in the video you explain the complexity for the access at B-Tree. Thanks a lot for your teachments

[@PramodPattar-gl4df]

Hi Chris, Amazing videos with simple and clear way of explanation.👍 I Do have a doubt, if we have a table where 90-95% of a column data values are unique. can we still create a primary key or unique index on that column. how can we use that column values efficiently for data access?

[@TheMagicofSQL]

The values in a PK/Unique index must be unique. If there are any duplicates you can't create a validated constraint. As long as your queries get "few" rows from the table a regular (non-unique) index works fine

[@PramodPattar-gl4df]

@@TheMagicofSQL Thank you Chris for quick reply. 🙂 How can I get in touch with you further. I have some queries using views which turns out to be a nightmare seeing their execution plans and performance is bad. Need your expertise guidance a bit.

[@TheMagicofSQL]

@@PramodPattar-gl4df You can submit questions at asktom.oracle.com; please ensure you include the execution plans & details of what you've tried

[@NuProg]

I have to create index for a huge table (In Gigabytes) for a new column. But when we tried this, it is bringing down the system completely so we had to drop that. Is there any way we can create this index without downtime.

[@TheMagicofSQL]

I'm unsure what "bringing down the system completely" means exactly. In Oracle Database you can CREATE INDEX ... ONLINE which allows the database to write to the table while creating the index which may help here.

[@NuProg]

@@TheMagicofSQL Thank you for the quick reply. 'Bringing down' - I was meaning that the CPU and memory were full due to the size of the table and was taking hours to complete. So is there a way to create index in a performant way on this table?

[@TheMagicofSQL]

While a table in the gigabytes is big, it still shouldn't take hours to create an index on it. Creating an index will only use 1 cpu by default. So either the system only has a couple of cpus (in which case you should look at getting more) or it's running in parallel - reducing the parallelism will help. I would still start by using the ONLINE keyword when creating the index before trying anything else though. It might take a long time, but other operations should continue normally (unless your system does have to few CPUs for the workload)

[@NuProg]

@@TheMagicofSQL Thanks again. I shall try this and will update here on how it goes.

[@kevingergely2462]

This was awesome man thank you very much

[@TheMagicofSQL]

You're welcome, glad you enjoyed this

[@premjithtube]

How many index can create against a table?? Instead of creating index(color,shape), wat abt creating two separate indexs with these columns and use in the query?

[@TheMagicofSQL]

You can create an index for each unique combination of columns and index properties. You can create a separate index on each column and the optimizer can scan each to find which rows to access. In general this is less efficient than one multi-column index though. If there's little/no correlation between colour and shape and each have four distinct values, finding all the rows for a (colour, shape) combo will get ~1/16 of the rows. With separate indexes, the database has read a quarter of the rows from each index. Then combine these to get down to the ~1/16 rows in the table. Whereas with the multicolumn index the database can just search the index to find these rows.

[@premjithtube]

@@TheMagicofSQL thanku for ur reply sir. If i hav a txn table wit colmns txndate,branch,currency,country,bank. If i mak index wit txndate&branch, txndate&currency, txndate&country, txndate&bank den if i hav a query with where condition wit txndate, branch, bank. Wat wil be the impact?

[@TheMagicofSQL]

It depends which the optimizer thinks is the best choice - it all comes down to the specifics of your data. Watch the other videos in this performance series to see what affects these decisions.

[@crtt5k45]

You really made my day :-)

[@TheMagicofSQL]

You're welcome!

[@LillyCode]

BRILLIANT thank you

[@TheMagicofSQL]

You're welcome, glad this helped :)

[@LillyCode]

@@TheMagicofSQL It did, thank you for the entertianment and education.

[@LillyCode]

Or other way around.... education and entertainment!.. The fact that you explained it in a fun way, made it really fun!

[@akadiryigit]

Hi Chris, Thanks for sharing the video, It is easy to understand and I love it. :) Could you please answer my questions when you have time ? I have a question about differences between Partition and Index. When should we use Index or Partition ? Why should choose Partition instead of Index. Thanks,

[@TheMagicofSQL]

This is a big topic, but broadly speaking: - You use an index to find a few rows - You partition a table to make massive tables easier to manage For more details on partitioning, see this guide from my colleague Connor asktom.oracle.com/partitioning-for-developers.htm

[@HarrietOmboga]

I like how its explained though I dont get most of it

[@TheMagicofSQL]

What is it you're struggling to understand?

[@sebon11]

P.s. you're from UK or USA? Or even Australia maybe?