hi
i have a table c_table with two columns
no,
date
it contains records
no n_date
1 10-AUG-03
2 21-AUG-03
when we issue
select * from c_table where n_date='10-AUG-03';
it will successfully return the record
but when we give
select * from c_table where n_date='21-AUG-03';
it will not return the record
after using to_date function like
select * from c_table where to_date(n_date, 'DD/MM/YY')='21/08/03';
it will successfully return the records
but the main problem is that
when we have table that contains 50,000,00 records
**and then using to_date function then it will slow down the performance
how we can solve the. 2 25295
Hi,
for part of your question related to the performance, i think you could modify your query to get
select * from c_table where n_date = to_date('21/08/03','DD/MM/YY');
so the to_date function get call only once, of course if you have store the date data with differents time you still will need to
call the trunc function.
select * from c_table where trunc(n_date) = to_date('21/08/03','DD/MM/YY');
"KULJEET" <ku***********@ hotmail.com> wrote in message news:fe******** *************** ***@posting.goo gle.com... hi i have a table c_table with two columns no, date it contains records
no n_date 1 10-AUG-03 2 21-AUG-03
when we issue select * from c_table where n_date='10-AUG-03'; it will successfully return the record
but when we give select * from c_table where n_date='21-AUG-03';
it will not return the record
after using to_date function like select * from c_table where to_date(n_date, 'DD/MM/YY')='21/08/03';
it will successfully return the records but the main problem is that when we have table that contains 50,000,00 records **and then using to_date function then it will slow down the performance
how we can solve the. ku***********@h otmail.com (KULJEET) wrote in message news:<fe******* *************** ****@posting.go ogle.com>...
[] the main problem is that in when i issue --------------------- select to TO_CHAR(N_DATE, 'DD/MM/YY HH24:MI') from c_table the it will return 10/08/03 00:00 10/08/03 00:00 10/08/03 00:00 21/08/03 16:21 ---------------- so 10/08/03 date it will store time as 00:00 10/08/03 00:00
but in 21/08/03 it will store time as 16:21
21/08/03 16:21
BUT IT WILL NOT WORK select * from c_table where n_date = to_date('21/08/03','DD/MM/YY'); BUT THIS WILL WORK select * from c_table where trunc(C_date) = to_date('21/08/03','DD/MM/YY'); CAN TRUNC FUNCTION SLOW DOWN THE PERFORMANCE????
Unless you have created a matching function-based index, any function
on a column will prevent the use of indices and thus Possibly affect
performance.
The questions you need to ask yourself are:
A:Is the time portion of the date really needed?
B:Is the performance unacceptable using the various function based
solutions?
If the answer to A is NO, then update the data to truncate (or round)
the time portion away, then all your dates will be at midnight and
will match appropriately and use possible indices as the Optimizer
sees fit.
If the answer to A is YES and the answer to B is NO, then using the
trunc() doesn't hurt you case.
If A is YES and B is YES, then consider either: creating a function
based index OR adding a search date column (another column that
contains the truncated date values used only for searching and
indexing).
finally here's an untested query that should still use possible
indices and gets by the trunc():
SELECT * FROM c_table WHERE n_date BETWEEN
to_date('21/08/03','DD/MM/YY') AND to_date('21/08/03','DD/MM/YY')+1
;
(NOTE the plus one).
HTH This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Damjan |
last post by:
I'm using Python 2.4, cx_Oracle-4.1 on Linux with Oracle instant client
10.1.0.3. This is the sql string:
SQL = """insert into D.D_NOTIFY values (:CARDREF, :BANKKEY, :OK1, :OK2 \
:DEBTEUR, :DEBTDEN, to_date(:INVOICE_DATE,'DD.MM.YY'),
to_date(:PAYMENT_DEADLINE,'DD.MM.YY'), :POINTS)"""
And I'm trying to execute it as:
c = db.cursor()
c.execute(SQL, CARDREF=id, BANKKEY=dc_kluc, OK1=okd, OK2=okc,
|
by: Amin Schoeib |
last post by:
Hi,
Can somebody tell me why this query don't work when I use to_date
Instead of to_char
By using to_char:
select
to_char(last_day(cast(current_date - interval '1 month' as date)),'DD.MM.YYYY')
;
|
by: gimme_this_gimme_that |
last post by:
Assuming data is being stored in a DB2 TIMESTAMP
what is the equivalent of Oracle's to_date function :
to_date('03/04/2005','MM/DD/YYYY')
It's OK if MM/DD/YYYY is the only supported format.
Thanks.
|
by: gane kol |
last post by:
Hi
when i try to use to_date function in C# codebehind, i am getting to_date
doesnt exist in class or namespace error.
i am trying to use something similar to
INSERT INTO OracleTypesTable VALUES ( 'test', 2, to_date('2000-01-11
12:54:01','yyyy-mm-dd hh24:mi:ss'), '0001020304' )";
ref link:
|
by: ina |
last post by:
Good Morning,
My Name is Ina and I have a problem if a test in the xsl file, I am
newbie in XML transformation :)
So this is a piece of my code xml. Contracts nodes I have fund contract
<garage>
<Contracts>
<Car>
<Contracts>
| |
by: rysulliv |
last post by:
I am trying to run the query bellow, and I get a mismatched datatype error for the two "sum" computation columns. I thought that using "null" in the corresponding columns would eliminate the problem, but it does not. If I print the "sum" columns from both select statements in the same column it works, but I need them to be side by side...any ideas?
select o.market,
sum(
round(t.gross_comm * e.ex_for_per_min *...
|
by: gillian3114 |
last post by:
why this sum function won't work ?
eg Sum(personal)
lblPersonal.Caption it wont retrieve the sum value of personal
Dim cn6 As New ADODB.Connection
Dim strCNString4 As String
Dim rs6 As New ADODB.Recordset
Dim intFound6 As Double
strCNString6 = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
|
by: hydroraven |
last post by:
Hello all,
I am trying to use the TO_DATE function to convert a string to a date so that I can perform periodic maintenance on a table based upon the date.
Here is the SQL statement.
SELECT * FROM APPROVED
WHERE TO_DATE(CREATION_TIME, 'DY MON DD HH24:MI:SS TZD YYYY') > SYSDATE-30
|
by: KULJEET |
last post by:
hi
i have a table c_table with two columns
no,
date
it contains records
no n_date
1 10-AUG-03
2 21-AUG-03
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |