Range & List-Partitioned Tables

A range partition allows Oracle to place data in a partition based on the input value being between two values. 'MaxValue' is used to hold everything higher than the previous value listed.

A list partition allows Oracle to place data in a partition based on the input value matching a value in the list. The 'Default' is used to hold everything not mentioned in the list.

For instance, in the range-partitioned table below, data is grouped based on the year

partition Part_2004 - any year less than 2005

partition Part_2005 - any year between 2005 and 2006

partition Part_Max - any year greater than 2006

SQL> Create Table Test_Range
  2   ( Id    Number(3)
  3   , Year  Number(4)
  4   )
  5    Partition By Range(Year)
  6    (
  7      Partition Part_2004 Values Less Than (2005)
  8    , Partition Part_2005 Values Less Than (2006)
  9    , Partition Part_Max  Values Less Than (MaxValue)
 10    );
 
SQL> Insert Into Test_Range Values (1,2003);
SQL> Insert Into Test_Range Values (2,2004);
SQL> Insert Into Test_Range Values (3,2005);
SQL> Insert Into Test_Range Values (4,2006);
SQL> Insert Into Test_Range Values (5,2007);

SQL> Select * From Test_Range;

ID       YEAR
-- ----------
 1       2003
 2       2004
 3       2005
 4       2006
 5       2007

SQL> Select * from Test_Range PARTITION (Part_2004);

ID       YEAR
-- ----------
 1       2003
 2       2004

SQL> Select * from Test_Range PARTITION (Part_2005);

ID       YEAR
-- ----------
 3       2005

SQL> Select * from Test_Range PARTITION (Part_Max);

ID       YEAR
-- ----------
 4       2006
 5       2007

---

In the List-partitioned table below, data is grouped based on continents:

partition Part_Europe - France or Italy

partition Part_America - Canada or United States

partition Part_Others - any country not listed in the other two partitions.

SQL> Create Table Test_List
  2   ( Id      Number(3)
  3   , Country Varchar2(30)
  4   )
  5    Partition By List(Country)
  6     (
  7       Partition Part_Europe  Values ('France', 'Italy')
  8     , Partition Part_America Values ('Canada', 'United States')
  9     , Partition Part_Others  Values (Default)
 10     );

SQL> Insert Into Test_List Values (1,'France');
SQL> Insert Into Test_List Values (2,'Italy');
SQL> Insert Into Test_List Values (3,'Canada');
SQL> Insert Into Test_List Values (4,'United States');
SQL> Insert Into Test_List Values (5,'Australia');
SQL> Insert Into Test_List Values (6,'China');

SQL> Select * from Test_List;

ID COUNTRY
-- -------------
 1 France
 2 Italy
 3 Canada
 4 United States
 5 Australia
 6 China
 
SQL> Select * from Test_List PARTITION (Part_Europe );

ID COUNTRY
-- -------------
 1 France
 2 Italy

SQL> Select * from Test_List PARTITION (Part_America);

ID COUNTRY
-- -------------
 3 Canada
 4 United States
 
SQL> Select * from Test_List PARTITION (Part_Others );

ID COUNTRY
-- -------------
 5 Australia
 6 China

For more information see the Oracle documentation on Partitioned Tables and Indexes

How to Find a Linking Table

What do you do if you need to select data from two tables, but discover they have no common column? You look for the table that links them.

As an example, I'm using the Oracle-supplied HR schema. This query will find the table (if there is one) that has a field in Locations, and also one in Regions. (This is based on column names so if a column was named 'RegID', it would not match to 'Region_ID' even if the data was the same.)

SQL> desc locations
 Name              Null?    Type
 ----------------- -------- ------------
 LOCATION_ID       NOT NULL NUMBER(4)
 STREET_ADDRESS             VARCHAR2(40)
 POSTAL_CODE                VARCHAR2(12)
 CITY              NOT NULL VARCHAR2(30)
 STATE_PROVINCE             VARCHAR2(25)
 COUNTRY_ID                 CHAR(2)

SQL> desc regions
 Name              Null?    Type
 ----------------- -------- ------------
 REGION_ID         NOT NULL NUMBER
 REGION_NAME                VARCHAR2(25)

When the query is run, the user supplies two table names. The query then gets a list of available column names, and searches User_Tab_Columns to find other tables with columns in that list.

Set Newpage 1 Pagesize 50 Linesize 200 Echo &Onoff Feedback &Onoff Verify &Onoff

Accept Table1 Prompt '1st Table: '
Accept Table2 Prompt '2nd Table: '

Column Table_Name       Heading 'Tables From|Which Data|Is Needed'
Column Connecting_Table Heading 'Table Through|Which Connection|Needs To Be Made'
Column Column_Name      Heading 'Connecting|Column_Name'

--+-----------------------------------------------------------------------------
--| Get a list of columns in either table
--+-----------------------------------------------------------------------------
With Available_Columns As
      (
       Select Table_Name
            , Column_Name
         From User_Tab_Columns
       Where Table_Name In ( Upper('&Table1')
                           , Upper('&Table2')
                           )
      )
--+-----------------------------------------------------------------------------
--| Get the final list of original table names, connecting tables, and their
--|  columns.
--+-----------------------------------------------------------------------------
Select Table_Name
     , Connecting_Table
     , Column_Name
  From
        --+---------------------------------------------------------------------
        --| We need to count them up because if it doesn't have at least 2 
        --| columns (Col_Count > 1),  or they're both in the same table 
        --| (Tbl_Count = 1), it wouldn't be a usable set.
        --| 
        --| Table_Name Column_Name Connecting_Table Col_Count Tbl_Count
        --| ---------- ----------- ---------------- --------- ---------
        --| LOCATIONS  COUNTRY_ID  COUNTRIES                2         1
        --| REGIONS    REGION_ID   COUNTRIES                2         1
        --| LOCATIONS  LOCATION_ID DEPARTMENTS              1         1
        --+---------------------------------------------------------------------
       (
        Select Table_Name
             , ac.Column_Name
             , Connecting_Table
             ,                  Count(*) Over (Partition By Connecting_Table) 
                                As
               Col_Count
             ,                  Count(*) Over (Partition By Connecting_Table, 
                                                            Table_Name) 
                                As
               Tbl_Count
          From Available_Columns ac,
              --+---------------------------------------------------------------
              --| Get a list of columns from other tables that match those found
              --| in the two tables requested.
              --| 
              --| Connecting_Table   Column_Name
              --| ------------------ -----------
              --| COUNTRIES          COUNTRY_ID
              --| COUNTRIES          REGION_ID
              --| DEPARTMENTS        LOCATION_ID
              --+---------------------------------------------------------------
              (
              Select                 Table_Name 
                                     As 
                     Connecting_Table
                   , Column_Name
                From User_Tab_Columns
               Where Table_Name Not In ( Upper('&Table1')
                                       , Upper('&Table2')
                                       )
                 And Table_Name Not Like 'BIN$%'    /* not in Recycle Bin */
                 And Column_Name In
                            (
                            Select Column_Name
                            From Available_Columns
                            )
              )T2
              --+---------------------------------------------------------------
        Where ac.Column_Name = T2.Column_Name
       )
Where Col_Count > 1
  And Tbl_Count = 1;
  
Undefine OnOff Table1 Table2

Printing Labels from SQL through HTML

I recently needed to print 3-up address labels with the same information on each. I used a combination of padding, and adjusting the margins in the browser 'Page Setup' to get these to print in the perfs.

• This title shows in the browser title, and tab: Head "<Title>Test Labels</Title>
  

• Where there are four options, they are in this order: top right bottom left.

• Styles are Dotted Dashed Solid Double Groove Ridge Inset Outset.

This section is for the entire table:

<!-- Body {Border-Width: 5px 5px 5px 5px;             -
           Border-Style: outset outset outset outset; -
           Border-Color: Gray Gray Gray Gray;         -
           Background-Color: White;                   -
          }

This section is for each cell:

      Td {Border-Width: 5px 10px 5px 10px;           -
          Padding: 0px 50px 0px 0px;                 -
          Border-Style: Inset Inset Inset Inset;     -
          Border-Color: Gray Gray Gray Gray;         -
          Background-Color: White;                   -
          -Moz-Border-Radius: 10px 0px 0px 0px;       (Rounds edges of cells)

---

The code:

Set Term Off Echo Off Heading Off Linesize 2000 Feedback Onoff

Set Markup Html On Entmap Off Spool On Preformat Off   -
Head "<Title>Test Labels</Title>                       -
<Style Type='Text/Css'>                                -
<!-- Body {Border-Width: 0px 0px 0px 0px;              -
           Border-Style: Outset Outset Outset Outset;  -
           Border-Color: Gray Gray Gray Gray;          -
           Background-Color: White;                    -
          }                                            -
       Td {Border-Width: 0px 0px 0px 0px;              -
           Padding: 8px 11px 8px 5px;                  -
           Border-Style: Inset Inset Inset Inset;      -
           Border-Color: Gray Gray Gray Gray;          -
           Background-Color: White;                    -
           -Moz-Border-Radius: 0px 0px 0px 0px;        -
          }                                            -
</Style>" -
Table "Width='100%' Border='0'"

Column Title Entmap Off

Spool Labels.Html

Select --+----------------------------------------------------------------------
       --| Left Side Label
       --+----------------------------------------------------------------------
         '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
       --+----------------------------------------------------------------------
       --| Middle Label (Same Code As Above)
       --+----------------------------------------------------------------------
     ,    '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
       --+----------------------------------------------------------------------
       --| Right side Label (Same Code As Above)
       --+----------------------------------------------------------------------
     ,    '<Font Size=5 Face="Comic Sans" Color="Magenta"><B><Center>'
       || First || '</B> <Br> <Font Size = 3 Color = "Black">'
       || Last || ', ' || Mom_First || '<Br> Age:<B>'|| Age
       || '</B> Birthdate:' || To_Char(Birthdate,'Mm/Dd/Yy') || ' </Font>'
  From Moms_And_Kids
 Order By Last, Birthdate Desc;

Spool Off
Set Markup Html Off Entmap Off Spool Off Preformat Off
Set Echo On Term On

List Rows with at Least One Alpha Character

How this is done is based on the version. If you're running 10 or greater, you can use a regular expression. The first example looks for any alphabetic character; the second for anything not numeric.

10g Example 1:

Select Postal_Code
  From Hr.Locations
 Where Regexp_Like(Postal_Code,'[[:alpha:]]{1}');

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
3029SK

10g Example 2:

Select Postal_Code
  From Hr.Locations
 Where Not Regexp_Like(Postal_Code, '^[0-9]+$');

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
01307-002
3029SK

If you're using a version before 10, you'll have to use a translate. In this case, I'm translating all the numbers to a space, trimming them out, and then seeing if it has a length. Notice that this one picks up a zip that has a dash, where the RegExp is looking for purely A-Z.

SQL>
Select Postal_Code
  From HR.Locations
 Where Length(Trim(Translate(Postal_Code,'0123456789','          ')))>= 1;

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
01307-002
3029SK