Redo - Part 1

Oracle Redo is oftentimes misunderstood, but most developers and DBAs assume it to mean that in the case of instance failure the database can 'look' here in an attempt to 'redo' operations in a recovery situation.

And they are essentially right. The redo logs are a log of all the changes made to the data. The logs also contain undo information so the data can be 'unwound' to the starting position. What they do not contain is data changes for which we have asked that there be no logging i.e. tables created with NOLOGGING or truncate operations.

But how do we measure our REDO? You can query v$my_stat, see how much redo you generated so far, update or insert a couple of rows, requery and subtract. Nice and simple.

Remember doing row-by-row operations with commits generates a lot more redo than a one-hit SQL statement before commit.

The amount of redo generated for each operation for the same SQL statement varies from release to release.

Converting Between Character Sets

If you have extended characters with accents you can convert them consistently and safely as follows:

SELECT CONVERT ('ediária', 'US7ASCII', 'WE8ISO8859P1') AS converted 
  FROM DUAL;

ediaria

Consistent and already available for you to use.

Number to Words Conversion

This is something useful only as a trick or a quick hack.

How do you get from a number to the same number spelled out in words.

Here is something that works perfectly well but was not intended to be used in the way this example shows. It is a mistreatment of the to_char function based on a manipulating a date for output.

SELECT n, TO_CHAR (DATE '-4712-01-01' + (n - 1), 'jspth')
  FROM (SELECT 1721058 n
          FROM DUAL);

one million seven hundred twenty-one thousand fifty-eighth

So it does work - just don't rely on it - it is after all a to_char on a date field.

Learning Oracle

I have seen so many questions posted on discussion boards all with the same subject - How do I Learn Oracle?

This is an unfortunate misunderstanding. Oracle is more than one product to learn. Everyone using Oracle, myself included, will know SQL, possibly something about the responsibilities of a DBA and something about operating systems at a minimum. There is no "one" thing to learn, no course where you start at the beginning and come out the other end "knowing" Oracle.

What tends to happen, is that you start out learning some SQL and PL/SQL. This should be the most fundamental part of starting a career in using Oracle. Then you either expand your skill set out into another area, DBA for example, or you concentrate on enhancing your knowledge. Unlike most careers, there is no clear path that once started down means you have burned your bridges and cannot back up and try something else.

After a few years most Oracle professionals have settled into a niche that suits them, some as developers, some as administrators, some as hybrids, some doing Forms/Reports etc.... The field of possible skills is so vast and increasing all the time that it is normal to specialise in one area. All areas of knowledge have one core commonality - SQL.

Learn it well.

Take half an hour a week to look up a function in the documentation and try it out. Look through the provided packages - there may be something there you could use. I guess my message is - There is only one way to learn Oracle start with SQL and keep learning until the day you leave.

Quick Text Count

Ever wondered how a search engine gives you such a quick answer: 5000 pages match your search term....

Here's how to do it in Oracle using Oracle Text looking for the words my and search.

declare
lcount number;
begin
 lcount := ctx_query.count_hits(index_name => 'IDX_IM_SEARCHIDX',
                                text_query => 'my and search',                        
                                exact => false);
dbms_output.put_line('Number of matching docs '||lcount);
end;

The call to count_hits can be adjusted for accuracy. (exact => true) takes longer to complete but is accurate while (exact => false) gives a best guess, usually very close anyway.

Note no column was queried. The index was hit directly and assuming there is no thesaurus or fuzzy matching taking place results are extremely fast.

REGEXP_REPLACE

Get rid of extra spaces.

WITH t AS (SELECT 'NO MORE  EXTRA      SPACES' str FROM dual)
SELECT REGEXP_REPLACE(str, ' {2,}', ' ') str
  FROM t;

STR                 
--------------------
NO MORE EXTRA SPACES


1 row selected.

This is actually a very neat example of solving a simple problem using regular expressions. Of course regular expressions are so much more powerful, indeed books have been written about them, but that is beyond the scope of this simple example.

Longops

Just how long do I have to wait for my long running SQL to complete?

SELECT *
  FROM v$session_longops
 WHERE sofar < totalwork;

Shows you what long running operations are in progress.

You can tie this back to your - or another sessions SQL.

CUBE

I just found this script in my collection

SELECT *
  FROM (SELECT   c1, c2, c3, c4, c5, c6
          FROM (SELECT 'a' c1, 'b' c2, 'c' c3, 'd' c4, 'e' c5, 'f' c6
                  FROM DUAL)
         GROUP BY CUBE (c1, c2, c3, c4, c5, c6));


C1 C2 C3 C4 C5 C6
               
               f
            e   
            e  f
         d      
         d     f
         d  e   
         d  e  f
      c         
      c        f
      c     e   
      c     e  f
      c  d      
      c  d     f
      c  d  e   
      c  d  e  f
   b            
   b           f
   b        e   
   b        e  f
   b     d     
   b     d     f
   b     d  e  
   b     d  e  f
   b  c         
   b  c        f
   b  c     e   
   b  c     e  f
   b  c  d      
   b  c  d     f
   b  c  d  e   
   b  c  d  e  f
a               
a              f
a           e   
a           e  f
a        d     
a        d     f
a        d  e  
a        d  e  f
a     c        
a     c        f
a     c     e  
a     c     e  f
a     c  d     
a     c  d     f
a     c  d  e   
a     c  d  e  f
a  b            
a  b           f
a  b        e   
a  b        e  f
a  b     d      
a  b     d     f
a  b     d  e   
a  b     d  e  f
a  b  c         
a  b  c        f
a  b  c     e   
a  b  c     e  f
a  b  c  d      
a  b  c  d     f
a  b  c  d  e   
a  b  c  d  e  f

and it illustrates the beauty (and complexity) of CUBE.

A very simple SQL example that shows off what is possible from something very simple.

NVL

Returning data is the function of a SELECT statement, and it does it very well. But what do we do with that data where we don't know what it is - the dreaded null.

It is not valid to say that a null value returned from a select statement is incorrect. It is entirely possible that for the row of data returned that particular piece of data is unknown, or was unknown at data entry time.

We do however, have a problem when it comes to performing calculations with nulls. A null value in a column will not affect a sum or avg calculation though they treat it differently.

Running the following example:

create table t (x number);

insert into t values (1);
insert into t values (1);
insert into t values (1);
insert into t values (null);
insert into t values (1);

select sum(x) , avg(x) , count(x) from t;

gives.....

    SUM(X)     AVG(X)   COUNT(X)
---------- ---------- ----------
         4          1          4


1 row selected.

The average and count seem to be wrong. Actually - the way they are written they 'ignore' rows with null values. Sum on the other hand seems to assume that null means zero. Correct me if I am wrong.

We can affect the results by telling the SUM and AVG functions to 'fill-in' a value if there is a null by using NVL.

SELECT SUM (NVL (x, 0)), AVG (NVL (x, 0)), 
       COUNT (NVL (x, 0)), COUNT (*)
  FROM t;

SUM(NVL(X,0)) AVG(NVL(X,0)) COUNT(NVL(X,0))   COUNT(*)
------------- ------------- --------------- ----------
            4            .8               5          5


1 row selected.

And all because of a null..........

Number to Words

A neat trick to convert a number to the same number in words ( 12 becomes twelve ).

select n, to_char(date '-4712-01-01' + (n-1),'jspth')
  from (select 1721058 n from dual);

Try it out and play with the NLS LANG settings for different results.

Bind Variables

Part of tuning any application developed means making your code more secure and friendly.

This should mean you use bind variables. This should make your code more secure and resilient and potentially better performance wise.

At an application level it is preferable to have one hard parse of a SQL statement and many soft parses of the same, and this reduces contention and CPU load. Bind variables also have an added benefit in eliminating the effects of SQL Injection. SQL Injection will be discussed in a later posting.

The first time a query is run it is hard parsed. It is checked for syntax, semantics, validity and finally a plan is generated, a very time consuming job. The next time this SQL statement is encountered a soft parse ensues, a more simple 3 stage execution:

1. syntax check
2. semantics check
3. go to shared pool and look if the SQL is there

Executing a SQL statement by concatenating the variables to the SQL string results in hard parses for each value of the variable. Lots of serialisation, contention and less scalability. Rewriting code to use bind variables is not time consuming or hard. A PL/SQL block of code might contain the following:

....
execute immediate 'select * from customer 
                    where first_name = :first_name' 
  using l_fname 
   into lr_customer;
....

Upon encountering this statement for the first time a hard parse is performed and all subsequent times a soft parse for all values of l_fname.

This will give us our goal of having a high soft to hard parse ratio. There is so much more to bind variables that I'm sure I'll be revisiting this subject again and again.

Thanks for the inspiration for this subject Tom.

When is a Null Null

Since the dawn of the computer age 'NULL' has confused and misled developers and designers alike. What makes NULL deserve so much attention and frustration in Oracle.

Let's look at what NULL is for a start.

Null is an unknown value. It is not no value it is indeterminate.

So by that definition we can never know the value of something that is null. It is not less than or greater than a value. In fact a NULL cannot be compared to another NULL for equality or otherwise. One unknown value is not equal to another unknown value neither are they not equal.

This introduces the IS NULL operator and allows a demonstration to sum up what has been said above in a more Oracle centric way.

SELECT 'NULL = NULL' AS "result"
  FROM DUAL
 WHERE NULL = NULL
 UNION ALL
SELECT 'NULL <> NULL' AS "result"
  FROM DUAL
 WHERE NULL <> NULL
 UNION ALL
SELECT 'NULL <= 0' AS "result"
  FROM DUAL
 WHERE NULL <= 0
 UNION ALL
SELECT 'NULL >= 0' AS "result"
  FROM DUAL
 WHERE NULL >= 0
 UNION ALL
SELECT 'NULL IS NULL' AS "result"
  FROM DUAL
 WHERE NULL IS NULL;

result
---------------
NULL IS NULL

The only way to find out if something is null or has a null value is to test it for 'nullness'.

Security Update

It's that time again. Oracle has released it's quarterly security patch. Details can be found from Oracle here.

There are 14 database patches, 5 for the application server, 11 for the e-business suite, 1 for enterprise manager and 2 for peoplesoft.

While the exact workings of some of these Oracle security vulnerabilities are not readily available you should at least investigate whether you are affected by this.

Don't delay, patch today.

SUBSTR

We have all used SUBSTR at some time in our varied programming careers and can be fairly justified that we get what we need from the function.

As in previous articles there is more to this simple function than meets the eye.

The basic format of all SUBSTR type functions is that they take an input string, a start position and a number of characters and return that portion of the input string from the start position to the length of the number of characters requested.

for example:

SELECT SUBSTR ('1234567890', 5, 3)
  FROM DUAL;


SUB
---
567

1 row selected.

starts at position 5 and returns the next three characters from the input string.

There are several overloaded variations on this.

To return the remaining string from position 5 to the end you would write something like

SELECT SUBSTR ('1234567890', 5)
  FROM DUAL;


SUBSTR
------
567890

1 row selected.

The last argument has been omitted, and the assumption, on the part of the programmer, is that the number of characters requested is to the end of the string.

So now what? For 99% of what we want to do this is all we want and need to know. With a little manipulation we can extract portions of any string if we know its length and have a starting position.

What we should be doing is either, having a quick dip into the manual to see if that is all there is to SUBSTR, or trying to break it by throwing assorted combinations of inputs at it. So here goes:

SELECT SUBSTR (1234567890, 5, 2)
  FROM DUAL;


SU
--
56

1 row selected.

Well it seems to handle implicit conversions of numbers into strings, how about trying some negative number inputs? Lets see if we can get the number of characters before a start postion.

SELECT SUBSTR ('1234567890', 5 , -2)
  FROM DUAL;






1 row selected.

Not quite broken, but no error thrown. So that didn't work out too well, lets now try reversing the whole idea and seeing if a negative starting position has any effect?

SELECT SUBSTR ('1234567890', -4 , 2)
  FROM DUAL;


SU
--
78

1 row selected.

So from this example we can go 4 positions back from the end of the input string to start and return the 2 characters from there onwards. Omitting the number of characters returns the remainder of the input string. This means that I can now find the last x number of characters of any length input string without knowing it's length.

As an exercise to you, try repeating the last SQL statement but replace -4 with -14.

TRUNC

TRUNC is one of those functions in Oracle you use and cast away probably without realising the true power available in the command.

Most programmers are aware that to 'TRUNC' a number removes all information to the right of the decimal point. So for example

SELECT TRUNC(2.7) FROM DUAL;


TRUNC(2.7)
----------
      2

1 row selected.

Some programmers are aware that you can TRUNC a date data type to return the same date with the time portion set to midnight (00:00:00).

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as NO_TRUNC ,
    TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY HH24:MI:SS') as WITH_TRUNC
FROM DUAL;


NO_TRUNC            WITH_TRUNC      
------------------- -------------------
17/04/2007 10:35:00 17/04/2007 00:00:00

1 row selected.

And there most programmers knowledge of TRUNC stops. Probably because that is sufficient for most programming tasks that need to be taken care of. The other reason is, probably, that most programmers first encountered TRUNC while examining other people's code and thought that that was all there was to know about using this function. There is more to TRUNC than just this 'neat' trick. As with most inbuilt functions, TRUNC has been overloaded to accept additional arguments.

TRUNC no longer has to be wrapped in a SQL statement using the dual table but can also be use in direct variable assignments in PL/SQL as follows:

DECLARE
dt   DATE := TRUNC (SYSDATE);
BEGIN
NULL;
END;

Not the most elegant of code, but it does illustrate the point.

When dealing with number data types the additional argument is the number of decimal places to TRUNC to. Note that changing the number to negative replaces numbers to the left of the decimal separator with zeros.

SELECT TRUNC (77.77, 1) AS pos, TRUNC (77.77, -1) AS neg
FROM DUAL;


    POS        NEG
---------- ----------
   77.7         70

1 row selected.

For date data types TRUNC has been overloaded to accept a format mask. If you are familiar with casting date data types to their character equivalents using the TO_CHAR function you should be aware of format masks. The format masks I tend to use most are 'MM' for month and 'YYYY' for year, which give you the first day of the month and first day of the year respectively.

SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'DD-MON-YYYY') AS mon,
    TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'DD-MON-YYYY') AS yr
FROM DUAL;


MON         YR      
----------- -----------
01-APR-2007 01-JAN-2007

1 row selected.

So there you have it, some hidden functionality of a simple inbuilt function revealed to you. No doubt there may be more available features in later releases of Oracle, but for now 99% of my coding and SQL needs have been satisfied.

Remember that even the most innocuous of commands may have hidden features. Features that are only hidden in the manual waiting for you to discover them.

A List of Values

How often do you need to generate a list of numbers. OK well perhaps not every day, but the technique shown here should be easily adaptable to a number of situations.

WITH tbl AS
    (SELECT     ROWNUM AS RN
           FROM DUAL
     CONNECT BY LEVEL < 10 )
SELECT *
 FROM tbl;

This will generate a result set of nine rows each row containing one of the numbers 1 - 9.

Notice I could have just written

SELECT     ROWNUM AS rn
     FROM DUAL
CONNECT BY LEVEL < 10 

This example also introduces the WITH keyword which I personally find extremely useful. Other colleagues find it annoying, though I expect that will change once they become accustomed to using it more.

Personally I like the structure using WITH enforces on my coding. I also find understanding the workings of SQL statements much easier. Maybe it's because the inner workings are broken up into more accesible chunks, with the inline view code moved out of the FROM clause, decluttering the SQL.

A Date With Oracle

Dates are funny things. First you think they are something to do with days of the year then you turn around and they are numbers. What do numbers and dates have in common?

Internally all dates, including the time portion of a day, are stored as a certain number of days since a base date. We are not really concerned with that we are more interested in the fact that 0.5 of a day is midday and 0.0 means midnight. All the other hours can be worked out once we realise that 1/24 is the same as saying 1 am.

This concept allows the accurate, to seconds - not tiny fractions of seconds, caculations on dates to be performed. We can state reliably that one date less another will give a number. We can add, or subtract, a number from a date. We cannot however add two dates together as this is quite meaningless, both to us as observers and internally to the database.

A date plus or minus a number gives a date, whereas a date less a date gives a number. The number result can be more meaningfully expressed as an interval. Luckily for us there are several built in functions to display this more meaningfully.

As an aside, I did once spend a few days writing routines to extract the difference between two dates and give a meaningful result back days:hours:min:sec. Then someone more junior pointed out the interval functions to me. My embarasment soon passed, when I realised that I now had interval routines that worked, and that maybe relying on past knowledge meant I was missing something that could make my life easier.

Make a New Resolution

You find yourself amazed, and a little bit jealous, at the success of those around you. Not only are they new at the job, they have less experience than you, and still they seem to know so much. What has happened here?

This is a classic situation, and one I found myself in a few years ago. I had become complacent. There, that says it all! I was complacent. Of course at the time I didn't realise that was what was happening. I just felt that I was somehow left by the side of the road without realising how I got there.

What did I do? To be honest, nothing immediately. I was to immersed in the problem to see the solution, and the solution proved to be more simple than I thought. "READ MORE".

By saying "Read more", I mean read within your subject area, not just narrowly but branch out a bit. Read blogs, better yet, subscribe to some. Google a word or two and read the articles. Last of all "Read the Manual". I don't mean start at page one and work through to the end, and don't open the manual at random, use the index to guide you.

Do this over lunch, or take a sneaky ten to fifteen minutes, and nose around a subject. You will find that within a month or two of solid effort, sorry no simple fix here, your complacency will have all but disappeared.

Lastly, lighten up a little. When you are depressed you cannot work to the best of your ability, so smile. It's not that bad.

Starting Out

Well, it's time to take the plunge and begin a blog. The whole idea of creating content and inviting comment appeals to the inner sadist in us all.

I hope to share some of the insights I have gained in my years of working with Oracle, over many versions, and on many platforms with you.

As with any subject of a technical nature there are gotchas and nifty tricks to show and yes I have some of those. But what I would most like to promote is a more sensible way of using the database, not just using it as the "black box" behind the web server.