• Why won't optimizer use indexes?

    From Jonathan Ball@21:1/5 to All on Sat Jan 19 15:40:59 2019
    I need to put phony SSN values into a copy of the business database as part
    of a personal information obfuscation requirement. I have numerous tables
    that have SSN columns, and a cross reference table that has old and new SSN values in a one-to-one correspondence. Call a business application table BUS_TBL, and the cross reference SSNXREF.

    Here's my update statement:

    update bus_tbl b
    set bus_ssn_col =
    (select new_ssn
    from ssnxref
    where old_ssn = b.bus_ssn_col)
    where exists
    (select *
    from ssnxref
    where old_ssn = b.bus_ssn_col)

    I created indexes over SSNXREF on both SSN_OLD and SSN_NEW, and an index
    over BUS_TBL on BUS_SSN_COL. The optimizer doesn't use any of the indexes, despite the fact there are hundreds of millions of rows in the business
    table, and 10 million rows in the cross reference.

    Why wouldn't it?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)