Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query on date greater than in Oracle

 
jayash samaiya
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All ,

I want to execute below query on table having 1030987 rows :


select * from OPERATION_TL where start_ts<='22-09-14 02:06:00.000000000 AM' or greater than.


Now I have tried using indexed but none are workinng .

Currently the cost of query is 17000

Can anyone please help me out .
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not a query.
What is the "or greater than" part?

But the first thing that springs to mind is, presuming start_ts is not a VARCHAR, you should not be relying on the db to do the data conversion for you as that can muck up the use of indexes.

What database is it (and version)?
What are the stats for the start_ts column?
What does the explain plan for the query show (not just the cost)?
 
jayash samaiya
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Explain plan is :

OPERATION OBJECT_NAME CARDINALITY COST

SELECT STATEMENT FR_DS_PERS_OPERATION_BASE_TL 133947 4651

Filter Predicates

START_TS<=TO_TIMESTAMP('22-09-14 02:06:00.000000000 AM')

And The Query is select * from FR_DS_PERS_OPERATION_BASE_TL where start_ts<='22-09-14 02:06:00.000000000 AM'

Also START_TS is a TIMESTAMP type field

The main question is that it is going for full scan , can any thing could be done .
 
Dave Tolls
Ranch Hand
Posts: 2110
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database?

How many records are there in the table?
How many are there with a start_ts <= that timestamp?
Does that column have an index?
 
jayash samaiya
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
DataBase is Oracle .

The Query finally returns 462724 rows (total rows are 1030987)

Yes There is a index on coulmn START_TS but it dosen't use it
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic