About indexes in relational databases for systems and business analysts
The meeting is led by the club's founder, systems analyst Alexander Krotov.
Invited guest Sergey is a PL/SQL developer with over 15 years of experience.
We welcome your questions in the comments or in the club's Telegram chat https://t.me/sys_analyst_chat
00:00 Organizational Part
00:48 Introducing the Guest
01:11 Definition of the Word "Index"
01:53 General Information about Indexes
03:12 Question from the Audience about "Create Index"
03:52 Is it possible to modify a previously created index?
04:22 How a Compound Index Works
06:55 Index Types
07:28 Index Types: "B⁺-Tree" (Example)
07:48 Index Types: "B⁺-Tree" (Search)
11:53 Index Types: "B⁺-Tree" (Appending Data)
13:52 Index Types: "Bitmap Index"
16:39 Index Types: "Reverse Index"
17:52 Index Types: "Function-Based Index"
16:39 Index Types: "Hash Index"
20:42 Index Types: "Compound Index"
23:21 Index Creation Examples
25:18 Chat Question: "Good evening, I have an index on the document date/document number/document type fields. I need to export data based on the document date and document type. Will this index be used when selecting with the date and document type criteria?"
27:40 What data is recommended for indexing
28:15 Primary key indexing
29:23 Foreign key indexing
30:56 Indexing fields frequently used for searching, as well as fields used for sorting and grouping data.
33:19 What data is NOT recommended for indexing
35:28 Who is responsible for choosing, when, and which indexes to create?
39:15 What are the downsides of indexes? What's wrong with them?
41:37 Chat comment: "It seems there needs to be some changes in the CA job market (subjective perception here). In the last six months, major banks and integrators have been looking for architects instead of CAs. Hence the requirement for index knowledge at the level of a database developer (personal experience)."
43:04 Chat comment: "There are also many openings for DWH Analysts. Apparently, it's the same beast when a database analyst-architect is needed."
44:26 When are indexes not enough and it's time to consider abandoning a relational database?
47:09 Can I see the contents of an index?
47:41 Can I control the use of indexes in a query myself, or will the DBMS do it better? The concept of a hint.
49:51 Question from chat: "Am I understanding correctly that indexes are the next stage of database design?"
49:51 Are there indexes on views?
51:06 Do indexes interfere with each other if they are associated with the same table?
51:41 When is an index recalculated?
53:00 Question from chat: "The question is related to the fact that the index task comes from the IT backlog – the business won't bring this. The architect or someone else "at the top" in IT should understand and bring the task to the DB developer (in terminal cases, CA, apparently). Correct me if I'm wrong. The question is related to the fact that at work, we've never had any issues with indexes – we have a DWH developer with his own magic."
53:36 What are the purposes of guids and indexes? In what cases can a guid be used as an index?
55:16 How is data written and rebuilt in shaded databases?
57:19 What are full-text, spatial, clustered, local, inverted, and hash indexes?
01:00:53 Question from chat: "Am I correct in understanding that indexes can actually be generated and stored outside of our main DBMS—for example, when Elasticsearch operates as a search engine, and NOT as a standalone DBMS (like for storing logs). That is, Oracle or PostgreSQL doesn't have indexes, but Elasticsearch does. And it will have its own indexing rules and schedules."
01:02:31 Off-topic. Since the beginning of the year, again subjectively, I've noticed a movement toward import substitution. I haven't delved deeply into this area—I thought there wasn't any particular problem with solutions that aren't subject to sanctions. I'm talking about out-of-the-box DWH solutions.
01:03:58 Do relational databases always have indexes, and when does a systems analyst work with them?
01:07:58 Chat question: "What will be the final document (description) when describing indexes? Is there an example?"