Oracle FIRST_ROWS and FIRST_ROWS (N)

The difference between oracle FIRST_ROWS and FIRST_ROWS (N) is that many new users are not familiar with oracle databases. The following describes the differences between oracle FIRST_ROWS and FIRST_ROWS (N) for your reference.

According to the Oracle documentation, FIRST_ROWS is not exactly a CBO model, but also contains a large number of rule-based optimization methods. The FIRST_ROWS (N) added by 9i is completely cost-based.

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2 (30), DOCS VARCHAR2 (4000 ));

The table has been created.

SQL> INSERT INTO T
2 select rownum, OBJECT_NAME,
3 OWNER | ''| OBJECT_TYPE |'' | OBJECT_NAME
4 FROM DBA_OBJECTS;

You have created 31313 rows.

SQL> CREATE INDEX IND_T_NAME ON T (NAME );

The index has been created.

SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS. CONTEXT;

The index has been created.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 't', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 100 ')

The PL/SQL process is successfully completed.

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS (USER, 'ind _ T_DOCS ')

The PL/SQL process is successfully completed.

SQL> SET AUTOT ON EXP
SQL> COL DOCS FORMAT A50
SQL> SELECT * FROM T
2 where contains (DOCS, 'sys ', 1)> 0
3 and name = 'dual'
4 order by score (1 );

ID NAME DOCS
-------------------------------------------------------------------------
7112 DUAL SYS TABLE DUAL

Execution Plan
----------------------------------------------------------
0 select statement Optimizer = CHOOSE (Cost = 5 Card = 1 Bytes = 74)
1 0 SORT (order by) (Cost = 5 Card = 1 Bytes = 74)
2 1 table access (by index rowid) OF 'T' (Cost = 2 Card = 1 Bytes = 74)
3 2 INDEX (range scan) OF 'ind _ T_NAME '(NON-UNIQUE) (Cost = 1 Card = 2)

Based on the distribution of column data and index statistics, Oracle selects a common index instead of a full-text index.

The following two prompts show the difference between FIRST_ROWS and FIRST_ROWS (N:

 
 

  
  
SQL> SELECT/* + FIRST_ROWS */* FROM T
  
  
2 where contains (DOCS, 'sys ', 1)> 0
  
  
3 and name = 'dual'
  
  
4 order by score (1 );
  
  
 
  
  
ID NAME DOCS
  
  
--------------------------------------------------------------------------
  
  
7112 DUAL SYS TABLE DUAL
  
  
 
  
  
Execution Plan
  
  
----------------------------------------------------------
  
  
0 select statement Optimizer = HINT: FIRST_ROWS (Cost = 2643 Card = 1 Bytes = 74)
  
  
1 0 table access (by index rowid) OF 'T' (Cost = 2640 Card = 1 Bytes = 74)
  
  
2 1 domain index of 'ind _ T_DOCS '(Cost = 2369 Card = 2)
  
  
 
  
  
SQL> SELECT/* + FIRST_ROWS (1) */* FROM T
  
  
2 where contains (DOCS, 'sys ', 1)> 0
  
  
3 and name = 'dual'
  
  
4 order by score (1 );
  
  
 
  
  
ID NAME DOCS
  
  
--------------------------------------------------------------------------
  
  
7112 DUAL SYS TABLE DUAL
  
  
 
  
  
Execution Plan
  
  
----------------------------------------------------------
  
  
0 select statement Optimizer = HINT: FIRST_ROWS (Cost = 5 Card = 1 Bytes = 74)
  
  
1 0 SORT (order by) (Cost = 5 Card = 1 Bytes = 74)
  
  
2 1 table access (by index rowid) OF 'T' (Cost = 2 Card = 1 Bytes = 74)
  
  
3 2 INDEX (range scan) OF 'ind _ T_NAME '(NON-UNIQUE) (Cost = 1 Card = 2)
 
 

Oracle FIRST_ROWS (N) is determined based on statistics, while oracle FIRST_ROWS contains some default rules. Therefore, FIRST_ROWS selects the domain index, without selecting a common index with a lower cost.

This may also be because of the above considerations. When Oracle chooses the default CBO mode, ALL_ROWS is selected instead of FIRST_ROWS.
 

Oracle index type

Step 5: Create an oracle full-text index

How to create an Oracle Index

C # connect to the Oracle database to query data

Instances that use oracle Stored Procedure Paging