Oracle stuff

Tuesday, 3 December 2013

Using fake values for NULL is a BAD idea

Quote from Tom Kyte

But what does this have to do with NULL values? Nothing really—it has to do with what can happen when developers do not use a NULL value when they should have. Many times developers fear using NULLs: they do not understand them, and they do not believe they can be indexed, so they avoid them. They will use a “fake” value—such as 01-JAN-9999—to represent a missing date value. This is a bad idea for many reasons; the first I’ll show you is how it can throw off cardinality estimates.

Taken from
http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html

Loading files with the list of files in an external table

You could use a variant of this technique

create table df
  2  (
  3   fsname   varchar2(100),
  4   blocks   number,
  5   used     number,
  6   avail    number,
  7   capacity varchar2(10),
  8   mount    varchar2(100)
  9  )
 10  organization external
 11  (
 12    type oracle_loader
 13    default directory exec_dir
 14    access parameters
 15    (
 16      records delimited
 17      by newline
 18      preprocessor
 19      exec_dir:'run_df.sh'
 20      skip 1
 21      fields terminated by
 22      whitespace ldrtrim
 23    )
 24    location
 25    (
 26      exec_dir:'run_df.sh'
 27    )
 28  )
 29  /
Table created.

taken from

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html

Also look at the /*+ materialize */ hint for external tables.