Yes, this looks fine. You'll notice that most of the tables are accessed
via a unique index. This means that most likely you only needed to read one
block in the index and one block in the table.
The only exception is the CUSTOMER_ACCOUNT_CONTACTS which is accessed via an
index range scan. This is probably fine. The exception is if for this
particular query the indexed column (for index CUSTOMER_ACCOUNT_CONTACTS_1)
has a particularly high number of hits for the value in the query. I have
no idea what the distribution of data is in your tables, but if the query is
checking a large %age of the rows it is faster to do a full table than to an
index range scan first (since Oracle has to first read the Index blocks in
addition to the Table blocks).
If you have histogram on the column Oracle will check the distribution of
data automatically UNLESS the query is using bind varibles.
-----Original Message-----
Sent: Friday, May 11, 2001 6:56 PM
To: Multiple recipients of list ORACLE-L
Hi all,
I know how to generate the query plan but how to
interplate the query plan, really? The only thing I
know is if there is a FULL TABLE SCAN, that most
probably is bad, need to build an index. However, if
the query plan shows me something else, I don't know
what to improve.
For example, does the following look good??? (this is
just one example, please throw me any idad you can
think of. Thank you!)
Query Plan
-----------------------------------------------------------------------
SELECT STATEMENT cost=
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID
CUSTOMER_ACCOUNT_CONTACTS
INDEX RANGE SCAN
CUSTOMER_ACCOUNT_CONTACTS_1
TABLE ACCESS BY INDEX ROWID PHONE
INDEX UNIQUE SCAN PK_PHONE
TABLE ACCESS BY INDEX ROWID PHONE
INDEX UNIQUE SCAN PK_PHONE
TABLE ACCESS BY INDEX ROWID PHONE
INDEX UNIQUE SCAN PK_PHONE
TABLE ACCESS BY INDEX ROWID PHONE
INDEX UNIQUE SCAN PK_PHONE
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Janet Linsy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
