Infrastructure at your Service

Tag

index Archives - Blog dbi services

Franck Pachot

Amazon DynamoDB: a r(el)ational Glossary

By | AWS, Cloud | No Comments

By Franck Pachot . There are many NoSQL databases. And, because SQL is an ISO standard, “No SQL” also means “No Standard”. Many have a similar API and similar objects, but with completely different names. Today, NoSQL databases are used as an additional datastore for some well-defined use cases for which a hashed key-value store fits better than a relational table. And it quickly became “Not Only SQL” as it is complementary to RDBMS databases…

Read More
Franck Pachot

Amazon DynamoDB: the cost of indexes

By | AWS, NoSQL | No Comments

By Franck Pachot . That’s common to any data structure, whether it is RDBMS or NoSQL, indexes are good to accelerate reads but slow the writes. This post explains the consequences of adding indexes in DynamoDB. Secondary Indexes What we call an index in DynamoDB is different from an index in RDBMS. They have the same goal: store your data with some redundancy in order to have it physically partitioned, sorted, and clustered differently than…

Read More

RDBMS (vs. NoSQL) scales the algorithm before the hardware

By | AWS, NoSQL | No Comments

By Franck Pachot . In The myth of NoSQL (vs. RDBMS) “joins dont scale” I explained that joins actually scale very well with an O(logN) on the input tables size, thanks to B*Tree index access, and can even be bounded by hash partitioning with local index, like in DynamoDB single-table design. Jonathan Lewis added a comment that, given the name of the tables (USERS and ORDERS). we should expect an increasing number of rows returned…

Read More
Franck Pachot

Oracle non-linguistic varchar2 columns to order by without sorting

By | Cloud, Oracle | No Comments

By Franck Pachot . Sorting data is an expensive operation and many queries declare an ORDER BY. To avoid the sort operation you can build an index as it maintains a sorted structure. This helps with Top-N queries as you don’t have to read all rows but only those from a range of index entries. However, indexes are sorted by binary values. For NUMBER or DATE datatypes, the internal storage ensures that the order is…

Read More
Franck Pachot

Oracle 12c – pre-built join index

By | Cloud, Database Administration & Monitoring, Oracle | No Comments

By Franck Pachot . This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I have created a few tables in the previous post with a mini-snowflake scheme: a fact table CASES with the covid-19 cases per country and day. And a dimension hierarchy for the country with COUNTRIES and CONTINENTS tables. This title may look strange for people used…

Read More
Franck Pachot

DynamoDB: adding a Global covering index to reduce the cost

By | AWS, Cloud, Database Administration & Monitoring | No Comments

By Franck Pachot . People often think of indexes as a way to optimize row filtering (“get item” faster and cheaper). But indexes are also about columns (“attribute projection”) like some kind of vertical partitioning. In relational (“SQL”) databases we often add more columns to the indexed key. This is called “covering” or “including” indexes, to avoid reading the whole row. The same is true in NoSQL. I’ll show in this post how, even when…

Read More
Morgan Patou

Documentum – Large documents in xPlore indexing

By | Application integration & Middleware, Entreprise content management | No Comments

Documentum is using xPlore for the Full Text indexing/search processes. If you aren’t very familiar with how xPlore is working, you might want to know how it is possible to index large documents or you might be confused about some documents not being indexed (and therefore not searchable). In this blog, I will try to explain how xPlore can be configured to be able to index these big documents without causing too much trouble because…

Read More
Grégory Steulet

Oracle Open World 2018 D1: Top Five MySQL Query Tuning Tips

By | Database Administration & Monitoring, Database management | No Comments

Yesterday (22.10.2018) I participated to the Janis Griffin’s session about “Top Five Query Tuning Tips” at #OOW2018. Janis is Senior DBA /Performance Evangelist for SolarWinds and Ace Director. She is specialized in Performance Tuning. She introduces her session by speaking about Challenges of Tuning. “Tuning takes time.”, “You cannot give enough power if SQL is ineficient”, “You therefore have to monitor wait time”. It sounds basic telling that it doesn’t worth adding CPU or memory when…

Read More