Update returning old values, possible solution

Warning:  While this appears to work, I do not know if the behavior is defined.

It seems that using a scalar subquery in the select list of an updatable view allows us to return the old value of a column.  I’ll update this post with more details when I have time to do some additional testing.

•  Correctness
  • Can I get it to return incorrectly?
  • What happens when an update restarts?
• Performance
  • How does this compare to pre-selecting?
  • Is it possible to have latch issues, even though using dual?
• ?

Here is a sample script you can use to start your own tests.

drop table t1;

create table t1 as select n n1 ,n n2
from (select dbms_random.random() n from
(select * from dual connect by level <= 1000)
, (select * from dual connect by level <= 1000)
);

declare
type n3 is record (n1 number, n2 number, n3 number);
type n3_tab is table of n3;
rets n3_tab;

n1_cnt number := 0;
n2_cnt number := 0;
other_cnt number := 0;
minus_1_cnt number := 0;
begin
update (select n1, n2, (select n2 from dual) n3 from t1)
set n2 = n2 + 1 — where rownum <= 10
returning n1, n2, n3 bulk collect into rets;

for i in rets.first .. rets.last loop
declare
r n3 := rets(i);
begin
<<inc_cnts>> case
when r.n3 = r.n1 then
n1_cnt := n1_cnt + 1;
when r.n3 = r.n2 then
n2_cnt := n2_cnt + 1;
else
other_cnt := other_cnt + 1;
end case inc_cnts;
if r.n3 = r.n2-1 then
minus_1_cnt := minus_1_cnt + 1;
end if;
end;
end loop;
dbms_output.put_line(‘returned correctly cnt: ‘ || minus_1_cnt);
dbms_output.put_line(‘==n1: ‘ || n1_cnt);
dbms_output.put_line(‘==n2: ‘ || n2_cnt);
dbms_output.put_line(‘else: ‘ || other_cnt);
end;
/

 

Quiz: index including a constant

I was surprised when i tried to create an index like this and it actually worked:

Create index t1idx on t1(n1,1);

The question is: Why would you want to do this?

There is at least one valid use case.

Trying to learn FRP 4 (stepped macros)

The game in the last post worked, but the macros jumped to their target instead of following each step.  If we upgrade to Reactive-Banana 0.5, we can use the spill function to get an event that fires with the values of an array in sequence.

Thanks to Heinrich Apfelmus for clarifying that this usage is valid.

The rules of the game:

The user starts at the point (1,0)
Moving onto a point on the x=y line loses
moving onto the point (6,4) wins
The commands UP,DOWN,LEFT,RIGHT move the user
The command DEF <name> <string>…  defines macros
The command UNDEF String removes a macro
macros may refer to other macros

I moved the relevant FRP parts to the begining, but I still need the imports first

> import Reactive.Banana
> import Control.Monad
> import Data.Monoid
> import qualified Data.Map as Map
> import Prelude hiding (Left,Right)
> import Data.IORef   — I wish I could get rid of this

> networkDescription :: (IORef GameCondition) -> NetworkDescription t (Event t String) -> NetworkDescription t ()
> networkDescription condRef lineEnd = do
>    lineE <- lineEnd

The filterJust function fits nicely with our parse functions to create events that only fire on a successful parse

>    let defE = filterJust $ parseDef . words <$> lineE
>        undefE = filterJust $ parseUndef . words <$> lineE

The current dictionary is just the accumulation of the updates from the successful parses

>        dictB = accumB initialDict $ defE `union` undefE

We need both a behavior for the players position and an event that fires when we update the behavior.
A combinator for this is easy to build on top of the mapAccum combinator.

>        accumEB :: a -> Event t (a->a) -> (Event t a, Behavior t a)
>        accumEB seed e = mapAccum seed (fmap (\f -> pairAp f) e)
>           where pairAp f a = (f a, f a)

Here is the updated functionality to step through the macros.
We need to fire an event containing the list of steps in response to an entered line event.
Then, we spill that event to fire a sequence of events each containing a step.

>        stepsE = getMove <$> dictB <@> lineE
>        stepsEs = spill stepsE

We can determine the position with the sequence just like we did with the old aggregated event

>        (positionE, positionB) = accumEB (Sum 1, Sum 0) $ fmap mappend stepsEs

now we can fire events when the player reaches a position

>        loseE = filterE (uncurry (==)) positionE
>        winE = filterE (== (Sum 6, Sum 4)) positionE
>        quitE = filterE (== “quit”) lineE

Last, we react to some of the events.

>        exitWith retval = reactimate . fmap (const $ writeIORef condRef retval)
>
>    reactimate $ fmap print positionE
>    exitWith Lost loseE
>    exitWith Won winE
>    exitWith Quit quitE

Once again this was very easy. Here is a sample output to verify the change:

*Main> main
def n Up
(Sum {getSum = 1},Sum {getSum = 0})
def e Right
(Sum {getSum = 1},Sum {getSum = 0})
def wl e e e e e n n n n n n n n
(Sum {getSum = 1},Sum {getSum = 0})
wl
(Sum {getSum = 2},Sum {getSum = 0})
(Sum {getSum = 3},Sum {getSum = 0})
(Sum {getSum = 4},Sum {getSum = 0})
(Sum {getSum = 5},Sum {getSum = 0})
(Sum {getSum = 6},Sum {getSum = 0})
(Sum {getSum = 6},Sum {getSum = 1})
(Sum {getSum = 6},Sum {getSum = 2})
(Sum {getSum = 6},Sum {getSum = 3})
(Sum {getSum = 6},Sum {getSum = 4})
(Sum {getSum = 6},Sum {getSum = 5})
(Sum {getSum = 6},Sum {getSum = 6})
(Sum {getSum = 6},Sum {getSum = 7})
(Sum {getSum = 6},Sum {getSum = 8})
You Won

Notice that output continued after I won. I’ll fix that in the next post.

I’m not sure why I did not get a lost message when I hit (6,6); lazyness?

> eventLoop condRef fire = loop
>    where
>    loop = do
>       s <- getLine
>       fire s
>       cond <- readIORef condRef
>       when (cond == Playing) loop

> main = do
>   gameCondRef <- newIORef Playing
>   (addHandler,fire) <- newAddHandler
>   compile (networkDescription gameCondRef $ fromAddHandler addHandler ) >>= actuate
>   eventLoop gameCondRef fire
>   cond <- readIORef gameCondRef
>   putStrLn $ “You ” ++ show cond

Lets pull some types out of that desciption

> type XY = (Sum Int,Sum Int)

> data GameCondition = Won | Lost | Quit | Playing
>    deriving (Show, Eq)

> data Direction = Up | Down | Left | Right | Stay
>    deriving (Show, Enum)

> data Macro = Macro Direction
>            | Refs [String]
>    deriving Show

> type Dict = Map.Map String Macro

and the obvious functions

> xy :: Direction -> XY
> xy Up = (Sum 0,Sum 1); xy Down = (Sum 0,Sum (-1)); xy Left = (Sum (-1),Sum 0); xy Right = (Sum 1,Sum 0); xy Stay = (Sum 0, Sum 0)

> gameCond :: XY -> GameCondition
> gameCond p@(x,y) | x == y     = Lost
>                  | p == (Sum 6,Sum 4) = Won
>                  | otherwise  = Playing

> getMove :: Dict -> String -> [XY]
> getMove d k = case Map.lookup k d of
>                 Nothing -> [xy Stay]
>                 Just (Macro m) -> [xy m]
>                 Just (Refs rs) -> concatMap (getMove d) $ rs

> parseDef :: [String] -> Maybe (Dict -> Dict)
> parseDef (“def”:name:rs) = Just $ Map.insert name (Refs rs)
> parseDef _ = Nothing

> parseUndef :: [String] -> Maybe (Dict -> Dict)
> parseUndef ["undef",name] = Just $ Map.delete name
> parseUndef _ = Nothing

We’ll need to start with a dictionary containing the basic movement commands

> initialDict = Map.fromList . map namer $ [Up .. Right]
>   where namer x = (show x, Macro x)

Trying to learn FRP 3 (a simple “game”)

In this post I’ll implement a simple “game” where the user must move on a grid from the origin to a given position.

Let’s get the boilerplate out of the way

> import Reactive.Banana
> import Control.Monad
> import Data.Monoid
> import qualified Data.Map as Map
> import Prelude hiding (Left,Right)
> import Data.IORef   — I wish I could get rid of this

> eventLoop condRef fire = loop
>    where
>    loop = do
>       s <- getLine
>       fire s
>       cond <- readIORef condRef
>       when (cond == Playing) loop

> main = do
>   gameCondRef <- newIORef Playing
>   (addHandler,fire) <- newAddHandler
>   compile (networkDescription gameCondRef $ fromAddHandler addHandler ) >>= actuate
>   eventLoop gameCondRef fire
>   cond <- readIORef gameCondRef
>   putStrLn $ “You ” ++ show cond

The user starts at the point (1,0)
Moving onto a point on the x=y line loses
moving onto the point (6,4) wins
The commands UP,DOWN,LEFT,RIGHT move the user
The command DEF <name> <string>…  defines macros
The command UNDEF String removes a macro
macros may refer to other macros

Lets pull some types out of that desciption

> type XY = (Sum Int,Sum Int)

> data GameCondition = Won | Lost | Quit | Playing
>    deriving (Show, Eq)

> data Direction = Up | Down | Left | Right | Stay
>    deriving (Show, Enum)

> data Macro = Macro Direction
>            | Refs [String]
>    deriving Show

> type Dict = Map.Map String Macro

and the obvious functions

> xy :: Direction -> XY
> xy Up = (Sum 0,Sum 1); xy Down = (Sum 0,Sum (-1)); xy Left = (Sum (-1),Sum 0); xy Right = (Sum 1,Sum 0); xy Stay = (Sum 0, Sum 0)

> gameCond :: XY -> GameCondition
> gameCond p@(x,y) | x == y     = Lost
>                  | p == (Sum 6,Sum 4) = Won
>                  | otherwise  = Playing

> getMove :: Dict -> String -> XY
> getMove d k = case Map.lookup k d of
>                 Nothing -> xy Stay
>                 Just (Macro m) -> xy m
>                 Just (Refs rs) -> mconcat . map (getMove d) $ rs

> parseDef :: [String] -> Maybe (Dict -> Dict)
> parseDef (“def”:name:rs) = Just $ Map.insert name (Refs rs)
> parseDef _ = Nothing

> parseUndef :: [String] -> Maybe (Dict -> Dict)
> parseUndef ["undef",name] = Just $ Map.delete name
> parseUndef _ = Nothing

We’ll need to start with a dictionary containing the basic movement commands

> initialDict = Map.fromList . map namer $ [Up .. Right]
>   where namer x = (show x, Macro x)

Finally, we are at the FRP part.

> networkDescription :: (IORef GameCondition) -> NetworkDescription (Event String) -> NetworkDescription ()
> networkDescription condRef lineEnd = do
>    lineE <- lineEnd

The filterJust function fits nicely with our parse functions to create events that only fire on a successful parse

>    let defE = filterJust $ parseDef . words <$> lineE
>        undefE = filterJust $ parseUndef . words <$> lineE

The current dictionary is just the accumulation of the updates from the successful parses

>        dictB = accumB initialDict $ defE `union` undefE

We need both a behavior for the players position and an event that fires when we update the behavior.
A combinator for this is easy to build on top of the mapAccum combinator.

>        accumEB :: a -> Event (a->a) -> (Event a, Behavior a)
>        accumEB seed e = mapAccum seed (fmap (\f -> pairAp f) e)
>           where pairAp f a = (f a, f a)
>        (positionE, positionB) = accumEB (Sum 1, Sum 0) (fmap mappend $ getMove <$> dictB <@> lineE)

now we can fire events when the player reaches a position

>        loseE = filterE (uncurry (==)) positionE
>        winE = filterE (== (Sum 6, Sum 4)) positionE
>        quitE = filterE (== “quit”) lineE

Last, we react to some of the events.

>        exitWith retval = reactimate . fmap (const $ writeIORef condRef retval)
>
>    reactimate $ fmap print positionE
>    exitWith Lost loseE
>    exitWith Won winE
>    exitWith Quit quitE

That was easier than I thought it would be. I would like to find a better way to communicate back to the event loop than IORefs.
Other than that, I like this code.

Trying to figure out FRP 2 (Behaviors)

In the last post I created a simple echo program using just events.  In this post I introduce behaviors which are time varying values.

Let’s get the boilerplate out of the way

> import Reactive.Banana
> import Control.Monad
> import Data.Monoid

> eventLoop fire = loop
>    where
>    loop = do
>       s <- getLine
>       fire s
>       when (s /= “quit”) loop

> main = do
>   (addHandler,fire) <- newAddHandler
>   compile (networkDescription $ fromAddHandler addHandler ) >>= actuate
>   eventLoop fire

> networkDescription lineEnd = do
>   let run p f = lineEnd >>= \a -> reactimate $ fmap f (filterE p a)
>   run (/=”quit”) $ putStrLn . (“You typed: “++)
>   run (==”quit”) $ const (putStrLn “Goodbye”)

Let’s make a series of behaviours, starting with a behaviour that is constantly 1:

>   let oneB :: Behavior Integer
>       oneB = pure 1

Displaying a behaviors value took a little thinking to figure out.  Behaviors are continous functions of time, so there is no function to transform one to an event.
What we can do is apply a function in a behavior to the value in an event.

>   let bToE :: Behavior a -> Event b -> Event a
>       bToE b = apply  (const <$> b)
>       oneE = bToE oneB

Then we can output the transformed event.

>   let outpB e b f = e >>= \a -> reactimate $ fmap f (bToE b a)
>   outpB lineEnd oneB $ putStrLn . (“oneB: ” ++) . show

We can create a behavior that changes values to an event’s value when it fires with the stepper function.

>   lineE <- lineEnd
>   let lineB =  stepper “” lineE
>   outpB lineEnd lineB $ putStrLn . (“lineB: ” ++) . show

Events form a monoid that we can use to create a behavior that changes value with either of two events.

>   let ynB = stepper “N” $ filterE (==”Y”) lineE
>                 `mappend` filterE (==”N”) lineE
>   outpB lineEnd ynB $ putStrLn . (“ynB: ” ++) . show

We can also create behaviors that fold a function valued event. This is good for accumulating updates.
For example, an increment event can be used to get a count of commands given

>   let incE = fmap (const (+1)) lineE
>       countB = accumB 0 incE

>   outpB lineEnd countB $ putStrLn . (“countB: ” ++) . show

We are not limitted to single updates

>   let undoE = fmap (const (subtract 2)) $ filterE (==”undo”) lineE
>       realCountB = accumB 0 $ incE `mappend` undoE

>   outpB lineEnd realCountB $ putStrLn . (“realCountB: ” ++) . show

This last example shows the promise of FRP. We can specify a complex behavior that involves multiple events declaratively and in one place.

Trying to figure out FRP

This is my attempt at learning FRP and sharing as I go.

I’ll be using Reactive-Banana as it looks simple and seems to have the most activity.

> import Reactive.Banana
> import Control.Monad

To keep things simple I will forgo a gui library. A console application is not the usual target for FRP programs, but it allows me to focus on the logic instead of the framework.
So, we need an event loop for the console that will allow us to fire events.

> eventLoop fire = loop
>    where
>    loop = do
>       s <- getLine
>       fire s
>       when (s /= “quit”) loop

A non FRP implementation of our program is very simple; simply make the fire function generate an IO action:

> echoNonFRP = eventLoop $ putStrLn . (“You typed: “++)

In this trivial example, it makes sense to handle the input right here. FRP is definitely overkill, but I will implement an FRP solution to show how it allows us to seperate the firing of an event from response to the event.

> echoFRP = do

first we use the newAddHandler action to generate a pair of a handler and a firing function. These are linked together somehow so that the handler can be used to recognize events that are created by the firing function

>   (addHandler,fire) <- newAddHandler

we can use the addhandler to build a network description. We’ll flesh out the details of the description later.  For now, we just compile it into a nework and then actuate the network to turn it into an IO action.

>   compile (fromAddHandler addHandler >>= networkDescription’) >>= actuate

The type of fire (a -> IO ()) is exactly what we need to pass into our eventLoop.

>   eventLoop fire

Now we just need to fill in our network description. The reactimate function is used to emit the value of an event when it occurs. We need an IO action instead of a String, so we map a function (a -> IO) over the event.

> networkDescription’ lineE = reactimate $ fmap (putStrLn . (“You typed: “++)) lineE

and that is it. This functions identically to the non FRP version.

here it is without interuption and special handling for the quit command:

> networkDescription lineE = do
>   let run p f = lineE >>= \a -> reactimate $ fmap f (filterE p a)
>   run (/=”quit”) $ putStrLn . (“You typed: “++)
>   run (==”quit”) $ const (putStrLn “Goodbye”)

> echo = do
>   (addHandler,fire) <- newAddHandler
>   compile (networkDescription $ fromAddHandler addHandler ) >>= actuate
>   eventLoop fire

An example of circular programming

Something clicked the other day and I was able to get past the mental block I had with circular programming.

I was trying to optimize counting the transpositions between a sting and a permutation of that string. Earlier code left me at a nice starting point,where the pair of strings was represented as [[(Int,Int)]] with the first Int being the position of a character in the first string and the second being the position of the same character int the permutation. For example:

abcd_abcd = [ [(1,1)]
            , [(2,2)]
            , [(3,3)]
            , [(4,4)]
            ] :: [[(Int,Int)]]

abcd_acbd = [ [(1,1)]
            , [(2,3)]
            , [(3,2)]
            , [(4,4)]
            ] :: [[(Int,Int)]]

dcbaabcd_dbcaacbd = [ [(4,4),(5,5)]
                    , [(3,2),(6,7)]
                    , [(2,3),(7,6)]
                    , [(1,1),(8,8)]
                    ] :: [[(Int,Int)]]

One way to solve this is to concatenate the lists, sort by the first elements of the pairs, and then fold with a function that counts the changes in direction of the second elements of the pairs.

foldl' f (GT,0,0) . map snd . sort . concat
   where f (dir, cnt,n) n' = (dir',  if dir = dir' then cnt else cnt +1, n')
         dir' = compare n' n

This works, but I wanted a one pass solution. I also didn't need a full sort, just the ability to access the next and previous match. I got stuck for quite a while before deciding to start with a two pass solution. First I have a hashmap defined recursively in terms of itself. I did not realize it at the time, but this is actually very similar to the fold above. The base case of the first element corresponds loosely to the initializer and the recursive case corresponds to carrying of "dir" and "n" in the accumulating function

h :: [[(Int,Int)]] -> HM.HashMap Int (Ordering, Ordering, Int)
h ms =  foldl (foldr f) HM.empty ms
   where h' = h ms
         f (key, val) = case key of 
                          1 -> HM.insert 1 (GT, GT, val)
                          n -> let (old_dir, old_val) = find (n-1) in HM.insert n (old_dir, compare val old_val, val)
         find n = let (Just (dir, _, val)) = HM.lookup n h' in (dir, val)

Now I just need a second pass for the counting:

trans :: HM.HashMap Int (Ordering, Ordering, Int) -> Int
trans = HM.fold f 0
  where f (old_dir, dir, _) c = if dir == old_dir then c else c+1
                                 

This seems really easy in retrospect, but writing it involved a lot of false starts and felt like going down the rabbit hole. Once I got done celebrating, moving on to a one pass solution was easy. I already knew I could have a structure that would have the preceding element available by the time I needed it. I just need one that also has the next element available by the time I need it.

h2 :: [[(Int,Int)]] -> HM.HashMap Int (Ordering, Ordering, Int, Int)
h2 ms = foldl (foldr f ) HM.empty ms
   where h2' = h2 ms
         f (key, val) = case key of 
                          1 ->  HM.insert key (GT, GT, val, 0)
                          n -> let (old_dir, old_val) = prev n 
                                   new_c = next n
                                   dir = compare val old_val
                                   in HM.insert n (old_dir, dir, val, if old_dir == dir then new_c else new_c+1)
         prev n = let (Just (dir, _, val, _)) = HM.lookup (n-1) h2' in (dir, val)
         next n = case HM.lookup (n+1) h2' of
                       Nothing -> 0
                       Just (_, _, _, c) -> c 

trans2 ms = let (Just (_, _, _, c)) = HM.lookup 2 (h2 ms) in c

The correspondence to my original fold has broken down. The base case is still the same, but now it looks like my recursive case is recursing in both directions at once. So why do I need to go both ways? If I accumulated upwards, I would not know where to look for the answer. Accumulating downwards lets me always look at element 2. Similarly, I can't calculate the direction changes downwards because I wouldn't know where to start.

My takeaways from this exercise are:

1. Just pretend like you already have the structure you need
2. don't get caught up in how magical it seems.
3. don't overthink it. Assume it will work and do it

crazy rewrites

Jonathan Lewis’s posts on index joins inspired me to run a few test cases of my own. I thought it was pretty neat that the optimizer could split a single table query into a 6 way self join. It really blew my mind when I found it could do the reverse. I also found it quite cute that it does the opposite of what I tell it too. That’s just how stuff works for me.

This was run on a fresh install of the developer vm in VirtualBox:

Edit – I really need to learn to preview. Here is the corrected script and output
set echo on
set linesize 200

drop table t1;

create table t1 (n1,n2,n3,n4,n5,n6,filler)
nocache
as
select trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
trunc(rownum/100),
lpad(rownum, 1000, '0')
from dual connect by level <= 100000;

create bitmap index t1_n1 on t1(n1);
create bitmap index t1_n2 on t1(n2);
create bitmap index t1_n3 on t1(n3);
create bitmap index t1_n4 on t1(n4);
create bitmap index t1_n5 on t1(n5);
create bitmap index t1_n6 on t1(n6);

call dbms_stats.gather_table_stats('SCOTT','T1');

set timing on;

set autotrace traceonly;

set arraysize 5000;

select n1,n2,n3,n4,n5,n6 from t1;

select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);


---


SQL> select n1,n2,n3,n4,n5,n6 from t1;

100000 rows selected.

Elapsed: 00:00:01.65

Execution Plan
———————————————————-
Plan hash value: 362797529

——————————————————————————————————
| Id  | Operation                         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————————
|   0 | SELECT STATEMENT                  |                  |   100K|  2343K|  2018   (1)| 00:00:25 |
|   1 |  VIEW                             | index$_join$_001 |   100K|  2343K|  2018   (1)| 00:00:25 |
|*  2 |   HASH JOIN                       |                  |       |       |            |          |
|*  3 |    HASH JOIN                      |                  |       |       |            |          |
|*  4 |     HASH JOIN                     |                  |       |       |            |          |
|*  5 |      HASH JOIN                    |                  |       |       |            |          |
|*  6 |       HASH JOIN                   |                  |       |       |            |          |
|   7 |        BITMAP CONVERSION TO ROWIDS|                  |   100K|  2343K|    11   (0)| 00:00:01 |
|   8 |         BITMAP INDEX FULL SCAN    | T1_N1            |       |       |            |          |
|   9 |        BITMAP CONVERSION TO ROWIDS|                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  10 |         BITMAP INDEX FULL SCAN    | T1_N2            |       |       |            |          |
|  11 |       BITMAP CONVERSION TO ROWIDS |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  12 |        BITMAP INDEX FULL SCAN     | T1_N3            |       |       |            |          |
|  13 |      BITMAP CONVERSION TO ROWIDS  |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  14 |       BITMAP INDEX FULL SCAN      | T1_N4            |       |       |            |          |
|  15 |     BITMAP CONVERSION TO ROWIDS   |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  16 |      BITMAP INDEX FULL SCAN       | T1_N5            |       |       |            |          |
|  17 |    BITMAP CONVERSION TO ROWIDS    |                  |   100K|  2343K|    11   (0)| 00:00:01 |
|  18 |     BITMAP INDEX FULL SCAN        | T1_N6            |       |       |            |          |
——————————————————————————————————

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

2 – access(ROWID=ROWID)
3 – access(ROWID=ROWID)
4 – access(ROWID=ROWID)
5 – access(ROWID=ROWID)
6 – access(ROWID=ROWID)

Statistics
———————————————————-
1  recursive calls
0  db block gets
86  consistent gets
0  physical reads
0  redo size
526501  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

SQL>
SQL> select a.n1, b.n2, c.n3, d.n4, e.n5, f.n6
2  from t1 a, t1 b, t1 c, t1 d, t1 e, t1 f
3  where a.rowid = all(b.rowid, c.rowid, d.rowid, e.rowid, f.rowid);

100000 rows selected.

Elapsed: 00:00:07.78

Execution Plan
———————————————————-
Plan hash value: 3617692013

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |   100K|  3515K|  3919   (1)| 00:00:48 |
|   1 |  TABLE ACCESS FULL| T1   |   100K|  3515K|  3919   (1)| 00:00:48 |
————————————————————————–

Statistics
———————————————————-
1  recursive calls
0  db block gets
14307  consistent gets
14286  physical reads
0  redo size
103737103  bytes sent via SQL*Net to client
628  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

Thanks Computer Junkyard

Just a quick thank you to Computer Junkyard I had a great experience there today.

If you are in or around Tampa, please check them out.

Understanding Monads I

It turns out that understanding monads is even harder than finding the time to write about monads. I don’t want to waste this opportunity, so I’m just going to make it up as I go along.

For me to really have a motivation to understand something, I need to see a need for it. So just what good are monads anyways? The first thing I thought of was you can view monads as models to evaluate functions, that are  defined with the monadic operators, under. Admittedly this is stupidly simple example but I’m at the stupidly simple stage of learning:

> let failOn0 n = n >>= \x -> if x == 0 then fail “zero” else return x

This seems tailor made to evaluate under the Maybe monad which models possible failure:

> failOn0 (Just 1)
Just 1
> failOn0 (Just 0)
Nothing

You could also evaluate under the List monad which models non-deterministic choice. Basically, you get back all possible results:

> failOn0 [1,2,3]
[1,2,3]
> failOn0 [0,2,3]
[2,3]
> failOn0 [0,0,0]
[]

Finally you could evaluate it under the Identity monad which models variable substitution.  This one is a little different because it can really fail:

> failOn0 (Id 1)
Id 1

>failOn0 (Id 0)

*** Exception: zero

So I can write one function and evaluate it several different ways.  That’s enough of a reason for me to dig in deeper.  Now I’d suggest reading Monads as containers for good explanation of writing simple monads. Then I would write Identity Monad and the Tree Monad on paper. I would follow that up by writing the reductions for several examples of fmap, join, and >>= on paper.  And yes, I really do mean on paper.

I just finish the paper exercizes today and I think I have a good handle it. I think I can almost tackle part II where I will get into more of the usefulness of monads, conditioning myself to what >>= really means, and the state monad.