- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Problem via date and time field in Oracle HP U...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-27-2009 01:37 AM
тАО02-27-2009 01:37 AM
I tried to look for particular data but realized that our date field is time included.
Example :
Select count(*) from CDR where date='26-feb-2009';
The result :
count(*)
5
It will only count the row with date 26-feb-2009 12:00 AM, and not the others row. The date format look like this
Date
-------
26-feb-2009 12:00 AM
This is wrong because we have tons of CDR, something like 5000 or more for an hour.
and I tried to used
Select count(*) from CDR where date='26-feb-2009%';
or
Select count(*) from CDR where date like '26-feb-2009%';
Both took really long time to response!
Now, please anyone can guide how to select the date field which it will count the whole date for 24 hours ?
Sorry for my bad english :)
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-27-2009 01:55 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО02-27-2009 01:55 AM
тАО02-27-2009 01:55 AM
Re: Problem via date and time field in Oracle HP UX
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2009 01:09 AM
тАО03-05-2009 01:09 AM
Re: Problem via date and time field in Oracle HP UX
the correct way would be:
Select count(*) from CDR where trunc(date)='26-feb-2009';
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО03-05-2009 03:52 AM
тАО03-05-2009 03:52 AM
Re: Problem via date and time field in Oracle HP UX
Laurent>> string matching is always very costly.
A little more CPU, but is would not cause 'a really long time to respond'
There must have been an index on that column, and this particular string match cause Oracle no longer to be able to use the index. The reason is that the algorithm for this string match is surely a sub-string function. Well, imagine the query had been "like 26%" or "like 26%-feb%". Clearly for those queries Oracle must read all rows.
You and I know that when the full date is specified, that it could have used the (binary) date column, but apparently the optimizer does not realize this subtle threshhold because it is thinking string, not date.
fwiw,
Hein.