How To Provide Hint

Today Learned new way to provide hint in oracle. May be other guys know this but i didn’t know this and one of our developer showed me. Thought to give a shot on this. Anyways, generally we provide hint with /*+..*/ Format. But see this


Taral@Taral1> drop table test1 purge;

Table dropped.

Taral@Taral1> create table test1 as select * from dba_objects;

Table created.

Taral@Taral1> insert --+ append
  2  into test1 select * from dba_objects;

73253 rows created.

Taral@Taral1> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select count(*) from test1;

  COUNT(*)
----------
    146506

============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id  | Operation                        | Name       | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT                 |            |       |       |   261 |           |
| 1   |  LOAD AS SELECT                  |            |       |       |       |           |
| 2   |   VIEW                           | DBA_OBJECTS|   68K |   14M |   261 |  00:00:04 |
| 3   |    UNION-ALL                     |            |       |       |       |           |
| 4   |     TABLE ACCESS BY INDEX ROWID  | SUM$       |     1 |     9 |     1 |  00:00:01 |
| 5   |      INDEX UNIQUE SCAN           | I_SUM$_1   |     1 |       |     0 |           |
| 6   |     TABLE ACCESS BY INDEX ROWID  | OBJ$       |     1 |    30 |     3 |  00:00:01 |
| 7   |      INDEX RANGE SCAN            | I_OBJ1     |     1 |       |     2 |  00:00:01 |
| 8   |     FILTER                       |            |       |       |       |           |
| 9   |      HASH JOIN                   |            |   72K | 8828K |   258 |  00:00:04 |
| 10  |       TABLE ACCESS FULL          | USER$      |    93 |  1581 |     3 |  00:00:01 |
| 11  |       HASH JOIN                  |            |   72K | 7598K |   255 |  00:00:04 |
| 12  |        INDEX FULL SCAN           | I_USER2    |    93 |  2046 |     1 |  00:00:01 |
| 13  |        TABLE ACCESS FULL         | OBJ$       |   72K | 6006K |   253 |  00:00:04 |
| 14  |      TABLE ACCESS BY INDEX ROWID | IND$       |     1 |     8 |     2 |  00:00:01 |
| 15  |       INDEX UNIQUE SCAN          | I_IND1     |     1 |       |     1 |  00:00:01 |
| 16  |      NESTED LOOPS                |            |     1 |    29 |     2 |  00:00:01 |
| 17  |       INDEX SKIP SCAN            | I_USER2    |     1 |    20 |     1 |  00:00:01 |
| 18  |       INDEX RANGE SCAN           | I_OBJ4     |     1 |     9 |     1 |  00:00:01 |
| 19  |     NESTED LOOPS                 |            |     1 |   105 |     3 |  00:00:01 |
| 20  |      TABLE ACCESS FULL           | LINK$      |     1 |    88 |     2 |  00:00:01 |
| 21  |      TABLE ACCESS CLUSTER        | USER$      |     1 |    17 |     1 |  00:00:01 |
| 22  |       INDEX UNIQUE SCAN          | I_USER#    |     1 |       |     0 |           |
------------------------------------------------------+-----------------------------------+

Dumping Hints
=============
  atom_hint=(@=29F46C58 err=0 resol=0 used=1 token=814 org=1 lvl=1 txt=APPEND ())

To Further confirm this hint is been used. See the PLAN and last part. It says it’s used. And this will work with mostly all kind of hints. Everyday new learning.

Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: