Tag Archives: Databases

Data Modeling at different levels

When you are designing database for an application, there can be 3 core levels at which you can design your database.

1. Conceptual Level: At this level you are only aware of high level entities and their relationships. For example you know that you have “Employee” Entity who “works for” a “Department” and “has” an “Address”. You are not worried about details.

2. Logical Level: You try to add as much details as possible, without worrying about how it will actually be converted to a physical database structure. So will provide any attributes for “Employee” i.e. Id, FirstName, LastName, AddressId, Salary and define primary and foreign key relations.

3. Physical Level: This is the actual representation of your database design with exact column names, types etc.

database

More info- http://www.1keydata.com/datawarehousing/data-modeling-levels.html

Shared Nothing vs Shared Everything

In database cluster implementation we can have multiple ways to make sure how different nodes will communicate with each other.

Shared nothing approach: None of the nodes will use others memory or storage. This is best suited for the solutions where inter node communication is not required, i.e. a node can come up with a solution on its own.

Shared Memory: In this approach memory is shared, i.e. each node/ processor is working with same memory. This is used when we need nodes to share solutions/ calculations done by other nodes and are available in memory.

Shared Everything: In this approach nodes share memory plus storage. This makes sense when nodes are working on problem where calculations and data created/ used by node is dependent on others.

Further Reads:
https://www.quora.com/What-are-the-differences-between-shared-nothing-shared-memory-and-shared-storage-architectures-in-the-context-of-scalable-computing-analytics

https://en.wikipedia.org/wiki/Shared_nothing_architecture

Kill blocking session in Oracle

To check which all session are live on an oracle server

select * from v$session

To check blocking sessions

SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
FROM
V$LOCKED_OBJECT L,
DBA_OBJECTS O,
V$SESSION S,
V$PROCESS P,
V$SQL SQ
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

To kill a session

alter system kill session ‘SID,SERIAL#’;

Returning auto generated id in spring – mybatis

Faced a problem with returning the autogenerated id for new rows being created for a postgres table through spring – mybatis.

@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

One solution ofcourse was to simply get nextval from the sequence, but I wanted something cleaner where I need not be worried about multithreading issues.

Solution one Tried: One good solution found for postgres was using “Returning” keyword with Insert statement

insert into user (name) values (#{name}) Returning id;

Somehow that did not work with mybatis.

Solution that worked:

@Options(useGeneratedKeys=true, keyProperty = "userId", keyColumn="id")
@Insert("insert into user (name) values (#{name})")
public void insertActor(User user);

@Options, with useGeneratedKeys flag worked just fine. keyProperty defined the property name in Java and keyColumn name defined name of column in table.

Postgres error: Relation Does not exist

Faced an interesting error while accessing postgres table with a simple java code.

"select * from User"

A little googling revealed that if you are using multicase table name (U is caps), you will need to give the table name in quotes.

http://stackoverflow.com/questions/695289/cannot-simply-use-postgresql-table-name-relation-does-not-exist

A better fix would be to always use lower case characters when declaring tables in postgres.

Forgot password for Oracle accounts

I have a oracle installation on my windows machine but that was done a few months back and I could not recollect passwords for default sys and sysadmin records. The following trick helped reset passwords for these accounts

1. Go to Command prompt and type sqlplus /nolog. It should open SQL/> prompt.
2. On SQL/> prompt, type connect /as dba
Now you are logged in as dba, changing password is simple matter of
SQL> alter user sys identified by “newpassword”

Union vs Union All

The simple difference between using Union and Union All is that Union selects only distinct records from both the resultsets, whereas Union All will return everything.

But today I figured out one more use of union All today. In a ms sql query, I was using union to add up resultset of multiple queries, and got this error

“The text data type cannot be selected as DISTINCT because it is not comparable.”

As I knew none of my queries will return duplicate records, I did not need distinct clause. Replacing Union with “Union All” did the trick in this case.

Shared lock- Effecting Select query’s performance

A shared log in SQL query is applied by default by SELECT statements. The difference between Shared and exclusive locks is that exclusive lock will stop any update on the data being locked, whereas shared lock suggests the select query to wait if some update or insert operation is in progress for the row.

Shared lock will not stop any other request from selecting or updating the records.

For example- select * from employee;

The above statement will fetch employee data from employee table, but if at the same time some insert or update query is executing, it will impact the performance of select query as it has to wait for other operation (on the row/ page it is trying to read) to finish.

Workaround: If we are sure that other operations executing at the same time are not going to impact my select query, i.e. I am ok to read a few old records/ dirty read, Sql provides us keyword NOLOCK

select * from employee with (NOLOCK);

This will make sure our select query does not get impacted by insert and updates running at the same time, though we might have problem of dirty read.

Workaround 2: With NOLOCK we know we can have a dirty read problem, to avoid that, we can use READPAST. This will make the select query skip any rows being updated at the query time and hence avoids dirty reads, So we are achieving our goal of not waiting for insert/ update operations to be over plus. On the downside, the resultset will miss some of the records.

select * from employee with (READPAST);

Why rownum will nor work for greater or equal to checks

Someone asked a question on a forum that why rownum=2 does not work for his query, so i though of sharing some details here.

Using rownum is a tricky affair. Safest bet is to use it only when you want to limit the number of results to be shown. For example rownum<2 or rownum<=5.

Why rownum=2 or rownum>2 will not work?

Read here – http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

In summary, this is how oracle execute a query

  1. The FROM/WHERE clause goes first.
  2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
  3. SELECT is applied.
  4. GROUP BY is applied.
  5. HAVING is applied.
  6. ORDER BY is applied.

rownum<=2 clause will get converted to

ROWNUM = 1
for x in
( select * from emp )
loop
    exit when NOT(ROWNUM <= 2)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

if you change exit when NOT(ROWNUM <= 2) with rownnum=2, you can see it will fail in the first run itself

So if I cannot use rownum, what can I use. Try using row_number() http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

It works something like

SELECT last_name FROM
   (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
   WHERE R BETWEEN 51 and 100;