1 有兩個表，A表800多萬，B表900多萬，這兩個表要做關聯查詢，沒有GROUP BY等操作，只是普通的查詢，如何提高速度？
[1]把能限制范圍最小的條件放在最前面,此條件對應的列加一下索引
[2]盡量把Where后面給出的條件所要用的字段建立索引,
避免使用like, 避免在where中使用is null或者is not null,
另外,先要限制從兩張表取的數據,最后才寫兩表關聯的字段(兩表連接的條件)
[3]索引是提升速度的關鍵?？梢钥紤]分成幾個查詢，每個查詢對應一個最佳的索引
[4]強制索引；少用IN，用EXISTS；可以的話，多建臨時表；
[5]技術上提高查詢辦法：建立合適的索引，避免用影響查詢速度的條件,比如in、%等，如果可以作partition的，就作partition，盡量分段查詢。
兩個大表關聯查詢，屬于業務問題：數據量巨大，本身就是很費時間的事。像移動、電信，基本上不給打印1年的帳單，就是避免數度太慢影響業務。
[6]關鍵看你做什么樣的關聯操作，where條件很重要（是否用了in/not in,like等條件），還有是否用了union（因為union all 的執行要比union快很多）
[7]關鍵字建立索引
ID采用LONG型而不采用varchar,對于海量數據的查詢可以提速很多
其它的,有待樓下進一步提供
[8]表空間分配合理,回滾段空間分配250M左右
[9]建立索引之后 分批查詢
[10]優化查詢條件,應該是比較實際的辦法,將返回的結果縮小在最小的范圍內的查詢放到前面,依次
[11]應先進行表空間的優化，回滾段的優化，索引優化，再進行查詢語句的優化
如果還是不行可試試建個視圖，建立視圖快照
2 Transitive predicate generation
如果想查出某一段時間的有效的單據中（或者某些產品）的明細資料，一般的做法：
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 = 進倉
and b.code = 有效的;
這個查詢的速度會因為這兩個表過于巨大而非常的緩慢，
現在有一個解決辦法，就是給兩個表都加上條件，即使是兩個表關聯的字段也加：
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 = 進倉
and b.io = 進倉
and b.code = 有效的;
這樣的速度要比前一種快了至少10倍以上。
人工調優，特別對于索引，條件順序。
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
總之查詢首先考慮查詢自身的優化，將記錄數盡可能減少，即where條件要盡可能充分（特別注意可推導和重復的區別），投影優先于聯接，時刻以減少聯接數為分析的主要原則。然后是對索引的分析。至于量化分析，則是對多種策略的評價的直接方法。