Practice: SECTION 16
Section 16.1
1. Using CREATE TABLE AS subquery syntax, create a seq_d_songs table of all the
columns in the DJs on Demand database table d_songs. Use the SELECT * in the
subquery to make sure that you have copied all of the columns.
Tampilan Table:
2. Because you are using copies of the original tables, the only constraints that were carried
over were the NOT NULL constraints. Create a sequence to be used with the primary-key
column of the seq_d_songs table. To avoid assigning primary-key numbers to these tables
that already exist, the sequence should start at 100 and have a maximum value of 1000.
Have your sequence increment by 2 and have NOCACHE and NOCYCLE. Name the
sequence seq_d_songs_seq.
3. Query the USER_SEQUENCES data dictionary to verify the seq_d_songs_seq
SEQUENCE settings.
4. Insert two rows into the seq_d_songs table. Be sure to use the sequence that you created
for the ID column. Add the two songs shown in the graphic.
ID TITLE DURATION ARTIST TYPE_CODE
Island Fever 5 min Hawaiian Islanders 12
Castle of Dreams 4 min The Wanderers 77
Tampilan Table:
5. Write out the syntax for seq_d_songs_seq to view the current value for the sequence. Use
the DUAL table. (Oracle Application Developer will not run this query.)
6. What are three benefits of using SEQUENCEs?
- A SEQUENCE is a shareable object used to automatically generate unique numbers
- Typically, sequences are used to create a primary-key value
- This object is a time-saver because it reduces the amount of code you need to write
- Sequence numbers are stored and generated independently of tables. Therefore, the
same sequence can be used for multiple tables
7. What are the advantages of caching sequence values?
Using cache option gives a slight performance advantage as the numbers are pre-
allocated and stored in-memory.
8. Name three reasons why gaps may occur in a sequence?
- Same sequence being used by multiple tables.
- Rolling back a statement containing a sequence.
- System crash if system caches values in-memory
Extension Exercise
1. Create a table called “students”. You can decide which columns belong in that table and
what datatypes these columns require. (The students may create a table with different
columns; however, the important piece that must be there is the student_id column with a
numeric datatype. This column length must allow the sequence to fit, e.g. a column length
of 4 with a sequence that starts with 1 and goes to 10000000 will not work after student
#9999 is entered.)
2. Create a sequence called student_id_seq so that you can assign unique student_id numbers
for all students that you add to your table.
Detail Table:
3. Now write the code to add students to your STUDENTS table, using your sequence
“database object.”
Tampilan Table:
Section 16.2
1. What is an index and what is it used for?
An Oracle Server index is a schema object that can speed up the retrieval of rows by
using a pointer. An index provides direct and fast access to rows in a table. It is used for
reduce the necessity of disk I/O (input/output) by using an indexed path to locate data
quickly.
2. What is a ROWID, and how is it used?
ROWID is a base 64 string representation of the row address containing block
identifier, row location in the block, and the database file identifier. Indexes use ROWID's
because they are the fastest way to access any particular row.
3. When will an index be created automatically?
Oracle Server automatically creates index when you define a column in a table to
have a PRIMARY KEY or a UNIQUE KEY constraint. It means that primary key/unique
key use already existing unique index but if index is not present already, it is created while
applying unique/primary key constraint. Oracle also creates index automatically for LOB
storage, xmltype and materialized view.
4. Create a nonunique index (foreign key) for the DJs on Demand column (cd_number) in
the D_TRACK_LISTINGS table. Use the Oracle Application Developer SQL Workshop
Data Browser to confirm that the index was created.
Bukti bahwa indeks telah dibuat:
5. Use the join statement to display the indexes and uniqueness that exist in the data
dictionary for the DJs on Demand D_SONGS table.
6. Use a SELECT statement to display the index_name, table_name, and uniqueness from
the data dictionary USER_INDEXES for the DJs on Demand D_EVENTS table.
7. Write a query to create a synonym called dj_tracks for the DJs on Demand
d_track_listings table.
8. Create a function-based index for the last_name column in DJs on Demand
D_PARTNERS table that makes it possible not to have to capitalize the table name for
searches. Write a SELECT statement that would use this index.
*Membuat table indeks*
*memulai monitoring indeks*
*Lihat kembali detail table*
*melihat tampilan table d_partners*
*Lihat kembali detail table*
*Hentikan monitoring*
9. Create a synonym for the D_TRACK_LISTINGS table. Confirm that it has been created
by querying the data dictionary.
*Memastikan bahwa table indeks telah dibuat*
10. Drop the synonym that you created in question 9.
*Melihat kembali table yang sudah di DROP*
