Introduction In this article, we are going to see how a deadlock can occur in a relational database system, and how Oracle, SQL Server, PostgreSQL, or MySQL recover from a deadlock situation. Database locking Relational database systems use various locks to guarantee transaction ACID properties. For instance, no matter what relational database system you are using, locks will always be acquired when modifying (e.g., UPDATE or DELETE) a certain table record. Without locking a row that was modified by a currently running transaction, Atomicity would be compromised. Using locking for controlling access... Read More
10 Older Entries »
Introduction Ever wanted to connect to a relational database using Java and didn’t know which JDBC Driver Maven dependency to use? If so, this article is surely going to help you from now on. Oracle Since September 2019, the Oracle JDBC Driver is available on Maven Central. For JDK 10 and 11, use the following Maven dependency: For JDK 8, use the ojdbc8 artifact instead: For more details about the proper version to use, check out the following Maven Central link. MySQL The MySQL Driver is available on Maven Central, so just... Read More
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there’s really no reason at all why one should be faster than the other. But is the myth justified?
How does COUNT(…) work?
But first, let’s look into some theory. The two ways to count things are not exactly the same thing. Why?
COUNT(*)counts all the tuples in a group
COUNT(<expr>)counts all the tuples in a group for which
<expr>evaluates to something that
IS NOT NULL
This distinction can be quite useful. Most of the time, we’ll
COUNT(*) for convenience, but there are (at
least) two cases where we don’t want that, for example:
When outer joining…[Read more]
Introduction In this article, we are going to see how we can limit the SQL query result set to the Top-N rows only. Limiting the SQL result set is very important when the underlying query could end up fetching a very large number of records, which can have a significant impact on application performance. Why limit the number of rows of a SQL query? Fetching more data than necessary is the number one cause of data access performance issues. When a given business use case is developed, the amount of data available... Read More
The post How to limit the SQL query result set to Top-N rows only appeared first on Vlad Mihalcea.
Introduction In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function. This is a very handy trick, especially when you want to shuffle a given result set. Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets. If you have to shuffle a large result set and limit it afterward, then it’s... Read More
Ready to transition from a commercial database to open source, and want to know which databases are most popular in 2019? Wondering whether an on-premise vs. public cloud vs. hybrid cloud infrastructure is best for your database strategy? Or, considering adding a new database to your application and want to see which combinations are most popular? We found all the answers you need at the Percona Live event last month, and broke down the insights into the following free trends reports:
- Top Databases Used: Open Source vs. Commercial
- Cloud Infrastructure Analysis: Public Cloud vs. On-Premise vs. Hybrid Cloud
- Polyglot Persistence Trends: …
I have been managing multiple databases, mostly in Microsoft SQL Server and MySQL server, both on on-premise and cloud. We have faced a lot of challenging issues such as records deleted from a user table, backup file is corrupted, backup file is not compatible, backup files got deleted, backup storage is full and backup is running for long time, etc. When you are facing this issues for the first time, it is surprising to see new kind of issues every day and if you are not good in documentation, repetitive issues will keep occurring and we keep fixing rather than suctioning. If you are facing same challenges, then you need to focus on your backup and recovery strategy.
A well-designed backup and recovery strategy maximizes data availability and minimizes data loss without tolerating business requirement. In this post, we will discuss about the following topics:
- Recovery Time Objective (RTO) …
Introduction Ever wanted to connect to a relational database using Java and didn’t know the URL connection string? Then, this article is surely going to help you from now on. Oracle The JDBC connection properties look as follows: JDBC Driver oracle.jdbc.OracleDriver JDBC Url jdbc:oracle:thin:@localhost:1521/orclpdb1 Hibernate Dialect org.hibernate.dialect.Oracle12cDialect And, if you want to connect using a … Continue reading JDBC Driver Connection URL strings →
This edition of Log Buffer covers Cloud, Oracle, SQL Server and MySQL and much more.
Introducing managed SSL for Google App Engine
Using Cloud Foundry CUPS to inject Spring Security credentials into a Spring Boot Application
ClusterControl in the Cloud – All Our Resources
Monitoring Amazon Aurora Audit Events with Amazon CloudWatch
Integrating Teradata with Amazon …[Read more]
The SQL language has one great advantage over procedural, object oriented, and “ordinary” functional programming languages. The fact that it is truly declarative (i.e. a 4GL / fourth generation programming language) means that a sophisticated optimiser can easily transform one SQL expression into another, equivalent SQL expression, which might be faster to execute.
How does this work?
Expression Tree Equivalence
Here’s an introduction to equivalent expression trees from my SQL Masterclass: Let’s assume we want to evaluate the following expression:
A + (A - B)
Now in maths, it can be proven trivially that by the laws of associativity, the above expression …[Read more]
10 Older Entries »