473,714 Members | 2,361 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL query not using index and explain plan gives very high cost

6 New Member
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;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 748 M 569565
MERGE JOIN 748 M 152G 569565
SORT JOIN 965 51 K 97
TABLE ACCESS FULL USER1.SMALL_TAB LE 965 51 K 21
SORT JOIN 15 M 2G 569468
TABLE ACCESS FULL USER1.HISTORY_T ABLE 15 M 2G 137725
Feb 28 '08 #1
7 5782
skaushik
6 New Member
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 ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 748 M 569565
MERGE JOIN 748 M 152G 569565
SORT JOIN 965 51 K 97
TABLE ACCESS FULL USER1.SMALL_TAB LE 965 51 K 21
SORT JOIN 15 M 2G 569468
TABLE ACCESS FULL USER1.HISTORY_T ABLE 15 M 2G 137725

I also tried using hints to force the usage of index on column A by the following query.

select /*+ index(history_t able ColumnA_IDX)*/ * from history_table ht, small_table st
where ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;

The explain plan as a result of this query also was the same as above. I was not able to understand the reason for this behavior by the optimizer. Please let me know if any one knows the reason for this. This query is frequently used and so is causing a huge performance problem for the database.

Thanks a lot.
Feb 28 '08 #2
debasisdas
8,127 Recognized Expert Expert
Please find a related discussion here .
Feb 29 '08 #3
debasisdas
8,127 Recognized Expert Expert
Both the threads are merged for better management of the forum.
Feb 29 '08 #4
skaushik
6 New Member
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.

Both the threads are merged for better management of the forum.
Feb 29 '08 #5
andreasksp
1 New Member
Please find a related discussion here .
Sorry, but the link is not valid. Can Anyone tell me a valid Link to the Article because I have the same Problem with a few SQL Statements
Mar 26 '08 #6
hi..

if ur history table is having more data ..then try once by analysing it and rebuild the index as well...
and if ur current index is on only one column and its not on date column which u r using then drop that index and create composite index on both columns...

i hope this would help
Oct 23 '10 #7
amitpatel66
2,367 Recognized Expert Top Contributor
What is your database version?
Oct 25 '10 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

6
4515
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select statement joins 15 tables . SOme of the tables are outer joined. It runs much slow when parameters (From & To Date) are for a month.
3
5223
by: Paul Mateer | last post by:
Hi, I have been running some queries against a table in a my database and have noted an odd (at least it seems odd to me) performance issue. The table has approximately 5 million rows and includes the following columns: DocID (INTEGER, PRIMARY KEY, CLUSTERED) IsRecord (INTEGER, NONCLUSTERED)
1
2789
by: Robert Wille | last post by:
I have a number of very common queries that the optimizer plans a very inefficient plan for. I am using postgres 7.2.3. I vacuum hourly. I'm wonderingwhat I can do to make the queries faster. Here are the relevant tables: create table image( imageid integer not null, /* The image's ID */ containerid integer not null, /* The container that owns it */ name varchar(120) not null, /* Its name */ state bigint not null default 0,...
2
3478
by: ruben | last post by:
Hi: I must have missed something, but how is it possible that a join on tables A and B is faster (a lot faster) than a query to one of the tables with the same conditions? The problem seems to be with the query plan, in the case os a query to table_a only, the planner executes a "Seq Scan", in the case of a join, an "Index Scan". table_a has about 4M records, so the difference is quite noticeable.
13
1989
by: Dmitry Tkach | last post by:
Hi, everybody! Here is a weird problem, I ran into... I have two huge (80 million rows each) tables (a and b), with id as a PK on both of them and also an FK from b referencing a. When I try to run a query like: select * from a, b where a.id >= 7901288 and a.id=b.id limit 1; The query takes *forever*.
6
1926
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a look at the query below. There is a btree index on both m.account_id and a.account_id. Query (1) does not use the index on the messages table, instead opting for a full table scan, thus killing performance. The messages table can contain...
8
3266
by: Együd Csaba | last post by:
Hi All, how can I improve the query performance in the following situation: I have a big (4.5+ million rows) table. One query takes approx. 9 sec to finish resulting ~10000 rows. But if I run simultaneously 4 similar queries it takes nearly 5 minutes instead of 4 times 9 seconds or something near of that. here is a sample query: select mertido, fomeazon, ertektipus, mertertek from t_me30 where fomeazon in (select distinct fomeazon...
21
6973
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on pc.product_id = p.id where pc.category_id = $category_id order by p.title
2
720
by: Zygo Blaxell | last post by:
I have a table with a few million rows of temperature data keyed by timestamp. I want to group these rows by timestamp intervals (e.g. every 32 seconds), compute aggregate functions on the columns, and ultimately feed the result into a graph-drawing web thingy. I'm trying a few different ways to get what seems to be the same data, and seeing some odd behavior from the query planner. The table looks like this:
0
9314
Oralloy
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...
0
9175
jinu1996
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...
1
9075
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,...
0
9017
tracyyun
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...
1
6634
isladogs
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...
0
5948
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();...
0
4464
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...
1
3158
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
3
2111
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.