| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: High CPU Query
"Kumar" <vinodky2000_at_yahoo.com> wrote in message
news:1144123308.169940.233660_at_g10g2000cwb.googlegroups.com...
> Hi,
>
> Execution plan is correct. I suspect there is CARTESIAN. Anyway,
> although I do not like too many indexes but here after creating a index
> on employee_id+supervisor_3 query took less than 2 sec. there are no
> 40m rows now.
>
> Regards,
>
How are you actually checking that the
execution plan you see in the tkprof output
is the execution plan that actually ran ?
I maintain that the following cannot happen
40928544 NESTED LOOPS
13428 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
40928544 TABLE ACCESS GOAL: ANALYZED (BYINDEX ROWID) OF
'HRM_CAREER'
3048 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'HRM_CAREER_PK' (UNIQUE)
Specifically, you cannot get 3,048 rowids from
an index, and then get 4,000,000 million rows
from the table using those rowids.
Here's an example of a forced Cartesian join (though not labelled as Cartesian)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9000000 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T2'
9003001 NESTED LOOPS
3000 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T1' 9000000 INDEX GOAL: ANALYZED (FULL SCAN) OF 'T_I2' (NON-UNIQUE)
Note - the visits to the table cannot (for a b-tree index) exceed the visits to the index.
-- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.htmlReceived on Fri Apr 07 2006 - 10:29:59 CDT
 |
 |