Hi Everyone,
I'm having trouble convincing myself that Oracle is executing a query
of mine as efficiently as possible, and I'm looking for a little
guidance.
The situation is pretty simple. I just have two tables, PARENT and
CHILD.
PARENT(
pkey int primary key,
value int,
vdate date
)
CHILD(
pkey int references PARENT(pkey),
cdata VARCHAR2
)
In this simple example, I have indices on every column except cdata.
The query is:
select CHILD.*
from CHILD, PARENT
where PARENT.value=10 0
and CHILD.pkey=PARE NT.pkey
and PARENT.vdate between date1 and date2
An explain plan shows that this query always does a FTS on CHILD, and
I can't really see why. I guess I don't really understand the
optimizer well, but it would seem like a FTS isn't necessary because
of the index on CHILD.pkey. In fact, if I change the * to just
CHILD.pkey, the index is used as expected.
This is in Oracle 9.2.0.3. Both tables and all their indices have been
analyzed, and CHILD has approximately 1M rows in it, while PARENT has
about 200K. The whole thing's running on Windows 2000.
I'd appreciate any insights you may have,
ry 4 15540
Try giving it a HINT:
select /*+ INDEX(CHILD pkey) */ CHILD.*
from CHILD, PARENT
where PARENT.value=10 0
and CHILD.pkey=PARE NT.pkey
and PARENT.vdate between date1 and date2;
--
Posted via http://dbforums.com
evidently the optimizer has decided the full table scan is faster than the
indexes for this query
your selection criteria is on the smaller table -- the traditional rule of
thumb for using an index is 2% or less of rows returned. likely, your
parent.value and parent.vdate predicates are not very selective
oracle is pretty fast at full table scans, all things being equal, with
multiblock reads and such.
consider how you'd have to do this 'manually' -- is i'm looking for a
reasonable large number of occurrences of a value in a book, do i want to
flip thru the pages (since i'm a good speed reader) or do i want to flip
back and forth from the index, often visiting the same page (i.e. data
block) multiple times
if you had some selection criteria on the CHILD table, you'd more likely see
index usage
--
Mark C. Stock
email mcstock -> enquery(dot)com www.enquery.com
(888) 512-2048
"ryan" <ry*******@yaho o.com> wrote in message
news:29******** *************** ***@posting.goo gle.com... Hi Everyone, I'm having trouble convincing myself that Oracle is executing a query of mine as efficiently as possible, and I'm looking for a little guidance.
The situation is pretty simple. I just have two tables, PARENT and CHILD.
PARENT( pkey int primary key, value int, vdate date )
CHILD( pkey int references PARENT(pkey), cdata VARCHAR2 )
In this simple example, I have indices on every column except cdata. The query is:
select CHILD.* from CHILD, PARENT where PARENT.value=10 0 and CHILD.pkey=PARE NT.pkey and PARENT.vdate between date1 and date2
An explain plan shows that this query always does a FTS on CHILD, and I can't really see why. I guess I don't really understand the optimizer well, but it would seem like a FTS isn't necessary because of the index on CHILD.pkey. In fact, if I change the * to just CHILD.pkey, the index is used as expected.
This is in Oracle 9.2.0.3. Both tables and all their indices have been analyzed, and CHILD has approximately 1M rows in it, while PARENT has about 200K. The whole thing's running on Windows 2000.
I'd appreciate any insights you may have, ry ry*******@yahoo .com (ryan) wrote in message news:<29******* *************** ****@posting.go ogle.com>... Hi Everyone, I'm having trouble convincing myself that Oracle is executing a query of mine as efficiently as possible, and I'm looking for a little guidance.
The situation is pretty simple. I just have two tables, PARENT and CHILD.
PARENT( pkey int primary key, value int, vdate date )
CHILD( pkey int references PARENT(pkey), cdata VARCHAR2 )
In this simple example, I have indices on every column except cdata. The query is:
select CHILD.* from CHILD, PARENT where PARENT.value=10 0 and CHILD.pkey=PARE NT.pkey and PARENT.vdate between date1 and date2
An explain plan shows that this query always does a FTS on CHILD, and I can't really see why. I guess I don't really understand the optimizer well, but it would seem like a FTS isn't necessary because of the index on CHILD.pkey. In fact, if I change the * to just CHILD.pkey, the index is used as expected.
This is in Oracle 9.2.0.3. Both tables and all their indices have been analyzed, and CHILD has approximately 1M rows in it, while PARENT has about 200K. The whole thing's running on Windows 2000.
I'd appreciate any insights you may have, ry
Ryan, it would have been nice to see the actual explain plan. I am
not sure from your post if Oracle is driving on the child table or
performing the FTS in a loop.
Based on the way the query is written I would guess that Oracle should
drive on the Parent table entering it with either the index on value
or the index on the date range. Value appears to be a constant. If
this is true and if 100 is selective then a histogram on this column
might be useful if Oracle did not chose to drive on this. The access
by vdate would appear to use bind variables so Oracle does not know
what percentage of the table will have to be scanned.
You did not tell us what type of join Oracle chose to do? Did Oracle
chose a hash join? That would pretty much require a FTS of child if
Oracle drove on Parent.
HTH -- Mark D Powell -- ry*******@yahoo .com (ryan) wrote in message news:<29******* *************** ****@posting.go ogle.com>... Hi Everyone, I'm having trouble convincing myself that Oracle is executing a query of mine as efficiently as possible, and I'm looking for a little guidance.
The situation is pretty simple. I just have two tables, PARENT and CHILD.
PARENT( pkey int primary key, value int, vdate date )
CHILD( pkey int references PARENT(pkey), cdata VARCHAR2 )
In this simple example, I have indices on every column except cdata. The query is:
select CHILD.* from CHILD, PARENT where PARENT.value=10 0 and CHILD.pkey=PARE NT.pkey and PARENT.vdate between date1 and date2
An explain plan shows that this query always does a FTS on CHILD, and I can't really see why. I guess I don't really understand the optimizer well, but it would seem like a FTS isn't necessary because of the index on CHILD.pkey. In fact, if I change the * to just CHILD.pkey, the index is used as expected.
This is in Oracle 9.2.0.3. Both tables and all their indices have been analyzed, and CHILD has approximately 1M rows in it, while PARENT has about 200K. The whole thing's running on Windows 2000.
I'd appreciate any insights you may have, ry
Hi again,
Thanks for all the advice. My solution to the problem was to add a
hint forcing the index on CHILD.pkey to be used. Deleting the
statistics on the table had the same effect. I'm just starting the
load process, and it's also possible that my selection criteria wasn't
selective enough. Once I get more data in the tables, I re-enable the
CBO and see if it does a better job. Until then, thanks again,
ry This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Carlos Ibarra |
last post by:
In various places I have seen mentioned the importance of indexing
foreign keys to avoid table locks on the child table on parent
update/delete and full table scans when the constraint has action ON
DELETE CASCADE. For example, see
http://asktom.oracle.com/pls/ask/f?p=4950:8:10827638093976934265::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:292016138754,
However, if I have no index on the FKs, the only mention of full table
scans on the...
|
by: Marcus |
last post by:
Hi
I have a very complex sql query and a explain plan. I found there is a
full table scan in ID=9
9 8 TABLE ACCESS (FULL) OF 'F_LOTTXNHIST'
(Cost=84573 Card=185892 Bytes=7063896)
How can I correlate which part of the SQL statement is running on full
table scan. Please see below for the code and explain plan
SQL Code
|
by: talfano |
last post by:
Hello,
We are having a very strange problem. We have a table with about 5
million rows in it. The problem is with one of the non clustered
indexes. I have noticed that sometimes in query analyzer, when doing
an execution plan, the optimizer is NOT doing an index seek, or a
bookmark lookup when the query should. It sometimes will do a full
clustered index scan on the primary key, which takes much longer. For
example:
|
by: LUIS FAJARDO |
last post by:
I have the following sintax:
Select *
From Inventory
Where PartId = Coalesce(v_PartId, PartId)
this type of query is used within an store procedure that provide the
v_PartId parameter, the data type for PartId on the database is
CHAR(25) same for the parameter. The problem that I have is that this
query uses a FULL SCAN to the table instead of using the PartId index
|
by: db2udbgirl |
last post by:
If I perform a select count(*) from tred.order_delivery query will it
internally perform a full table scan to determine the row count for the
following scenario
case 1: There is a primary key on a column
case 2: There is a compound key on 1 columns col1, col2
case 3: No primary key in the table (Just for my education)
| |
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables...
|
by: shilpasharma |
last post by:
Hi,
Can anybody let me know how I can optimise following Query.
Select * from reports
where
( exists ( SELECT 1 FROM results_required rr, item_claims_trials ict, results res
WHERE ict.t_t_id IN ( 3725 )
and res.rr_rr_id = rr.rr_id
AND rr.rt_rt_id = -1
and rr.ict_ict_id = ict.ict_id
|
by: Steffen Stellwag |
last post by:
Truely is often better to scan a table in full passing by an index ,
but if you can force the optimizer to use an index via a hint for
testing and comparing the results.
But the index in the above example is not used , because the hint is
malformed,
if tables in a Select statment are named by aliases you have to
specify the alias name in the hint statment , not the table name
/*+ INDEX (ICWOIMP PK_ICWOIMP) */ change to /*+ INDEX (A...
|
by: ryan |
last post by:
Hi Everyone,
I'm having trouble convincing myself that Oracle is executing a query
of mine as efficiently as possible, and I'm looking for a little
guidance.
The situation is pretty simple. I just have two tables, PARENT and
CHILD.
PARENT(
pkey int primary key,
|
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: 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: 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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |