TABLE ACCESS FULL - INDEX PROBLEM?

[patrickdrd]

Hi guys!

I am issuing this query on a database table that has around 12 million rows:

SELECT zone_cd, sector_cd, agent_cd, terminal_nr, device_id, occured_dt, event_id
FROM machine_events
WHERE occured_dt BETWEEN TO_DATE ('14/01/2004 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
AND TO_DATE ('14/01/2004 23:59:59', 'dd/mm/yyyy hh24:mi:ss')

and I have an index on all the select fields: zone_cd, sector_cd, agent_cd, terminal_nr, device_id, occured_dt, event_id

However, when I see the Explain Plan, I get a TABLE ACCESS FULL message?

How can this be corrected?

 

[patrickdrd]

p.s. the index is a composite one on all the fields I mentioned

 

[jaggiebunnet]

Are your statistics up to date?

You're where condition is on the occured_dt field, I don't think it needs its own index but it might be worth looking at, but check the statistics first!

 

[patrickdrd]

ok, I'm going to run:

analyze table machine_events compute statistics

 

[patrickdrd]

I'm still waiting for the command to finish.
Did I guess right though? Run this command and examine with the explain plan what happens afterwards?

 

[sem]

Patrick, why do you think that access by index would be more efficient? occured_dt field is not the first field in index. Another question, why do you need that huge index?

Regards, Dima

http://seminihin.narod.ru

 

[patrickdrd]

I'll get in contact with my administrator later,
I do not know where this huge index is used?

So, what do you suppose? An extra index on occured_dt alone?

 

[sem]

It depends on the number of different values (selectivity) in occured_dt field. If that table contains only records for several weeks FULL SCAN is better than INDEX. Actually you don't need a dedicated index for occured_dt but rather one with occured_dt at first place.

Regards, Dima

http://seminihin.narod.ru

 

[patrickdrd]

unfortunately, the selectivity of occured_dt column is very large:

7,419,128 compared to the 11,889,963 total number of rows

So, what should I try?

 

[sem]

Use full scan. What are you going to do with 7M records?

Regards, Dima

http://seminihin.narod.ru

 

[patrickdrd]

I just need this query to run more quickly, passing two substitution variables for 'from' and 'to' dates

I suggest I'll try with an additional index on occured_dt

 

[sem]

You can not speed it up by utilizing index. When you need to find 1 red ball in a box with 1000 balls of different colors you need good vision, but to take out 500 of them you need good muscles even when all 500 red balls are on top. In both cases it's a complex task.

Regards, Dima

http://seminihin.narod.ru