Oracle Tips and Tricks — David Fitzjarrell

July 21, 2016

On An Average Day

Filed under: General — dfitzjarrell @ 09:18

"There are other advantages," continued the child. "For instance, if one rat were cornered by nine cats,
on the average, each cat would be ten percent rat and the rat would be ninety percent cat. If you happened
to be a rat, you can see how much nicer it would make things."
-- Norton Juster, The Phantom Tollbooth

Recently an interesting question appeared on “Ask Tom” with regard to the ‘average’ between two dates. Which is not saying it’s the ONLY interesting question on “Ask Tom” but it did catch my attention, notably the topic of the discussion. Why, on earth, would anyone want the ‘average’ of two dates? Digging a bit deeper it became obvious what the original poster wanted to accomplish. Let’s take that trip and see where it leads.

The average person doesn’t usually ask for the average between two dates; averages are computed on salaries, on distance, on temperatures, on volumes, on numbers, in general, not dates. If we look at what the ‘average’ would be between two dates, however, we find it’s the date smack-dab in the middle of the two dates supplied. Connor McDonald provided this solution to the question:


SQL> with t as
  2     ( select
  3         date '2015-09-01' d1,
  4         date '2015-12-07' d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from t;

LEAST(D1,
---------
19-OCT-15

SQL>

which provides the correct answer given the date range specified. It’s not an ‘average’, really, it’s more like the interval middle value, but it can serve a purpose. Let’s say that Millicent Muggwumpp, super-star billing clerk, needs to determine the middle date for a billing cycle so invoices can be generated and sent to the various customers. The solution Connor provided (with some modification) can provide that information. Let’s ‘hack away’ at Connor’s code and see what we end up with:


SQL> with interval_middle as
  2     ( select
  3         to_date('&&1', 'RRRR-MM-DD') d1,
  4         to_date('&&2', 'RRRR-MM-DD') d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from interval_middle;

LEAST(D1,
---------
15-JUN-16

SQL>

Looking at the changes it should be noted that replaceable parameters have been substituted for the hard-coded dates and a more robust explicit date conversion has been written, including the expected date string format. Millicent can now use this script and pass to it any valid date range and return the middle date of that range. [SQL*Plus allows us to shut off the parameter replacement message showing the original code and the current version with the parameters supplied, but we could easily allow that to be displayed:


SQL> with interval_middle as
  2     ( select
  3         to_date('&&1', 'RRRR-MM-DD') d1,
  4         to_date('&&2', 'RRRR-MM-DD') d2
  5      from dual )
  6  select
  7    least(d1,d2) + abs(d1-d2)/2
  8  from interval_middle;
old   3:        to_date('&&1', 'RRRR-MM-DD') d1,
new   3:        to_date('2016-06-01', 'RRRR-MM-DD') d1,
old   4:        to_date('&&2', 'RRRR-MM-DD') d2
new   4:        to_date('2016-06-29', 'RRRR-MM-DD') d2

LEAST(D1,
---------
15-JUN-16

SQL>

if confirmation of the passed-in date values is required.]

Of course Connor’s answer wasn’t the only one provided as Stew Ashton questioned the need for LEAST() and ABS() in the query:


No matter what date you start with, you either subtract a positive number from the later date or subtract a negative number from the earlier date.


alter session set nls_date_format='YYYY-MM-DD HH24:MI';

with t as (
  select date '2016-07-01' d1, date '2016-07-18' d2
  from dual
)
select d1 - (d1 - d2) / 2 result1,
       d2 - (d2 - d1) / 2 result2
from t;

RESULT1          RESULT2
---------------- ----------------
2016-07-09 12:00 2016-07-09 12:00

Modifying this code in the same manner as Connor’s was produces:


SQL> with t as (
  2    select to_date('&&1', 'RRRR-MM-DD') d1, to_date('&&2', 'RRRR-MM-DD') d2
  3    from dual
  4  )
  5  select d1 - (d1 - d2) / 2 result1,
  6         d2 - (d2 - d1) / 2 result2
  7  from t;

RESULT1          RESULT2
---------------- ----------------
2016-06-15 00:00 2016-06-15 00:00

SQL>

The same result is produced by both queries, so LEAST() and ABS() aren’t absolutely necessary. Choose the approach that provides a sufficient level of comfort to you.

Averaging dates may not be an obvious operation but it can be useful, if in a limited way. We all look at dates in unusual ways on occasion so what may seem illogical to you, at least on the face of it, may be completely and utterly logical to someone else. Who knows, Aunt Edna may ask you to find the date between Uncle Milton’s and Aunt Haggatha’s birthdays because she wants to ship a plaster cat to Guatemala. Stranger things have happened.

And that’s about average.

July 14, 2016

Size Does Matter

Filed under: General — dfitzjarrell @ 11:07

"You see, to tall men I'm a midget, and to short men I'm a giant;
to the skinny ones I'm a fat man, and to the fat ones I'm a thin man."
-- Norton Juster, The Phantom Tollbooth

Someone in one of the Oracle forums asked the following question:


How to estimate datafile size of tablespace when it is almost full? Could someone please explain?

which is ambiguous to say the least, since no information was provided by the person posting as to system configuration, database size, transaction volume, etc. Several responses later the original poster replied:


My question is how much space to allocate to datafile when tablespace of it reaches almost max size?

which, on the face of it, seems to be a more ‘answerable’ question. Information is still missing, however, so it’s difficult to provide any real solution to the person who asked. Since Oracle doesn’t really monitor tablespace growth –it does monitor file size and space within the datafiles but those are only part of the required information — it’s up to the DBA (which, presumably, is you) to monitor such growth. It isn’t difficult, but it does take some preparation before any results can be returned. Let’s look at one way tablespace monitoring can be achieved.

The following script sets up a table and a sequence that will be populated by another script running as a scheduled job or task on the database server:


create table tablespace_monitor(
	mon_run	number,
	run_dt	date,
	tablespace_name varchar2(35),
	available  number,
	used	number,
	free	number,
	pct_free number(9,2))
tablespace users;

create sequence tab_mon_seq
start with 1 increment by 1 nocycle nomaxvalue nocache;

Created when connected as SYS as SYSDBA this will hold the necessary data to monitor tablespaces and their growth. This table will be populated by the following script:


insert into tablespace_monitor (tablespace_name, available, used, free, pct_free)
(
select tname_a tablespace_name, 
       ttl_avail available, 
       (ttl_avail - ttl_free) used, 
       ttl_free free, 
       round((ttl_free/ttl_avail)*100,2) "PCT FREE"
from
 (select tablespace_name tname_a, sum(user_bytes) ttl_avail
 from dba_data_files
 group by tablespace_name) avail left join
 (select tablespace_name tname_f, sum(bytes) ttl_free
 from dba_free_space
 group by tablespace_name) free on tname_f = tname_a
union
select tablespace_name, 
       sum(bytes_used+bytes_free) ttl_avail,
       sum(bytes_used),
       sum(bytes_free),
       round((sum(bytes_free)/sum(bytes_used+bytes_free))*100,2) pct_free
from v$temp_space_header
group by tablespace_name);

commit;

column next_seq noprint
select tab_mon_seq.nextval next_seq from dual;

update tablespace_monitor
set mon_run = tab_mon_seq.currval,
    run_dt = sysdate
where mon_run is null;

commit;

The provided scripts have been tested on 12.1.0.2 and work as expected. The second script is scheduled to run once daily to provide daily tablespace size data, which will be used by the next script to generate a report:


set linesize 200 numwidth 13 pagesize 40
column "CONSUMED/FREED (+)" format a20
column "TABLESPACE NAME" format a15

select m1.tablespace_name "TABLESPACE NAME", m2.free "BYTES FREE", m2.pct_free "PCT FREE",
      case when trunc((m1.free - m2.free)/(1024*1024)) > 0 then lpad(to_char(round((m1.free - m2.free)/(1024*1024),2)),20)
            when trunc((m1.free - m2.free)/(1024)) between 1 and 999 then lpad(to_char(round((m1.free - m2.free)/(1024),2)),20)
           when trunc((m1.free - m2.free)/(1024*1024)) > 0 then 'MB'
            when abs(trunc((m1.free - m2.free)/(1024))) between 1 and 999 then 'KB'
            else null end unit,
       case when trunc((((m1.free - m2.free)/(1024*1024))/((m2.run_dt - m1.run_dt)*1440))) > 0 then round((((m1.free - m2.free)/(1024*1024))/((m2.run_dt - m1.run_dt)*1440)),2)
            when trunc((((m1.free - m2.free)/(1024))/((m2.run_dt - m1.run_dt)*1440))) between 1 and 999 then round((((m1.free - m2.free)/(1024))/((m2.run_dt - m1.run_dt)*1440)),2)
            when trunc((((m1.free - m2.free)/(1024*1024))/((m2.run_dt - m1.run_dt)*1440))) >  0 then 'MB'
            when abs(trunc((((m1.free - m2.free)/(1024))/((m2.run_dt - m1.run_dt)*1440)))) between 1 and 999 then 'KB'
            else null end "UNIT PER MIN",
       round((m2.run_dt - m1.run_dt)*1440,2) "WINDOW IN MIN",
       to_char(m1.run_dt, 'RRRR-MM-DD HH24:MI:SS') "BEGIN DATE/TIME",
       to_char(m2.run_dt, 'RRRR-MM-DD HH24:MI:SS') "LAST RUN DATE/TIME"
from tablespace_monitor m1, tablespace_monitor m2
where m2.mon_run = (select max(mon_run) from tablespace_monitor)
and m1.mon_run = (select min(mon_run) from tablespace_monitor)
and m2.tablespace_name = m1.tablespace_name

spool &1
/
spool off

set linesize 80

The script takes the most recent data then takes the previous snapshot data and generates the difference, showing the growth in the last 24 hours based on running this script once per day. Of course it can be run more than once per day, but the report will only be generated on the most recent data and the run prior to that; if it is desired to run the population script more than once per day it’s recommended that the report also be run more than once per day, to report on all of the generated values stored in the tablespace_monitor table.

The report looks like this:


TABLESPACE NAME    BYTES FREE      PCT FREE CONSUMED/FREED (+)   UN   AVG PER MIN UN WINDOW IN MIN BEGIN DATE/TIME     LAST RUN DATE/TIME
--------------- ------------- ------------- -------------------- -- ------------- -- ------------- ------------------- -------------------
INDX                103809024           100                    0                0             9.87 2016-07-14 10:00:20 2016-07-14 10:10:12
SYSAUX               62193664          7.61                 2.19 MB        227.03 KB          9.87 2016-07-14 10:00:20 2016-07-14 10:10:12
SYSTEM               62849024          7.06                    0                0             9.87 2016-07-14 10:00:20 2016-07-14 10:10:12
TEMP                 42991616         68.33                    0                0             9.87 2016-07-14 10:00:20 2016-07-14 10:10:12
UNDOTBS1           1986789376         97.47                   23 MB          2.33 MB          9.87 2016-07-14 10:00:20 2016-07-14 10:10:12
USERS             33880604672         98.69               88 (+) MB          8.92 MB          9.87 2016-07-14 10:00:20 2016-07-14 10:10:12

Over a period of time growth trends can be determined for each tablespace, and space additions can be planned accordingly. Since it’s not likely that the grown patterns will be linear it’s a good idea to make note of reports of exceptionally large space usage and the period of time in which they occur. This should provide a window to plan on storage needs and give time to the System Admins and Storage Admins to prepare additional storage for use.

Storage cannot be managed without usage and growth data yet it seems that some DBAs try to do just that; a S.W.A.G CAN get you there on occasion but more often than not it’s simply glorified guesswork without a crystal ball or Ouija board handy. Regardless of how the end-users see them databases are part of a complex managed data entry and retrieval system that should not be left to chance or ‘sophisticated’ guesswork. Planning and monitoring are necessities in the IT environment; the database should not be left ‘out in the cold’ to suffer on its own.

Whether the provided scripts are used to monitor tablespace growth or you decide to write your own implementing tablespace monitoring should be at or near the top of every DBAs ‘to-do’ list. Keeping ‘tabs’ on how much space is being used in which tablespace can only help the DBA by ensuring that the users don’t run out of space because usage wasn’t tracked. Emergency space additions take time and interrupt the business flow; scheduling such additions can prevent the end-users from complaining and will keep management happy because lack of space didn’t stop the flow of transactions. Which means everybody wins.

It does seem that size is important.

July 6, 2016

He’s Making A List

Filed under: General — dfitzjarrell @ 10:16

"But I suppose there's a lot to see everywhere, if only you keep your eyes open."
-- Norton Juster, The Phantom Tollbooth

Recently a tweet by Chris Saxon (@chrisrsaxon) with respect to SQL, the language and implementation, started a list of contributions which should cause people to think a bit differently about how the language works and how queries should be structured. The list, found here, is by no stretch of the imagination complete but it is a very good place to begin re-thinking how to write SQL. Let’s look at a few of the listed items a bit further (since the list is likekly to grow not all of the list members can be covered).

Franck Pachot start the list with the following items:


				1. SQL is a declarative language
				2. It is compiled to procedural operations
				3. It operates on row sets

So what, exactly, is a “declarative language”? According to the accepted definition it is:


	... non-procedural and very high-level (4th generation). This means the programmer specifies what
        needs to be done rather than how to do it.

Unlike languages such as C/C++, Pascal, FORTRAN, BASIC, Java, Perl and Python, the SQL language simply requires that you decide what you want done and, in the case of Oracle, the optimizer decides HOW to get it done. That doesn’t mean that you can’t have some input to influence the ‘how’, it simply means you don’t need to think about it. Certainly hints are available to ‘nudge’ the optimizer in the direction more experienced coders may think is ‘best’, but there is no requirement to use them.

His second item is a reference to PL/SQL, the procedural component to Oracle’s SQL*Plus, the ADA-based language extension where procedures, functions, triggers and anonymous procedural blocks are written. Yes, you can go happily along with many tasks and not need PL/SQL but it does come in handy when repeated tasks are necessary or when intermediate feedback (well, sort of) is desired. [Pipelined functions do provide immediate feedback during execution, but they are a special case.] Many PL/SQL examples have been posted here, so look around and they can be found.

His third item was addressed by Jonathan Lewis in an Oracle Forums response; let’s look at the problem that initiated the set-based response and Jonathan Lewis’ offering.

A query and part of the execution plan were posted with the poster asking how to get rid of the table scan. The table has a primary key index so, on the face of it, it is interesting that the primary key wasn’t used.

Looking at the definition for the ‘problem’ table (LF_HOTEL_TEMP) we find only two columns, both of which are in the primary key definition. The tables were created:


SQL> create table LF_HOTEL_TEMP
  2  (
  3    HOTEL_CODE VARCHAR2(4000),
  4    SERVICE_ID NUMBER(3)
  5  )
  6  /

Table created.

SQL>
SQL> create table LF_TS_ROOMTYPE_PROPERTIES
  2  (
  3    HOTEL_CODE    VARCHAR2(20),
  4    ROOM_TYPE     VARCHAR2(500),
  5    BOARD_TYPE    VARCHAR2(500),
  6    ROOM_AMT      FLOAT,
  7    SERVICE_ID    NUMBER,
  8    CURRENCY_CODE VARCHAR2(10)
  9  )
 10  /

Table created.

SQL>
SQL> create table LF_HB_ROOMTYPE_PROPERTIES
  2  (
  3    HOTEL_CODE    VARCHAR2(20),
  4    ROOM_TYPE     VARCHAR2(500),
  5    BOARD_TYPE    VARCHAR2(500),
  6    ROOM_AMT      FLOAT,
  7    SERVICE_ID    NUMBER,
  8    CURRENCY_CODE VARCHAR2(10)
  9  )
 10  /

Table created.

SQL>
SQL> create table LF_GTA_ROOMTYPE_PROPERTIES
  2  (
  3    HOTEL_CODE    VARCHAR2(20),
  4    ROOM_TYPE     VARCHAR2(500),
  5    BOARD_TYPE    VARCHAR2(500),
  6    ROOM_AMT      FLOAT,
  7    SERVICE_ID    NUMBER,
  8    CURRENCY_CODE VARCHAR2(10)
  9  )
 10  /

Table created.

SQL>

and then the primary key and additional indexes were created:


SQL> alter table lf_hotel_temp
  2    add constraint lf_hotel_temp_PK primary key (HOTEL_CODE,service_id)
  3  /

Table altered.

SQL>
SQL> create  index LF_hb_roomtype_prop_IDX on lf_hb_roomtype_properties (HOTEL_CODE)
  2  /

Index created.

SQL>
SQL> create  index LF_ts_roomtype_prop_IDX on lf_ts_roomtype_properties (HOTEL_CODE)
  2  /

Index created.

SQL>
SQL> create index LF_gta_roomtype_prop_IDX on lf_gta_roomtype_properties (HOTEL_CODE)
  2  /

Index created.

SQL>

The original poster reported 278,000 rows in the LF_HOTEL_TEMP table so data was generated to reproduce that nunber of rows. Next the remaining tables were populated so that none of the three remaining tables shared any data:


SQL> begin
  2  	     for i in 1..278000 loop
  3  		     insert into lf_hotel_temp
  4  		     values(i, mod(i,999)+1);
  5  	     end loop;
  6
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> insert into lf_ts_roomtype_properties
  2    (hotel_code)
  3  select hotel_code from lf_hotel_temp
  4  where service_id  insert into lf_ts_roomtype_properties
  2    (hotel_code)
  3  select hotel_code from lf_hotel_temp
  4  where service_id between 313 and 642;

91740 rows created.

SQL> insert into lf_gta_roomtype_properties
  2    (hotel_code)
  3  select hotel_code from lf_hotel_temp
  4  where service_id between 271 and 823;

153743 rows created.

SQL> insert into lf_hb_roomtype_properties
  2    (hotel_code)
  3  select hotel_code from lf_hotel_temp
  4  where service_id between 571 and 999;

119262 rows created.

SQL> COMMIT;

Commit complete.

SQL>

Let’s look at the original query and its execution plan:


SQL> set autotrace on linesize 140
SQL>
SQL> -- original query
SQL> SELECT a.hotel_code
  2    FROM lf_hotel_temp a
  3  WHERE a.service_id = : p_service_id
  4  	    AND (NOT EXISTS (SELECT *
  5  	       FROM lf_ts_roomtype_properties b
  6  	      WHERE a.hotel_code = b.hotel_code)
  7  	     or NOT EXISTS (SELECT *
  8  	       FROM lf_gta_roomtype_properties b
  9  	      WHERE a.hotel_code = b.hotel_code)
 10  	    or	NOT EXISTS (SELECT *
 11  	       FROM lf_hb_roomtype_properties b
 12  	      WHERE a.hotel_code = b.hotel_code));

HOTEL_CODE
-------------------------------------------------------------------
1998
999
5994
...
243756
235764
238761

278 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4111332730

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                          |     3 |    33 |   512   (4)| 00:00:01 |
|*  1 |  FILTER            |                          |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| LF_HOTEL_TEMP            |   278 |  3058 |    94  (16)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | LF_TS_ROOMTYPE_PROP_IDX  |     1 |     7 |     3   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | LF_GTA_ROOMTYPE_PROP_IDX |     1 |     7 |     1   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN | LF_HB_ROOMTYPE_PROP_IDX  |     1 |     7 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT 0 FROM "LF_TS_ROOMTYPE_PROPERTIES" "B" WHERE
              "B"."HOTEL_CODE"=:B1) OR  NOT EXISTS (SELECT 0 FROM "LF_GTA_ROOMTYPE_PROPERTIES" "B"
              WHERE "B"."HOTEL_CODE"=:B2) OR  NOT EXISTS (SELECT 0 FROM "LF_HB_ROOMTYPE_PROPERTIES"
              "B" WHERE "B"."HOTEL_CODE"=:B3))
   2 - filter("A"."SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))
   3 - access("B"."HOTEL_CODE"=:B1)
   4 - access("B"."HOTEL_CODE"=:B1)
   5 - access("B"."HOTEL_CODE"=:B1)



Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
       2077  consistent gets
        872  physical reads
          0  redo size
       4119  bytes sent via SQL*Net to client
        570  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        278  rows processed


SQL>

All but LF_HOTEL_TEMP use the indexes to speed access to the data; this may be due to the fact that LF_HOTEL_TEMP is a two-column table and the priomary key index will be larger than the table since it contains the table data and a rowid for each row. The main idea illustrated by this problem involves being able to find a logically equivalent way of writing the SQL. Jonathan Lewis decided that set operations might produce a ‘better’ plan and re-wrote the query as shown below, producing a plan accessing LF_HOTEL_TEMP by the primary key index:


SQL> -- JL solution
SQL> var p_service_id number
SQL> exec : p_service_id := 1

PL/SQL procedure successfully completed.

SQL>
SQL> select  /*+ dynamic_sampling(0) */
  2  	     hotel_code
  3  from    lf_hotel_temp
  4  where   service_id = :p_service_id
  5  minus   (
  6  	     select  hotel_code
  7  	     from    lf_ts_roomtype_properties
  8  	     where   hotel_code is not null
  9  	     intersect
 10  	     select  hotel_code
 11  	     from    lf_gta_roomtype_properties
 12  	     where   hotel_code is not null
 13  	     intersect
 14  	     select  hotel_code
 15  	     from    lf_hb_roomtype_properties
 16  	     where   hotel_code is not null
 17  	     )
 18  ;

HOTEL_CODE
---------------------------------------------------------------
100899
101898
102897
...
999
9990
99900


278 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 775735246

--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                          |     1 |  2159 |     4 (100)| 00:00:01 |
|   1 |  MINUS                |                          |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT  |                          |     1 |  2015 |     1 (100)| 00:00:01 |
|*  3 |    INDEX FULL SCAN    | LF_HOTEL_TEMP_PK         |     1 |  2015 |     0   (0)| 00:00:01 |
|   4 |   INTERSECTION        |                          |       |       |            |          |
|   5 |    INTERSECTION       |                          |       |       |            |          |
|   6 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
|*  7 |      INDEX FULL SCAN  | LF_TS_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)| 00:00:01 |
|   8 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
|*  9 |      INDEX FULL SCAN  | LF_GTA_ROOMTYPE_PROP_IDX |     4 |    48 |     0   (0)| 00:00:01 |
|  10 |    SORT UNIQUE NOSORT |                          |     4 |    48 |     1 (100)| 00:00:01 |
|* 11 |     INDEX FULL SCAN   | LF_HB_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - access("SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))
       filter("SERVICE_ID"=TO_NUMBER(:P_SERVICE_ID))
   7 - filter("HOTEL_CODE" IS NOT NULL)
   9 - filter("HOTEL_CODE" IS NOT NULL)
  11 - filter("HOTEL_CODE" IS NOT NULL)



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5996  consistent gets
       2416  physical reads
     234680  redo size
       4119  bytes sent via SQL*Net to client
        570  bytes received via SQL*Net from client
         20  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        278  rows processed

SQL>

Notice that using set-based processing improved the execution plan. Because of the first item on this list it’s easy to forget that SQL is a set-based implementation; sometimes it takes ‘interesting’ problems to lead us down the set-based pathway.

As the tweet thread progressed Jeff Smith (@thatjeffsmith) offered additional items that, not specific to SQL, are good to remember nonetheless:


				4. getting results back doesn't mean your code is right.
				5. learning never stops

Number 4 is an important lesson to remember; just because you get data doesn’t necessarily mean that data is right. Many times queries are written and run only to discover that the join conditions aren’t correct or are missing entirely, making the results useless. Always think through the process, and, if possible, manually generate a few rows to know what SHOULD be returned. Comparing those to the query results should show if the logic in the query is correct.

That learning never stops is one reason blogs are written and read; there’s always something new, different, or changed that makes ‘resting on one’s laurels’ nearly impossible, especially in IT. A day doesn’t go by that I don’t learn something, and, to be honest, if that day ever comes I’ll consider it wasted.

There are more items in that list that I won’t mention here but just because they aren’t commented on doesn’t make them any less important. Read through the list (the link provided should bring up the latest chain of tweets) and ponder those responses. It’s time to think about SQL anew, especially if some of these comments weren’t in your idea of what SQL is and what it does. Perspectives change, and ideas along with them, so never stop learning. The more you know, the more you can grow.

Santa isn’t the only one who makes lists, you know.

Create a free website or blog at WordPress.com.