Oracle
SQLLDR装载换行符的小问题
利用 sqlldr 快速上傳資料
bat 檔案:
----------------------------------------------------------------------
SqlLdr.exe "user/pass@DB_Host control='DM_DATA.ctl' errors=100000 parallel=TRUE rows=1000 bindsize=6553600"
----------------------------------------------------------------------
DM_DATA.ctl:
----------------------------------------------------------------------
load data
infile 'DM_DATA.TXT'
into table TABLE_NAME_TMP
APPEND
fields terminated by '|'
(
USER_NAME
)
----------------------------------------------------------------------
DM_DATA.txt
----------------------------------------------------------------------
AAA
BBB
CCC
DDD
----------------------------------------------------------------------
大量匯入匯出 Oracle 資料表資料
Oracle Performance
l最好的刪除重複資料方法
- nDELETE FROM FXLOTDB E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM FXLOTDB X WHERE X.LOT_ID = E.LOT_ID) - 多使用 Decode 提高 Query 時間
- l少用 *
- lcount(Lot_ID) >= count(*) >= count(1)
- l用 TRUNCATE 替代 DELETE 來刪除Table 資料
- l>= V.S <>
- l“C%” V.S “%C”
- l用 NOT EXISTS 替代 NOT IN
Oracale 錯誤編碼查詢
oracle index 查詢
Oracle 進階 by 日期展開查詢
select 'report_item' report_item,
to_char(sysdate -7,'MM/DD') ||'('|| to_char(sysdate -7,'Dy') || ')' d7,
to_char(sysdate -6,'MM/DD') ||'('|| to_char(sysdate -6,'Dy') || ')' d6,
to_char(sysdate -5,'MM/DD') ||'('|| to_char(sysdate -5,'Dy') || ')' d5,
to_char(sysdate -4,'MM/DD') ||'('|| to_char(sysdate -4,'Dy') || ')' d4,
to_char(sysdate -3,'MM/DD') ||'('|| to_char(sysdate -3,'Dy') || ')' d3,
to_char(sysdate -2,'MM/DD') ||'('|| to_char(sysdate -2,'Dy') || ')' d2,
to_char(sysdate -1,'MM/DD') ||'('|| to_char(sysdate -1,'Dy') || ')' d1
from dual
union all
select report_item,
to_char( sum(d7)) d7,
to_char( sum(d6)) d6,
to_char( sum(d5)) d5,
to_char( sum(d4)) d4,
to_char( sum(d3)) d3,
to_char( sum(d2)) d2,
to_char( sum(d1)) d1
from
(select report_item,decode(d,to_char(sysdate -7,'MM/DD'),CNT,0) d7,
decode(d,to_char(sysdate -6,'MM/DD'),CNT,0) d6,
decode(d,to_char(sysdate -5,'MM/DD'),CNT,0) d5,
decode(d,to_char(sysdate -4,'MM/DD'),CNT,0) d4,
decode(d,to_char(sysdate -3,'MM/DD'),CNT,0) d3,
decode(d,to_char(sysdate -2,'MM/DD'),CNT,0) d2,
decode(d,to_char(sysdate -1,'MM/DD'),CNT,0) d1
from
(select report_item,to_char(to_date(shift_dt,'yyyy/mm/dd'),'MM/DD') d,Level_3 CNT from f3cim.RPT_EMAP2_ESFABSUM_BT where shift_dt>= to_char(sysdate-7,'yyyy/mm/dd')
)
) group by report_item
case when
decode ( expression , search , result [, search , result]... [, default] )
任選固定資料
sSql := 'select ' +
stmp_ColumnSum +
','''' as "USER答案" ' +
' from ' +
'(' +
'(' +
'select a.* from ' +
'(' +
'select a.*,1 as ID from QUESTION_UT a ' +
'where ' +
'a.QUESTION_TYPE = ''是非'' and ' +
'a.PAPER_ID = ''' + cmb_Paper_ID02.Text + ''' and ' +
'a.DELETE_TIME is null ' +
'order by dbms_random.random ' +
') a where rownum <= ' + grd_Question_Num_Set00.Cells[ 2 , 1 ] + ' ' +
')' +
'union ' +
'(' +
'select a.* from ' +
'(' +
'select a.*,2 as ID from QUESTION_UT a ' +
'where ' +
'a.QUESTION_TYPE = ''單選'' and ' +
'a.PAPER_ID = ''' + cmb_Paper_ID02.Text + ''' and ' +
'a.DELETE_TIME is null ' +
'order by dbms_random.random ' +
') a where rownum <= ' + grd_Question_Num_Set00.Cells[ 2 , 2 ] + ' ' +
')' +
'union ' +
'(' +
'select a.* from ' +
'(' +
'select a.*,3 as ID from QUESTION_UT a ' +
'where ' +
'a.QUESTION_TYPE = ''複選'' and ' +
'a.PAPER_ID = ''' + cmb_Paper_ID02.Text + ''' and ' +
'a.DELETE_TIME is null ' +
'order by dbms_random.random ' +
') a where rownum <= ' + grd_Question_Num_Set00.Cells[ 2 , 3 ] + ' ' +
')' +
'union ' +
'(' +
'select a.* from ' +
'(' +
'select a.*,4 as ID from QUESTION_UT a ' +
'where ' +
'a.QUESTION_TYPE = ''填充'' and ' +
'a.PAPER_ID = ''' + cmb_Paper_ID02.Text + ''' and ' +
'a.DELETE_TIME is null ' +
'order by dbms_random.random ' +
') a where rownum <= ' + grd_Question_Num_Set00.Cells[ 2 , 4 ] + ' ' +
')' +
')' +
'order by ' +
'ID,QUESTION_ID ';
沒有留言:
張貼留言