com função ou só em cima de colunas) conceitualmente é simples
(fisicamente é muito complexo, o algoritmo se preocupa com
balanceamento, com ordenação), mas o conceito é : o índice se
relaciona com APENAS UMA tabela, há uma ou mais colunas que serão a
chave do índice, para CADA linha da tabela que entrar no índice
(linhas com campos-chave nulos não entram) haverá uma LISTA no
estilo :
VALOR DA CHAVE ROWID
1 AAABBBBCCCC
2 AAABBBBCCCD
3 AAABBBBCCCE
assim por diante, e essa lista é MONTADA na hora da criação/rebuild
do índice, lendo a chave para CADA registro. SE for um índice com
função, a conceito é o mesmo, a função RETORNA a chave , que será
listada no índice para CADA registro : assim se a função pode
retornar 10 valores mas na hora da criação do índice só haviam
registros para os dois primeiros valores, APENAS ESTES estarão no
índice, a sua afirmação "Quando criamos uma coluna indexada com
função, o Oracle cria uma estrutura interna onde para cada valor
possivel na funcao" é FALSA, ao final eu dou uma demonstração, o
correto é "para cada valor não-null QUE A TABELA RETORNE APLICANDO A
FUNÇÃO EM CADA REGISTRO".
O que se costuma fazer em FBIs (além do caso de simplesmente
armazenar o valor-chave alterado, como um FBI com UPPER, por exemplo)
é um trucão aproveitando que os NULLs não entram, suponha os dados :
[EMAIL PROTECTED]:SQL>@desc BIG_TABLE
Nome Nulo? Tipo
---------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
COLUNA_MIX VARCHAR2(200)
[EMAIL PROTECTED]:SQL>select owner, count(*) from BIG_TABLE group by owner;
OWNER COUNT(*)
---------------- ------------------
CHIAPPA 10
VIDEO4 90
TOAD 30
FCO 50
SYS 12290
SCOTT 790
PUBLIC 15700
SYSTEM 20
Imagine que eu preciso rapidamente localizar apenas os registros com
o campo OWNER sendo 'CHIAPPA' ou 'SYSTEM', eu poderia criar uma
função que retornasse NULL pra outros OWNERs que não os que me
interessam, aí só entram no índice os que me interessam, tipo :
[EMAIL PROTECTED]:SQL>create or replace function acha_chiappa_system(P_OWNER
char) return number
2 DETERMINISTIC as
3 BEGIN
4 if P_owner in ('CHIAPPA', 'SYSTEM') then
5 return 0;
6 end if;
7 return null;
8 END;
9 /
Função criada.
[EMAIL PROTECTED]:SQL>create index fbi_acha_chiappa_system on BIG_TABLE
(acha_chiappa_system(OWNER)) nologging;
Índice criado.
[EMAIL PROTECTED]:SQL>analyze table BIG_TABLE compute statistics for table
for all indexes for all indexed columns size 2;
Tabela analisada.
==> vou hintar o SQL para poder demonstrar porque estou rodando isso
numa máquina servidora MUITO potente e que permite paralelismo (multi-
CPUs), então pruma tabela de centenas de milhares de linhas como é a
BIG_TABLE ele (corretamente) vai preferir full scan....
[EMAIL PROTECTED]:SQL>l
1 select /*+ INDEX (BIG_TABLE, fbi_acha_chiappa_system) */
2* * from BIG_TABLE where acha_chiappa_system(owner) = 0 and
owner='CHIAPPA'
[EMAIL PROTECTED]:SQL>/
OWNER OBJECT_NAME OBJECT_ID
---------------- ------------ --------- ........
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
CHIAPPA PLAN_TABLE 37368
10 linhas selecionadas.
Plano de Execução
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=99)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=2
Card=1 Bytes=99)
2 1 INDEX (RANGE SCAN) OF 'FBI_ACHA_CHIAPPA_SYSTEM' (NON-
UNIQUE) (Cost=1 Card=30)
Estatística
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
779 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
[EMAIL PROTECTED]:SQL>
Veja que DETERMINÍSTICO quer dizer que SEMPRE que vc chamar a função
com o mesmo argumento na mesma tabela o retorno é o mesmo, SE esse
retorno depender duma OUTRA tabela (que, óbvio, PODE mudar a qquer
instante em tese) acabou o determinismo, acabou em princípio a chance
de FBI, se é isso que vc quis dizer no parágrafo "uma outra coluna de
uma outra
tabela contiver um determinado valor, agora, se essa coluna de outra
tabela
for alterada, o valor que a funcão retorna deve ser outro" vc NÃO vai
conseguir usar esta técnica.SE for isso, manda pra gente o CREATE
TABLE (resumido, só com as cols importantes!) das tabelas, um script
que alimente-as com dados (umas tantas centenas de milhares de
linhas, via loop) e uma explicação geral do que vc quer, que a gente
pode tentar te dar outras opções.
[]s
Chiappa
==> demonstração de internals dum índice FBI :
[EMAIL PROTECTED]:SQL>select * from dept;
DEPTNO DNAME LOC
------------------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
[EMAIL PROTECTED]:SQL>create function F_DEPTO (P_DEPTO number) return char
DETERMINISTIC is
2 BEGIN
3 if P_DEPTO = 10 then
4 return 'AAAAAAAAAA';
5 elsif P_DEPTO = 20 then
6 return 'BBBBBBBBBB';
7 elsif P_DEPTO = 21 then
8 return 'CCCCCCCCCC';
9 elsif P_DEPTO = 22 then
10 return 'DDDDDDDDDD';
11 else
12 return null;
13 end if;
14 END;
15 /
Função criada.
[EMAIL PROTECTED]:SQL>create index FBI_DEPT on dept (F_DEPTO(deptno))
nologging;
Índice criado.
sid=prd:prd:/u1/app/oracle/admin/prd/udump>cat prd_ora_734.trc
....
Start dump data blocks tsn: 42 file#: 43 minblk 32649 maxblk 32777
buffer tsn: 42 rdba: 0x0ac07f89 (43/32649)
scn: 0x0675.06b7e67d seq: 0x01 flg: 0x04 tail: 0xe67d1001
frmt: 0x02 chkval: 0xa4c6 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1
#blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x0ac07f8b ext#: 0 blk#: 1 ext size: 127
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 98837 flag:
0x40000000
Extent Map
-----------------------------------------------------------------
0x0ac07f8a length: 127
nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
buffer tsn: 42 rdba: 0x0ac07f8a (43/32650)
scn: 0x0675.06b7e67d seq: 0x01 flg: 0x04 tail: 0xe67d0601
frmt: 0x02 chkval: 0x51ba type: 0x06=trans data
Block header dump: 0x0ac07f8a
Object id on Block? Y
seg/obj: 0x18215 csc: 0x675.6b7e67c itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck
Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc
0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn
0x0675.06b7e67c
Leaf block dump
===============
header address 9223372041150910556=0x800000010011d05c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 7976=0x1f28
kdxcoavs 7936
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8016
row#0[7996] flag: -----, lock: 0
col 0; len 10; (10): 41 41 41 41 41 41 41 41 41 41
col 1; len 6; (6): 0a c0 05 8a 00 00
row#1[7976] flag: -----, lock: 0
col 0; len 10; (10): 42 42 42 42 42 42 42 42 42 42
col 1; len 6; (6): 0a c0 05 8a 00 01
----- end of leaf block dump -----
==> tenho entradas na strut do índice para 'AAAAAAAAAA'
e 'BBBBBBBBBB', para os outros valores POSSÍVEIS na função ** não
**....
--- Em oracle_br@yahoogrupos.com.br, "Marcelo Cauduro"
<[EMAIL PROTECTED]> escreveu
>
> Quando criamos uma coluna indexada com função, o Oracle cria uma
estrutura
> interna onde para cada valor possivel na funcao ele tem uma
resultado.
> Como ela é deterministica isso é possível.
>
> Mas aí é que esta o problema.
> tenho uma função que se você passar um determinada valor , ela
sempre vai
> retornar o mesmo valor se , e somente se , uma outra coluna de uma
outra
> tabela contiver um determinado valor, agora, se essa coluna de
outra tabela
> for
> alterada, o valor que a funcão retorna deve ser outro.
>
> Mas o que esta acontencedo ?
>
> Quando eu passo o valor para a FBI ela retorna um valor X, se eu
altero a
> coluna da outra
> tabela que devia fazer a FBI retorna Y ao inves de X, ela ainda
retorna X,
> na verdade
> ela retorna Y apenas se, e somente se, eu der uma rebuild no index.
>
> Como solucionar esse problema sem ter que ficar dando rebuild nos
indices ?
>
> Oracle 8i e 9i.
>
>
> [As partes desta mensagem que não continham texto foram removidas]
>
--------------------------------------------------------------------------------------------------------------------------
Atenção! As mensagens deste grupo são de acesso público e de inteira responsabilidade de seus remetentes.
Acesse: http://www.mail-archive.com/oracle_br@yahoogrupos.com.br/
--------------------------------------------------------------------------------------------------------------------------__________________________________________________________________
Este Grupo recebe o apoio da SQL Magazine - www.devmedia.com.br/sqlmagazine
__________________________________________________________________
O grupo Oracle_br não aceita anexos. Quando oferecer algum arquivo, tenha o link do mesmo para evitar trafego(pedidos) desnecessário.
| Yahoo! Grupos, um serviço oferecido por: | |
|
Links do Yahoo! Grupos
- Para visitar o site do seu grupo na web, acesse:
http://br.groups.yahoo.com/group/oracle_br/
- Para sair deste grupo, envie um e-mail para:
[EMAIL PROTECTED]
- O uso que você faz do Yahoo! Grupos está sujeito aos Termos do Serviço do Yahoo!.
