Pular para o conteúdo principal

Indíces Virtuais

Acredito que todos já precisaram criar algum indice para teste de performance na consulta, porém isso pode demandar tempo para indexar uma tabela grande sem contar espaço em disco, ou você não sabe os efeitos de criar um indice e afetar outras consultas do seu banco,
Dado esse fato, podemos utilizar os indices virtuais, ou indices sem segmentos, abaixo um exemplo de como funciona:


SQL> create table tab_vindex as (select * from dba_objects where object_id is not null);

Table created.

SQL> ALTER TABLE tab_vindex add constraint pk_tab_vindex PRIMARY KEY (object_id);

Table altered.

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'tab_vindex', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> SET AUTOTRACE TRACEONLY EXP

SQL> SELECT * FROM tab_vindex where object_id = 100;

Execution Plan
----------------------------------------------------------
Plan hash value: 2213869000

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 100 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 1 | 100 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_TAB_VINDEX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=100)

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 4059569637

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='TAB_VINDEX')

SQL> CREATE INDEX ix_tab_vindex ON tab_vindex(object_name) NOSEGMENT;

Index created.

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 4059569637

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 528 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TAB_VINDEX | 2 | 200 | 528 (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='TAB_VINDEX')

PARA FUNCIONAR É NECESSÁRIO SETAR O PARAMETRO: _use_nosegment_indexes

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session altered.

SQL> SELECT * FROM tab_vindex where object_name = 'TAB_VINDEX';

Execution Plan
----------------------------------------------------------
Plan hash value: 1624739785

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_VINDEX | 2 | 200 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_VINDEX | 2 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='TAB_VINDEX')

SQL> SET AUTOTRACE OFF

SQL> SELECT index_name FROM user_indexes;

INDEX_NAME
------------------------------
PK_TAB_VINDEX

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
PK_TAB_VINDEX
IX_TAB_VINDEX

SQL> spool off

Comentários

  1. O uso de Indíces Virtuais é um recurso extremamente interessante e pouco utilizado, não sei porquê, digo pouco utilizado porque já trabalhei com ótimos dba's e vi poucos, pra nao dizer quase nenhum utilizar ou citar.

    òtimo o seu blog

    ResponderExcluir
  2. Obrigado Hudson... acho uma excelente feature e para análise em sistemas transacionais é muito útil.

    ResponderExcluir

Postar um comentário

Postagens mais visitadas deste blog

Webcenter Content - Cache Configuration for JdbcFileStore configuration

The configuration values below, control the temporarily cached files keep in FileCache table. FsCacheThreshold Specifies the maximum cache size, in megabytes (default=100) Once the threshold is met, Content Server starts deleting files that are older than the minimum age, as specified by the FsMinimumFileCacheAge parameter FsMaximumFileCacheAge The age at which files are deleted, expressed in days. The default is 365 days FsMinimumFileCacheAge The minimum age at which cached files can be deleted This parameter is used in conjunction with the FsCacheThreshold parameter to determine when to delete cached files FsCleanUpCacheDuringIndexing This parameter activates vault cache cleaning, while FsCacheThreshold, FsMaximumFileCacheAge and FsMinimumFileCacheAge are controling its behaviour.  Expect, when this is enabled on a system that currently has a large number of files in the FileCache table, the first cycle will take a long time to complete. How to Confi...

Assign Backup Policy for Boot and Block Volumes - OCI

To get Availability Domain oci iam availability-domain list  #!/bin/bash OCI_AD="hQsi:SA-SAOPAULO-1-AD-1" OCI_COMPARTMENT="ocid1.compartment.oc1..aaaaaaaavgyjpoffz5qliieawamjaksfb6juxejurtduclqjkvv66ookcxja" #Get Silver policy ocid funct_get_policy(){ OCI_POL_OCID=$(oci bv volume-backup-policy list | jq -r '.data[] | select(."display-name"|contains("silver"))' | jq -r '.id') } funct_boot_vol (){ #Create a file with all boot volume ocid oci bv boot-volume list --availability-domain ${OCI_AD} -c  ${OCI_COMPARTMENT} | jq -r '.data[].id' > boot-vol.txt while IFS="" read -r p || [ -n "$p" ] do   printf 'Seeting backup policy for %s\n' "$p"   oci bv volume-backup-policy-assignment create  --asset-id  $p  --policy-id ${OCI_POL_OCID} done < boot-vol.txt } funct_block_vol (){ #Create a file with all block volume ocid oci bv volume list  -c ${OCI_COMPARTMENT} | jq -r '.data[].id' ...

Logical Backup Using dbms_datapump

Sample scripts to make a logical backup using dbms_datapump -- BACKUP BY SCHEMA declare h1 NUMBER; begin h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => 'TESTE2', version => 'COMPATIBLE'); dbms_datapump.set_parallel(handle => h1, degree => 1); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.LOG', directory => 'BKP_DATA_PUMP', filetype => 3); dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0); dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''DENILSON'')'); dbms_datapump.add_file(handle => h1, filename => 'BKP_SCHEMAS.DMP', directory => 'BKP_DATA_PUMP', filetype => 1); dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1); dbms_datapump.set_parameter(handle => h1, name ...