SQL Webinar Success!

To date, we have held sessions focussing on analytic functions, rules of outer joins, and aggregate functions. There has been a steady increase in the number of individuals registering for the events. For that reason we are pleased to announce that we will continue to host these free 1 hour webinars on a monthly basis. A list of future topics as well as the dates and times will be available soon. To register for future events and gain access to recorded sessions visit our website. If you have ideas for topics, questions, or comments please email us at info@clearwatertg.com.

Deterministic vs. Non-Deterministic Functions in Oracle

In a previous post (PL/SQL, Deterministic Functions, and Function-Based Indexes) we discussed the creation of a function-based index using a deterministic, user-defined function to improve performance, as well as a means of creating reusable code. This is a continuation to that post investigating the meaning of DETERMINISTIC.

A deterministic function is one that will always return the same value given the same set of parameter values. As an example, SUBSTR is a deterministic function (as is IS_DATE, the user-defined function we created in the post mentioned above) because each time you call SUBSTR with the same parameter values the same result will be returned [e.g. SUBSTR('Hello World', 1, 5) will always return 'Hello']. As we described in our previous post, in order to create a function-based index using a user-defined function, we must specify the function is DETERMINISTIC. But what will happen if we specify a function is deterministic when in reality, it is not? We will use another simple example to find out.

The following function returns the number of years from today given a specified date parameter value. This function is not deterministic because the function returns a different value for a given parameter value depending upon the day it is executed; however, we have created it using the DETERMINISTIC keyword to see the effects.

CREATE OR REPLACE FUNCTION years_from_today
 ( p_date   IN DATE )
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN ABS(MONTHS_BETWEEN(SYSDATE, p_date) / 12);
END years_from_today;
/

SHO ERR

To test the function, we will create a test table with a date column then query the table and compare the current date to the date in the DATE_VALUE column.

CREATE TABLE det_test AS
SELECT TO_DATE('01-JUL-2009', 'DD-MON-YYYY') AS date_value 
FROM   dual;

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-10                   1.21861774

Create a function-based index on the new table.

CREATE INDEX det_test_fbi ON det_test (years_from_today(date_value));

Examine the execution plan to verify the use of the index when YEARS_FROM_TODAY is placed in the WHERE clause.

EXPLAIN PLAN FOR
SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

SELECT * FROM TABLE(dbms_xplan.display());

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| DET_TEST     |
|*  2 |   INDEX RANGE SCAN          | DET_TEST_FBI |
---------------------------------------------------- 

Now, to see the implications of our DETERMINISTIC choice, change the date on the server ... in a test environment of course ... to move ahead a full year. Even though the date has changed, running the query again will still return the same value as before from YEARS_FROM_TODAY, along with the same row, because the index is used instead of executing the function.

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                    1.2186201 

Without the WHERE clause, the query should return the following:

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                   2.21867063

As is evident from the erroneous output, a function should never be created as deterministic unless it will ALWAYS return the same value given the same parameters.

PL/SQL, Deterministic Functions, and Function-Based Indexes

User-defined functions provide a tremendous way to create consistent, modular, and re-usable code used to format output and process data within SQL. Unfortunately, when PL/SQL functions are used in SQL statements, performance often suffers due to context switches incurred as Oracle moves between the SQL and PL/SQL engines of the database. To improve performance, function-based indexes can be created, which the optimizer can use in lieu of executing the PL/SQL function for each row. In order for a function-based index to be created with a user-defined function, the function must be identified as DETERMINISTIC.

To illustrate, we will use a user-defined function to solve a problem encountered by many of our clients that use a specific Advancement product where dates are stored as character strings in the database. In this product, the "date" column is mandatory; however, there may be situations when the end user does not know the date value to be entered. In that case, as much information is entered as possible. This may lead to zeroed out values for unknown date components. For example, if the year is not known, but the month and day is, the end user may enter a value of '00000905' (for September 5th with an unknown year). If no date component is known, the end user may enter placeholder values such as all zeros or a space. To further complicate matters, since the date is a character string and validation at the database level is not possible, there may be occurrences of invalid date formats entered.

Note: Of course storing dates as character strings is not good practice, we are simply using this as an example of overcoming a problem with the use of user-defined functions.

To begin, we will create a test table with "date" values stored as character strings. The expected "good" format for the date values should be 'YYYYMMDD'.

CREATE TABLE date_test
( date_string VARCHAR2(8) );

Now a few rows will be inserted into the table.

INSERT INTO date_test VALUES ('00000000');
INSERT INTO date_test VALUES (' ');
INSERT INTO date_test VALUES ('19920821') -- Valid format;
INSERT INTO date_test VALUES ('08011999');
INSERT INTO date_test VALUES ('00000810');

Additional rows are added to illustrate performance strain encountered with user-defined functions and context switching.

INSERT INTO date_test SELECT * FROM date_test;
/
/
/
/
/
/
/
/
/
/
/
/
/

COMMIT;

Next, a function is created to test for valid dates. The function tries to perform an explicit conversion using TO_DATE. If the conversion fails, the date is not valid and an 'N' is returned; otherwise, a 'Y' is returned.

CREATE OR REPLACE FUNCTION is_date
 ( p_string  IN VARCHAR2
 , p_format  IN VARCHAR2 DEFAULT 'YYYYMMDD' )
RETURN VARCHAR2 IS
  tmp  DATE;
BEGIN
  tmp := TO_DATE(p_string, p_format);
  RETURN 'Y';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'N';
END is_date;
/

SHO ERR

Take a look at the number of rows in the table, along with the result of the IS_DATE function for each distinct value.

SELECT COUNT(*) FROM date_test;

  COUNT(*)
----------
     81920

COL is_date FORMAT A7
SELECT DISTINCT date_string, is_date
FROM ( 
       SELECT date_string
            , is_date(date_string) AS is_date 
         FROM date_test 
     );

DATE_STR IS_DATE
-------- -------
00000000 N
08011999 N
00000810 N
19920821 Y
         N 

Next, we will use the IS_DATE function to find only rows with a valid date format. Examine the execution plan of the query. Notice the execution plan makes use of a full table scan.

EXPLAIN PLAN FOR
SELECT * FROM date_test
WHERE is_date(date_string) = 'Y';

SELECT * FROM TABLE(dbms_xplan.display());

---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|*  1 |  TABLE ACCESS FULL| DATE_TEST |
--------------------------------------- 

Attempt to create a function-based index to improve performance. Unfortunately, a user-defined function cannot be used in a function-based index unless the function is determined to be DETERMINISTIC.

CREATE INDEX date_test_fbi ON date_test(is_date(date_string));
*
ERROR at line 1:
ORA-30553: The function is not deterministic

Change the function to be 'DETERMINISTIC' to allow an FBI to be created.

CREATE OR REPLACE FUNCTION is_date
 ( p_string  IN VARCHAR2
 , p_format  IN VARCHAR2 DEFAULT 'YYYYMMDD' )
RETURN VARCHAR2 DETERMINISTIC IS
  tmp  DATE;
BEGIN
  tmp := TO_DATE(p_string, p_format);
  RETURN 'Y';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'N';
END is_date;
/

SHO ERR

Attempt to create the index again. This time the index will be created successfully.

CREATE INDEX date_test_fbi ON date_test(is_date(date_string));

Re-examine the execution plan of the query executed earlier to make sure the index is being used.

EXPLAIN PLAN FOR
SELECT * FROM date_test
WHERE is_date(date_string) = 'Y';

SELECT * FROM TABLE(dbms_xplan.display());

-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DATE_TEST     |
|*  2 |   INDEX RANGE SCAN          | DATE_TEST_FBI |
----------------------------------------------------- 

Now that the index is used, the function provides a much improved performance boost, as the function does not have to be executed, thus allowing the PL/SQL logic to be contained within a single function (rather than trying to valid dates directly in SQL) in an efficient way.

Our next blog will illustrate the difference between DETERMINISTIC and NON-DETERMINISTIC functions.

Some things to take away from the AASP Conference in Davis, CA

href="http://www.clearwatertg.com/downloads/data_warehouse_strategies.pdf">presentation

Here is Brian Dowlings presentation on Data Warehousing.

Here is the link to our latest Press Release

http://www.free-press-release.com/news-clearwater-technical-group-1279909370.html

How to use Social Media for better SEO

What is SEO? SEO stands for Search Engine Optimization, which is a fancy way of saying "making it easier to be found in search engines." It is every company's goal to have better SEO so they are more likely to be found on the web. How do businesses and organizations do this? How does it help with the bottom line?

There are two different definitions of SEO. There is organic and inorganic SEO. Inorganic SEO is the pay-per-click type of media marketing within search engines like Google and Yahoo. A business or organization can pay to have their logo or advertisement listed when searching for specific information. It is very reliable to put their ad in front of the potential customer searching the web. The problem that sometimes arises for this type of SEO is a lack of trust. People often ignore the pay-per-click type of advertising because they DO view it as an advertisement.

The organic type of SEO is sometimes more difficult to achieve and takes more time, but can be more reliable with better bottom line results. This type of SEO is accomplished naturally by the behaviors of those that search, which is why it is more trusted than the pay-per-click. The key is to create popular content on the web. This means the more exposure the content gets, the more likely it will show up in search. Say you’re a backpacking guide company in the State of Washington; the logical thing your company wants from your SEO is that in a search from a potential customer you would show up in the first page of results in a search for backpacking guide companies in Washington State.

So how do you accomplish this? Well, for starters you have to have a plan! Of course you need a website for customers to visit so potential clients can find out information about you . Then, you need to create popular content and key phrases that are connected to your website that will result in repetitive searches. Content should be creative and meaningful to your potential audience. Then, using social media applications like Twitter, Facebook, Youtube, and Flicker, you can post that content to the web. Using keywords in your taglines will help potential customers find that information. Audio, video, and pictures of backpacking trips and customer feedback from previous events can help keep the content fresh and appealing. Finally, monitor and engage in the feedback to personalize the experience. The end result is a free and functional marketing campaign that will provide potential clients with valuable information about you.

AASP Regional Conference in Davis, California!

Clearwater Technical Group is a major sponsor this year for AASP's Regional Conference in Davis, California!

The theme for the conference is "Hot Topics" and will have lots of discussions on what is going on right now in the Advancement community.  To view the agenda for this event you can click on the link below!

http://www.advserv.org/Default.aspx?pageId=674502

We look forward to seeing all of our Advancement friends at the conference and can't wait to enjoy beautiful Davis, California!