2008年6月13日 星期五

select * from goods_file
AS OF TIMESTAMP timestamp'2008-06-12 14:53:47'
where g_no = 11080531367948

2008年6月11日 星期三

Oracle DBCA (Database Configuration Assistant)

Template
1.Data Warehouse
2.Transaction Processing
3.General Purpose


1.Dedicated Server Mode
2.Shared Server Mode

Reset SYS , SYSTEM account default password.

Oracle Instance
1.System Global Area
SGA_MAX_SIZE , DB_CACHE_SIZE , LOG_BUFFER , SHARED_POOL_SIZE , LARGE_POOL_SIZE
2.Background processes

Data Cache Buffer
1.Write List (dirty buffers wait for write to disk)
2.LRU list (free , dirty , pinned buffers)
LRU Least Recently Used
MRU Most Recently Used

Total Size = DB_BLOCK_SIZE(2~32KB) * DB_BLOCK_BUFFERS

Redo Log Buffer (bytes)
Default : Redo Log Buffer = 4 * System Block Size

Share Pool (SHARED_POOL_SIZE = 8 MB in 32-bit , 6 MB in 64-bit system )
1. Library cache
Share SQL Area (SQL parse tree , execution plan)
Private SQL Area
PL/SQL Program Unit
2. Dictionary cache
parse information like table , view name , column name , data type
user privileges

DBWn - Database Writer
write dirty blocks from Data Cache Buffer to data files. => checkpoint
default only DBW0
btw in OLTP , update busy
DBW1~DBW9 is prefered.


LGWR - Log Writer
Write to redo log files
When
1. transaction commited
2. Auto Writing each 3 seconds.
3. free size of Redo log buffer is less than 1/3.
4. Before DBWn write dirty blocks to data files
Write - ahead protocol :
if LGWR write Redo data to redo logs finished
DBWn write dirty blocks to data files
else
Call LGWR write Redo data to redo logs
Then DBWn do its work.

SMON - System Monitor
Database Start Recovery checking
1. After Database restart , nessesary recovery activities.
2. Release not used temporary segments.
3. Collect and manage free extents
4. Recovery abort transaction.

PMON - Process Monitor
1. Recovery abort Process
2. Clear Data Buffer cache Unused block, reset transaction table status,
Unlock row , remove terminated process ID.

CKPT - Checkpoint
1. make sure MRU Dirty Data will write to data file regularly.
2. when instance recovery , can ignore the record before the currently checkpoint.
3. if(checkpoint finished)
CKPT call DBWn;
Then update control file and header of data file.
RECO (Recover)
handle transaction in Cluster database.
in-doubt transaction . transaction between servers.

ARCn (Archiver) (LOG_ARCHIVE_MAX_PROCESSES LIMIT IS 10 OR ALTER SYSTEM to change)
log switch. Archived logs.
indeed, Oracle will judge it automatically.

2008年6月9日 星期一

MySQL Index Building Tips

1. 資料種類較少 ex 男, 女 的index 在複合 index 中 放越前越好 (B-Tree divide)

2. 會按 index 建立的時間來吃, 吃到就不會繼續往下找,即使有更好的index~~

3. where date_format('A') = '20080723'
mod('A') = 5
=> 在設計之初 , 就要規劃好 避免在where 中 轉 格式.

4. where A is NULL => 會 full table scan
可以放default , ex: '0' => where A = 0 or "NULL" => where A = 'NULL'

2008年6月2日 星期一

Oracle Text

Form http://www.oracle.com/technology/products/text/index.html
Oracle Text
Oracle Text uses standard SQL to index, search, and analyze text and documents stored in the Oracle database, in files, and on the web. Oracle Text can perform linguistic analysis on documents, as well as search text using a variety of strategies including keyword searching, context queries, Boolean operations, pattern matching, mixed thematic queries, HTML/XML section searching, and so on. It can render search results in various formats including unformatted text, HTML with term highlighting, and original document format. Oracle Text supports multiple languages and uses advanced relevance-ranking technology to improve search quality. Oracle Text also offers advanced features like classification, clustering, and support for information visualization metaphors.

Oracle TimesTen In-Memory Database

http://www.oracle.com/timesten/index.html

Database Built in Memory : Oracle TimesTen