1 有兩個(gè)表，A表800多萬(wàn)，B表900多萬(wàn)，這兩個(gè)表要做關(guān)聯(lián)查詢，沒(méi)有GROUP BY等操作，只是普通的查詢，如何提高速度？
[1]把能限制范圍最小的條件放在最前面,此條件對(duì)應(yīng)的列加一下索引
[2]盡量把Where后面給出的條件所要用的字段建立索引,
避免使用like, 避免在where中使用is null或者is not null,
另外,先要限制從兩張表取的數(shù)據(jù),最后才寫(xiě)兩表關(guān)聯(lián)的字段(兩表連接的條件)
[3]索引是提升速度的關(guān)鍵。可以考慮分成幾個(gè)查詢，每個(gè)查詢對(duì)應(yīng)一個(gè)最佳的索引
[4]強(qiáng)制索引；少用IN，用EXISTS；可以的話，多建臨時(shí)表；
[5]技術(shù)上提高查詢辦法：建立合適的索引，避免用影響查詢速度的條件,比如in、%等，如果可以作partition的，就作partition，盡量分段查詢。
兩個(gè)大表關(guān)聯(lián)查詢，屬于業(yè)務(wù)問(wèn)題：數(shù)據(jù)量巨大，本身就是很費(fèi)時(shí)間的事。像移動(dòng)、電信，基本上不給打印1年的帳單，就是避免數(shù)度太慢影響業(yè)務(wù)。
[6]關(guān)鍵看你做什么樣的關(guān)聯(lián)操作，where條件很重要（是否用了in/not in,like等條件），還有是否用了union（因?yàn)閡nion all 的執(zhí)行要比union快很多）
[7]關(guān)鍵字建立索引
ID采用LONG型而不采用varchar,對(duì)于海量數(shù)據(jù)的查詢可以提速很多
其它的,有待樓下進(jìn)一步提供
[8]表空間分配合理,回滾段空間分配250M左右
[9]建立索引之后 分批查詢
[10]優(yōu)化查詢條件,應(yīng)該是比較實(shí)際的辦法,將返回的結(jié)果縮小在最小的范圍內(nèi)的查詢放到前面,依次
[11]應(yīng)先進(jìn)行表空間的優(yōu)化，回滾段的優(yōu)化，索引優(yōu)化，再進(jìn)行查詢語(yǔ)句的優(yōu)化
如果還是不行可試試建個(gè)視圖，建立視圖快照
2 Transitive predicate generation
如果想查出某一段時(shí)間的有效的單據(jù)中（或者某些產(chǎn)品）的明細(xì)資料，一般的做法：
select 所需字段
from detail_tab a,main_tab b
where a.prod_id = b.prod_id
and a.yyyymmdd = b.yyyymmdd
and a.paper_no = b.paper_no
and a.io = b.io
and b.yyyymmdd = 指定日期
and a.io = 進(jìn)倉(cāng)
and b.code = 有效的;
這個(gè)查詢的速度會(huì)因?yàn)檫@兩個(gè)表過(guò)于巨大而非常的緩慢，
現(xiàn)在有一個(gè)解決辦法，就是給兩個(gè)表都加上條件，即使是兩個(gè)表關(guān)聯(lián)的字段也加：
select 所需字段
from detail_tab a,main_tab b
where a.prod_id = b.prod_id
and a.yyyymmdd = b.yyyymmdd
and a.paper_no = b.paper_no
and a.io = b.io
and b.yyyymmdd = 指定日期
and a.yyyymmdd = 指定日期
and a.io = 進(jìn)倉(cāng)
and b.io = 進(jìn)倉(cāng)
and b.code = 有效的;
這樣的速度要比前一種快了至少10倍以上。
人工調(diào)優(yōu)，特別對(duì)于索引，條件順序。
3
http://www.componentace.com/help/absdb_manual/increasesqlperformance.htm
Absolute Database search and filter optimizations are based on the use of available indexes.
Always create unique (primary) indexes when possible, as the unique index allow much better optimization then non-unique index.
To improve peformance of a LIKE condition, you can create an appropriate index, but note that this index can improve performance for patterns like 'str%' only。
Available indexes for JOIN conditions。To improve a JOIN query, please check that each field from JOIN conditions has an index.
Rewriting query with OR conditions as a UNION
Available indexes for ORDER BY clause If you want to speed up a "live" SELECT from a single table with ORDER BY clause, you can create a compound index for ORDER BY fields.
Available indexes for GROUP BY clause。To get a better performance for SELECT from a single table with GROUP BY clause, you can create a compound index for GROUP BY fields.
Select from in-memory tables。Your query perofrmance could be increased also if you will move all data from disk tables to in-memory tables and you will perform a query using in-memory copies of the disk tables
4
The fastest way of inserting and updating
The fastest way of batch inserting / updating / deleting is a buffered transaction.
We recommend to call TABSDatabase.StartTransaction before bulk inserts and
TABSDatabase.Commit(False) after the end of the batch operation. The use of transaction can significantly increase performance of the batch operation
How to speed up an UPDATE query
If you are using several subqueries in an UPDATE query, you could try to transform your query like pair values, not separated.
5
Common subexpression elimination
Subquery “flattening”
Complex/simple view merging
Predicate pushdown and pullup
OR-expansion 等等。這些參考http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_general_query_optimization_10gr2_0605.pdf
總之查詢首先考慮查詢自身的優(yōu)化，將記錄數(shù)盡可能減少，即where條件要盡可能充分（特別注意可推導(dǎo)和重復(fù)的區(qū)別），投影優(yōu)先于聯(lián)接，時(shí)刻以減少聯(lián)接數(shù)為分析的主要原則。然后是對(duì)索引的分析。至于量化分析，則是對(duì)多種策略的評(píng)價(jià)的直接方法。