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 Go to next message
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 #440191 is a reply to message #440189] Thu, 21 January 2010 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What should be done if/when the query can be satisfied by only accessing the index & no data from the table itself is needed?
Re: Table Access by ROWID vs UNIQUE INDEX SCAN [message #440244 is a reply to message #440191] Thu, 21 January 2010 15:34 Go to previous messageGo to next message
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
Junior Member

Hi

any responses / comments?

thanks
Re: Table Access by ROWID vs UNIQUE INDEX SCAN [message #440246 is a reply to message #440244] Thu, 21 January 2010 16:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What should be done if/when the query can be satisfied by only accessing the index & no data from the table itself is needed?
Re: Table Access by ROWID vs UNIQUE INDEX SCAN [message #440260 is a reply to message #440246] Thu, 21 January 2010 21:35 Go to previous message
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
Previous Topic: Determining UPDATE statements from DBA_HIST_SQL_PLAN
Next Topic: Query Fetching data within Two hrs
Goto Forum:
  


Current Time: Mon Nov 04 11:00:40 CST 2024