Oracle Database: I have a table with 400000 rows. I create an index for field1. The following query is still very slow (700ms):
select field1, field2
from table
where
field1 = '0903400110106156' or
field1 = '0903400110106160' or
field1 = '0903400110106190' or
field1 = '0903400110106471' or
field1 = '0903400110106480' or
field1 = '0903400110106494' or
field1 = '0903500110100001' or
field1 = '0903500110100012' or
field1 = '0903500110100021' or
field1 = '0903500110100031' or
field1 = '0903500110100039' or
field1 = '0903500110100047' or
field1 = '0903500110100050'
I drop the index and I still get 700ms. I create the index again and I still get 700ms. What is wrong???
The create index statement:
CREATE INDEX myindex
ON table (field1)
EDIT: Explain Plan
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 60 | 2 |
|* 1 | TABLE ACCESS FULL | table | 4 | 60 | 2 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("table"."field1"='0901690339400674')
Note: cpu costing is off
EDIT2: OK, I did a table analysis for 2 tables. table1 has the speed problem. table2 is a lot faster (10msec instead of 700msec) although its size is similar to table1. Strange speed problem!!! Please help...
chk Owner Name Partition Subpartition Tablespace NumRows Blocks EmptyBlocks AvgSpace ChainCnt AvgRowLen AvgSpaceFLBlocks NumFLBlocks UserStats GlobalStats LastAnalyzed SampleSize Monitoring Status PartType PartInfo IsExternal
TRUE user table1 tablespace 4846开发者_开发知识库27 6858 182 878 777 103 NO NO 15/3/2011 18:34 125977 NO Normal, Successful Completion FALSE
TRUE user table2 tablespace 366159 6480 176 786 16565 130 NO NO 15/3/2011 18:34 89657 NO Normal, Successful Completion FALSE
The question is a bit old meanwhile but since I had the same problem right now and no answer worked for me here is the correct answer which is missing in my opinion. After creating an index use following statement to tell Oracle that there is a new index and it has to use it whenever possible.
ANALYZE TABLE <tablename> COMPUTE STATISTICS;
As an alternative you can tell Oracle to compute statistics just in the moment you create a new index by:
CREATE INDEX myindex ON table (field1) COMPUTE STATISTICS;
try
where
field1 in ('0903500110100050', '0903500110100050', .. )
It doesn't matter whether or not you have an index on field1. The database has to read the entire table before it can satisfy an OR condition (or an IN predicate) on the WHERE clause.
It would be faster to put an index on field1 and request field2 for one field1 at a time (WHERE field1 = :value) in a loop.
Try an index on (field1, field2)
CREATE INDEX myindex ON table (field1, field2)
OK, Solved.
Yesterday before I leave work to go home I did a last try and my query was still slow (no index use).
Today in the morning before I do anything else I did another try and suddenly the index works and I get only 10 msec.
If you have any explanation please post it.
I am adding the last explain plan:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| table | 1 | 23 | 4 |
|* 2 | INDEX RANGE SCAN | myIndex | 1 | | 3 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("table"."field1"='0901690339400674')
Note: cpu costing is off
精彩评论