index not used for negative numbers

[PAVB]

Hi
sorry
correct display is as follows
select * from employee
where status = 1;
30

select * from employee
where status = -1;
521

Is the table 551 rows in size?

As a rule of thumbs Oracle optimizer would not use an index when more than 15% of the rows have to be retrieved - it is cheaper in terms of I/O to do a full table scan.

[mitpat121]

Hi all,

how can i find stale stats on particular table.

[vnktummala]

Please make a note that if you are using Oracle 10g or above, Oracle has a inbuilt scheduled maintenance job which collect the stats for the required objects as and when needed.

However, follow the below steps to check.

1. count the rows in the table (select count(*) from table)
2. check last_analyzed_date, num_rows from user_tables. num_rows should match with count(*)

I am not sure if there are any dictionary views available to find the same.

Thanks,

[Mohith123]

looks like There are more record with status=-1.So optimizer is using FTS.
Since no of records with status=1 is less optimizer is using Index scan.

[mitpat121]

hi all,

I am facing performance issue, fornt end these queries are used, it takes 4-5 minutes to load module application page. erlier it was taking max 30 seconds.

plz provide some suggetions

thx

[Mohith123]

find if stats for table is stale

use dba_tab_statistics table

[vnktummala]

Thanks for shedding more light Mohith. STALE_STATS column in ALL_TAB_STATISTICS is telling about the stale stats.

Thanks,

[mitpat121]

hi all,

i checked dba_tab_statistics for stale stats
but STALE_STATS column contain no and null values.
null value for global temporary table what is it mean and
one more thing i noticed global temporary table has never been analyzed.

plz provide suggetions
thx

[vnktummala]

hi all,

i checked dba_tab_statistics for stale stats
but STALE_STATS column contain no and null values.
null value for global temporary table what is it mean and
one more thing i noticed global temporary table has never been analyzed.

plz provide suggetions
thx

that is quite obvious. They are Global TEMPORARY Tables. Please google it for more information.

Thanks,

[jpsaccount]

I second Mohit123, the optimizer algorithm would find it easier to use an index on the table to find +1 because they are much lesser in number and probably distributed throughout the table and would not use an index if its -1 because its easier to pick the whole thing into memory and find what is required instead of index scan and iterations.

If you really want to test out the optimizer working, you should make this a much bigger table in a test environment with similar distribution of data. It is then likely to use an index in both cases.