RE: Index not used
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>, <ranko.mosic@xxxxxxxxx>, "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- Date: Tue, 16 May 2006 17:56:09 -0400
Oh, I knew I forgot something:
You mentioned that when you specified the index hint, cardinality
dropped significantly. But, that's still in the realm of CBO cost
calculations. How many buffer gets does the query do with the FTS? how
many with the index being hinted?
Also, are your stats up to date?
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Tuesday, May 16, 2006 5:53 PM
To: ranko.mosic@xxxxxxxxx; Ric Van Dyke
Cc: _oracle_L_list
Subject: RE: Index not used
Hmm....those seem to be high clustering factors, but, to be sure, we
need to know how many rows are in t2, and how many blocks in t2 are
below the HWM. The clustering factor will always range between the
number of blocks below the HWM for the table (good) to number of rows in
the table (bad).
Before anyone else attempts to go there, let me just say it: Rebuilding
the index(es) will NOT affect the clustering factor!
What's the execution plan look like? What's the join order? Looks like
you have filter predicates on t1 and t3. Are the columns which have the
filter predicates indexed well? What kind of cardinality do you see
from t1, based on the filter predicates? What about t3? Whichever
produces the smaller rowsource is the likely candidate to be the driving
table.
On another note, as far as the select list goes, do you really need all
columns from all tables? If your select list was 't1.*,t3.*', the
horrible clustering factor on the t2 indexes would no longer have an
effect on the optimizer.
Just some thoughts, but really, need to see the execution plan and some
answers to the questions posed above.
Hope that helps,
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
For a successful technology, reality must take precedence over public
relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ranko Mosic
Sent: Tuesday, May 16, 2006 5:36 PM
To: Ric Van Dyke
Cc: _oracle_L_list
Subject: Re: Index not used
Correction, query looks like this:
SELECT *
2 FROM tab1 t1 ,
3 tab2 t2 ,
4 tab3 t3
5 WHERE t1.id <http://t1.id/> = t2.id <http://t2.id/>
6 AND t2.id <http://t2.id/> 1 = t3.id <http://t3.id/>
7 AND t1.type IN
8 ('A','B','C')
9 AND t3.type = 'A'
10* AND t3.start_date = '01-APR-2004'
FTS is probably not the right thing to do - when I put index hint
cardinality went down dramatically.
Clustering_factor for index on t2.id is 110582943 and on t2.id1 is
42661968
Traces are coming if I can get them .
Regards, Ranko
On 5/16/06, Ric Van Dyke <ric.van.dyke@xxxxxxxxxx> wrote:
A full table scan might be the right thing to do. What's the
10046 and 10053 traces look for the run of the plan? What's the Cluster
Factor on the indexes?
Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624
-----------------------
Hotsos Symposium March 4-8, 2007. Be there.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ranko Mosic
Sent: Tuesday, May 16, 2006 4:36 PM
To: _oracle_L_list
Subject: Index not used
Hi List,
SELECT *
2 FROM tab1 t1 ,
3 tab2 t2 ,
4 tab3 t3
5 WHERE t1.id <http://t1.id/> = t2.id <http://t2.id/>
6 AND t2.id <http://t2.id/> = t3.id <http://t3.id/>
7 AND t1.type IN
8 ('A','B','C')
9 AND t3.type = 'A'
10* AND t3.start_date = '01-APR-2004'
All join cols are indexed on leading cols. There is FTS on t2 .
Stats are fresh and computed .
Why FTS on t2 ? ( v 9.2 ).
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMo
sicMain.html
--
Regards,
Ranko Mosic
Contract Senior Oracle DBA
B. Eng, Oracle 10g, 9i Certified Database Professional
Phone: 416-450-2785
email: mosicr@xxxxxxxxxx
http://ca.geocities.com/mosicr@xxxxxxxxxx/ContractSeniorOracleDBARankoMo
sicMain.html
Other related posts:
