How to Create Database Indexes: Databases for Developers: Performance #4
ฝัง
- เผยแพร่เมื่อ 13 มิ.ย. 2024
- Indexes are one of the most common performance tools available.
But how do you create one? And how does it help the database find your data faster?
Watch this video to learn how to create an index and know if the optimizer used it.
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement. - วิทยาศาสตร์และเทคโนโลยี
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!
Thanks, glad you found this useful :)
I wish I had watched this years ago to explain the ordering. I'm going to be breaching this video
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.
Thanks, glad you found this useful!
I was about to thumb it up and realized it already was. Plenty of Aha! Moments
Very clear, and fun too :)
Great to hear :)
Thank you very much... It's really really very very useful.,.. This series deserves more views..
You're welcome, please share these with anyone you think they can help :)
Amazing!! Thanks!
Brilliant! This deserves more views
Thanks, glad you enjoyed this!
This was awesome man thank you very much
You're welcome, glad you enjoyed this
You really made my day :-)
You're welcome!
BRILLIANT thank you
You're welcome, glad this helped :)
@@TheMagicofSQL It did, thank you for the entertianment and education.
Or other way around.... education and entertainment!.. The fact that you explained it in a fun way, made it really fun!
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,
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
Wonderful explanation. Only a observation, complexity for the use of index in B-tree is O(log(N)) where the logarithm base is b.
Thanks. Is your observation related something specific in the video?
@@TheMagicofSQL Only a clarification, in the video you explain the complexity for the access at B-Tree. Thanks a lot for your teachments
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?
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
@@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.
@@PramodPattar-gl4df You can submit questions at asktom.oracle.com; please ensure you include the execution plans & details of what you've tried
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?
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.
@@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¤cy, txndate&country, txndate&bank den if i hav a query with where condition wit txndate, branch, bank. Wat wil be the impact?
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.
P.s. you're from UK or USA? Or even Australia maybe?