Abaixo, as tabelas relacionadas e a quantidade de linhas de cada uma:
SQL> SELECT table_name, num_rows, partitioned, blocks, avg_row_len, compression
2 FROM all_tables
3 WHERE table_name IN (
4 'GL_JE_HEADERS'
5 , 'GL_JE_BATCHES'
6 , 'GL_LEDGERS'
7 , 'GL_JE_LINES'
8 , 'GL_LOOKUPS'
9 , 'GL_CODE_COMBINATIONS'
10 , 'GL_IMPORT_REFERENCES'
11 )
12 /
TABLE_NAME NUM_ROWS PARTITIONED BLOCKS AVG_ROW_LEN COMPRESSION
------------------------------ ---------- ----------- ---------- ----------- -----------
GL_JE_BATCHES 1881900 NO 67374 248 DISABLED
GL_JE_HEADERS 4358320 NO 203131 293 DISABLED
GL_CODE_COMBINATIONS 9087650 NO 223193 114 DISABLED
GL_IMPORT_REFERENCES 1044946030 NO 30487669 203 DISABLED
GL_JE_LINES 1132691380 YES 22311097 140
GL_LEDGERS 206 NO 13 299 DISABLED
6 rows selected
SQL>
Uma delas, tem Partition pelo campo PERIOD_NAME. (ou seja, cada MÊS está em uma partição).
Minha query, é assim:
SELECT gled.name ledger_name
, gjb.name batch_name
, gjh.name journal_name
, gjh.doc_sequence_value document_number
, gjc.user_je_category_name
, gjh.default_effective_date accouting_date
, gl.meaning status
, gjh.currency_code
, gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' || gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' || gcc.segment7 || '.' || gcc.segment8 aff
, gjl.entered_dr gl_dr_entered
, gjl.entered_cr gl_cr_entered
, gjl.accounted_dr gl_dr_accounted
, gjl.accounted_cr gl_cr_accounted
, gir.reference_6
, gir.reference_2
, gjl.entered_dr
, gjl.entered_cr
FROM gl_je_headers gjh
, gl_je_batches gjb
, gl_je_sources_vl gjs
, gl_je_categories_vl gjc
, gl_ledgers gled
, gl_je_lines gjl
, gl_lookups gl
, gl_code_combinations gcc
, gl_import_references gir
WHERE gjh.period_name = &P_PERIOD
AND gjs.je_source_name = gjh.je_source
AND gjc.je_category_name = gjh.je_category
AND gled.ledger_id = gjh.ledger_id
AND gjl.je_header_id = gjh.je_header_id
AND gjb.je_batch_id = gjh.je_batch_id
AND gl.lookup_type = 'BATCH_STATUS'
AND gl.lookup_code = gjh.status
AND gcc.code_combination_id = gjl.code_combination_id
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gir.reference_3 = 'GLJE'
O plano de execução está da seguinte forma: (repare que todos índices estão sendo usados perfeitamente).
A única coisa que eu observo são os Nested Loops (que são necessários), mas as tabelas são muito grandes.
Alguém já passou por uma situação assim? Na minha opinião, o plano de execução está bom...
SELECT STATEMENT
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES
INDEX RANGE SCAN FND_LOOKUP_VALUES_U1
TABLE ACCESS BY INDEX ROWID GL_JE_HEADERS
INDEX RANGE SCAN GL_JE_HEADERS_N2
TABLE ACCESS BY GLOBAL INDEX ROWID GL_JE_LINES
INDEX RANGE SCAN GL_JE_LINES_U1
TABLE ACCESS BY INDEX ROWID GL_IMPORT_REFERENCES
INDEX RANGE SCAN GL_IMPORT_REFERENCES_N1
INDEX UNIQUE SCAN GL_JE_SOURCES_TL_U1
TABLE ACCESS BY INDEX ROWID GL_LEDGERS
INDEX UNIQUE SCAN GL_LEDGERS_U2
TABLE ACCESS BY INDEX ROWID GL_JE_CATEGORIES_TL
INDEX UNIQUE SCAN GL_JE_CATEGORIES_TL_U1
TABLE ACCESS BY INDEX ROWID GL_JE_BATCHES
INDEX UNIQUE SCAN GL_JE_BATCHES_U1
INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1
TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS