Oracleda uygulama gerçekleştiren her yazılımcının bilmesi gerekenler

1- cost based optimizer kullandığımız için mutlaka istatistik almak

BEGIN
— Table Name = table_1

DBMS_STATS.GATHER_TABLE_STATS(
ownname =>
‘joeholmes’
, tabname =>
‘table_1’
, estimate_percent => NULL)

2-Genel data dictionary tablolarımız.

Common DD tables:
– user_tables , user_indexes
• temel bilgiler tablolar ve indexler
• son analiz bilgileri CBO
– user_tab_columns , user_ind_columns
– user_db_links , user_synonyms , user_constraints

3-Commit rollback bilgileri
Can ROLLBACK before COMMIT
• Explicit COMMIT
– Commits changes INSERT, UPDATE, DELETE to database
– SET AUTOCOMMIT on;
• Implicit COMMIT
– Eğer CREATE table, index
– DROP, TRUNCATE table
– ROLLBACK will not reverse it

4-Decode yazılımı
SQL*Plus DECODE beraber IF-THEN-ELSE logic
• Daha hızlı daha güçlü

, DECODE(alanadı, a, x, b, y, z)
• Mantığı
IF alan= a, then assign x to answer
ELSEIF field1 = b, then assign y to answer
ELSE assign z to answer;

5-Null örneği
NULL != NULL
– Use IS NULL, IS NOT NULL, NVL(field,value
WHERE a.fielda IS NULL
AND a.fieldb IS NOT NULL
AND NVL(a.field1,0) = NVL(b.field1,0)
AND NVL(a.field2, ‘ ‘) = NVL(b.field2, ‘ ‘);

6-Concat index
Birden fazla alanı indexlemek
• Hızlı geri dönüşüm daha az alan kullanımı
• alan sıralaması önemlidir
– en kısıtlayıcı olanı ilk alana yazmak önemlidir.

7-Normalization in RDBMS
– Tekrar eden gruplardan uzaklaşalım
– Integrity, alan azaltır, update,kolaylaştırır değişiklikler tek yerde yapılır.
• Problem
– SQL eğer çok tablo join yapılırsa yavaşlar
– Az tablo, Çok performance
• Denormalization
– Pre-join into TEMP table
– Temporary use, infrequently updates
– Data Warehousing – FAT table, star schema, dimensions

8-Tablolar geçici olacaksa nologingde kullanmak
CREATE TABLE temp_table1 NOLOGGING AS (
SELECT t1.field f1
, t2.field2 f2
FROM table1 t1
, table2 t2
WHERE t1.field1 = t2.field1);

CREATE GLOBAL TEMPORARY TABLE
temp_table2 (
field NUMBER
, field2 NUMBER
) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE
temp_table3 (
field NUMBER
, field2 NUMEBR
) ON COMMIT PRESERVE ROWS

ON COMMIT DELETE ROWS; – transactiondan sonra siler
ON COMMIT PRESERVE ROWS; – session sonunda siler

9-Materilized view

– Birçok defa yerine, maşiyeti yüksek queryler , complex joins
– matamatiksel summary & aggregate data

CREATE MATERIALIZED VIEW sf_sales AS
SELECT prod_code, SUM(amt) AS tot_amt
FROM sales
WHERE city_name = ‘OTTAWA’
GROUP BY prod_code;

10- Storage
Bir tablolunun nereden başlayacağı nereye gideceği konusu

CREATE TABLE table1 AS ( . . .)
STORAGE (INITIAL 100M NEXT 5M PCTINCREASE 0 PCTFREE 25 PCTUSED 75);

11-in-line select

CURSOR crs_province_code_activities IS
SELECT a.worksite_code
, a.o_prcode
, a.processing_date
, . . .
FROM arpcode_activities a
, (SELECT o_prcode
, MAX(processing_date) processing_date
FROM arpcode_activities
WHERE o_prcode = p_province_code
AND status = ‘A’
GROUP BY o_prcode) b
WHERE a.o_prcode = b.o_prcode
AND a.processing_date = b.processing_date
AND a.status = ‘A’;

inline select direk tune edilmiş gelir. tercih etmemiz doğru olur.

12-variables-define

DEFINE table_name = &1;
COL table1 NEW_VALUE table1;
SELECT ‘geo_’ ‘&table_name’ table1
FROM DUAL;
SELECT *
FROM &table1
WHERE ROWNUM DEFINE table_name = street
SQL> COL table1 NEW_VALUE table1
old: SELECT ‘geo_’ ‘&table_name’ table1
FROM DUAL
new: SELECT ‘geo_’ ‘street’ table1
FROM DUAL
SQL> SELECT ‘geo_’ ‘street’ table1
FROM DUAL
table1
———-
GEO_street
1 row selected.
old: SELECT *
FROM &table1
WHERE ROWNUM SELECT *
FROM geo_street
WHERE ROWNUM DECLARE
2 sql_stmt VARCHAR2(200);
3 dept_id NUMBER(2) := 50;
4 dept_name VARCHAR2(20) := ‘STATISTICS CANADA’;
5 dept_loc VARCHAR2(13) := ‘OTTAWA’;
6 BEGIN
7 EXECUTE IMMEDIATE ‘CREATE TABLE table_a (AID NUMBER(5), amount NUMBER)’;
8 —
9 sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;
10 EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, dept_loc;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> DESC table_a;
Name Null? Type
—————————————– ——– —————————-
AID NUMBER(5)
AMOUNT NUMBER
SQL>
SQL> SELECT * FROM dept;
DEPT_ID DEPT_NAME DEPT_LOC
———- ——————– ————-
50 STATISTICS CANADA OTTAWA

17- case kullanmak. Plsqlde önemlidir.

CASE prov_id
WHEN ‘AB’ THEN
capital_name := ‘EDMONTON’;
WHEN ‘BC’ THEN
capital_name := ‘VICTORIA’;
. . .
WHEN ‘YK’ THEN
capital_name := ‘WHITEHORSE’;
ELSE capital_name := ‘UNKNOWN’;
END CASE;

CASE
WHEN region_name = ‘ATLANTIC’ THEN
saleperson := ‘JONES’;
WHEN division_name = ‘WEST’ THEN
salesperson := ‘SMITH’;
ELSE salesperson := ‘WHITE’;
END CASE

18-Bulk sql operasyonları

DECLARE
TYPE COL1_TYPE IS TABLE OF TABLE1.COL1%TYPE INDEX BY NATURAL;
TYPE COL2_TYPE IS TABLE OF TABLE1.COL1%TYPE INDEX BY NATURAL;
COL1_VAR COL1_TYPE;
COL2_VAR COL2_TYPE;
BEGIN
SELECT COL1, COL2
BULK COLLECT INTO COL1_VAR, COL2_VAR
FROM TABLE1;
END;
DECLARE
TYPE ArrayIds_Type IS TABLE OF CII_ARRAY_IN_TEMP.ARRAY_ID%TYPE
INDEX BY NATURAL;
ArrayIdsTable_in ArrayIds_Type;
BEGIN
FORALL i IN 1 .. ArrayIdsTable_in.COUNT
INSERT INTO CII_ARRAY_IN_TEMP
(SEQUENCENUM
,ARRAY_ID
,ARRAY_PASSWORD)
VALUES (var_SequenceArray(i)
,ArrayIdsTable_in(i)
,PasswordTable_in(i));
END;

19- basic oracle toolarını bilmek

Basic Oracle SQL Tools
– SET TIMING on – very useful
– EXPLAIN_PLAN, SQL_TRACE, TKPROF
– STATSPACK, V$ tables

Advertisements

About oracledocuments

Zekeriya Beşiroğlu, It joined the Bilginc IT Academy in July 2000. In the meantime, the Oracle Education, Oracle Data Base Management System and Oracle Internet Technologies, Oracle development technologies such as the training of Oracle products is responsible for training as consultants. During this task, since 1 April 2008 with 293 Oracle training in total, in 2150 Oracle customers successfully submitted. Oracle 10g New York in November 2002 have received training and education in Turkey was the first time the consultants. 20 April 2008 on education in Turkey 11g is the first who was a consultant. Oracle Real Application Cluster Expert advisor is certified is the first . Http://zekeriyabesiroglu.blogspot.com and Http://www.oracleforum.info owner and manager of the site. Its own has more than one hundred articles. In addition, creation and improvement of training materials are also related to work. Oracle products are used, database management and reporting Academy eruditely It also is working on. From the date of 1 January 2009 will continue to work as the Technical Director
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s