Tuning a query

Itz Urgent ............ I hav a table manage_flow and added a column of char(1) say(mid) in it which can hav null values .. i have to write a query select col1 ,col2 from manage_flow where mid ='argument' . My ques is how to tune this query ? thr are 10000 rec in table also which index can be created on mid column ?? help me out immediately .. Thanks in advance!!!!!!

Just a simple index may do, depending on the spread of values. Not sure how this could hold the value 'argument' though if it is only a char(1)?
Create index manage_flow_idx1 on manage_flow(mid)
If there are only a few distinct values then a bitmap index may be more appropriate, but only if the table is quite static.
Regards
Jules

Kindly guide me whether the query is well tuned because msg_id column is newly added & will have values only for few rows (say less than 50 out of 1 lakh records) ,suggest me any good alternatives for this ..

They mean that changes in the index (when insert/delete/update of table data) are logged and NOPARALLEL means that the operation are done in a single thread... that's all.

If you have some delay issues when insert/update/delete, you may change it to let's say PARALLEL 2. but before doing so, I would recommend you toe read few papers about it. Coy the parrallelism bepends a lot of how is your DB & Hardware (# CPUS, available Memory an so on...)

To be honest I'm afraid not to be a very good mentor on that specific point....

What actually help you is that when applying your where clause, Oracle uses the index instead of browsing the whole table.

Hi ,
I am using PL/SQL Developer tool so Logging and NOParallel are not part of default options (mine).
It means (Logging) that whenever indexes are updated, it will happen at once ( single thread)
Also wt is the use of logged data
Yes . The execution time decreased .

Bt i think usage of hint is not required in my case.
Also i hope that usage of hint doesnt hav any sideeffects .
Thanks .

the hint I put for you: /* INDEX_FFS.... is (in my mind) the one that gives the most impacts.

It specify to the optimizer to simply disregard the table and only browse the index.

As you just do a count, who cares of the actual table?

Here we will have to talk a bit about how it works normally.

When browsing a table based on an index, oracle will take all the entries in the index (matching where clause, if any) what is actually a list of the index values and pointers to the table entries. Then jump to the table and take each row pointed to by the list.

In your case, a simply count, or let's say a select of only the index values, there is no added value to retrieve the whole list of fields of each row...

The Hint INDEX_FFS (Index Fast Full Scan) specify to the optimizer to just keep the index values and not jump to the table.

For big table (I used it for a select of the 2 fields in the index in a huge table of ~100 fields) it did make a difference (from 8 mins to < 1 sec)...

If you were selecting fields outside the index, the optimizer will just disregard the hint... (at least in my experience).