Tom,
There is an index on a table, but that index is not being used by SQL(even with hint).
Can you please tell if something is wrong with the syntax?
Below is the definition of the index on the table
XLA.XLA_TRANSACTION_ENTITIES (PS: I cannot change the definition of the index).
CREATE INDEX XLA.XLA_TRANSACTION_ENTITIES_N1 ON XLA.XLA_TRANSACTION_ENTITIES
(LEDGER_ID, ENTITY_CODE, NVL("SOURCE_ID_INT_1",(-99)), NVL("SOURCE_ID_INT_2",(-99)), NVL("SOURCE_ID_INT_3",(-99)),
NVL("SOURCE_ID_INT_4",(-99)), NVL("SOURCE_ID_CHAR_1",' '), NVL("SOURCE_ID_CHAR_2",' '), NVL("SOURCE_ID_CHAR_3",' '), NVL("SOURCE_ID_CHAR_4",' '))
Below is the SQL:
EXPLAIN PLAN FOR
SELECT /*+ index(XLA.XLA_TRANSACTION_ENTITIES XLA_TRANSACTION_ENTITIES_N1)*/ xte.*
FROM XLA.XLA_TRANSACTION_ENTITIES XTE, UP_PO_ENCUMB_RELIEF_PO2_STG PO
WHERE XTE.APPLICATION_ID = 201
AND XTE.LEDGER_ID = 1
AND XTE.SOURCE_ID_INT_1 = PO.PO_HEADER_ID
AND NVL(XTE."SOURCE_ID_INT_2",(-99)) = -99
AND NVL(XTE."SOURCE_ID_INT_3",(-99)) = -99
AND NVL(XTE."SOURCE_ID_INT_4",(-99)) = -99
AND NVL(XTE."SOURCE_ID_CHAR_1",' ') = ' '
AND NVL(XTE."SOURCE_ID_CHAR_2",' ') = ' '
AND NVL(XTE."SOURCE_ID_CHAR_3",' ') = ' '
AND NVL(XTE."SOURCE_ID_CHAR_4",' ') = ' '
NOTE: Columns SOURCE_ID_INT_2, SOURCE_ID_INT_3, SOURCE_ID_INT_3, source_id_char_1, source_id_char_2, source_id_char_3,source_id_char_4 will always be null for the data that I am interested in.
EXPLAIN PLAN
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122K| 16M| | 10134 (1)| | |
| 1 | HASH JOIN | | 122K| 16M| 2160K| 10134 (1)| | |
| 2 | TABLE ACCESS FULL | UP_PO_ENCUMB_RELIEF_PO2_STG | 122K| 717K| | 151 (0)| | |
| 3 | PARTITION LIST SINGLE| | 1518K| 189M| | 4260 (2)| KEY | KEY |
| 4 | TABLE ACCESS FULL | XLA_TRANSACTION_ENTITIES | 1518K| 189M| | 4260 (2)| 4 | 4 |
-------------------------------------------------------------------------------------------------------------------
Can you tell me what is wrong with my query ?
Thanks.