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,

[mitpat121]

[LEFT]desc employees
Name Null Type
EMP_ID NOT NULL NUMBER
EMP_EMP_CODE NOT NULL VARCHAR2(16 CHAR)
EMP_PRODUCT_ID NUMBER(5)
EMP_STATUS VARCHAR2(10 CHAR)
EMP_DELETED NUMBER
EMP_CREATED_DATE NOT NULL TIMESTAMP(6)
EMP_CREATED_BY NOT NULL VARCHAR2(16 CHAR)
EMP_MODIFIED_DATE TIMESTAMP(6)
EMP_MODIFIED_BY VARCHAR2(16 CHAR)

select * from employee
where status = -1;
30

select * from employee
where status = -1;
521

[vnktummala]

[LEFT]
select * from employee
where status = -1;
30

select * from employee
where status = -1;
521

different counts with same status ?? please post the actual query that you have executed.

Thanks,

[mitpat121]

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

select * from employee
where status = -1;
521

[vnktummala]

in either case it should not use Index. However, if you still want to use index by force use index hint.

Also, gather stats on this table. You might have stale stats on it.

Thanks,