Oracle stuff

Tuesday, 3 December 2013

Misc

ref_cursors,

global temp table

RELY constraints, even though they are not used for data validation, can:

Enable more sophisticated query rewrites for materialized views. See Chapter 22, "Query Rewrite" for further details.
Enable other data warehousing tools to retrieve information regarding constraints directly from the Oracle data dictionary.

Creating a RELY constraint is inexpensive and does not impose any overhead during DML or load. Because the constraint is not being validated, no data processing is necessary to create it.

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.