Re: my problem with

I did run explain plan and with first query I had :

  Statement Id=5 Type=COUNT
  Cost=0 TimeStamp=30-08-04::15::37:22

       (1)  SELECT STATEMENT  HINT: FIRST_ROWS
     Est. Rows: 1  Cost: 107.847.126
       (7)  SORT ORDER BY
     Est. Rows: 1  Cost: 107.847.126
           (6)  FILTER


               (2)  TABLE TABLE ACCESS FULL  Table1  [Analyzed]

               (2)   Blocks: 3.210 Est. Rows: 209.907 of 209.907  Cost: 717

                    Tablespace: USERS


               (5)  FILTER

                   (4)  COUNT STOPKEY
                       (3)  INDEX (UNIQUE) INDEX FULL SCAN Field1 
[Analyzed]
                            Est. Rows: 209.907  Cost: 532

and with better second query :

  Statement Id=7 Type=INDEX
  Cost=117 TimeStamp=30-08-04::15::34:51

       (1)  SELECT STATEMENT  HINT: FIRST_ROWS
     Est. Rows: 10  Cost: 1.791
       (13)  SORT ORDER BY
     Est. Rows: 10  Cost: 1.791
           (12)  NESTED LOOPS
                Est. Rows: 10  Cost: 1.790


               (9)  VIEW VIEW SYS.VW_NSO_1

                    Est. Rows: 10  Cost: 1.780
                   (8)  MINUS
                       (4)  SORT UNIQUE
                            Est. Rows: 10
                           (3)  COUNT STOPKEY
                               (2)  INDEX (UNIQUE) INDEX FAST FULL SCAN 
Field1  [Analyzed]
                                    Est. Rows: 209.907  Cost: 117
                       (7)  SORT UNIQUE
                            Est. Rows: 1
                           (6)  COUNT STOPKEY
                               (5)  INDEX (UNIQUE) INDEX FAST FULL SCAN 
Field1  [Analyzed]
                                    Est. Rows: 209.907  Cost: 117


               (11)  TABLE TABLE ACCESS BY INDEX ROWID Table1  [Analyzed]

               (11)   Blocks: 3.210 Est. Rows: 1 of 209.907  Cost: 1

                    Tablespace: USERS
                   (10)  INDEX (UNIQUE) INDEX UNIQUE SCAN Field1  [Analyzed]
                        Est. Rows: 1

You see, first time there is : (2) TABLE TABLE ACCESS FULL Table1 [Analyzed] and (7) SORT ORDER BY
and this is problem.

You didn't understend what I need. User of my application have to see records in order by Field1 but only records with first 50 values of that field, that's meen records with value from '000001' to '000050' You can try to insert records in nonsequenced order and you will see that.

Does you now is any oracle command that I say database to use index in some query?

>
> Did you run explain plan and compare the 2 plans? No? Your answer is
> in the explain plan.
> Also, there is no such thing as 'first 50 rows' as  tables are the
> representation of a set, and sets are unordered. Your approach is
> likely to fail sooner or later.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA