background preloader

DB

Facebook Twitter

The Index You've Added is Useless. Why? Recently, at the office: Bob: I’ve looked into that slow query you’ve told me about yesterday, Alice. I’ve added the indexes you wanted. Everything should be fine nowAlice: Thanks Bob. I’ll quickly check … Nope Bob, still slow, it didn’t seem to workBob: You’re right Alice!

It looks like Oracle isn’t picking up the index, for your query even if I add an /*+INDEX(...)*/ hint. And so, the story continues. True story! Bob Forgot about Oracle and NULL Poor Bob forgot (or didn’t know) that Oracle doesn’t put NULL values in “ordinary” indexes. Now, Bob thinks that his index solves all problems, because he verified if the index worked using the following query: (of course, you generally shouldn’t SELECT *) And the execution plan looked alright: This is because Bob’s predicate doesn’t rely on NULL being part of the I_PERSON_DOB index.

So, essentially, Alice’s query checked if anyone had their date of birth at a given date. Alice can solve this issue with NOT EXISTS It’s easy. Related Whitepaper: A Visual Explanation of SQL Joins. I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right.

We'll populate them with four records each. id name id name -- ---- -- ---- 1 Pirate 1 Rutabaga 2 Monkey 2 Pirate 3 Ninja 3 Darth Vader 4 Spaghetti 4 Ninja Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams. There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram: SELECT * FROM TableA CROSS JOIN TableB This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. NOT NULL Constraints affect Performance in the Oracle Database. To index an IS NULL condition in the Oracle database, the index must have a column that can never be NULL. That said, it is not enough that there are no NULL entries. The database has to be sure there can never be a NULL entry, otherwise the database must assume that the table has rows that are not in the index.

“An indispensable manual for anyone, DBA, developer or system administrator”Luigi Zambetti on Amazon.co.uk (5 stars) The following index supports the query only if the column LAST_NAME has a NOT NULL constraint: DROP INDEX emp_dob; CREATE INDEX emp_dob_name ON employees (date_of_birth, last_name); SELECT * FROM employees WHERE date_of_birth IS NULL Removing the NOT NULL constraint renders the index unusable for this query: ALTER TABLE employees MODIFY last_name NULL; Tweet this tip Tip A missing NOT NULL constraint can prevent index usage in an Oracle database—especially for count(*) queries. The extra condition in the where clause is always true and therefore does not change the result.

Confused by the glut of new databases? Here’s a map for you — Cloud Computing News. Graph Visualization and Neo4j. So far we’ve learned how to get Neo4j up and running with Neography, how to find friends of friends and degrees of separation with the Neo4j REST API and a little bit of the Gremlin and Cypher languages. However, all we’ve seen is text output. We haven’t really “seen” a graph yet, but that’s about to change. Vouched holds a graph of skill specific recommendations people have made to each other and visualizes it. I extracted the visualization, and hosted it on github as neovigator. You can get your very own visualization up and running or take a look at this instance running on Heroku.

Let’s get it up and running and then we’ll go through some pieces of the code. Then visit localhost:9292 to see it running. The website is a Sinatra Application with only two routes. The JSON object we create will have this structure: When we fill this JSON object and pass it to the visualization, this is what we get: We create a route in Sinatra and set it to return JSON. Update! Like this: Like Loading... NOSQL Patterns. Over the last couple years, we see an emerging data storage mechanism for storing large scale of data. These storage solution differs quite significantly with the RDBMS model and is also known as the NOSQL. Some of the key players include ...GoogleBigTable, HBase, HypertableAmazonDynamo, Voldemort, Cassendra, RiakRedisCouchDB, MongoDB These solutions has a number of characteristics in commonKey value storeRun on large number of commodity machinesData are partitioned and replicated among these machinesRelax the data consistency requirement.

(because the CAP theorem proves that you cannot get Consistency, Availability and Partitioning at the the same time)The aim of this blog is to extract the underlying technologies that these solutions have in common, and get a deeper understanding on the implication to your application's design. I am not intending to compare the features of these solutions, nor to suggest which one to use. API model The basic form of API access is Data replication. Data model. In software engineering, the term data model is used in two related senses. In the sense covered by this article, it is a description of the objects represented by a computer system together with their properties and relationships; these are typically "real world" objects such as products, suppliers, customers, and orders.

In the second sense, covered by the article database model, it means a collection of concepts and rules used in defining data models: for example the relational model uses relations and tuples, while the network model uses records, sets, and fields. Overview of data modeling context: Data model is based on Data, Data relationship, Data semantic and Data constraint. A data model provides the details of information to be stored, and is of primary use when the final product is the generation of computer software code for an application or the preparation of a functional specification to aid a computer software make-or-buy decision. Overview[edit] Three perspectives[edit] Graph.