MacLochlainns Weblog

Playing around with illustrating date math, I noticed that Oracle 11g still hasn’t fixed the TO_YMINTERVAL function. The function should let you to subtract months, years, or a combination of month and years from any date. However, it doesn’t like leap years or going from the last day of a longer month to the last day of a shorter month.

You can add or subtract a year from a date in the middle of the month:

SELECT TO_DATE('15-JAN-08') - TO_YMINTERVAL('01-00') FROM dual;

You can also add or subtract a month from a date in the middle of the month:

SELECT TO_DATE('15-FEB-08') - TO_YMINTERVAL('00-01') FROM dual;

You can’t subtract a year from the last day of February in a leap year. There is also no logic to let you add a year to 28-FEB-07 and get 29-FEB-08 (like a Boolean for last_day). Likewise, you can’t add a month to the last day of a month where the next month has fewer days in it; or subtract a month from the last day of a month where the last day is greater than the prior months last day without raising an ORA-01839 error.

Here’s what happens subtracting a year from this years leap day:

SELECT TO_DATE('29-FEB-08') - TO_YMINTERVAL('01-00') FROM dual
                            *
ERROR at line 1:
ORA-01839: DATE NOT valid FOR MONTH specified

You can’t complain about it though, it’s apparently compliant with the SQL92 standard according to metalink.

You may like the standard syntax of listing tables as comma-delimited lists, rather than using an inner, left, right, full, natural, and cross join syntax. I do! The former method eliminates matching result sets and generally is less tedious.

The natural join is my least favorite of these because it can create problems long after the query is written. The problem is an outcome of its design. A natural join matches rows from one table to another by using all matching column names. This query makes a simplifying assumption: Nobody includes a column of the same name in two tables where they may be joined that isn’t a foreign key.

The following is quick example that creates two tables. The VEHICLE table uses the VEHICLE_ID column as a primary key. The WORK_ORDER table uses the VEHICLE_ID column as a foreign key.

CREATE TABLE vehicle
( vehicle_id NUMBER
, vin VARCHAR2(20));
 
CREATE TABLE work_order
( work_order_id NUMBER
, vehicle_id NUMBER);
 
INSERT INTO vehicle VALUES (1,'VIN_NUMBER1');
INSERT INTO vehicle VALUES (2,'VIN_NUMBER2');
INSERT INTO work_order VALUES (1, 1);
INSERT INTO work_order VALUES (2, 2);
INSERT INTO work_order VALUES (3, 1);

The following queries resolve the relationship by using an explicit join:

SELECT * FROM vehicle v, work_order wo WHERE v.vehicle_id = wo.vehicle_id;
SELECT * FROM vehicle v JOIN work_order wo ON v.vehicle_id = wo.vehicle_id;
SELECT * FROM vehicle v JOIN work_order wo USING(vehicle_id);

A natural query also resolves the relationship by implicitly finding the columns that share column names, as follows:

SELECT * FROM vehicle v NATURAL JOIN work_order wo;

All of these return a three row result set. The unnatural part of the natural query arrives during maintenance programming when somebody adds a VIN column name to the WORK_ORDER table (let’s say to simplify the ETL to the data warehouse). The code using a natural join isn’t invalidated but its runtime behavior is altered because it now joins both the VEHICLE_ID and VIN columns. Continuing the example, you’d change the table by:

ALTER TABLE work_order ADD (vin VARCHAR2(20));

The explicit queries still work after the change. The implicit (natural) query now returns no rows because the VIN column contains all null values, and it is automatically added to the implicit join statement. After updating the VIN column, the query resolves when the data matches but not when the VIN column contains different data. What if the VIN column in WORK_ORDER only contains the make and model portion of the vehicle identification number while the VIN column in VEHICLE contains the complete unique vehicle identifier. You would receive no rows selected from the natural query.

It appears the best thing to do, is avoid natural joins.

I subsequently wrote a post about SQL join semantics. You can find it here.

Somebody asked me how you include a colon in a Native Dynamic SQL (NDS) statement when it’s not related to a placeholder. A colon without a placeholder raises an ORA-00947 error when parsing the statement. The error means you’ve failed to submit enough bind variables. You can substitute a CHR(58) where you need to insert the standalone colon. The NDS or DBMS_SQL parsing phase ignores a CHR(58), which translates during actual SQL statement parsing as a colon.

Let’s say you want to insert a column value with an ASIN (Amazon Standard Identification Number) code in the format: ASIN: B000VBJEEG

Using NDS, you have two choices. You can let the entry person type the full string and pass that string as a bind variable, or you can substitute CHR(58) for the colon and enter only the ASIN code. The example (Oracle Database 11g PL/SQL Programming, pp. 386-387) implements the latter:

CREATE OR REPLACE PROCEDURE insert_item
( asin VARCHAR2
, item_type VARCHAR2
, item_title VARCHAR2
, item_subtitle VARCHAR2 := ''
, rating VARCHAR2
, agency VARCHAR2
, release_date DATE ) IS
 
  -- Local variable for a dynamic SQL statement.
  stmt VARCHAR2(2000);
 
BEGIN
 
  -- Create a dynamic statement with bind variables.
  stmt := 'INSERT INTO item '
  || '( item_id'
  || ', item_barcode'
  || ', item_type'
  || ', item_title'
  || ', item_subtitle'
  || ', item_desc'
  || ', item_blob'
  || ', item_photo'
  || ', item_rating'
  || ', item_rating_agency'
  || ', item_release_date' 
  || ', created_by'
  || ', creation_date'
  || ', last_updated_by'
  || ', last_update_date ) '
  || 'VALUES '
  || '( item_s1.nextval'
  || ',''ASIN''||CHR(58)||:asin'
  || ',(SELECT common_lookup_id'
  || ' FROM common_lookup'
  || ' WHERE common_lookup_type = :item_type)'
  || ', :item_title'
  || ', :item_subtitle'
  || ', empty_clob()'
  || ', NULL, NULL'
  || ', :rating'
  || ', :agency'
  || ', :release_date'
  || ', 3, SYSDATE, 3, SYSDATE)';
 
  -- Print debug statement.
  dbms_output.put_line(stmt);
 
  -- Execute dynamic statement with bind variables.
  EXECUTE IMMEDIATE stmt
  USING asin, item_type, item_title, item_subtitle, rating, agency, release_date;
END insert_item;
/

There’s quite a nifty or nasty trick inside the dynamic SQL statement. You’ll notice that the colon is concatenated to the ASIN and a bind variable. It is critical that you don’t encapsulate the bind variable inside quotes, or you’ll raise an ORA-01006 exception (check Table 11-2 in the Oracle Database 11g PL/SQL Programming book for more detail). You can’t enclose a string in single quotes because the string is substituted as a string, and it trips up the parser.

You can test this dynamic SQL statement with the following anonymous PL/SQL block:

BEGIN
  insert_item('B00005JPO1'
             ,'DVD_WIDE_SCREEN'
             ,'Indiana Jones and the Crystal Skull'
             ,'2-Disc Special Edition'
             ,'PG-13'
             ,'MPAA'
             ,'14-OCT-08');
END;
/

You can do base type and subtype comparisons with the IS OF or IS NOT OF operators. They work like the typeof operator in Java. They also eliminate null values when you use them in a WHERE clause or an IF block. They work against all SQL user-defined object types. The prototypes are:
 

object_variable IS OF (object_type1 [, object_type2, [object_type(n+1)]])
object_variable IS NOT OF (object_type1 [, object_type2, [object_type(n+1)]])

 
The left operand should be an object column or element of a collection. The IS OF returns true when the object_variable matches the object type or a member of the list of object types. The IS NOT OF returns true when the object_variable doesn’t match, and both return false when the object_variable is a null value.

After replacing the contents of a BLOB or CLOB column, you will see the size grow because old indexes and segments aren’t deleted or removed from the index. The only way to get rid of the old information is to perform some simple table maintenance. The following provides an example of dumping redundant or obsolete space and indexes.

You should first check space, by using the query provided earlier in my blog to compare LOB indexes and segments. Such extremes as the regular expression in that query aren’t needed when DBAs ensure that LOBs are created with named segments. In this test case, this is the starting point:

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416

You create a temporary CLOB column in the target table. Then, you update the temporary column with the value from your real column.

ALTER TABLE item ADD (item_temp CLOB);
UPDATE item SET item_temp = item_desc;

When you requery the table’s indexes and segments, you’d find something like the following. You should note the size of the index and segments are three times larger in the real column than the temporary columns.

TABLE COLUMN                              Segment      Segment
Name  Name      Segment Name              TYPE           Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00007$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00007$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00006$$  LOBINDEX      393216
ITEM  ITEM_DESC SYS_LOB0000074435C00006$$ LOBSEGMENT 226492416
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the real column and add it back, or simply rename the new table to the old column once you’ve dropped it. Then, you update the real column with the values from the temporary column.

ALTER TABLE item DROP COLUMN item_desc;
ALTER TABLE item ADD (item_desc CLOB);
UPDATE item SET item_desc = item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712
ITEM  ITEM_TEMP SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_TEMP SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

You drop the temporary column after making the change.

ALTER TABLE item DROP COLUMN item_temp;

You can now requery the table and find that you’ve eliminated extraneous space.

TABLE COLUMN                              Segment     Segment
Name  Name      Segment Name              TYPE          Bytes
----- --------- ------------------------- ---------- ---------
ITEM  ITEM_BLOB SYS_IL0000074435C00006$$  LOBINDEX       65536
ITEM  ITEM_BLOB SYS_LOB0000074435C00006$$ LOBSEGMENT   2097152
ITEM  ITEM_DESC SYS_IL0000074435C00016$$  LOBINDEX      131072
ITEM  ITEM_DESC SYS_LOB0000074435C00016$$ LOBSEGMENT  65011712

I enjoyed Tom Kyte’s example of how you find matching a LOB Index to Segment (Expert Oracle Database Architecture, pp. 542). I’ve noticed variations of it posted in various locations. While it works well for sample scheme that have only one LOB, the following works for any number of LOBs in any schema. This simplifies working with system- and user-defined segment names. The first CASE statement ensures that joins between user-named segment names are possible. The second CASE statement ensures two things: (a) Joins between system generated segment names don’t throw an error when matching unrelated system generated return values found in the DBA_SEGEMENTS view; and (b) Joins between named segments are possible and don’t throw an error.

COL owner         FORMAT A5  HEADING "Owner"
COL TABLE_NAME    FORMAT A5  HEADING "Table|Name"
COL column_name   FORMAT A10 HEADING "Column|Name"
COL segment_name  FORMAT A26 HEADING "Segment Name"
COL segment_type  FORMAT A10 HEADING "Segment|Type"
COL bytes                    HEADING "Segment|Bytes"
 
SELECT   l.owner
,        l.table_name
,        l.column_name
,        s.segment_name
,        s.segment_type
,        s.bytes
FROM     dba_lobs l
,        dba_segments s
WHERE    REGEXP_SUBSTR(l.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END) =
REGEXP_SUBSTR(s.segment_name,'([[:alnum:]]|[[:punct:]])+'
, CASE
    WHEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1) > 0
    THEN REGEXP_INSTR(s.segment_name,'[[:digit:]]',1)
    ELSE 1
  END)
AND      l.table_name = UPPER('&table_name')
AND      l.owner = UPPER('&owner')
ORDER BY l.column_name, s.segment_name;

It produces the following type of output:

Table  Column                               Segment      Segment
Name   Name       Segment Name              Type           Bytes
------ ---------- ------------------------- ---------- ---------
PLSQL  ITEM_BLOB  SYS_IL0000074435C00007$$  LOBINDEX      65,536
PLSQL  ITEM_BLOB  SYS_LOB0000074435C00007$$ LOBSEGMENT 2,097,152
PLSQL  ITEM_DESC  SYS_IL0000074435C00006$$  LOBINDEX      65,536
PLSQL  ITEM_DESC  SYS_LOB0000074435C00006$$ LOBSEGMENT   720,896

This should help you monitor growth of LOBs in your database. The maximum CHUNK size is 32.768. If you elect a value greater than that you raise an ORA-22851 error.