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!
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.
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
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.
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.
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.
@@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?
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)
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.
