Re: rowid hint in 10G?
Date: Fri, 7 Mar 2008 07:20:35 -0800 (PST)
Message-ID: <803dc4ab-470c-4b5a-a56b-40e56a12070f@x30g2000hsd.googlegroups.com>
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 Received on Fri Mar 07 2008 - 09:20:35 CST