Formats, Sequences, and Indexes

1
Formats, Sequences, and Indexes

• Chapter Seven

2
Format of Dates

• Dates and Times are stored together within a
  single data type
• Dates are stored in a very compressed manner
• Translated into a Character string such as Jan.
  1, 2002
• The Date Format you specify tells the database
  how you want the dates to be Displayed

3
Dates and Formats in Oracle

• Oracle Default Date format is dd-mon-yy
• Authors SQLFUN_LOGIN script changes Oracle
  Default Date format to dd-mon-yyyy
• To display other formats, they must be explicitly
  stated in the format you wish to use
• In Oracle, Dates and Times are enclosed in single
  quotes
• In Access, Dates and Times are enclosed in pound
  signs ()

4
Oracle Date Formats
5
Oracle Time Formats
6
Oracle Other Formats
7
Access Date Formats
8
Access Time Formats
9
Access Other Formats
10
Displaying Formatted Dates

• In Oracle, the to_char function specifies the
  format to use when displaying at date
• To_char means to convert the date format to a
  character format
• In Access, the FORMAT function is used in the
  same manner

11
Date Functions Oracle and Access

• Date Functions have two parameters
• First, is the name of the column containing the
  dates
• Second, is the format to be used in displaying
  the date
• Format specification must be enclosed in single
  quotes
• Text may be added to the format, but it must be
  in double quotes

12
Sample code

• Column formatted_date format a20
• Select employee_id, first_name, hire_date,
• To_char(hire_date, mm-dd-yyyy hhmi am)
• As formatted_date
• From L_employees
• Order by employee_id

13
Formats in Access

• Format is usually specified in Field Properties
  of Table Design
• Can be specified within a SELECT Query using the
  FORMAT function
• Also formats for numbers, text, and yes/no data
  types
• Access offers more data types than Oracle

14
Sequences

• A Sequence is used to generate numbers
  sequentially
• After generated, their value is fixed and are
  only numbers
• If a row is deleted, the remaining row sequence
  numbers are unchanged
• Data type must be numeric for data generated by a
  Sequence

15
Uses for Sequence Data

• Used to put the rows in a specific order
• Used to make sure no two rows are identical
• May be used as a meaningless primary key
• May be used to show what record was entered
  first

16
Sequences in Oracle and Access

• Sequences are offered in both Oracle and Access
• Implemented in different ways
• In Oracle
• A Sequence is a database object, similar to a
  table
• Has a ROWNUM function to generate sequences
• In Access
• Sequence is implemented as a data type

17
Creating Sequences in Oracle

• A Sequence is a type of object in the database
• Exists within the database in the same way that a
  table or view exists
• Created with words CREATE SEQUENCE followed by
  the name of the new sequence
• Starting number and the increment can be set in
  the Create Sequence command

18
Using Sequences in Oracle

• A Sequence can do just two things
• It can give you
• its Current Value
• Its Next Value
• To get either of these, use
• Name of Sequence followed by a period then
  Currval or Nextval

19
Creating Sequences in Access

• In Access, use the AUTONUM on the GUI level and
  COUNTER on the Jet engine level
• Is treated as a data type
• Access automatically assigns sequential numbers
  to new rows
• Note Jet engine level of Access is the level
  that processes the SQL

20
Indexes

• They are a mystery
• They lurk behind the scenes
• You rarely work with them directly

21
To Index or Not To Index

• An Index is used to make SQL process more
  efficiently
• An Index can make a SELECT statement run much
  faster
• Indexes are usually created by the DBA
• Users only need to know the efficiencies rather
  than the nuts-and-bolts of Indexes

22
Creating an Index

• It is very simple to create an Index
• The hard part is to know when one will make SQL
  run more efficiently
• An Index is formed on certain columns of a
  particular table
• An Index is an additional layer of organization
  that enable the DBMS to locate data faster

23
Contents of an Index

• It contains pointers that go directly into the
  table
• It is a database object
• It contains data drawn from the Table
• It requires disk storage space larger Tables
  will require larger amounts of disk storage for
  the indexes of the larger Tables
• Some indexes for large Tables may require as much
  disk space as the original Table

24
Pros and Cons of Indexes

• Indexes are a double-edged sword
• It will speed up a SELECT statement
• It will slow down changes that are being made to
  the data
• Indexes are updated Dynamically at runtime,
  whenever data in a table is changed, all indexes
  must be updated at the same time
• Indexes will speed Queries for up older, larger
  tables

25
Creating Indexes

• Command is
• CREATE INDEX ix_name_of_the_Index
• ON table_name (ordered_list_of_columns_in_the_Inde
  x)
• Primary Keys have Index automatically built
• Unique Index is another kind of Index (to be
  discussed in Chapter 8)

26
How an Index works

• Index usage is never coded in the SELECT
• The Optimizer automatically uses the Indexes
• Detail description on pages 296 - 297

27
Finding More Info in theData Dictionary

• Info about the Data type of a Column
• Info about Sequences
• Info about Indexes
• Info about all of the Database Objects
• Info about the uses of Index of Dictionary Tables
  in Oracle
• Info about the use of Index of Dictionary Columns
  in Oracle