Why Oracle By Mazmur Tarigan Email Oluckysatgmail com

Why Oracle ? By Mazmur Tarigan Email : Oluckysat@gmail. com 5/28/2005 By Mazmur Tarigan

Database n n n n Foxpro, DB-plus, Clipper : . dbf Microsoft Access : . mdb Microsoft Exel: . xls Paradox : . db My. SQL Sybase MS-SQL Server * Oracle * * Mendapat perhatian khusus bagi software development di Indonesia. Dan akan dilihat perbandingannya 5/28/2005 By Mazmur Tarigan 2

Platform comparison • SQL Server 2000 only works on Windows-based platforms, including Windows 9 x, Windows NT and Windows 2000. • In comparison with SQL Server 2000, Oracle 9 i Database supports all known platforms, including Windows-based platforms, AIX-Based Systems, Compaq Tru 64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on. 5/28/2005 By Mazmur Tarigan 3

Hardware requirements n To install SQL Server 2000, you should have the Intel or compatible platforms and the following hardware: Hardware Processor Memory Hard disk space 5/28/2005 Requirements Pentium 166 MHz or higher 32 MB RAM (minimum for Desktop Engine), 64 MB RAM (minimum for all other editions), 128 MB RAM or more recommended 270 MB (full installation), 250 MB (typical), 95 MB (minimum), Desktop Engine: 44 MB Analysis Services: 50 MB minimum and 130 MB typical English Query: 80 MB By Mazmur Tarigan 4

Hardware requirements… n n Oracle 9 i supports Intel or compatible platforms, AIX-Based Systems, Compaq Tru 64 UNIX, HP 9000 Series HP-UX, Linux Intel, Sun Solaris and so on. To install Oracle 9 i under the Intel or compatible platforms, you should have the following hardware: Hardware Processor Memory Hard disk space Requirements Pentium 166 MHz or higher RAM: 128 MB (256 MB recommended) Virtual Memory: Initial Size 200 MB, Maximum Size 400 MB 140 MB on the System Drive plus 4. 5 GB for the Oracle Home Drive (FAT) or 2. 8 GB for the Oracle Home Drive (NTFS) To install Oracle 9 i Database under the UNIX Systems, such as AIX-Based Systems, Compaq Tru 64 UNIX, HP 9000 Series HP-UX, and Sun Solaris, you should have the following hardware: Hardware Memory Swap Space Hard disk space 5/28/2005 Requirements A minimum of 512 MB RAM A minimum of 2 x RAM or 400 MB, whichever is greater 4. 5 GB By Mazmur Tarigan 5

Software requirements n SQL Server 2000 comes in six editions: Enterprise, Standard, Personal, Developer, Desktop Engine, and SQL Server CE (a compatible version for Windows CE) and requires the following software: Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE Windows CE No No No Yes Windows 9 x No No Yes No Windows NT 4. 0 Workstation with Service Pack 5 No No Yes Yes No Windows NT 4. 0 Server with Service Pack 5 Yes Yes Yes No Windows NT 4. 0 Server Enterprise Edition with Service Pack 5 Yes Yes Yes No 5/28/2005 By Mazmur Tarigan 6

Software requirements… n Oracle 9 i Database comes in three editions: Enterprise, Standard and Personal and requires the following software: Platform Operating System Version Required Patches Windows-based Windows NT 4. 0 Service Pack 5 Windows-based Windows 2000 Service Pack 1 Windows-based Windows XP Not Necessary AIX-Based AIX 4. 3. 3 Maintenance Level 09 and IY 24568, IY 25282, IY 27614, IY 30151 AIX-Based AIX 5. 1 AIX 5 L release 5. 1 ML 01+ (IY 22854), IY 26778, IY 28766, IY 28949, IY 29965, IY 30150 Compaq Tru 64 UNIX Tru 64 5. 1 patchkit 4 Compaq Tru 64 UNIX Tru 64 5. 1 A patchkit 1 HP-UX version 11. 0 (64 -bit) Sept. 2001 Quality Pack, PHCO_23792, PHCO_24148, PHKL_24268, PHKL_24729, PHKL_ 25475, PHKL_25525, PHNE_24715, PHSS_23670, PHSS_24301, PHSS_24303, PHSS_24627, PHSS_22868 Linux Su. SE Linux Enterprise Server 7 (or SLES-7) with kernel 2. 4. 7, and glibc 2. 2. 2 Not Necessary Sun Solaris 32 -Bit 2. 6 (5. 6), 7 (5. 7) or 8 (5. 8) Not Necessary Sun Solaris 64 -Bit 8 (5. 8) Update 5 5/28/2005 By Mazmur Tarigan 7

Performance comparison n It is very difficult to make the performance comparison between SQL Server 2000 and Oracle 9 i Database. The performance of your databases depends rather from the experience of the database developers and database administrator than from the database's provider. You can use both of these RDBMS to build stable and efficient system. However, it is possible to define the typical transactions, which used in inventory control systems, airline reservation systems and banking systems. After defining these typical transactions, it is possible to run them under the different database management systems working on the different hardware and software platforms. 5/28/2005 By Mazmur Tarigan 8

Price comparison n Compare pricing for SQL Server 2000 Standard Edition and Oracle 9 i Standard Edition: Number of CPUs Oracle 9 i Standard Edition SQL Server 2000 Standard Edition 1 $15, 000 $4, 999 2 $30, 000 $9, 998 4 $60, 000 $19, 996 8 $120, 000 $39, 992 16 $240, 000 $79, 984 32 $480, 000 $159, 968 5/28/2005 By Mazmur Tarigan 9

Price comparison… n Compare pricing for SQL Server 2000 Enterprise Edition (which include OLAP and Data Mining) and Oracle 9 i Enterprise Edition with OLAP and/or Data Mining: Number of CPUs Oracle 9 i Enterprise Edition with OLAP or Data Mining Oracle 9 i Enterprise Edition With OLAP and Data Mining SQL Server 2000 Enterprise Edition 1 $40, 000 $60, 000 $80, 000 $19, 999 2 $80, 000 $120, 000 $160, 000 $39, 998 4 $160, 000 $240, 000 $320, 000 $79, 996 8 $320, 000 $480, 000 $640, 000 $159, 992 16 $640, 000 $960, 000 $1, 280, 000 $319, 984 32 $1, 280, 000 $1, 920, 000 $2, 560, 000 $639, 968 5/28/2005 By Mazmur Tarigan 10

T-SQL vs PL/SQL n The dialect of SQL supported by Microsoft SQL Server 2000 is called Transact. SQL (T-SQL). The dialect of SQL supported by Oracle 9 i Database is called PL/SQL is more powerful language than T-SQL. This is the brief comparison of PL/SQL and T-SQL: Feature Indexes Tables Triggers Procedures Arrays 5/28/2005 PL/SQL T-SQL B-Tree indexes, Bitmap indexes, Partitioned indexes, Function-based indexes, Domain indexes Relational tables, Object tables, Temporary tables, Partitioned tables, External tables, Index organized tables BEFORE triggers, AFTER triggers, INSTEAD OF triggers, Database Event triggers PL/SQL statements, Java methods, third-generation language (3 GL) routines Supported B-Tree indexes By Mazmur Tarigan Relational tables, Temporary tables AFTER triggers, INSTEAD OF triggers T-SQL statements Not Supported 11

SQL Server 2000 and Oracle 9 i limits Here you can find some SQL Server 2000 and Oracle 9 i Database limits: Feature SQL Server 2000 Oracle 9 i Database database name length 128 8 column name length 128 30 index name length 128 30 table name length 128 30 view name length 128 30 stored procedure name length 128 30 max columns per index 16 32 max char() size 8000 2000 max varchar() size 8000 4000 max columns per table 1024 1000 max table row length 8036 255000 max query size 16777216 recursive subqueries 40 64 constant string size in SELECT 16777207 4000 constant string size in WHERE 8000 4000 5/28/2005 By Mazmur Tarigan 12

Introduction 5/28/2005 By Mazmur Tarigan

Pengenalan Oracle n n Oracle Server adalah relational database management system(RDBMS) RDBMS adalah perangkat lunak untuk membuat dan mengelola database, sering juga disebut database engine. 5/28/2005 By Mazmur Tarigan 14

RDBMS 5/28/2005 By Mazmur Tarigan 15

Database 5/28/2005 By Mazmur Tarigan 16

Kemampuan 1. 2. 3. 4. 5. 6. Dapat bekerja di lingkungan client/server(pemprosesan tersebar) Menangani manajemen space dan basis data yang besar Mendukung akses secara simultan Performansi pemrosesan transaksi yang tinggi Menjamin ketersediaan yang terkontrol Lingkungan yang terreplikasi 5/28/2005 By Mazmur Tarigan 17

Arsitektur Oracle n n Oracle Database * Oracle Instance (memory, process, dan files) * Oracle Database akan dikupas berkaitan dengan tujuan training sekarang untuk database developer. 5/28/2005 By Mazmur Tarigan 18

… Oracle Database n Physical Database Structure n n n 5/28/2005 Datafiles : menyimpan table dan objek database Redo Log Files : menyimpan info yang dibutuhkan untuk proses Recovery, yaitu bila terjadi crash pada sistem, maka Oracle berusaha memperbaikinya melaui Redo Log Files Control Files : Berisi info berupa konfigurasi, lokasi data dan redo-log files, start database instance dan lainnya. By Mazmur Tarigan 19

… Oracle Database (1) n Logical Database Structure n n 5/28/2005 Tablespace : mengelompokkan objek database menjadi satu kesatuan. Tablespace menyimpan data dalam datafiles yang secara fisik tersebar dalam satu atau beberapa disk. Data Schema : kumpulan dari objek database yang disebut sebagai schema objects. By Mazmur Tarigan 20

… Oracle Database (2) n 5/28/2005 Data schema : n Table : berisi nama table, kolom, baris(record) yang merupakan basic dari database. n Cluster: himpunan tabel yang secara fisik disimpan bersama sebagai sebuah tabel yang mempunyai kolom bersama. n Index : digunakan untuk mencari data secara lebih cepat ketimbang pencarian sekuensial. n View : tampilan dari satu atau beberapa tabel yang dikoleksi melalui instruksi SQL. n Stored Procedure : instruksi SQL yang dibungkus dalam bentuk program dan dieksekusi seperti fungsi atau prosedur dalam bahasa pemrograman. n Triggers : instruksi SQL seperti prosedur yang dijalankan berdasarkan suatu kejadian(event) n Sequence : secara otomatis membuat nomor urut(tersusun) dan tunggal(unique) yang digunakan oleh aplikasi untuk menjamin integritas data. By Mazmur Tarigan 21

Create Database n Untuk memulai membuat sebuat database : Start oracle – orahome 90 configuration and migration tools Database Configuration Assistant * Sebaiknya create database dilakukan oleh DBA saja, dalam hal ini instruktur 5/28/2005 By Mazmur Tarigan 22

Create Database (1) 5/28/2005 By Mazmur Tarigan 23

Create Database (2) 5/28/2005 By Mazmur Tarigan 24

Create Database (3) 5/28/2005 By Mazmur Tarigan 25

Create Database (4) 5/28/2005 By Mazmur Tarigan 26

Create Database (5) 5/28/2005 By Mazmur Tarigan 27

Create Database (5)… 5/28/2005 By Mazmur Tarigan 28

Create Database (6) 5/28/2005 By Mazmur Tarigan 29

Create Database (7) 5/28/2005 By Mazmur Tarigan 30

Create Database (8) 5/28/2005 By Mazmur Tarigan 31

Create Database (9) 5/28/2005 By Mazmur Tarigan 32

Create Database (10) 5/28/2005 By Mazmur Tarigan 33

Create Database (11) 5/28/2005 By Mazmur Tarigan 34

Create Database (12) 5/28/2005 By Mazmur Tarigan 35

Create Database (12)… 5/28/2005 By Mazmur Tarigan 36

Operate Database 5/28/2005 By Mazmur Tarigan 37

SQL * Plus n n Aplikasi pada oracle server yang digunakan untuk memanipulasi instruksi SQL, pemrograman PL/SQL dan menjalankan instruksi Administrasi database. Memulai SQL * Plus dengan mengaktifkannya dari menu Start oracle – orahome 90 application development SQL Plus 5/28/2005 By Mazmur Tarigan 38

SQL * Plus (1) 5/28/2005 By Mazmur Tarigan 39

SQL * Plus (2) n n n User name adalah login yang diperlukan untuk mendapat akses database Password umumnya diberikan oleh DBA(DB-Administrator) Connect String adalah teks yang diperlukan untuk melakukan koneksi, umumnya adalah nama server. 5/28/2005 By Mazmur Tarigan 40

SQL * Plus (3) 5/28/2005 By Mazmur Tarigan 41

SQL * Plus (4) n Beberapa cara untuk menjalankan SQL adalah sebagai berikut : n Dengan semi-kolom pada akhir baris : n SQL> n select * from tab; Dengan tanda / pada baris berikutnya : n SQL> select * from tab 2 / 5/28/2005 By Mazmur Tarigan 42

Table (Basic) n Data Definition Language (DDL) n n n 5/28/2005 CREATE TABLE ALTER TABLE DROP TABLE By Mazmur Tarigan 43

Table – Create table … Table name : mahasiswa Structure : Nim char(8), Nama varchar 2(25), Jk char(1), Alamat varchar 2(50), Kota varchar 2(10) SQL> create TABLE mahasiswa( 2 Nim char(8), 3 Nama varchar 2(25), 4 jk char(1), 5 alamat varchar 2(50), 6 kota varchar 2(10) 7 ); Table created. * Coba masukkan data mahasiswa yang sama 5/28/2005 By Mazmur Tarigan 44

Table – Create table … SQL> Insert into mahasiswa values(‘ 0001’, ’Nurlela’, ’P’, ’Jl. Damar’, ’Padang’); SQL> Insert into mahasiswa values(‘ 0002’, ’Azijah’, ’P’, ’Jl. Yamin’, ’Pariaman’); SQL> Insert into mahasiswa values(‘ 0001’, ’Yanto’, ’L’, ’Jl. Damar’, ’Padang’); SQL> Select * from mahasiswa; SQL> drop TABLE mahasiswa; Table dropped. SQL> create TABLE mahasiswa( 2 Nim char(8), 3 Nama varchar 2(25), 4 jk char(1), 5 alamat varchar 2(50), 6 kota varchar 2(10), 7 CONSTRAINT PK_MHS PRIMARY KEY(Nim) 8 ); Table created. * Coba lagi memasukkan data yang sama 5/28/2005 By Mazmur Tarigan 45

Case Study n n Createlah tabel : n Mahasiswa n Matakuliah n Nilai Sesuai dengan struktur tabel yang sudah anda buat. Masukkan data kedalam masing-masing table, minima : n 5 record untuk table mahasiswa n 3 record untuk table matakuliah n 15 record untuk table Nilai 5/28/2005 By Mazmur Tarigan 46

SQL Dasar n n n Searching : Select dengan operator ( = , !=, < >, < , >, <=, >=, LIKE ) Penentuan Baris : Where (OR, AND, NOT). Juga bisa menggunakan BETWEENdan IN Pengurutan : ORDER BY Kolom Nilai secara unik : DISTINCT Operasi Aritmatika : (+, -, *, /, %) 5/28/2005 By Mazmur Tarigan 47

SQL Dasar n n n Pengelompokan : GROUP BY Subquery : NESTED SQL Penambahan baris : INSERT Pengubahan baris : UPDATE Penghapusan Baris : DELETE Fungsi Agregate Dasar : AVG, COUNT, MAX, MIN, SUM 5/28/2005 By Mazmur Tarigan 48

SQL Dasar n Fungsi – fungsi String : n n n n 5/28/2005 ASCII : ASCII(char) CHAR : CHAR(ascii) LOWER UPPER LTRIM REPLICATE : REPLICATE(‘AIM’, ’ 3’) SUBSTRING : SUBSTRING(‘STMIK JAYA NUSA’, 7, 4) By Mazmur Tarigan 49