Home » RDBMS Server » Performance Tuning » Table Access by ROWID vs UNIQUE INDEX SCAN (10.2.0.4)
| Table Access by ROWID vs UNIQUE INDEX SCAN [message #440189] |
Thu, 21 January 2010 09:04  |
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
|
Junior Member |
|
|
Hi,
could someone please shed some light on as to what is the difference between
A) operation = "Table Access" , Options = "by index row id"
and
B) operation = "index, options = "unique scan" --> as reported in DBA_HIST_SQL_PLAN
Doesn't UNIQUE INDEX access means looking up the index root branch, traverse through to the leaf block, get the rowid of the data in the table and access it? If thats true, then wouldn't this be "Table Access By Index RowID"? If yes, then how does this differ from "unique index scan"
In order to see what are the SQLs representing these 2 different access types. I first ran the query to get the sql_text for table access with rowid and then the same query to get the sql_text for unique index scan and the results/ returned queries matching both the criteria were similar
Below are the results
select sql_text from dba_hist_sqltext
where sql_id in
(
select p.sql_id
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
s.snap_id between &bgn_id and &end_id
and
p.object_owner not in ('SYS','SYSTEM','SYSAUX','SYSMAN')
and
operation='INDEX' and options='UNIQUE SCAN'
and
p.sql_id = s.sql_id
)
;
select COMPANY_NO c0, DIVISION_NO c1, USE_CHSPFILB c2, PART_NO_TRACING c3, CRTD_SCREEN_CHAIN c4, INVOICE_LINE_CUST_DATA c5,
SHIPMENT_MARKS c6, TPS_BROKER_ID c7
from BC_AUX
where :S1=COMPANY_NO and :S2=DIVISION_NO order by c0 asc, c1 asc
select FILE_NO, TRACING_DATE_NO, DATE_TRACING_SHIPMENT, TIME_TRACING_SHIPMENT, TIME_UPDATED, DATE_UPDATED,
MODIFIED_BY, CUST_NO
from SHIP_DT
where SHP_DT.FILE_NO=:phE2 and SHP_DT.TRACING_DATE_NO=:phE1
select sql_text from dba_hist_sqltext
where sql_id in
(
select p.sql_id
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
s.snap_id between &bgn_id and &end_id
and
p.object_owner not in ('SYS','SYSTEM','SYSAUX','SYSMAN')
and
operation='TABLE ACCESS' and options='BY INDEX ROWID'
and
p.sql_id = s.sql_id
)
select COMPANY_NO c0, DIVISION_NO c1, USE_CHSPFILB c2, PART_NO_TRACING c3, CRTD_SCREEN_CHAIN c4, INVOICE_LINE_CUST_DATA c5,
SHIPMENT_MARKS c6, TPS_BROKER_ID c7
from BC_AUX
where :S1=COMPANY_NO and :S2=DIVISION_NO order by c0 asc, c1 asc
select FILE_NO, TRACING_DATE_NO, DATE_TRACING_SHIPMENT, TIME_TRACING_SHIPMENT, TIME_UPDATED, DATE_UPDATED,
MODIFIED_BY, CUST_NO
from SHIP_DT
where SHP_DT.FILE_NO=:phE2 and SHP_DT.TRACING_DATE_NO=:phE1
Why is this so? Any ideas?
If both the access type are similar why the are recorded differently in the data dictionary?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Table Access by ROWID vs UNIQUE INDEX SCAN [message #440260 is a reply to message #440246] |
Thu, 21 January 2010 21:35  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
TABLE ACCESS BY INDEX ROWID means that you have ALREADY accessed the index, and are now using the rowid(s) found in the index to lookup the table. It does not tell you anything about the index scan itself; it may have been a UNIQUE scan, a RANGE scan, a FULL scan, or a SKIP scan.
INDEX UNIQUE SCAN means that you are accessing the index and will retrieve 0 or 1 row only. If all the columns you need are not in the index, it will then go on to access the table (using a TABLE ACCESS BY INDEX ROWID).
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sun May 12 19:35:24 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
