Reining in that big query

Yesterday i posted about my attempts to tune one of my systems - it ended with me not having a solution and having to go back and rethink a solution.

Well i'm glad to say i came up with one and i'm very pleased with the result - it ends up being quite an elegant solution i think - here is what i did.

First up let me show you some dummy SQL that illustrates the first part of how i solved this

This SQL would return 1 row (ignore the fact it's a view here i just want to illustrate something about SQL constructs here)


select * from dba_tables
where table_name='DUAL' and cluster_name is null and (tablespace_name='SYSTEM' or status='VALID')

You'd never run that in reality but i just wanted to show some SQL that would be valid on any system.

This can be rewritten like this

select * from dba_tables
where (case when table_name='DUAL' and cluster_name is null and (tablespace_name='SYSTEM' or status='VALID') then 'Y' else null end)='Y'

Now this looks very odd - why would you do this - it looks like a pointless overcomplication of the SQL for no apparent benefit....?

Well in my case this rewrite is actually very useful - why you ask?

Well the answer is that then becomes an expression i can index - and in my specific data case there are very few values that would end up being 'Y' so i end up with a tiny index as the alternative to 'Y' is null which doesn't appear in the index.

So for my actual code the SQL now looks like this

 SELECT * FROM schemaname.trade_table
        WHERE ( case when (pos_del_activity_id IS NULL
        AND (trade_voided = 'Y' OR edit_count > 0)) then 'Y' else null end) ='Y';

I can then index this as shown here:

create index schemaname.rich12c_test99 on schemaname.trade_table(case when (pos_del_activity_id IS NULL
        AND (trade_voided = 'Y' OR edit_count > 0)) then 'Y' else null end) online;

And if i now run that statement and get the plan i see

SQL>  SELECT * FROM schemaname.trade_table
        WHERE ( case when (pos_del_activity_id IS NULL
        AND (trade_voided = 'Y' OR edit_count > 0)) then 'Y' else null end) ='Y';  2    3

Execution Plan
----------------------------------------------------------
Plan hash value: 3605007150

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                | 12416 |  1879K|     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TRADE_TABLE    | 12416 |  1879K|     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | RICH12C_TEST99 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(CASE  WHEN ("POS_DEL_ACTIVITY_ID" IS NULL AND ("TRADE_VOIDED"='Y' OR
              "EDIT_COUNT">0)) THEN 'Y' ELSE NULL END ='Y')

The statement returns pretty much instantly as the index is so small.

And of course this works fine in plsql too even when i have modified trade_table as the read consistency of the table and index is handled for me unlike the mview solution.

Quite a neat solution i think.........