My table:

CREATE TABLE Tab1 ( ....,
pole1 NUMBER,
pole2 DATE,
pole3 DATE,
pole4 DATE,
pole5 NUMBER,
.....);


There is 10 million recs in the table Tab1.
It is indexed like this:

CREATE INDEX idx_1 ON Tab1(pole1 DESC, pole2 DESC, pole3 DESC, pole4 DESC,
pole5 DESC );

Although Explain Plan says that there select is with INDEX RANGE SCAN and
then TABLE
ACCESS BY INDEX ROWID

The selection:

SELECT * FROM A_CWA_EVENTS
WHERE pole1 = 2008
AND pole2 IS NULL
AND pole3 IS NULL
AND pole4 >= (SYSDATE - 20*(1/(24*60)))
AND pole5 < 29707735

works so slowly i cant wait till it ends (more than 5 mins).

I think it slows down due to the comparison operator grater than/ smaller
than.
When I cut out two last ANDs it last less than a second).

How to fasten that query?

S.

Rather than one huge index you might see some gains with your query with
just an index on field 1. or maybe two sperate indexes on field 1 and 5.
The other fields would likely force a full tablescan, which your response
time implies.



Doing > < on date ranges is a great way
"sliver_1" <sliver_1 (AT) poczta (DOT) onet.pl> wrote

I am interested to see if an index organized table or range partioned table
would make your performance much better?


Let us know what the results are....



"Steve Kirby" <kirby (AT) igalaxy (DOT) net> wrote