[1] SELECT * FROM EMP
WHERE NAME = 'SMITH' ===== CORRECT
WHERE JOB_TITLE = 'CLEARK' ===== WRONG
WHERE GENDER = 'MALE' ===== WRONG
INDEX: Ruin Performance if more then 10% of rows of the index column is poor.
[2] ARRANGE TABLE
Table should be arrange from smallest effective # of rows to largest effective # of rows.
SELECT /* ordered */ D.NAME,E.NAME
FROM DEPT D,EMP E
WHERE D.MGR = E.SS ==================== CORRECT
SELECT /* ordered */ D.NAME,E.NAME
FROM EMP E,DEPT D
WHERE D.MGR = E.SS ==================== WRONG
[3] If Column use/accept NULL then it will not use the Index.
[4] HASH JOIN ==== Faster then ==== NEXTED LOOP
HASH JOIN = Effective when more memory intensive then nexted loop.
You need to set HASH_AREA_SIZE or PGA_AGGREGATE_TARGET
HINT = USE_HASH(T1,T2)
[5] SELECT COUNT(1) FROM T1 ====== BOTH ARE SAME
SELECT COUNT(*) FROM T1 ====== BOTH ARE SAME
SELECT NULL AS A FROM DUAL UNION
SELECT 'ABC' FROM DUAL
OUTPUT : 2 <--- TWO ROWS
SELECT COUNT(A) FROM (=====ABOVE QUERY=======)
OUTPUT : 1 <--- Row (Null not include)
Note : Count will not include the null value.
[6] Nester Loop
Use when joining small number of rows
use_nl(t1,t2) <---------- hint.
[7] Sort merge Join
=> use when source sorted already and
=> sort operation (order by) does not have to be done.
[8] use select col1,col2 from tbl1
Instead select * from tbl1.
[9] Exec dbms_stats.gather_table_stats(
'schema_name',
'table_name',
estimate_percent =>100,
degree=>4,
cascade=>true);
[10] cluster table
cluster index
=> Determne the physical order of data in a table.
=> Only one cluster index per table.
[11] composit index
=> contain more then one field called composit index.
=> Effective when
All field combined will give significantly less rows then the first field alone.
child_age ====|
parent_age ====| <------- not work
child_name ====|
parent_name ====| <------- work
[12] GTT (Global Temperory Table - Index)
=> Session Specific (Data auto delete on session end)
=> Index can be created on GTT table.
Scope of index is same as session.
Run time index created.
=> Data store in TEMP SEGMENT in TEMP TABLESPACE.
=> On commit delete row.
= Delete data end of the transaction.
=> On commit preserve row.
= delete data end of the session.
[13] Large Data + Index
=> Drop index before bulk loading data.
-> Large data p Drop index
-> nsert become Faster
-> Recreate Index and analyze table.
[14] Force View.
Create ore replace force view testview
as Select * from none_existant_view.
with Force option you can create a view if base table does not exist.
[15] NVL2
NVL2(string2,value_if_not_null,Value_if_null)
[16] PURGE
=> Remove from recyclebin.
=> Can not rollback PURGE statement.
To see recyclebin content.
select * from recyclebin <--- synonym
select * from user_recyclebin <--- synonym
Both statement gives same rows.
Use
=> PURGE table test; <---To bypass recyclebin.
=> DROP TABLE table1 PURGE; <--- Use it.
=> To Remove/make an empty recyclebin all objects.
PURGE RECYCLEBN;
[17] FORALL ..... DELETE
FORALL ..... UPDATE ..... <--- use for batter performance.
[18] parallel hint.
If you have 36 processor, the full scane can run 35 time faster.
[19] Nologging
[20] Explain Plan
=> CBO = Cost based optimization.
=> COST : Represent the estimated resource usage for an execution.
Resource : [1] I/O, CPU, MEMORY
[2] Number of row returned.
=> BYTE : Number of bytes returned by the operation.
=> CARDINALITY : How many unique value in a column of table.
WHERE NAME = 'SMITH' ===== CORRECT
WHERE JOB_TITLE = 'CLEARK' ===== WRONG
WHERE GENDER = 'MALE' ===== WRONG
INDEX: Ruin Performance if more then 10% of rows of the index column is poor.
[2] ARRANGE TABLE
Table should be arrange from smallest effective # of rows to largest effective # of rows.
SELECT /* ordered */ D.NAME,E.NAME
FROM DEPT D,EMP E
WHERE D.MGR = E.SS ==================== CORRECT
SELECT /* ordered */ D.NAME,E.NAME
FROM EMP E,DEPT D
WHERE D.MGR = E.SS ==================== WRONG
[3] If Column use/accept NULL then it will not use the Index.
[4] HASH JOIN ==== Faster then ==== NEXTED LOOP
HASH JOIN = Effective when more memory intensive then nexted loop.
You need to set HASH_AREA_SIZE or PGA_AGGREGATE_TARGET
HINT = USE_HASH(T1,T2)
[5] SELECT COUNT(1) FROM T1 ====== BOTH ARE SAME
SELECT COUNT(*) FROM T1 ====== BOTH ARE SAME
SELECT NULL AS A FROM DUAL UNION
SELECT 'ABC' FROM DUAL
OUTPUT : 2 <--- TWO ROWS
SELECT COUNT(A) FROM (=====ABOVE QUERY=======)
OUTPUT : 1 <--- Row (Null not include)
Note : Count will not include the null value.
[6] Nester Loop
Use when joining small number of rows
use_nl(t1,t2) <---------- hint.
[7] Sort merge Join
=> use when source sorted already and
=> sort operation (order by) does not have to be done.
[8] use select col1,col2 from tbl1
Instead select * from tbl1.
[9] Exec dbms_stats.gather_table_stats(
'schema_name',
'table_name',
estimate_percent =>100,
degree=>4,
cascade=>true);
[10] cluster table
cluster index
=> Determne the physical order of data in a table.
=> Only one cluster index per table.
[11] composit index
=> contain more then one field called composit index.
=> Effective when
All field combined will give significantly less rows then the first field alone.
child_age ====|
parent_age ====| <------- not work
child_name ====|
parent_name ====| <------- work
[12] GTT (Global Temperory Table - Index)
=> Session Specific (Data auto delete on session end)
=> Index can be created on GTT table.
Scope of index is same as session.
Run time index created.
=> Data store in TEMP SEGMENT in TEMP TABLESPACE.
=> On commit delete row.
= Delete data end of the transaction.
=> On commit preserve row.
= delete data end of the session.
[13] Large Data + Index
=> Drop index before bulk loading data.
-> Large data p Drop index
-> nsert become Faster
-> Recreate Index and analyze table.
[14] Force View.
Create ore replace force view testview
as Select * from none_existant_view.
with Force option you can create a view if base table does not exist.
[15] NVL2
NVL2(string2,value_if_not_null,Value_if_null)
[16] PURGE
=> Remove from recyclebin.
=> Can not rollback PURGE statement.
To see recyclebin content.
select * from recyclebin <--- synonym
select * from user_recyclebin <--- synonym
Both statement gives same rows.
Use
=> PURGE table test; <---To bypass recyclebin.
=> DROP TABLE table1 PURGE; <--- Use it.
=> To Remove/make an empty recyclebin all objects.
PURGE RECYCLEBN;
[17] FORALL ..... DELETE
FORALL ..... UPDATE ..... <--- use for batter performance.
[18] parallel hint.
If you have 36 processor, the full scane can run 35 time faster.
[19] Nologging
[20] Explain Plan
=> CBO = Cost based optimization.
=> COST : Represent the estimated resource usage for an execution.
Resource : [1] I/O, CPU, MEMORY
[2] Number of row returned.
=> BYTE : Number of bytes returned by the operation.
=> CARDINALITY : How many unique value in a column of table.