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 < street_id =" 11;" street_id =" 12;" street_id =" 14;" street_id =" :street_id;" street_id =" :street_id;" street_id =" :street_id;"> 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
Hiç yorum yok:
Yorum Gönder