0

I am recreating a database existing in SQL Server in Oracle. I am a novice in DDL and new to Oracle. I have come across the following table in SQL Server

CREATE TABLE [DailyCounts](
    [LDate] [datetime] NOT NULL CONSTRAINT [DF_LDate]  DEFAULT (getdate()),
    [SourceTable] [varchar](32) NOT NULL,
    [TableRowCount] [bigint] NOT NULL,
 CONSTRAINT [PK_DailyCounts] PRIMARY KEY CLUSTERED 
(
    [LDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

The table stores daily row counts for each of large tables in the database about 4 tables (4 rows per day).

What is the equivalent table definition for above table in Oracle especially the equivalent for the index options in Oracle? I don't know anything about index options and if they are required in this case.

CONSTRAINT [PK_DailyCounts] PRIMARY KEY CLUSTERED 
(
    [LDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

I know ON PRIMARY is not required in the above query, Oracle doesn't have clustered indexes and that Oracle creates indexes for primary keys. Thus personally I feel the following query should do the job.

CREATE TABLE DailyCounts(
    LDate timestamp DEFAULT sysdate CONSTRAINT PK_DailyCounts PRIMARY KEY,
    SourceTable varchar2(32) NOT NULL,
    TableRowCount number(19) NOT NULL
 );
Community
  • 1
  • 1
Ram
  • 3,092
  • 10
  • 40
  • 56
  • 1
    The equivalent of a clustered index in SQL Server is an index organized table in Oracle. But I agree your `CREATE TABLE` looks good, although I prefer `timestamp` over `date` just to document that fact that the time part *is* important. –  Oct 21 '14 at 18:50
  • @a_horse_with_no_name Agree with changing `date` to `timestamp`. What to do with the index options (`PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80`) in Oracle? I don't know anything about index options, if they are required in this case and if they have a equivalent in Oracle? – Ram Oct 21 '14 at 19:00
  • Keep the defaults for Oracle, in most of the cases they make sense. –  Oct 21 '14 at 19:24
  • I can't emphasize enough how bad an idea it is to use `date`, `datetime`, or `timestamp` as a field in a primary key. Foreign keys become a pain, you suddenly care about clock precision, and events like Daylight Saving Time or VM migrations can wreak havoc on your time accuracy. This table defined here, for example, can *never have multiple simultaneous row inserts* because that would be a key violation! Then there's stuff like SQL Server treats times in `datetime` within 3ms as the same for comparison... more key violations. – Bacon Bits Oct 21 '14 at 20:01
  • 1
    @BaconBits Thanks. I understand. I didn't design or create the tables in SQL Server and I have been assigned the job of just converting the SQL Server code to Oracle code not changing the design. I will bring it up in the next meeting. – Ram Oct 21 '14 at 20:07

0 Answers0