Home » SQL & PL/SQL » SQL & PL/SQL » Indexes
Indexes [message #18586] Wed, 06 February 2002 08:11 Go to next message
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 #18588 is a reply to message #18586] Wed, 06 February 2002 08:29 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
that means it is using index and is fastest way to access row in oracle.
Re: Indexes [message #18590 is a reply to message #18586] Wed, 06 February 2002 13:17 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
can u post full result of plan_table for your sql stmt? (options,operation,object_name,object_type columns are enough)
Re: Indexes [message #18606 is a reply to message #18590] Thu, 07 February 2002 05:32 Go to previous message
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)
Previous Topic: how do i insert the value like 232322.456 through sql loader
Next Topic: Primary key & Column name
Goto Forum:
  


Current Time: Sat Sep 25 01:40:04 CDT 2021