I need help trying to optimize a SQL query. I am using Oracle 8i.
I have a table with about 1.2 million records, lets call it T1. I am
doing a join from another table, lets say T2 which has a restriction
on it. T2's id is a foreign key on T1.
T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)
What happens is if I retrive just the T1.id from the query, it uses
the T2id2T1id index for a reverse walk. However as soon as I retrieve
some other column from T1, say T1.some_col, oracle decides to do a
full table scan of T1.
Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does
a full scan of this index.
Any clues to why oracle decides to do a full table scan on a
1.2million record table??? 3 13922
"Haider Kazmi" <hk****@despamm ed.com> wrote in message
news:f1******** *************** ***@posting.goo gle.com... I need help trying to optimize a SQL query. I am using Oracle 8i.
I have a table with about 1.2 million records, lets call it T1. I am doing a join from another table, lets say T2 which has a restriction on it. T2's id is a foreign key on T1.
T1 also has a index on T2id2T1id T1 (T1.t2Id, T1.id)
What happens is if I retrive just the T1.id from the query, it uses the T2id2T1id index for a reverse walk. However as soon as I retrieve some other column from T1, say T1.some_col, oracle decides to do a full table scan of T1.
Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does a full scan of this index.
Any clues to why oracle decides to do a full table scan on a 1.2million record table???
Are the datatypes of the joined fields the same? If not that might be the
problem. Also is the table and indexes analyzed? Are you retrieving a
small percentage of the rows or something larger like 10% or more? (that can
cause a full table scan because it might be faster.)
What is the explain plan? What is the tkprof output?
Jim hk****@despamme d.com (Haider Kazmi) wrote in message news:<f1******* *************** ****@posting.go ogle.com>... I need help trying to optimize a SQL query. I am using Oracle 8i.
I have a table with about 1.2 million records, lets call it T1. I am doing a join from another table, lets say T2 which has a restriction on it. T2's id is a foreign key on T1.
T1 also has a index on T2id2T1id T1 (T1.t2Id, T1.id)
What happens is if I retrive just the T1.id from the query, it uses the T2id2T1id index for a reverse walk. However as soon as I retrieve some other column from T1, say T1.some_col, oracle decides to do a full table scan of T1.
Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does a full scan of this index.
Any clues to why oracle decides to do a full table scan on a 1.2million record table???
Take a look at this article on setting the init.ora parameters
optimizer_index _cost_adj and optimizer_index _caching at http://www.dbazine.com/jlewis12.shtml .
I ran the following, written based on my understanding of the above
article, through out a day without any values overriding the defaults
to get a guess for some 'good' initials settings. After bouncing the
instance users did see a significant improvement in search time
because indexes were used more frequently. When I ran the same
scripts after setting them the first time and trying the new values
the response was actually worse.
set linesize 50;
select to_char(sysdate ,'YYYY-MM-DD-HH24:MI:SS') from dual;
select 'optimizer_inde x_cost_adj='||t o_char(round(10 0*(max(seq)/max(scat))))
as "jlewis init.ora settings"
from (
select average_wait as "SEQ",0 as "SCAT" from v$system_event
where event = 'db file sequential read'
union all
select 0,average_wait from v$system_event
where event = 'db file scattered read'
)
union all
select 'optimizer_inde x_caching='||to _char(round(avg (cache_hit_rati o)))
from (
select (1-(phy.value/(cur.value+con. value)))*100 cache_hit_ratio
from v$sysstat cur
,v$sysstat con
,v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
and (1-(phy.value/(cur.value+con. value)))*100 > 0
union all
select (1-((phy.value-dir.value)/(cur.value+con. value)))*100
cache_hit_ratio
from v$sysstat cur
,v$sysstat con
,v$sysstat phy
,v$sysstat dir
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and dir.name = 'physical reads direct'
and phy.name = 'physical reads'
and (1-((phy.value-dir.value)/(cur.value+con. value)))*100 > 0
union all
select (1-(physical_reads/
(decode(db_bloc k_gets+consiste nt_gets,0,.0000 000001,db_block _gets+consisten t_gets))))*100
cache_hit_ratio
from v$buffer_pool_s tatistics
where
(1-(physical_reads/
(decode(db_bloc k_gets+consiste nt_gets,0,.0000 000001,db_block _gets+consisten t_gets))))*100 0
);
Try something simple first.
If T1 and T2 have primary keys defined, and T2 has a FK to the PK of
T1, try DROPPING the combined index(es) first. Let the DB use PK's
only.
However, if you are trying to select other fields than the ones
indexed, it is normal for the DB to do a full table scan. Try a
COUNT(*) of the query. Does it run faster?
Post a script for the tables, PK's and FK's, and the query. It may
give us some more clues.
Cheers. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Eric B. |
last post by:
Hi,
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.
Perhaps someone can help me...
|
by: Alexander Anderson |
last post by:
I have a DELETE statement that deletes duplicate data from a table. It
takes a long time to execute, so I thought I'd seek advice here. The
structure of the table is little funny. The following is NOT the table,
but the representation of the data in the table:
+-----------+
| a | b |
+-----+-----+
| 123 | 234 |
| 345 | 456 |
|
by: Uros |
last post by:
Hello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
|
by: Serdar Kalaycý |
last post by:
Hi everybody,
My problem seems a bit clichè but I could not work around.
Well I read lots of MSDN papers and discussions, but my problem is a bit
different from them. When I tried to run the project in debug mode (by
hitting F5) it gives an error message
"Error while trying to run project: Unable to start debugging on the web
server.
|
by: Got2Go |
last post by:
Hello Group,
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.
When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
| |
by: Richard G. Riley |
last post by:
Without resorting to asm chunks I'm working on a few small routines
which manipulate bitmasks. I'm looking for any guidance on writing C
in a manner which tilts the compilers hand in, if possible, a
compiler/underlying processor independant way : althought to be fair I
cant see this stuff on anything other than x86, but who knows.
I found some ok info here:
http://www.eventhelix.com/RealtimeMantra/Basics/OptimizingCAndCPPCode.htm...
|
by: mamapossible |
last post by:
Hi, I've spent hours trying to optimize this simple query:
SELECT count(sites_jobs.id) as jobCount,
sites_jobs_categories.frn_site_id, sites_jobs_categories.id,
sites_jobs_categories.name,
FROM sites_jobs_categories
LEFT JOIN sites_jobs ON sites_jobs.frn_jobs_categories_id =
sites_jobs_categories.id
GROUP BY sites_jobs_categories.id
HAVING sites_jobs_categories.frn_site_id = #request.siteId#
|
by: Michael Hoffman |
last post by:
The peephole optimizer now takes things like
if 0:
do_stuff()
and optimizes them away, and optimizes away the conditional in "if 1:".
What if I had a function like this?
def func(debug=False):
|
by: Haider Kazmi |
last post by:
I need help trying to optimize a SQL query. I am using Oracle 8i.
I have a table with about 1.2 million records, lets call it T1. I am
doing a join from another table, lets say T2 which has a restriction
on it. T2's id is a foreign key on T1.
T1 also has a index on
T2id2T1id T1 (T1.t2Id, T1.id)
What happens is if I retrive just the T1.id from the query, it uses
|
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: 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: 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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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.
| |