| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ??? how to interpret query plan
Duh! <sound of hand smacking forehead>
I didn't notice that there was no cost listed next to the COST=
You're absolutely right. Of course a hint can still be used to change to COST, but certainly in this query it seems unlikely to be worthwhile unless there is a large %age of rows being returned from CUSTOMER_ACCOUNT_CONTACTS.
-----Original Message-----
Sent: Thursday, May 17, 2001 7:41 PM
To: Multiple recipients of list ORACLE-L
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
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!?
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).
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).
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).
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). Received on Fri May 18 2001 - 11:46:04 CDT
 |
 |