US State Abbreviations

US States, Abbreviations and Capitals
State Standard
Abbreviation
Postal
Abbreviation
Capital City
Alabama Ala. AL Montgomery
Alaska Alaska AK Juneau
Arizona Ariz. AZ Phoenix
Arkansas Ark. AR Little Rock
California Calif. CA Sacramento
Colorado Colo. CO Denver
Connecticut Conn. CT Hartford
Delaware Del. DE Dover
Florida Fla. FL Tallahassee
Georgia Ga. GA Atlanta
Hawaii Hawaii HI Honolulu
Idaho Idaho ID Boise
Illinois Ill. IL Springfield
Indiana Ind. IN Indianapolis
Iowa Iowa IA Des Moines
Kansas Kans. KS Topeka
Kentucky Ky. KY Frankfort
Louisiana La. LA Baton Rouge
Maine Maine ME Augusta
Maryland Md. MD Annapolis
Massachusetts Mass. MA Boston
Michigan Mich. MI Lansing
Minnesota Minn. MN St. Paul
Mississippi Miss. MS Jackson
Missouri Mo. MO Jefferson City
Montana Mont. MT Helena
Nebraska Nebr. NE Lincoln
Nevada Nev. NV Carson City
New Hampshire N.H. NH Concord
New Jersey N.J. NJ Trenton
New Mexico N.M. NM Santa Fe
New York N.Y. NY Albany
North Carolina N.C. NC Raleigh
North Dakota N.D. ND Bismarck
Ohio Ohio OH Columbus
Oklahoma Okla. OK Oklahoma City
Oregon Ore. OR Salem
Pennsylvania Pa. PA Harrisburg
Rhode Island R.I. RI Providence
South Carolina S.C. SC Columbia
South Dakota S.D. SD Pierre
Tennessee Tenn. TN Nashville
Texas Tex. TX Austin
Utah Utah UT Salt Lake City
Vermont Vt. VT Montpelier
Virginia Va. VA Richmond
Washington Wash. WA Olympia
West Virginia W.Va. WV Charleston
Wisconsin Wis. WI Madison
Wyoming Wyo. WY Cheyenne

Bookmarks for Oracle

Database Specialists – Presentations and White Papers
Oracle Topics  
Jeff Hunter’s Oracle DBA Tips
Oracle Berkeley DB
Oracle Commands
Oracle Covering today’s Oracle topics
Oracle Database Online Documentation 10g Release 2 (10.2)
Oracle FAQ Quizzes
Oracle FusionZone Business Intelligence
Oracle performance tuning
Oracle Database Journal Articles

Oracle SQL Quick Reference
Oracle Technology Network 
ORACLE-BASE – Upgrading to Oracle Database 10g
OraFusion.com
OraPerf.com – The Oracle Instance Performance Portal
PUSCHITZ.COM – Oracle Linux, Linux Security, Linux Tips  

Data Partitioning in SQL Server 2000

There are two types of data partitioning: horizontal partitioning and vertical partitioning. In Horizontal Partitioning, a table is split horizontally with a subset of rows to form another similar table with the same number of columns. The structure of the table will remain the same. In Vertical Partitioning, a table with a large number of columns or very large columns is split into multiple partitions, each with the same number of rows but less number of columns. 

Here I am discussing the Horizontal Partitioning. We have a large table ORDERLINES with columns such as ORDER_NO, LINE_NO, CROSS_REF, USED_BUT_NOT_PRICED, LOST, and ORIG_COMM. Let us first partition ORDERLINES into four different tables. The data is divided with the following criteria:


1.       ORDER_NO <= ‘1020000’
2.       ORDER_NO BETWEEN ‘1020000’ AND ‘1040000’
3.       ORDER_NO BETWEEN ‘1040000’ AND ‘1060000’
4.       ORDER_NO >= ‘1060000’ 

Given below are the steps involved in this process: 

I. Create 4 tables with the same structure as that of ORDERLINES 

1. CREATE TABLE [dbo].[ORDERLINES01] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

2. CREATE TABLE [dbo].[ORDERLINES02] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

3. CREATE TABLE [dbo].[ORDERLINES03] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

4. CREATE TABLE [dbo].[ORDERLINES04] (
[OLID] [int],
[ORDER_NO] [nchar] (15) NOT NULL ,
[LINE_NO] [int] NOT NULL ,
[LOST] [bit] NULL ,
[ORIG_COMM] [numeric](5, 2) NULL
) ON [PRIMARY]

II. Insert data into these four tables  


1. INSERT INTO ORDERLINES01 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [SED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO <= '1020000')
     GO
     UPDATE ORDERLINES01
     SET OLID = '1'
     GO

2. INSERT INTO ORDERLINES02 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1020000' AND '1040000')
 GO
 UPDATE ORDERLINES02
 SET OLID = '2'
 GO

3. INSERT INTO ORDERLINES03 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST], [ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER_NO BETWEEN '1040000' AND '1060000')
    GO
    UPDATE ORDERLINES03
    SET OLID = '3'
    GO

4. INSERT INTO ORDERLINES04 ([ORDER_NO], [LINE_NO],
 [CROSS_REF], [USED_BUT_NOT_PRICED], [LOST],  ORIG_COMM])
 SELECT *
 FROM ORDERLINES
 WHERE (ORDER ORDER_NO >= '1060000')
    GO
    UPDATE ORDERLINES04
    SET OLID = '4'
    GO


 III. Build Constraints

For the Query Processor to know that each partitioned table contains only a certain type of data, we need to build CHECK constraints on each table on the ID that we have partitioned. Hence we will build CHECK constraints on OLID.                        

 1. ALTER TABLE ORDERLINES01
   ADD CONSTRAINT CK_OL1 CHECK (OLID=1)
  2. ALTER TABLE ORDERLINES02
   ADD CONSTRAINT CK_OL2 CHECK (OLID=2)
  3. ALTER TABLE ORDERLINES03
   ADD CONSTRAINT CK_OL3 CHECK (OLID=3)
  4. ALTER TABLE ORDERLINES04
   ADD CONSTRAINT CK_OL4 CHECK (OLID=4)

IV. Build the View

CREATE VIEW ORDERLINES
   AS
    SELECT * FROM ORDERLINES01
   UNION ALL
    SELECT * FROM ORDERLINES02
   UNION ALL
    SELECT * FROM ORDERLINES03
   UNION ALL
    SELECT * FROM ORDERLINES04

Conclusion 

The View shown above is the same as the original ORDERLINES table. But a query like

SELECT * FROM ORDERLINES WHERE OLID=2,

will not seek all the ORDERLINES records. It will limit the seek to ORDERLINES02 table only.