I am now doing SQL tunning, although there will have several millions of recrods after put into production, we do have ten records right now, even I have built a index, oracle does not use it coz of smaller size of the table, oracle prefers to use table scan.
Is there any option on explain to specify the size of table even thought the table size right now is very small.
If I understood you well, in test environment you work with a small table (10 records), but in production it is expected that this table will have millions of records.
Optimizer will choose the best approach to execute a query. I *think* that it is not possible to specify future table size. However, you could use a HINT to order Oracle to use an index, although optimizer might decide differently. For example,
SELECT /*+ index (e idx_emp_empno) */ e.empno, e.ename
FROM emp e;
will tell Oracle: "Don't do full table scan. Use index instead."
The optimizer makes it choices based on what statistics the table/indexes has, therefore, if you are collecting statistics for these tables, you will always end up with full scan if the tables are small, unless of course you hint the sql as litlefoot said.
Try setting estimated statistics for these tables from production and use
dbms_stats.set_table_stats to set these. You could also export the statistics from production to test if you will.