Re: rowid hint in 10G?

On Mar 7, 3:20 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Mar 7, 8:59 am, "stephen O'D" <stephen.odonn...@xxxxxxxxx> wrote:

All,

I am trying to prove a test case, and I want to show the different
between Oracle doing a full scan in one case, and an index by rowid in
another (in my case I know that Oracle is making the correct choice
with the full scan, but I need my proof!).

Apparently the rowid hint was ROWID(table), but its been depreciated
in 10G - is there any alternative - lots of googling has turned up
nothing so far.

Thanks,

Stephen.

Why won't the /*+ index(table,index) */ hint prove your point?

SQL>
SQL> --
SQL> -- 'Bare' query, no hints, to see
SQL> -- what the optimizer chooses as a
SQL> -- path
SQL> --
SQL> select
2 count(*),
3 count(distinct sys_op_descend('OBJECT_ID')),
4 sys_op_countchg(substrb(rowid, 1, 15), 5)
5 from myobs
6 where object_id is not null
7 /

COUNT(*) COUNT(DISTINCTSYS_OP_DESCEND('OBJECT_ID'))
SYS_OP_COUNTCHG(SUBSTRB(ROWID,1,15),5)
---------- ------------------------------------------
--------------------------------------
282624
1 206838

Execution Plan
----------------------------------------------------------
Plan hash value: 1664352787

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 162 (11)|
00:00:02 |
| 1 | SORT GROUP BY | | 1 | 25 |
| |
|* 2 | INDEX FAST FULL SCAN| MYIDX | 308K| 7528K| 162 (11)|
00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
724 consistent gets
633 physical reads
0 redo size
354 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> --
SQL> -- Valid syntax: uses index full scan
SQL> --
SQL> select /*+
2 cursor_sharing_exact
3 dynamic_sampling(0)
4 no_monitoring
5 no_expand
6 index(myobs,myidx)
7 noparallel_index(myobs,myidx)
8 */
9 count(*),
10 count(distinct sys_op_descend('OBJECT_ID')),
11 sys_op_countchg(substrb(rowid, 1, 15), 5)
12 from myobs
13 where object_id is not null
14 /

COUNT(*) COUNT(DISTINCTSYS_OP_DESCEND('OBJECT_ID'))
SYS_OP_COUNTCHG(SUBSTRB(ROWID,1,15),5)
---------- ------------------------------------------
--------------------------------------
282624
1 206838

Execution Plan
----------------------------------------------------------
Plan hash value: 156953021

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 656 (4)|
00:00:08 |
| 1 | SORT GROUP BY | | 1 | 25 |
| |
|* 2 | INDEX FULL SCAN| MYIDX | 16 | 400 | 656 (4)|
00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
52502 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> --
SQL> -- Also valid syntax: uses index full scan
SQL> --
SQL> select /*+
2 cursor_sharing_exact
3 dynamic_sampling(0)
4 no_monitoring
5 no_expand
6 index(myobs myidx)
7 noparallel_index(myobs myidx)
8 */
9 count(*),
10 count(distinct sys_op_descend('OBJECT_ID')),
11 sys_op_countchg(substrb(rowid, 1, 15), 5)
12 from myobs
13 where object_id is not null
14 /

COUNT(*) COUNT(DISTINCTSYS_OP_DESCEND('OBJECT_ID'))
SYS_OP_COUNTCHG(SUBSTRB(ROWID,1,15),5)
---------- ------------------------------------------
--------------------------------------
282624
1 206838

Execution Plan
----------------------------------------------------------
Plan hash value: 156953021

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 656 (4)|
00:00:08 |
| 1 | SORT GROUP BY | | 1 | 25 |
| |
|* 2 | INDEX FULL SCAN| MYIDX | 16 | 400 | 656 (4)|
00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OBJECT_ID" IS NOT NULL)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
633 consistent gets
0 physical reads
0 redo size
359 bytes sent via SQL*Net to client
246 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

As you can see the index() hint does cause the index to be used, which
is what you want if I read your post correctly.

David Fitzjarrell

Arrgh - sorry - I meant access by rowid (in this case there is no
index in the proof) - I am so used to saying 'index by rowid' I made a
mistake.

I am trying to prove that oracle is doing the correct thing in mview
generation when it uses an mlog$ table to log the changed rowids to
replicate - it then joins the changed rowids back to the master table
to find the changed rows.

Sometimes it goes into the table using access by rowid, other times it
goes into table with a full scan and hash joins (depends on the data
in the mlog$ table) - I know its doing the correct thing, but I need
to prove it to convince others.

Instead of using a hint, I set stats on my mlog$ table, telling Oracle
it has a small numbers of row which proved my point nicely.

Thanks for the help, and sorry again for the rather important typo on
my part!

Stephen.
.

Relevant Pages

  • Re: rowid hint in 10G?
    ... another (in my case I know that Oracle is making the correct choice ... SQL> -- 'Bare' query, no hints, to see ... 246 bytes received via SQL*Net from client ... Arrgh - sorry - I meant access by rowid (in this case there is no ...
    (comp.databases.oracle.server)
  • Re: Oracle goes Free
    ... MS SQL has had a developer edition for a long time. ... you could join the Oracle partners program and get access to ... from having to install manage and deal with the real version. ... No client is going to accept an installation of this. ...
    (borland.public.delphi.non-technical)
  • Re: How to protect data within MS access?
    ... My application is client application ... If security is a priority then investing in some other database (SQL Server, ... Oracle) with better security would be better. ...
    (microsoft.public.data.ado)
  • Re: parallel query
    ... 859 bytes received via SQL*Net from client ... sorts (memory) ... SQL> show parameters hash ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.misc)
  • Re: Dont understand what version of SQL to install
    ... I'm talking about developing and testing databases for my client and ... To my XP, I can add SQL Developer 2008 to develop (I guess I should say, ... So I can use SQL Developer 2008 to create a database in standard 2005, ... installed on Client OSs such as Windows Vista. ...
    (microsoft.public.sqlserver.setup)