oracle 9i。 我有一个大table 叫 TA 吧, 7,8 个million record 吧。 table 有primary key。 由好几个column 组成。就叫 (col1,col2,col3,col4, col5,col6) 吧。 当我运行如下query 时,非常慢,要十几分钟。 1) select count(*) from TA a where a.col1=601 但当我运行下面的query 是 却非常快,十几秒就行了。 2) select count(*) from TA a where a.col1=601 and a.col2 like 'ABC%' 为什么呢? 有什么办法可以让 1) 也运行的快点吗?
p*m
4 楼
You were fooled.
c*t
5 楼
check out your execution plan. It seems the first query does full table scan.
【在 m*****i 的大作中提到】 : oracle 9i。 : 我有一个大table 叫 TA 吧, 7,8 个million record 吧。 : table 有primary key。 由好几个column 组成。就叫 (col1,col2,col3,col4, : col5,col6) 吧。 : 当我运行如下query 时,非常慢,要十几分钟。 : 1) select count(*) from TA a where a.col1=601 : 但当我运行下面的query 是 却非常快,十几秒就行了。 : 2) select count(*) from TA a where a.col1=601 and a.col2 like 'ABC%' : 为什么呢? 有什么办法可以让 1) 也运行的快点吗?
If it takes more then 10 minutes, you can try to use dynamic_sampling hint and set level >= 3 to get the optimal execution plan.
v*r
29 楼
”col1 is indexed“ is not a guarantee that index will be used. For example, if there are many rows equals 601, Oracle will not use index, why? because full table scan will be considered cheaper/faster in that case (Assuming the statistics are accurate). Anyway, a quick diff of two execution plans will tell you why one is slower and one is faster.
v*r
30 楼
”col1 is indexed“ is not a guarantee that index will be used. For example, if there are many rows equals 601, Oracle will not use index, why? because full table scan will be considered cheaper/faster in that case (Assuming the statistics are accurate). Anyway, a quick diff of two execution plans will tell you why one is slower and one is faster.
v*r
31 楼
”col1 is indexed“ is not a guarantee that index will be used. For example, if there are many rows equals 601, Oracle will not use index, why? because full table scan will be considered cheaper/faster in that case. Anyway, a quick diff of two execution plans will tell you why one is slower and one is faster.
n*r
32 楼
1. tell us oracle version 2. check statistics, check table row num 3. post the execution plan most likely it goes either FTS or Index skip scan.
e*e
33 楼
do the following and post the plan : 1. explain plan for select count(*) from TA a where a.col1=601; 2. set lines 140 pages 1000; 3. check the plan @?/rdbms/admin/utlxpls; Do you see full table scan? If so, try this: 1. explain plan for select count(*) from TA a where a.col1=601 and a.col2 like 'ABC%' 2. check the plan @?/rdbms/admin/utlxpls; Do you see index access path? If so, use hint to enable index access path used in the second query. explain plan for select /*+ index(TA index_name) */ cou