Over the past few articles we’ve looked at database joins. It started out with me noticing that joins appear to be getting bad press recently and wondering whether they really are the root of all evil that some people seem to think they are.
So far in the joins series we’ve looked at the effect removing joins (via denormalization) has on performance. We’ve seen that joins can cause primary key looks to do more work. Lowering the normalization level to remove these can negatively impact “search” style queries though. More importantly, we’ve seen the real cost of denormalizing to remove joins is when updating records, potentially leading to concurrency waits and application bugs.
So are joins always “good”?
In the previous article in the joins series we compared query performance between a third normal form schema and the same schema denormalized to second normal form. We then extended it the example so our denormalized schema was in just first normal form.
The normalized approach performed better overall. The differences were small though – generally just a few consistent gets and all the queries executed in under a second. As Jeff Atwood points out (more...)
Continuing the series on joins, I’m going to look at denormalization. This process reduces the number of joins necessary to return results for a schema.
One of the big arguments against normalizing data is “for performance”. The process of normalization creates new tables as relations are decomposed according to their functional dependencies. This means (more) joins are necessary to return the same results.
Joins appear (to me) to be getting a lot of bad press recently. In discussions I’ve had and articles I’ve read many give the position that joins are somehow inherently bad and to be avoided at all costs.
I’ve never been entirely convinced by the “joins are bad” arguments however. Partly because there’s few concrete cases actually demonstrating the (additional) cost of joins. Instead discussions tend to be hand-wavy arguments around extra CPU cycles used (more...)
The following update forms the basis of a DIY connection pool. It is intended to allocate an unused session to whoever requests it:
declare sess connection_pool.session_name%type; begin update connection_pool set in_use = 'Y', last_used = systimestamp where rowid in ( select rowid from ( select rowid from connection_pool where in_use = 'N' order by last_used ) where rownum = 1 ) returning session_name into sess; dbms_output.put_line ( sess ); (more...)
Back on the films today, here’s another six to get:
select talk from monarch; select * from (select city_name from cities where state = 'IL' order by population desc) where rownum = 1; select * from nations where not exists ( select null from people where status = 'retired' and sex = 'male' and p.location = n.location ); sqlplus -s young_ovis_aries select key from injury; select * from human_property where (more...)
create table from_12c_onwards ( you_can_have_your_pk number generated always as identity, so_you_dont_have_to_type_it varchar2(10) ); insert into from_12c_onwards ( so_you_dont_have_to_type_it ) values ( 'yes' ); insert into from_12c_onwards ( so_you_dont_have_to_type_it ) values ( 'Yes' ); insert into from_12c_onwards ( so_you_dont_have_to_type_it ) values ( 'YES!' ); select * from from_12c_onwards; YOU_CAN_HAVE_YOUR_PK SO_YOU_DON -------------------- ---------- 1 yes 2 Yes 3 YES!
If you’d like more details, there’s a nice article on oracle-base discussing how (more...)
Somehow I ended up with two rows in my table both with the same primary key value:
select the_pk from dodgy_data; THE_PK ---------- 1 1
The primary key does exist and THE_PK is the only column in it, so we can’t insert another row with THE_PK = 1:
select constraint_name, constraint_type from user_constraints where table_name = 'DODGY_DATA'; CONSTRAINT_NAME CONSTRAINT_TYPE -------------------- --------------- TAB_PK P select column_name from user_cons_columns where constraint_name = 'THE_PK'; COLUMN_NAME ----------- (more...)
alter table from_11g add column_with_a varchar2(100) default 'is instant provided you set it' not null;
For a quick and dirty demo, keep reading
create table from_11g as select rownum x from dual connect by level <= 1000000; table FROM_11G created. Elapsed: 00:00:01.610 alter table from_11g add column_with_a varchar2(100) default 'is instant provided you set it' not null; table FROM_11G altered. Elapsed: 00:00:00.078 alter table from_11g set unused column column_with_a; table FROM_11G altered. Elapsed: 00:00:00.078 alter table from_11g add column_with_a varchar2(100) default 'is instant provided you set it' null; table FROM_11G altered. Elapsed: 00:01:37.313 drop table from_11g purge; table FROM_11G dropped. Elapsed: 00:00:00.453