March 6, 2012 at 3:42 am
Hi,
I have Simple Oraclae query..
its take long time while run the query if we Don't have any data in table beetween date.
if we have data between dates.. got result...
query look like
select * from TestTable
where timestamp between TO_Date('Feb 3 2012 10:00AM', 'MM/dd/yyyy HH12:MI AM') and sysdate
as per above query , i don't have data after 3 rd feb... its took long time for geting result..
how can i decrese the execution time...
March 6, 2012 at 4:29 am
I would check the execution plan on the Oracle side and see if some index can support this query.
-- Gianluca Sartori
March 6, 2012 at 5:35 pm
mupparaju78 (3/6/2012)
I have Simple Oraclae query..its take long time while run the query if we Don't have any data in table beetween date.
if we have data between dates.. got result...
query look like
select * from TestTable
where timestamp between TO_Date('Feb 3 2012 10:00AM', 'MM/dd/yyyy HH12:MI AM') and sysdate
as per above query , i don't have data after 3 rd feb... its took long time for geting result..
how can i decrese the execution time...
Even if there is an index on "timestamp" column it will not be used because predicate includes a function e.g. TO_DATE()... also, in this particular scenario doing a between with SYSDATE at the higher end makes no sense at all, it can be re-written as "... timestamp > to_date(....)
If an index exists on "timestamp" column be sure you add a hint - asking Oracle to use it.
Also, check if performance statistics are up-to-date in both the base table and the index.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 7, 2012 at 1:41 am
PaulB-TheOneAndOnly (3/6/2012)
Even if there is an index on "timestamp" column it will not be used because predicate includes a function e.g. TO_DATE()... also, in this particular scenario doing a between with SYSDATE at the higher end makes no sense at all, it can be re-written as "... timestamp > to_date(....)
I'm not sure that's quite true. The function is on a scalar value, not a column - it will be evaluated first, then used to seek on an appropriate index, same as in SQL Server.
Agreed on checking statistics are up to date.
March 7, 2012 at 2:21 am
HowardW (3/7/2012)
PaulB-TheOneAndOnly (3/6/2012)
Even if there is an index on "timestamp" column it will not be used because predicate includes a function e.g. TO_DATE()... also, in this particular scenario doing a between with SYSDATE at the higher end makes no sense at all, it can be re-written as "... timestamp > to_date(....)I'm not sure that's quite true. The function is on a scalar value, not a column - it will be evaluated first, then used to seek on an appropriate index, same as in SQL Server.
Agreed. I tested it on my Oracle VM:
[oracle@ora-test-11-2-1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 7 10:01:03 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE toDateTest (
id int PRIMARY KEY,
dateColumn timestamp NOT NULL
);
INSERT INTO toDateTest
SELECT 1, to_date('2012/03/07', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 2, to_date('2012/03/06', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 3, to_date('2012/03/05', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 4, to_date('2012/03/04', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 5, to_date('2012/03/03', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 6, to_date('2012/03/02', 'yyyy/mm/dd') FROM dual
UNION ALL
SELECT 7, to_date('2012/03/01', 'yyyy/mm/dd') FROM dual;
CREATE INDEX IX_test ON toDateTest(dateColumn); 2 3 4
Table created.
SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
7 rows created.
SQL> SQL> SQL> SQL>
Index created.
SQL> EXPLAIN PLAN SET statement_id = 'todateplan' FOR
SELECT dateColumn
FROM toDateTest
WHERE dateColumn BETWEEN to_date('2012/03/05','yyyy/mm/dd') AND sysdate;
SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||
options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
AND statement_id = 'todateplan'
START WITH id = 0
ORDER BY id; 2 3 4
Explained.
SQL> SQL> SQL> 2 3 4 5 6 7 8 9
Rows Plan
------ -------------------------
3 SELECT STATEMENT
FILTER
3 INDEX RANGE SCAN IX_TEST
SQL>
-- Gianluca Sartori
March 7, 2012 at 2:45 am
As per my end... i don't have any chance for create index.. doing R & D how to come out from this problem with out create index..
March 7, 2012 at 2:50 am
mupparaju78 (3/7/2012)
As per my end... i don't have any chance for create index.. doing R & D how to come out from this problem with out create index..
Sorry, no way. Ask the Oracle DBA to create the index for you.
-- Gianluca Sartori
March 11, 2012 at 5:59 pm
Your query contains no linked server, therefore I conclude it is pure oracle query, nothing to do with sql server, at least for now.
You could try to use a hint, e.g. /*+RULE*/.
If you will fire this query from sql server over a linked server to oracle, try this:
EXEC('my select query with pure Oracle syntax') AT MyOracleLinkedServerName
This is much faster than using a linked server in TSQL as one would "normally" do.
Hope I helped
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply