Re: rowid hint in 10G?

On Mar 7, 3:20 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 7, 8:59 am, "stephen O'D" <stephen.odonn..._at_gmail.com> 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. Received on Fri Mar 07 2008 - 09:58:50 CST