RE: ??? how to interpret query plan

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Thu, 17 May 2001 15:42:40 -0700
Message-ID: <F001.00306F61.20010517154107@fatcity.com>

He works with rule based optimizer (cost= in explain plan). Maybe using choose annnnd not analyzing. So it does not matter how values distributed - if index exist rule based optimizer will use it.

Alex Hillman

-----Original Message-----
Sent: Wednesday, May 16, 2001 6:16 PM
To: Multiple recipients of list ORACLE-L

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: janetlinsy_at_yahoo.com

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: ListGuru_at_fatcity.com (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: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------