index not used for negative numbers

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

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

Thanks,

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

plz reply

Quote:

---

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,

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

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,

[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

Quote:

---

Originally Posted by mitpat121

[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,

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

select * from employee
where status = -1;
521

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,

Quote:

---

Originally Posted by mitpat121

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.

Hi all,

how can i find stale stats on particular table.

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,

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.

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

find if stats for table is stale

use dba_tab_statistics table

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

Thanks,

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

Quote:

---

Originally Posted by 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

---

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

Thanks,

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.

Quote:

---

Originally Posted by 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.

---

so... you are basically agreeing with my July 21st post, huh? :cool: