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 out in making sure that the indexes are always used even in the case of using bind variables.
Thanks.
3 4596
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 out in making sure that the indexes are always used even in the case of using bind variables.
Thanks.
How is the data being used? How many rows in the table? In general if >= 20% of rows in a table are going to be needed it will be faster for a full table scan than an index search. If your stats on the table are refreshed often then the optimizer will make the best possible decisions.
string bind variables behave differently than number bind variables as far as hard parses are conerned. If you are concerned about bind variable usage in a query try replacing the binds with actual values and see if the plan changes much.
Indexes do not always mean faster queries, it depends on the table and amount of data to be used within the table.
Please find a related discussion here .
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: -
-
SELECT *
-
FROM HISTORY_VIEW_NOW
-
WHERE begin_inst <= :endTime
-
AND end_inst >= :beginTime
-
AND family = 'ABC';
-
-
Explain plan:
-
-
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 3 3396
-
HASH JOIN 3 441 3396
-
HASH JOIN 6 444 103
-
TABLE ACCESS BY INDEX ROWID USER1.TABLE1 4 180 4
-
INDEX RANGE SCAN USER1.ENT_IDX1 4 2
-
VIEW USER1.MNT_TABLE1 1 K 52 K 99
-
UNION-ALL
-
HASH JOIN OUTER 630 25 K 6
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
TABLE ACCESS FULL USER1.TABLE2 507 14 K 1
-
HASH JOIN 107 2 K 44
-
VIEW 107 1 K 39
-
SORT UNIQUE 107 1 K 39
-
INDEX FAST FULL SCAN USER1.TABLE2_U01 507 5 K 1
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
HASH JOIN 503 16 K 45
-
TABLE ACCESS FULL USER1.TABLE1 630 7 K 4
-
VIEW 503 10 K 40
-
SORT UNIQUE 503 14 K 40
-
TABLE ACCESS FULL USER1.TABLE2 503 14 K 1
-
TABLE ACCESS FULL USER1.TABLE1 629 11 K 4
-
VIEW 4 K 304 K 3292
-
UNION-ALL
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 101 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 74 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
FILTER
-
TABLE ACCESS FULL USER1.TABLE3 51 1 K 3
-
FILTER
-
TABLE ACCESS FULL USER1.TABLE3 38 1 K 3
-
Without using bind variables in the query and very fast execution: (I am using the same values in this query as used for the bind variables above)
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop -
-
SELECT STATEMENT Optimizer Mode=CHOOSE 1 120
-
HASH JOIN 1 147 120
-
HASH JOIN 6 444 103
-
TABLE ACCESS BY INDEX ROWID user1.table1 4 180 4
-
INDEX RANGE SCAN user1.ENT_IDX1 4 2
-
VIEW user1.MNT_table1 1 K 52 K 99
-
UNION-ALL
-
HASH JOIN OUTER 630 25 K 6
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
TABLE ACCESS FULL user1.table2 507 14 K 1
-
HASH JOIN 107 2 K 44
-
VIEW 107 1 K 39
-
SORT UNIQUE 107 1 K 39
-
INDEX FAST FULL SCAN user1.table2_U01 507 5 K 1
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
HASH JOIN 503 16 K 45
-
TABLE ACCESS FULL user1.table1 630 7 K 4
-
VIEW 503 10 K 40
-
SORT UNIQUE 503 14 K 40
-
TABLE ACCESS FULL user1.table2 503 14 K 1
-
TABLE ACCESS FULL user1.table1 629 11 K 4
-
VIEW 1 K 128 K 16
-
UNION-ALL
-
TABLE ACCESS BY INDEX ROWID user1.history_table 4 192 5
-
INDEX RANGE SCAN user1.OEE_H_END_IDX 4 3
-
TABLE ACCESS BY INDEX ROWID user1.history_table 4 152 5
-
INDEX RANGE SCAN user1.OEE_H_END_IDX 4 3
-
FILTER
-
TABLE ACCESS FULL user1.table3 1 K 39 K 3
-
FILTER
-
TABLE ACCESS FULL user1.table3 769 21 K 3
-
Note that the cost for the query that uses the bind variables is much higher than the cost of the query without bind variables. I also tried the same query with the underlying table alone and still the execution time was long.
The modified query with the base table of the HISTORY_VIEW_NO W view is below. -
-
SELECT *
-
FROM HISTORY_TABLE
-
WHERE begin_inst <= :endTime
-
AND end_inst >= :beginTime
-
-
--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 2 K 1643
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 2 K 110 K 1643
-
INDEX RANGE SCAN USER1.OEE_H_BEGIN_IDX 7 K 25
-
-
--The modified query without bind variables (and using the same values as used ---for the bind variables) produced the explain plan below.
-
-
--Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
-
-
SELECT STATEMENT Optimizer Mode=CHOOSE 4 5
-
TABLE ACCESS BY INDEX ROWID USER1.HISTORY_TABLE 4 208 5
-
INDEX RANGE SCAN USER1.OEE_H_END_IDX 4 3
-
Any pointers to this problem would be very helpful.
Thanks.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Steve_CA |
last post by:
Hi,
The more I read, the more confused I'm getting ! (no wonder they say
ignorance is bliss)
I just got back from the bookstore and was flipping through some SQL Server
Administration books.
One says, that to get the best query performance, youi do two things:
|
by: Igor |
last post by:
Is there a way to force optimizer to use indexes without hints? (some
server setting or index type...)
I'll give an example to clarify :
I have a table with fields
Customer_Code char(10) not null
Invoice_Number int not null
and an index on those fields IX_1.
there are about 2,000,000 records in the table and those two fields are
|
by: pranab_bajpai |
last post by:
So I want to define a method that takes a "boolean" in a module, eg.
def getDBName(l2):
....
Now, in Python variables are bound to types when used, right?
Eg.
x = 10 # makes it an INT
whereas
|
by: Mike Leahy |
last post by:
Hello all,
I have a question related to the use of indexes. One of my tables is part of
a census, with over 2.5 million records in it. Clearly, just about any query
takes a rather long time to complete. I was hoping to reduce the amount of
time by using indexes. However, no matter what I do, PostgreSQL never seems
to use them. It would seem to make sense that if I calculate something
grouped by a set of variables that have an index...
|
by: bughunter |
last post by:
For example - very large table with a lot indexes and updates work very
slow. I'd like found unused or rarely used indexes. How? Yes, for
static I found all sql in packages and, after analyse, drop/recreate
some indexes. But for dynamic I have no idea. Only drop all and monitor
RR :-) Inhumanely, IMHO
Andy
| |
by: WindAndWaves |
last post by:
Hi Everyone
Is there anyone who has some sound rules of thumb for using indexes.
I see that, for example, access automatically adds them to linked tables,
but I feel, they are probably of more use in text fields (for sorting
purposes), etc....
Keen to hear your ideas.
|
by: Jeff |
last post by:
This is the first time that I remember ever having too many indexes on a
table, but it has happened.
I have en employees table and store in many places, on tables, the id of the
employee that performed some action. Yes, I know, that could be in an audit
trail but it isn't. For example, who printed a sales order, who processed it
etc is stored on the sales orders table.
Well, I have run out of indexes on the employees table when trying...
|
by: umair.cheema |
last post by:
Hi!
1-I read in MS Access help that Indexes are automatically made when we
declare a primary key and they are used for fast searching and sorting
purpose. But i am still confused that how can i use Indexes,where i can
see the in my table? and if i want to make my own indexes, should i use
nique attribute column such as vehicle registration number etc?
2-Another thing i wanna ask is; in MS access when we make a query, Is
it a view also?...
|
by: MMcCarthy |
last post by:
The more data you include in your tables the more you will need to have indexes to search and sort that data. However, there is a balance between having enough indexes and too many. Too many indexes will slow down the speed of updates on your records.
Access presets a number of Indexes for you. If you look in Tools ... Options under the Tables/Queries tab you will see that under "Auto Index on Import/Create" there is a list as follows:
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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: 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: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |