| how to tune query [message #218282] |
Wed, 07 February 2007 10:42  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
I have a problem with the query below and I cannot make it better myself - need help. The problem is that the tables have hundred of millions of rows and because of the not equal sign the index full scan performed slows down the execution.
I have all indexes needed sr.ent_id/sr.rel_id and s.ENT_ID.
Below are the 2 ways I tried but witht he same plan and timing.
select sr.rel_id, sr.ent_id, sr.l_conf, sr.rel_conf, sr.match_id
from sep_rel sr
where
not exists(select 1 from search s where s.ENT_ID = sr.ENT_ID)
--OR
--sr.ent_id not in (select ent_id from search s where s.ENT_ID = sr.ENT_ID)
and sr.rel_id in (?,?,?)
Thanks a lot, mj
|
|
|
|
|
|
| Re: how to tune query [message #218359 is a reply to message #218282] |
Wed, 07 February 2007 19:04  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Here are my numbers:
SQL> select count(*) from sep_rel;
COUNT(*)
-----------
408165802
SQL> select count(distinct rel_id) from sep_rel;
COUNT(DISTINCT REL_ID)
----------------------
112728065
Thanks a lot, mj
|
|
|
|
|
|
| Re: how to tune query [message #218518 is a reply to message #218282] |
Thu, 08 February 2007 09:23 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
Yes, I have indexes as follow:
sr.ent_id/sr.rel_id, (it is used for other reports as well)
sr.rel_id
and s.ENT_ID.
The plan includes full index scan on index sr.rel_id and range/table access by rowid on s.ENT_ID.
Thanks a lot ,mj
|
|
|
|
| Re: how to tune query [message #218522 is a reply to message #218282] |
Thu, 08 February 2007 09:35 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
According to your indexes you were supposed to get good EXPLAIN.
Please post yours.
What is the data_type of rel_id column of sep_rel table?
I think it's VARCHAR2, but I would like to know for shure.
If it's VARCHAR2 then try:
select sr.rel_id, sr.ent_id, sr.l_conf, sr.rel_conf, sr.match_id
from sep_rel sr
where
not exists(select 1 from search s where s.ENT_ID = sr.ENT_ID)
and sr.rel_id in (TO_CHAR(?),TO_CHAR(?),TO_CHAR(?))
HTH.
Michael
|
|
|
|
| Re: how to tune query [message #218595 is a reply to message #218522] |
Thu, 08 February 2007 21:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I agree with Michael. There must be a reason why the full index scan is not an INLIST ITERATOR. Casting is a very likely suspect.
Also try gathering statistics on the table and index if you have not done so recently.
Ross Leishman
|
|
|
|
| Re: how to tune query [message #218771 is a reply to message #218282] |
Sat, 10 February 2007 00:21 |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
All ID columns are numbers and the casting did not change anything. The statistics are fresh (I did rebuild the indexes even). And I rerun the server stats under the most recent load. The exec plan is not bad - just this full index scan and the timing that everybody complains.
Thanks a lot, mj
|
|
|
|
| Re: how to tune query [message #218840 is a reply to message #218282] |
Sun, 11 February 2007 05:03 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Can you post the results of following queries:
SELECT * FROM USER_TABLES WHERE table_name = <your table name>;
SELECT * FROM USER_INDEXES WHERE table_name = <your table name>;
SELECT * FROM USER_TAB_COLUMNS WHERE table_name = <your table name>;
I would like to see the number of distinct values/keys for all relevant objects.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
