How to create an Oracle function index

xiaoxiao2021-04-09  384

[IT168 Technical Document] The important new feature of Oracle8i is the index type of Function-based index (later is simply referred to as FBI). With this feature, Oracle DBA can use functions or expressions in the index. These functions can make Oracle's own functions, can also make users their own PL / SQL functions, and the like.

Oracle8i's very important new feature is an index type of Function-based Index (later simply referred to as FBI). With this feature, Oracle DBA can use functions or expressions in the index. These functions can make Oracle's own functions, can also make users their own PL / SQL functions, and the like.

A very common problem encountered in the process of SQL statement tuning is how to optimize statements that use functions in the WHERE clause. Because in the past, the use of functions in the WHERE clause will make the index created on this table not to be utilized, so that it is difficult to improve the performance of this statement.

example:

Use a cost-based optimizer, an index is a standard B tree index, and is built on the Surname column.

SQL

>

Create

Index

Non_fbi

on

Sale_Contacts (Surname);

SQL

>

Analyze

Index

Non_fbi

Compute

Statistics

;

SQL

>

: Analyze

TABLE

Sale_contacts

Compute

Statistics

;

SQL

>

SELECT

count

(

*

)

From

Sale_contacts

WHERE

Upper

(Surname)

=

'

Ellison

'

;

Execution

Plan

-

-------------------------------------------------- --------

0

SELECT

Statement Optimizer

=

Choose (COST)

=

3

Card

=

1

BYTES

=

In one

)

1

0

Sort (aggregate)

2

1

TABLE

ACCESS

Full

)

Of

'

Sales_Contacts

'

(COST)

=

3

Card

=

16

BYTES

=

272

)

The execution path generated from the autotrace from SQL * Plus can be seen, although we created an index on the Surname column in the WHERE clause, but still executing full table scanning. If this table is big, this time is consumed to consume a lot of time.

Now let's try to build an FBI index:

SQL

>

Create

Index

FBI

on

Sale_contacts

Upper

(Surname);

SQL

>

Analyze

Index

FBI

Compute

Statistics

;

SQL

>

Analyze

TABLE

Sale_contacts

Compute

Statistics

;

SQL

>

SELECT

count

(

*

)

From

Sale_contacts

WHERE

Upper

(Surname)

=

'

Ellison

'

;

Execution

Plan

-

-------------------------------------------------- --------

0

SELECT

Statement Optimizer

=

Choose (COST)

=

2

Card

=

1

BYTES

=

In one

)

1

0

Sort (aggregate)

2

1

Index

(Range Scan)

Of

'

FBI

'

(Non

-

Unique

) (COST)

=

2

Card

=

381

BYTES

=

6477

)

The execution plan returned from SQL * Plus We can see that this time, the Oracle pair is no longer scanned, but first scans the index because the optimizer can know the FBI index exist. The performance improvement that can be brought to the FBI index depends on the size of the table, the amount of recording in the table, and the columns used in the WHERE clause. One thing needs to be clear, the FBI index does not really store the result of the expression in the index, but uses an "Expression Tree).

The expression in the SQL statement is parsed by an optimizer, and the expression above the FBI index is compared. Here, the SQL function is sensitive to the case. Therefore, the function used in the SQL statement is required and the case where the SQL function to create the FBI index is consistent, otherwise this FBI index cannot be taken. Therefore, there is a good programming style when programming.

The parameters that need to be modified in the init.ra, the following parameters must be specified in Init.ra:

Query_rewrite_integrity

=

Trusted

Query_rewrite_enabled

=

True

Compatible

=

8.1

.

0.0

.

0

(

oral

Higher

Authorization:

To enable a user to create an FBI index, he must be granted: CREATE INDEX and Query Rewrite, or CREATE ANY INDEX and GLOBAL Query Rewrite these two permissions. The user of the index must be able to have the execution permission of the function used in the FBI index. If there is no corresponding permissions, then this FBI index is powered into disabled (dba_indexes).

If the FBI index status is disabled, then DBA can process:

A: Delete and rebuild

B: ALTER INDEX INDEX_NAME ENABLED. This enabled can only be used by the FBI index.

C: alter index unusable;

Note: If this index is used in a query, the status of this FBI index is disabled, but the optimizer selects this index, then a Oracle error will be returned.

example:

ORA Error:

Error At Line 1: ORA-30554: Function-based index myuser.fbi is disabled.

Moreover, once the status of this FBI index is disabled, all DML operations involving the index column will fail on this table. Unless this indexed state changes into unusable, Skip_unusable_Indexes is specified inside the initialization parameter to TRUE.

Some examples:

SQL

>

Create

Index

Expression_ndx

On

MyTable ((Mycola)

mycolc)

*

mycolb;

SQL

>

SELECT

Mycolc

From

MyTable

WHERE

(Mycola)

mycolc)

*

Mycolb

<=

256

;

Example of composite index:

SQL

>

Create

Indexexample_ndx

On

MyExample (Mycola,

Upper

(mycolb); mYcolc

SQL

>

SELECT

Mycolc

From

MyExample

WHERE

Mycola

=

55

AND

Upper

(MyColb)

=

'

Jones

'

;

Restrictions and rules summary:

For the following limitations, the FBI index cannot be created:

a) LOB column

b) Ref

c) Nested Table Columns

d) Objects containing the above data types

The FBI index must abide by the following rules:

a) The cost-based optimizer must be used, and the index must be analyzed after being created.

b) No NULL value cannot be stored. Because any function can return NULL values ​​without any case.

c) If a user-defined PL / SQL routine is invalid, and this routine is referenced by the FBI index, then the corresponding FBI index becomes disabled.

d) Creating a FBI index function must be certain. That is, for the specified input, the determination result is always returned.

e) The owner of the index If there is no permission to execute the function used in the FBI index, then this FBI index will become disabled.

f) You cannot use SUM and other total functions inside the creation of the index.

g) Reform a disabled index into enabled, which must be first enabled.

转载请注明原文地址:https://www.9cbs.com/read-133199.html

New Post(0)