Oracle partition pruning with NLS_COMP = Linguistic

Question

Oracle 10g.

We have a large table partitioned by a varchar2 column (if it were up to me, it wouldn't be this column, but it is) with each partition having a single value. Ex. PARTITION "PARTION1" VALUES ('C').

We also have NLS_COMP = LINGUISTIC.

Partition pruning, when indicating a value in that column, doesn't work.

SELECT * from table1 where column_partitioned_by = 'C'

That does a full table scan on all partitions and not only the relevant one.

According to the docs here, "The NLS_COMP parameter does not affect comparison behavior for partitioned tables."

If I issue:

ALTER SESSION SET NLS_COMP = BINARY

And then:

SELECT * from table1 where column_partitioned_by = 'C'

it does correctly prune the partitions down. (I'm basing the prune/not prune off of the plans generated)

Is there anything, short of hardcoding partition names into the from clause, that would work here?

Additionally, changing the partition definition is out as well. I'm in the minority on my team as even seeing this as a problem. Before I got there, the previous team decided it would "solve" this problem by sending all application sql queries through a string-find-and-replace that adds hardcoded partition names in the FROM clause and has somebody manually update partition names in stored procs as needed...but it will break one day and it will break hard. I'm trying to find the least invasive approach but I'm afraid there may not be one.

Preferably, it would be a solution that only changing queries themselves and not the underlying db structure. Like I said, this solution simply may not exist...

Answer 1

Some solutions to prototype:

The CAST function. You can partition by an expression; the downside is your application would have to provide a similar expression.

Partition on NLS_SORT(column_partitioned_by, 'NLSSORT=BINARY'). Again, application changes required.

Converting column_partitioned_by to a numeric value, possibly using a code table to transform between the two. You'd have to include a join to that table throughout the application, though.