SELECT Query - InVariable Time Taken to Display Data

[surajitmitra78]

Rebuilding or analysing the index might work but i want to get into the core of the problem and understand whats the issue all about & thats the reason i need the help from you guys.

[PAVB]

As Bore said, count the rows for both lookupid = '758983' and = '666666'.

I would be surprised if there is not a huge cardinality difference in between them.

[surajitmitra78]

for 666666 # of rows in the table is 4211
for 835402 # of rows in the table is 18

PAVB and Bore can you please look at the explain plan and statistics & suggest scope of improvement.

[hrishy]

Hi

How do you gather stats and when were the stats last gathered.

regards
Hrishy

[surajitmitra78]

The statistics are gather via ANALYSE statement and this was done around 7 months back.

[PAVB]

ANALYSE? What version of Oracle are you running?

You might want to use DBMS_STATS.GATHER_TABLE_STATS with CASCADE=TRUE and GRANULARITY=ALL options.

Check and post how many rows Oracle thinks you have in your table, look at num_rows on dba_tables, that's the number of rows you have gathered stats for. Also please post sample_size and last_analyzed values.

[surajitmitra78]

i am sorry gave you wrong information about the analyse thing.

I did use dbms_stats to gather stats and the Oracle version being used is 9.2.0.7

to do a reindexing of the table along with the stats, i will have to wait till the weekend as our db is online for use during week days and that is the reason i wanted to know if there can be a solution which will make me avoid using the reindexing.

[PAVB]

Okay, I can understand that... but you are saying nothing about values of num_rows, last_analyzed and sample_size for that table.

select  *
from    dba_tables
where   table_name = 'STATUSDATA'
;

Also, if you are using normal indexes you can certainly include the ONLINE option on your ALTER INDEX REBUILD command and have your index available 100% of the time. To be able to user ONLINE option you have to have free space 2.1 times the size of your index.

Probably you can find a window to rebuild indexes (ONLINE) and gather fresh stats during the night

[abhaysk]

consider coalesce instead of rebuilding the index.

[Bore]

See, I think you should coalesce or rebuild the index. I would go for rebuild.
If there have been many DMLs against the table, it can happen that your index contains many deleted rows. They are not deleted actually, but gaps stays into the index.
Hence, while doing index range scan you probably read many of these practically empty blocks, just to find out they are empty.
This is really rare and strange case, but that's the only thing that have left to be done

Regards