Hi.
I found a query with a buffer hit ratio about 60%:
SELECT b.Name, a.Racf, c.Manager, < fileds from STATS
table>
FROM BT.Stats a, BT.Employees b, BT.Employees c
WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02'
and a.Manager = c.Manager and
c.racf = 'RLEWI01' and b.Racf = a.Racf
and ( Product='RTI' OR Product = 'RIM' ... or Product
= 'WEB')
Group by b.Name, a.Racf, c.Manager Order by b.Name
Here is the plan under oracle 7.3.3 rbo:
1.0 SELECT STATEMENT (, , )
2.1 SORT GROUP BY (, , )
3.1 NESTED LOOPS (, , )
4.1 NESTED LOOPS (, , )
5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , )
6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , )
5.2 TABLE ACCESS BY ROWID STATS (, , )
6.1 INDEX RANGE SCAN STATS_FK2 (, , )
4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , )
5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , )
The largest table is STATS - 1.6 mil rows. STATS_FK2
is
an index on MANAGER field. We have only 115 different
managers so the index is not very selective. The
stats_date field is more selective. Does it make sence
for me to force Oracle to use that index in order to
reduce the number of disk reads?
thanks
Gene
__________________________________________________
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gurelei
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
