I have given the query details below. The oracle version used in Oracle 9.2. The details about this query are that HISTORY_VIEW_NO W is a view and it is very large. The columns begin_inst and end_inst have indexes on them.
Using bind variables in the query and very slow execution:
[code=oracle]
SELECT *
FROM HISTORY_VIEW_NO W
WHERE begin_inst <= :endTime
AND end_inst >= :beginTime
...
skaushik
Last Activity: Mar 19 '08, 10:45 PM
Joined: Feb 28 '08
Location:
-
-
Indexes not being used when using bind variables
Hi all,
I ran an explain plan for a sql query which has some bind variables used in it. The plan shows that index is being used but the execution takes a very long time. Also, in another query the use of bind variables totally discards the usage of indexes and the explain plan also does not show the use of indexes.
Any idea how the optimizer behaves when using bind variables? It would be great if someone can help me... -
Running asynchronous processes in Oracle
Hi all,
I am using Oracle 9.2 version and I had to run a stored procedure asynchronously. I was researching a little bit on this and found that Oracle jobs was a way to do it. Is this the only way or is there a way to run a stored procedure asynchronously?
Also, I had a question in using DBMS_JOBS package. Currently, if I decide to use jobs, I need to log new jobs every 6 minutes and the original transaction which logged... -
Thanks. The discussion in that thread was very useful. I am trying out the different suggestions to see if it helps to use the index. Let me know if you can think of some reason too for the query above....Leave a comment:
-
SQL query not using index and explain plan gives very high cost
Hi all,
I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table (columnA) but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below
select * from history_table ht, small_table st
where...Leave a comment:
-
SQL query not using index and explain plan gives very high cost
Hi all,
I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table column but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below
select * from history_table ht, small_table st
where ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;...
No activity results to display
Leave a comment: