index not used for negative numbers

[mitpat121]

hi all,

where condition of select query uses negati

i am searching by negative number on a particular table , in this case select query scans full table
index is not utilised

If i search by positive number on same column same table
index used

plz reply

[vnktummala]

Thanks for sharing the information. What is your question here??

Thanks,

[mitpat121]

Hi all,
how can i used non unique index on column which already has non unique index
but it doesnt use

plz reply

[vnktummala]

how can i used non unique index on column which already has non unique index
but it doesnt use

I am sorry, your question is not clear to understand what exactly your issue is. Could you please be more elaborate?

Thanks,

[mitpat121]

Hi all

select query on employee table which has non unique index on status column.

select * from employee
where status = -1;

In above query full table scan happened

select * from employee
where status = 1;

In above query index range scan happened

plz provide the solution , i want to use
select * from employee
where status = -1;

thx

[vnktummala]

as this is the simple query, based on number of rows returning from the query, optimizer will decide whether to use FTS or Index scan.

If you want to force the optimizer to use Index always they use Index hint.

Also, could you please post the result from the following queries?

desc employee;
select count(*) from employee where status=1;
select count(*) from employee where status=-1;

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,