Tudo jóia ? Vamos começar pela sua dúvida, que eu entendo ser Conceitual : para vc poder entender o que acontece no RDBMS Oracle, saiba que fisicamente os dados ficam armazenados em blocos nos discos, esses blocos são agrupados em pedaços contínuos (chamados de EXTENTs) , e o conjunto de extents é logicamente agrupado no SEGMENTO - podemos ter vários tipos de segmentos, como segmentos temporários, segmentos de índices, segmentos de dados, segmentos de LOBs, etc... Quando o RDBMS precisa ler os dados todos da tabela toda (para criar um índice, ou para atender a uma consulta aonde a filtragem não é feita por uma coluna indexada, ou coisas assim), o que ele faz é consultar as views e tabelas internas (visíveis para nós através das views de sistema como DBA_EXTENTs, DBA_OBJECTS, DBA_SEGMENTS, etc) para descobrir qual o segmento físico relacionado com a tabela desejada, quais extents compõem esse segmento, e vai pedir pro SO ler esses extents um a um, fazendo I/Os multibloco de acordo com o tamanho de cada extent E, se config do database e o Sistema Operacional e o hardware permitirem, essas leituras serão feitas em modo asíncrono e em modo direto, bypassando os caches do sistema operacional, que são inúteis nesse caso pois sabemos que queremos ler Toda a informação.... Isso posto, a sua resposta : não é "toda a tabela" que é lida, mas sim "todo o segmento" que corresponde a ela : no caso de LONGs e LONG RAW, isso certamente deve estar, sim, como vc supunha, influenciando DIRETAMENTE o teu cenário, pois (vide a Documentação Oracle) nós NÃO TEMOS um segmento que contém apenas os dados long/long raw, eles OBRIGATORIAMENTE vão estar "misturados" no segmento de dados junto com o resto das colunas... Isso é uma característica FÍSICA e INESCAPÁVEL, e foi por isso (entre muitas outras issues) que há muitos anos a Oracle DESUPORTOU os LONGs e introduziu os LOBs (CLOB e BLOB), os quais (eles sim) são fisicamente armazenados em LOB SEGMENTs, *** SEPARADOS *** do DATA SEGMENT de uma tabela : com isso não tem o que fazer, já que Qualquer bloco de qualquer extent do data segment pode ou não conter dados das outras coluna OU do LONG RAW, é verdade que o RDBMS vai ter que ler TODOS eles, o data segment INTEIRINHO.... Isso não vai ser eficiente, já que com certeza muitos desses blocos vão conter só informações de LONGs (tipicamente esses caras consomem mais de um bloco para cada registro) mas como, por Definição de arquitetura, LONGs ficam misturados com dados escalares, vc não tem como evitar esse I/O potencialmente inútil.... Veja que vc TEM comandos/opções para CONVERTER os seus LONGs em LOBs, mas antes de sequer pensar em fazer isso, vc NECESSARIAMENTE tem que obter Aprovação do fornecedor da Aplicação, que vai dizer se a aplicação dele Funciona/Aceita LOBs ou não... Isso te leva DIRETAMENTE a necessidade de atuar com o fornecedor, aí por mais que "... o modelo e a aplicação são de responsabilidade de outra empresa/fornecedor..." , com a qual nunca teve contato, vc VAI TER que levantar isso , alguém aí deve ter esse contato... Obtido isso, vc VAI TER QUE : a. notificar a Empresa que os LONGs NÂO SÃO MAIS Suportados/Recomendados pela Oracle - consulta o metalink que vc acha farto material a respeito b. por causa do acima, vc precisa de Confirmação se a Aplicação suporta LOBs, se não suportar essa alteração deverá ser Demandada ==> Nós sabemos muito bem que em 99,999% dos casos depois que vendeu a Aplicação a Empresa fornecedora não está nem aí para os Clientes dela, são Enormes as chances do teu contato com eles ser totalmente infrutífero, mas faz parte da tua Obrigação enquanto responsável pelo banco de dados mostrar (INCLUSIVE pros teus Superiores) que vc CONHECE o problema, que ele é EXTERNO ao database E QUE demandou uma solução do culpado.... Se vc não obtiver a Solução, ao menos vc tem como Demonstrar que a sua parte vc fez... Falando sobre as suas tentativas de acelerar o processo e outras ações possíveis : a. coisas como CACHE são inócuas, essa diretiva instrui o RDBMS pra deixar os blocos APÓS serem lidos no fim do queue de cache, pra este caso de leitura completa uma vez só (que é o que o CREATE INDEX precisa) não esperaria muito resultado disso não b. para que uma leitura full de segmento grande termine o mais rápido possível, entre outras coisas vc necessita que cada I/O traga o máximo possível de blocos (dentro do limite máximo de I/O do SO, normalmente de 1 MB no Linux), E QUE o SO não fique esperando um I/O terminar totalmente antes de atender o próximo : isso implica em I/Os multiblock e na capacidade máxima do teu ambiente.... VERIFIQUE, junto com teus sysadmins, que no SO isso está habilitado (por exemplo, se houver filesystem na parada, CONFIRME que entre as opções de mount dele estão a de Direct I/O e ASYNC I/O, isso varia de acordo com o FS em questão), e Verifique que se necessário no RDBMS isso está habilitado (parâmetro filesystemio_options).... É crítico também, para I/O multiblock o mais eficiente possível, que a o tamanho da maioria dos extents do segmento sendo lido em full seja múltiplo desse limite - dá uma verificada na tua DBA_EXTENTS e se isso não estiver acontecendo, pra uma possível próxima janela de manutenção pensa na possibilidade de mover/recriar os dados pra uma tablespace com extents size apropriado, ou com AUTOALLOCATE... Por agora, além de se assegurar dos itens anteriores, tenta um ALTER SESSION SET db_file_multiblock_read_count=nn; onde nn é uma quantidade de blocos que chega no limite máximo de I/O do seu ambiente - normalmente 128 , para tablespaces com blocos de 8K e ambientes com 1 MB de I/O máximo.
c. não tenho certeza se o Standard Edition permite DDL (como CREATE INDEX) em paralelo (felizmente até hoje não tive que mexer muito com essas Edições mais capadas/restritas do RDBMS), mas se permitir : -> o DOP (degree of parallelism, Grau de Paralelismo) a ser aplicado depende TOTALMENTE da capacidade de CPU e da banda de I/O que vc tem, a qual não sabemos : assim, não sabemos se esse 6 que vc está indicando é ótimo ou não.. Eu sugiro que vc comece testando com algo menor , tipo 2 ou 3, e depois tente aumentar -> não sei se ele exige, mas não machuca ninguém vc setar o DOP também na tabela a ser indexada, via ALTER TABLE nomedatabela parallel (degree nn); , além de indicar nn para o CREATE INDEX que já estará em NOLOGGING também... Deixar a tabela em NOLOG temporariamente, durante a criação do índice, pode ajudar também... -> paralelismo via de regra IMPLICA em leitura física, a partir dos discos, e em modo direto : cfrme https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1027523970333 , sempre é interessante se pedir um CHECKPOINT GLOBAL para que eventuais dados comitados presentes no UNDO mas não repassados aos datafiles o sejam d. nem preciso dizer se agendar essa criação para um momento em que menos pessoas estejam usando o sistema/acessando a tabela a indexar é de lei - isso entre outras evita concorrência de I/O e eventuais acessos ao UNDO para se obter leitura consistente.... e. para criar um índice além de um montão de I/O o RDBMS ** vai ter ** que fazer muita Ordenação, e VAI usar portanto hash area/sort area, temp area, memória... Já que (cfrme Acima) vc vai estar num momento com MENOS utilização geral, vc VAI indicar pro RDBMS que a sessão fazendo o CREATE INDEX deve obter mais recursos que o normal : tenha uma temp tablespace com amplo espaço livre, criada nos discos mais velozes possíveis, indique ( via ALTER SESSION set workarea_size_policy=manual; alter session set sort_area_size=nnnn; alter session set sort_area_size=nnnnnnnnn;) que vc quer uma alocação diferenciada... ==> Já sobre a questão do tempo em si : por mais que o hardware seja fraco (E, por falar nisso, vc não diz uma palavra sobre o teu hardware, e pra poder palpitar adequadamente nós Precisaríamos saber a tua capacidade de CPU - total e usada -, a sua RAM - total e usada -, o teu hardware de I/O - com detalhes tais como se o Storage em uso, se é JBOD ou RAID, qual nível de RAID, se são volumes RAW ou filesystem, que TIPO de filesystem tá em uso se for o caso, se tem ASM na parada ou não, a config do ASM, etc) , num ambiente Profissional com hardware server-class ainda que fraco penso vc deveria ter PELO MENOS (falando aqui de figuras MÍNIMAS, situações em que o hardware tá 'fritando' por causa de montes de acessos simultâneos) umas dezenas de GB por hora, eu acho... A V$SESSION_LONGOPS tem outras colunas pra vc mensurar performance (como OPNAME, TARGET/TARGET_DESC, TOTALWORK e SOFAR), eu diria pra (DEPOIS de todos os ajustes e verificações indicados) que vc deixe o comando de CREATE rodando por umas horas e veja qual taxa de progresso vc obtém... []s Chiappa
