Nov 072014
 

Continuing the nation theme, here’s another six SQL clues to countries. Can you get them all?

select first_person
from   roman_eleven
where  business = 'abbreviated';

select aquatic_limb
from   places;

select *
from   dinner
where  contents in ('beef', 'kidney beans');

select character
from   films
where  actor = 'Tom Green';

select unrepeatable_position
from   flightless_birds
where  allegiance = 'Confederate';

select fortification
from   wine
where  second_person = 'female';

As always, put your answers in the comments!

Oct 192014
 

Same concept, different theme. All the SQL statements below are clues to the English spelling of countries.

Can you get them all?

As always, put your answers in the comments!

select *
from   german_affirmative
join   cooking_vessel;

select *
from   nuts
where  anaphylaxis = 'sexually transmitted';

select *
from   olde_english_the
join   males;

select *
from   elements
where  atomic_number = 47;

select *
from   programming_languages
where  able_to = 'influence PL/SQL';

select *
from   places
where  H2O = 'frozen';
Oct 052014
 

Today celebrates one year of the database design quiz on the PL/SQL Challenge!

Here’s a look back at some of the stats for the quiz to date with some trivia related to the numbers :)

select count(*)
from   quizzes;

COUNT(*)
--------
      52

-- The number of white keys on a piano
-- the smallest number with 4 representations 
-- as the sum of 4 positive squares

select count(*)
from   players;

COUNT(*)
--------
     691

-- The mass (in kilos) of a typical dairy cow
-- ~7 times the average adult body power 
-- consumption (in watts)

select count(*)
from   answers_submitted;

COUNT(*)
--------
   13723

-- Approximately the length of the Great Wall 
-- of China (in miles) and the mass of 
-- a large dinosaur (in stones)

select name, score
from   (select name, score
        from   players
        order  by score desc
)
where  rownum <= 5;

NAME             SCORE
---------------- -----
mentzel.iudith   20618
Justin Cave      20038
Joaquin Gonzalez 19284
Krzysztof Helbin 19242
Elic             19127

-- Iudith and Justin are doing a great job, 
-- the only players to break 20,000 to date!

select avg(rating) 
from   player_surveys;

AVG(RATING)
-----------
       4.13

-- 50 times the half life of uranium 241 
-- (in hours)

-- Great to see the average rating above 4 :)
-- I have to thank my reviewers, particularly 
-- Yuan Tschang, for helping keep the quality 
-- high!

Thanks to everyone who’s taken part, pointed out my mistakes and added to the discussions for the quiz over the past year.

If you’ve not played yet, today’s a great time to get started. Just head over to www.plsqlchallenge.com, register for a free account and get playing!

Jun 032014
 

I insert a row into a table, roll it back, yet somehow I’ve created a new object in my database!

select count(*) from user_objects;

  COUNT(*)
----------
         2

insert into a_table
values ('that create', 1, 'new object');

rollback;

select count(*) from user_objects;

  COUNT(*)
----------
         3

There’s no triggers or other “trickery” involved – the code above is a copy-paste in a database where I have the only session.

How did this happen? Put your answers in the comments!

If you’d like to get more posts on using and designing Oracle databases, enter your details the form below to receive future updates.

Mar 242014
 

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 );
  
end;
/

There’s a serious flaw with this approach however – can you spot it?

Answers in the comments!

Mar 212014
 

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  duration between date '2000-01-01'
                and date '2012-12-31';

Finally, there’s something that links all the above films. Can you get it?

Put your answers in the comments!

Mar 142014
 

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
-----------
THE_PK     

insert into dodgy_data ( the_pk )
values ( 1 );

SQL Error: ORA-00001: 
  unique constraint (CHRIS.TAB_PK) violated

How? Answers in the comments!

Mar 032014
 

The optimizer does particularly badly with the query below. It estimates the query will return 5,101 rows when in fact nothing matches the predicate:

select *
from   this_query_returns_nothing
where  there_are_no_values < 0;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1995374266

----------------------------------------------------------------
| Id  | Operation         | Name                       | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |  5101 |
|*  1 |  TABLE ACCESS FULL| THIS_QUERY_RETURNS_NOTHING |  5101 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("THERE_ARE_NO_VALUES"<0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
        304  bytes sent via SQL*Net to client
        365  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Creating an index doesn’t help us because there’s only a few distinct values in the THERE_ARE_NO_VALUES column:

select count (distinct there_are_no_values) 
from   this_query_returns_nothing;

         C
----------
         2

create index no_better on 
this_query_returns_nothing ( there_are_no_values );

Index created.

select *
from   this_query_returns_nothing
where  there_are_no_values < 0;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1995374266

----------------------------------------------------------------
| Id  | Operation         | Name                       | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |  5101 |
|*  1 |  TABLE ACCESS FULL| THIS_QUERY_RETURNS_NOTHING |  5101 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("THERE_ARE_NO_VALUES"<0)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
        304  bytes sent via SQL*Net to client
        365  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

We can see that the lowest value in the table is 0:

select min(there_are_no_values)
from   this_query_returns_nothing;

MIN(THERE_ARE_NO_VALUES)
------------------------
                       0

More importantly, we know that values less than 0 won’t be recorded in the table.

How can we get the optimizer to figure this out? Answers in the comments!

(note: consider the stats up-to-date. I didn’t do anything to “trick” the optimizer by creating fake/non-representative stats).

Feb 242014
 

I dropped a materialized view (without the preserve table option), but the object still exists! Here’s a copy and paste from my session:

drop materialized view gone_for_good;

materialized view GONE_FOR_GOOD dropped.

select or_is_it
from   gone_for_good;

OR_IS_IT
--------
Nope!

-- covering off the easy option, 
-- there isn't a (public) synonym
-- with the same name:
select *
from   all_synonyms
where  synonym_name = 'GONE_FOR_GOOD';

no rows selected

How did this happen? Answers in the comments!

Feb 212014
 

Back on the film quizzes this time with a mathematical twist for the clues:

select count(*) 
from   days_of_the_week;

select * 
from   cerebrum
where  power(e, i * pi) + 1 = 0;

select * 
from   direction_finder
where  length = (1 + sqrt(5)) / 2;

select * 
from   shape
where  height = width
and    width = depth;

select *
from   frankensteins_possesions
where  (x * x) + (y * y) + (z * z) = (r * r);

select *
from   unknown_people
where  E = m * c * c;

Can you guess all six? Put your answers in the comments!