| Indexes [message #18586] |
Wed, 06 February 2002 08:11  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Hi,
If my Execution Plan tells me: TABLE ACCESS (BY INDEX ROWID)
does it mean that Oracle ignores my indexes and only uses Oracle generated ROWID?
Thank you
|
|
|
|
|
|
|
|
| Re: Indexes [message #18606 is a reply to message #18590] |
Thu, 07 February 2002 05:32  |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
Thank you,
Here is one of the query:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=352 Card=15 Bytes=88
6)
1 0 MINUS
2 1 SORT (UNIQUE NOSORT) (Cost=193 Card=2 Bytes=132)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=193 Card=2 Bytes=132)
5 4 CONCATENATION
6 5 NESTED LOOPS (Cost=116 Card=31 Bytes=2046)
7 6 HASH JOIN
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER_P
ROFILE' (Cost=15 Card=31 Bytes=558)
9 8 INDEX (RANGE SCAN) OF 'CP_CASE_CUST' (NON-
UNIQUE) (Cost=3 Card=31)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT_SO
URCE' (Cost=38 Card=169 Bytes=4563)
11 10 INDEX (RANGE SCAN) OF 'CASE_PAY_SOURCE' (N
ON-UNIQUE) (Cost=4 Card=169)
12 6 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER' (C
ost=2 Card=1008080 Bytes=21169680)
13 12 INDEX (UNIQUE SCAN) OF 'PK_CUSTOMER' (UNIQUE
) (Cost=1 Card=1008080)
14 5 NESTED LOOPS (Cost=116 Card=31 Bytes=2046)
15 14 HASH JOIN (Cost=54 Card=31 Bytes=1395)
16 15 INLIST ITERATOR
17 16 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER
_PROFILE' (Cost=15 Card=31 Bytes=558)
18 17 INDEX (RANGE SCAN) OF 'CP_CASE_CUST' (NO
N-UNIQUE) (Cost=3 Card=31)
19 15 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT_SO
URCE' (Cost=38 Card=169 Bytes=4563)
20 19 INDEX (RANGE SCAN) OF 'CASE_PAY_SOURCE' (N
ON-UNIQUE) (Cost=4 Card=169)
21 14 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER' (C
ost=2 Card=1008080 Bytes=21169680)
22 21 INDEX (UNIQUE SCAN) OF 'PK_CUSTOMER' (UNIQUE
) (Cost=1 Card=1008080)
23 1 SORT (UNIQUE) (Cost=153 Card=13 Bytes=754)
24 23 NESTED LOOPS (Cost=147 Card=13 Bytes=754)
25 24 HASH JOIN (Cost=79 Card=34 Bytes=1258)
26 25 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT_SOURCE'
(Cost=38 Card=34 Bytes=646)
27 26 INDEX (RANGE SCAN) OF 'CASE_PAY_SOURCE' (NON-UNI
QUE) (Cost=4 Card=34)
28 25 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER_PROFILE
' (Cost=40 Card=101 Bytes=1818)
29 28 INDEX (RANGE SCAN) OF 'CP_CASE_ID' (NON-UNIQUE)
(Cost=4 Card=101)
30 24 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER' (Cost=2
Card=1008080 Bytes=21169680)
31 30 INDEX (UNIQUE SCAN) OF 'PK_CUSTOMER' (UNIQUE) (Cos
t=1 Card=1008080)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
56557 consistent gets
507 physical reads
0 redo size
619300 bytes sent via SQL*Net to client
67461 bytes received via SQL*Net from client
606 SQL*Net roundtrips to/from client
68 sorts (memory)
0 sorts (disk)
9068 rows processed
indexes for PAY_SOURCE:
CASE_PAY_SOURCE(CASE_ID_NMBR)
PK_PAYMENT_SOURCE(CASE_CUST_ID, CASE_ID_NMBR, PAYMENT_ID, PAYMENT_SOURCE_TYPE)
Indexes for CUSTOMER_PROFILE:
CP_CASE_CUST(CASE_ID_NMBR, CUST_TYPE)
CP_CASE_ID ON (CASE_ID_NMBR)
CP_CUST_ID ON (CUST_ID) --This index is never used
CP_FAMILY_CUST (FAMILY_ID, CUST_TYPE, CASE_ID_NMBR) --This index is never used
PK_CUSTOMER_PROFILE (CASE_CUST_ID, CASE_ID_NMBR)--this index is never used
Indexes for CUSTOMER:
CUST_PRT_ID (PRT_ID, CUST_ID)
CUS_PRT_ID (PRT_ID) --Never used
PK_CUSTOMER (CUST_ID)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
